How to store Excel macros for future use

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…

Excel files contain data and vba code

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.
Below you will explore how to make your recorded and written macros available to the same workbook or to any other workbook (local or central)…

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…

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.

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…

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

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

Show hidden files

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

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

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

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.

You have great macros that can’t use on other workbooks? Share your experience with us, leave your thoughts below…