Store Your VBA Macro
A lot of times you may be getting confused to decide where you need to store your Excel Macro in when you were recording an Excel Macro.
To store Macro, you get these 3 options:
- Personal Macro Workbook
- New Workbook
- This Workbook
You will see a prompt will present itself asking where the macro should be stored while you will initially setup the recording of a macro.
This option itself determines you not only where to save the macro, but also from where you need to access the macro and where it can be used after saving.
The “THIS WORKBOOK” option will create a module where Excel can save the macro (VBA Code) because the module will be created within the active workbook.
You can use this macro within that workbook or if you have opened that workbook because it is stored in the active workbook.
If you will send this workbook to another Excel user or place it on a network drive, the macro will go with that workbook as well because you have stored the macro with the workbook it was created within, and when other users will open that workbook in their system they can use that macro too.
You must save the files as a .XLSM (MACRO ENABLED WORKBOOK) or .XLSB file that contains a macro or VBA code.
Some Common Uses:
- These Macros will be specific to workbook data.
- The Macros that will make data connections and refreshes the data contained with the workbook.
- It will also clean up data specific to the workbook.
Personal Macro Workbook
The PERSONAL MACRO WORKBOOK is located on a system which contains Microsoft Excel. It is a hidden workbook by default from the view, but always open.
If you save you macro in the PERSONAL MACRO WORKBOOK, then you can use this macro within any workbook on the system which contains this personal macro workbook.
If you want to share the Macros which is stored in a personal macro workbook then typically it is not possible to share them through normal exchange of Excel files. So, to share a Macro stored in the personal macro workbook; you will need to explicitly share your personal macro workbook by exporting the module which contains that macro or needs to copy the code to another file and send that new file to another person.
Some Common Uses:
- This is the One-time setup macros that later you can use them on multiple workbooks.
- These Macros are more specific to an individual job which you can hide from others if you don’t want them to share with others.
- It can be used in Repetitive tasks which is necessary across multiple Excel files.
A NEW WORKBOOK will create a new workbook at the time of the recording. This will contain all the macro and VBA code.
If you need to use these macros you should open the new workbook that Excel has created.
This New Workbook is not as common as the other two workbooks.