How to store the Excel macros you create or record for future use

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…

Excel files contain data and vba code

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.
Below you will explore how to make the macros you create in Excel (or record) available to the same workbook or to any other workbook (local or central)…

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…

Store macro in: this workbook, personal workbook

Record macro dialog

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…

All the macros in the current workbook

Macro dialog

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…

See all the macros that are in the personal workbook

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…

The personal macro workbook is always loaded

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)

Save the changes you made to the macros of the personal macro workbook

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…

Show hidden files

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)…

Excel macros are stored this way in a workbook

This is how your Excel macros are stored inside the VBA editor…

Excel macros are stored in modules and procedures

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…

Choose what macro to show in "macros-in" list, thisworkboo, all open workbooks, personal workbook, specific workbook

Make your written Excel macros available everywhere (central)

If you want to run a macro from any other workbook, see the picture below…

Assign macros to ribbon commands

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

Load any Excel addin or browse it

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.