Tag: VBA

[Latest update: November 15, 2024]

Sometimes I blog things mostly so I can remember them and in the off chance that they might be useful to others. This post falls into that category. It has long been the most popular post on the site. I’m glad people find it helpful. In service to them, I try to keep it up to date. This latest update adds information for Windows 11 and macOS Sonoma and Sequoia.

If you use Microsoft Excel, and you have a macro that you want to be available globally–in any open workbook–you can place it in your Personal Macro Workbook, which is just an Excel Workbook (in binary, XLSB, format, for speed) that lives at a particular location, where Excel will look for it whenever it launches. Where it lives, though, is a moving target.

Windows Versions

On my install of Windows 11, running Microsoft Excel for Microsoft 365 MSO (Version 2406) 64-bit, as part of Microsoft 365 Apps for Enterprise ¹ (under Parallels Desktop), it lives here (substitute your username, unless it happens to be identical to mine):

C:\Users\jamesmartin\AppData\Roaming\Microsoft\Excel\XLSTART

On Windows 10, running Excel 2016, it lives at the path below. As before, substitute your username. In this example, “wheatbread” was my username. It’s probably not also your username, unless we both have a particularly weird nickname. If there’s no Roaming directory in your setup, use Local instead. The rest of the path will remain the same:

C:\Users\wheatbread\Roaming\Microsoft\Excel\XLSTART\PERSONAL.XLSB

On Windows 7, it lives here. As in the Windows 10 example, if there’s no Roaming directory in your setup, use Local instead. The rest of the path will remain the same:

C:\Users\AppData\Roaming\Microsoft\Excel\XLSTART\PERSONAL.XLSB

On Windows XP, it lives here:

C:\Documents and Settings\Application Data\Microsoft\Excel\XLSTART\PERSONAL.XLSB

In any case, the easy way to create PERSONAL.XLSB is not to muck around with the file system directly. Instead, just record a macro in Excel and, when you’re prompted to save it, choose Personal Macro Workbook from the Store Macro In drop-down menu on the Record Macro dialog box. Record yourself typing a few numbers and adding some formatting to them or something similar. Afterwards, you can open up the Personal Macro Workbook via the Visual Basic button on the Developer tab and delete whatever you recorded. Once you create it, your Personal Macro Workbook will be listed in the VBA Editor as “VBAProject (PERSONAL.XLSB)”.

Mac Versions

Excel Personal Macro Workbook paths on macOS versions of Excel are truly strange. Here’s mine for Microsoft Excel for Mac (Version 16.91) as part of Microsoft 365 Subscription. The path is the same on macOS Sonoma (14.6.1) and Sequoia (15.1):

/Users/jamesmartin/Library/Group Containers/UBF8T346G9.Office/User Content.localized/Startup.localized/Excel

Finding the Path

The best way to find the path–hat tip to commenter Kevin Woodward–is to use the Visual Basic Editor (VBE). These days, both the Mac and Windows versions of Excel support VBA and have basically the same VBE. The only difference is the shortcut keys you use to interact with it.

On Windows:

1. Press alt+F11 to open the VBE.
   • Alternately, click Visual Basic on the Developer tab (if visible).
2. If the immediate window is not visible, press ctrl+g to make it so.
   • Alternately, choose View –> Immediate Window from the menus.
3. In the immediate window, type ?Application.StartupPath
4. Press enter/return

On a macOS:

1. Press fn+option+F11 to open the VBE.
   • Alternately, click Visual Basic on the Developer tab (if visible).
2. If the immediate window is not visible, press ctrl+command+g to make it so.
   • Alternately, choose View –> Immediate Window from the menus.
3. In the immediate window, type ?Application.StartupPath
4. Press enter/return

The path to the XLSTART folder will appear, even if it is hidden in Windows.

Turning on the Developer Tab

An easier way to get into the VBE is via the Visual Basic button in the Code group on the Developer tab of the Ribbon. However, this tab is hidden by default. Here’s how you turn it on.

Microsoft 360 Apps for Enterprise on Windows 11 & Excel 360 on Windows 10:

1. Choose File –> Options.
2. In Excel Options, in the left pane, choose Customize Ribbon.
3. In the right pane, check the box to the left of Developer in the Main Tabs section.
4. Press the OK button.

Microsoft Excel for Mac on macOS Sanoma (14.6.1) as well as macOS Monterey:

1. Choose Excel –> Preferences.
2. In Excel Preferences, in the Authoring section, choose Ribbon & Toolbar.
3. In the right pane, check the box to the left of Developer in the Main Tabs section.

Note for Mac Users

If you are using a MacBook Pro with a Touch Bar, getting the function keys to be visible and useful can be tricky. This support article from Apple helps a bit. But it’s not perfect. The trick, for me, once I had–in System Preferences –> Keyboard –> Shortcuts–specified Excel, per the article, as an app for which the function keys should appear, was to quickly and simultaneously press fn+option+F11 to open the VBE. You can’t hold fn down and then click the other two keys. Nor can your hold fn+option down and then click F11. Doing either will change the what is displayed in the Touch Bar, hiding the function keys.

Some Additional Paths, for the Truly Nerdy

The location of the Personal Macro Workbook is a little confusing. Different versions of Excel store it in different places. Roaming profiles also complicate matters. For these reasons, using the VBE, as noted above, to determine the path is the best approach. Still, for whatever historical value it might have, here are some additional paths I’ve noticed over the years.

For Excel Microsoft 360 Apps for Enterprise:

C:\Users\USERNAME\AppData\Roaming\Microsoft\Excel\XLSTART

For Excel 2010

C:\Program Files\Microsoft Office\Office14\XLSTART\

For Excel 2007:

C:\Program Files\Microsoft Office\Office12\XLSTART\

“Office 14” is the internal name for Office 2010. It’s actually the 13th version of Office, but Microsoft saw fit to skip naming it Office 13, due, one suspects to superstition. So “Office 12” is Office 2007, “Office 11” is Office 2003, and so on. If you happen to be stuck running Excel 2003 on a Windows XP box, you’ll find PERSONAL.XLSB at this path:

C:\Program Files\Microsoft Office\Office11\XLSTART\