The Trick to Universally Accessible Macros

Process Automation

May 1

Whether you’ve dabbled with macros a little already or you’re just beginning to learn how to utilize this automation tool, creating macros that are available every time you open Excel (not just in a single Workbook) is a useful thing to know.

When I first dove in to the world of macro-based automation, this was one of my first obstacles. At the time, we were manually separating a file into different files based on a certain cell value on a daily basis. After a few days of research and testing, I’d finally found a code that worked. However, I needed to scale my macro so that it could apply to each new daily file.

To save you the trouble of going through everything I had to, I’m going to share with you how to save universal macros by utilizing a hidden personal workbook.

Step 1: Create a Workbook (or if you already have a workbook with your macros in it, use that)

Step 2: Save the workbook in the startup folder of Excel
For me, that path is C:\Users\MyUser\AppData\Roaming\Microsoft\Excel\XLSTART
If you’re using a PC, you should be able to get your path by typing the following into the immediate window in Excel’s Visual Basic Editor (Developer Tab > Visual Basic ) See below:

Using the ?Application.StartupPath query returns the startup path for Excel.
You’ll then save your workbook in this path as Personal.xlsm (this is a macro-enabled workbook, which is necessary for macros to function).

For Macs: I recently had to add a Personal macro workbook to a Mac, and it wasn’t fun. I tried to use this query, but the path it provided was not where I needed to save my Personal workbook in order for it to show up. To save you the pain and suffering I had to go through, here’s the result of my research:
This is the path where you’ll need to save your file: ~/Library/Containers/com.microsoft.Excel/Data/Library/Application Support/User/Personal Macro Workbook
Disclaimer: Now, another problem we ran into when trying to work with macros on a Mac – you cannot import/export modules (this may be different for other versions of Excel – we are using 2016). As a work around, you can open modules into a text document and create a new module and paste the code in it, but it’s pretty inconvenient. If you want to get heavily involved in macro-based automation, I definitely recommend a PC.

Step 3: Open Excel
When you open Excel now, your Personal workbook should open. Assuming you don’t want to see this workbook every time you open Excel (which is usually the case), go to View > Hide > Personal.

Step 4: Open your Visual Basic Editor and see your macros!

If you have the project explorer enabled, you should be able to see your new personal workbook and any macros or userforms you have associated with it here.

Bonus tip!
You can rename your personal workbook in the Properties window. This may decrease confusion as you continue to scale your projects. See below!