If you acquire a brand new car and ignore the location of the keys, you couldn’t open the doors to get in, nor drive the car. In a sense, when you don’t know the location of your recorded Excel macros or written Excel VBA macros you won’t be able to get in the VBA editor to edit the Excel macro or run the macro.
Knowing the location of your Excel VBA macros is essential if you want to create, edit and run your Excel macros!
If you ask some of the questions below, then 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 Excel macros from others?
- How to use a macro I wrote or recorded before?
- How to make my macros available on other Excel VBA workbooks?
- How to create a macro in Excel?
- How to write a macro in Excel?
It’s very simple!
You just need to be aware of some details I will share with you here…
You can think on a workbook as a box with two compartments, one for spreadsheet-data and the other one for VBA-data. In other words, an Excel VBA workbook might contain spreadsheet-data and VBA-data, see the illustration below…
Now, you might ask, “if an Excel macro is stored inside a VBA 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 Excel macros vs. central Excel macros
Let’s suppose you create a macro in Excel VBA, which is a loan calculator app and 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 Excel macro generates the monthly payments in the same sheet.
You don’t want 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:
- Fixed-layout constrained macros. For example, a macro that works upon a fixed table scheme or report
- Sheet-specific macros. For example, a macro that uses the data of a particular sheet or sheets
- One-time macros
- Etc.
Now think for a moment in a macro you created in Excel 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 to 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:
- Dynamic macros that work on different sheet and arrays and layouts. E.g. a macro to format a report, write custom formulas on columns with dynamic rows, etc. You can create dynamic macros if you use loops and conditional statements. You can also record dynamic macros if you learn how to place the cursor and use relative references
- Repetition routines like: converting date format, inserting a given formula in the current cell, etc.
Location of recorded Excel macros
If you know how to record a macro in Excel, then you know that when you hit stop, your recorded macro is somewhere in your hard drive and 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.
Many Excel users think that once they learn the art and science of writing macros in Excel, the Excel recorder is not needed anymore, but knowing how to record a macro in Excel is critical in some cases. Here are the guidelines for taking full advantage 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 the Personal Macro Workbook?
It’s a hidden Excel 2007 Binary File workbook (.xlsb) that is always open so you can run its macros all the time. 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:/Users/Administrator/AppData/Roaming/Microsoft/Excel/XLSTART
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 create
You are free to put the Excel macros you create or write using VBA anywhere inside your Excel VBA compartment (VBA editor). You can store your Excel macros in any module and in any Sub procedure. For example: you can create a module called MyFormulas and create 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 the created 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 can run a macro in Excel in several ways…
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
- Shortcut
- Button
- Toolbar or Ribbon
- Triggered by event
- UDF (User defined function)
I hope this article helps.
