If you acquire a brand new car and ignore the location of its key, you can’t do anything with it, neither open the doors to get in, nor drive it. In a sense, when you don’t know the location of your recorded or written Excel macros you cannot do anything with them, neither edit nor run them.
Knowing the location of your Excel VBA macros is essential!
If you ask some of the questions below, this Excel VBA article is for you…
- Will my macro be there if I close my workbook or Excel?
- How can I share my Excel macros with others?
- How to use the macros of others?
- How to use a macro I wrote or recorded before?
- How to make my macros available on other workbooks?
It’s very simple!
You just need to be aware of some details I will share with you here…
You can think in a workbook as a box with two compartments, one for spreadsheet-data and the other one for VBA-data; in other words, a workbook might contain spreadsheet-data and VBA-data, see the illustration below…
Maybe you are now asking: if a macro is stored inside a workbook, will it be available to other workbooks?
That’s a good question.
The answer is NO, a macro is only available to the same workbook where it was created, but you can make it available to other workbooks, you need some tricks you will learn in just a moment.
First let’s explore the difference between…
Local vs. central Excel macros
Let’s suppose you developed a loan calculator app in Excel, it works this way: first you enter the arguments like loan amount, annual interest rate, loan period in years, etc, then you hit calculate, finally the macro generates the monthly payments in the same sheet.
You don’t need to run this macro on other workbooks because the relevant data is entered in the given loan sheet. This is a local macro.
Some examples of local macros might be:
- Layout constrained macros
- One-time macros, etc.
Now think for a moment in a macro you created to format a raw sales table you receive every week, the macro deletes some columns, sorts the table by invoice number, adds some headings here and there, etc. Every time it does the same on the file you receive weekly.
You want to use this macro each time you receive new data. This macro is required to be centrally stored so is available on each upcoming sales workbook.
Some examples of central macros might be:
- Relative cell editing
- Layout creation
- Corporate-reports formatting
- Custom formulas
- Repetition routines like: converting date format, inserting a given formula, etc.
Location of recorded Excel macros
When you hit stop, your recorded macro is somewhere in your PC; this location depends on what you specified in the “Record Macro” dialog, see the picture below…
Important notice: by default, your recorded Excel macros are named Macro1, Macro2, MacroN and stored in Module1, Module2, ModuleN of the specified workbook.
Here are the guidelines for optimal future use of your recorded Excel macros…
Make your recorded macros available to the same workbook only (local)
1) Choose “This Workbook” or “New Workbook” in the “Store macro in:” drop down list of your “Record Macro” dialog (see “Record macro dialog” picture above).
2) Then record your Excel macro as normal.
Run that macro by specifying the source workbook where you recorded the macro (it should be open). See the picture below…
Make your recorded macros available to any workbook (central)
1) Choose “Personal Macro Workbook” in the “Store macro in:” drop down list of your “Record Macro” dialog.
2) Then record your Excel VBA macro as normal.
You don’t need to open the source file to run your central macros because the “Personal Macro Workbook” is always loaded. See the picture below…
What’s Personal Macro Workbook?
It’s a hidden binary-format-workbook that is always open so you can run its macros all the time, it’s an Excel 2007 Binary File Format (.xlsb). See the picture below…
If you edit any macro of the “Personal Macro Workbook” and close Excel, you are presented with the message below (This is a handy proof this book is open behind the scenes)
The “Personal Macro Workbook” is stored by default in this location: C:UsersAdministratorAppDataRoamingMicrosoftExcelXLSTART
Important notice: show system folders this way (system folders are hidden by default). In the Windows explorer window, go to Organize>Folder and search options>View>Hidden files and folders>Show hidden files, folders, and drivers. See the picture below…
Location of the Excel macros you write
When you write your own macros, you are free to put them anywhere inside your Excel VBA compartment (VBA editor). You can store your macros in any module and in any Sub procedure. For example: you can create a module called MyFormulas and write the macros (procedures) MyAdvancedFormula1, MyAdvancedFormula2, MyFinancialFormula1, etc. Or you can create a module called MyCommonRoutines and write the macros: FormatMyReport, DeleteMyColumns, etc.
Here’s an illustration of the location of your macros inside the VBA editor (compartment)…
This is how your Excel macros are stored inside the VBA editor…
You can also put your macros in the “ThisWorkbook” VBA module or in any VBA Sheet module (under the folder Microsoft Excel Objects) of the VBA Project explorer pane.
Important notice: Sub procedures must be Public in scope so you can call them from outside the source module, e.g. from another module, from a button, shortcut, toolbar, ribbon, etc.
Make your written Excel macros available to the same workbook only (local)
Just write your macros in any module of your VBA editor (workbook compartment). It will be available next time when you open that workbook.
Your local macros will be listed in the Macro dialog, after you select “This workbook”, “All Open Workbooks” or any specific workbook in the “Macros in:” drop-down list. see the picture below…
Make your written Excel macros available everywhere (central)
If you want to run a macro from any other workbook, see the picture below…
You have several options to do it…
1) Write your macro in the Personal Macro Workbook, it will be available next time on any workbook you open.
This method is not recommendable because you can’t control the location of the master macros book and you can’t organize your macros in meaningful files., e.g. a workbook for formatting macros, another one for calculations, etc.
The best way to do it is by storing your workbook as an add-in. Here’s how to do it…
1) Write your macro in any given VBA module of a new or existing workbook
2) Save your book as an Excel Add-in, go to Office button/File>Save As>Save as type:>set the file type as add-in, see below…
- .xlam – Excel Add-in workbook format in Excel 2007+
- .xla – Microsoft Excel Add-In format in Excel 97-2003
3) Go to Office button/File>Options>Add-Ins>Manage:>Excel Add-ins>Go
4) Then in the Add-ins dialog activate the desired add-in file, if is not listed here, choose “Browse…” and locate your add-in file
Now you can call your macros using any of the methods below…
- Macro dialog
- Toolbar or Ribbon
- Triggered by event
- UDF (User defined function)
I hope this article helps.
You have great macros that can’t use on other workbooks? Share your experience with us, leave your thoughts below…