Macro Toolbox
When storing utility macros, Excel users often elect to save them in the Personal Macro Workbook. The advantage is that the macros are available whenever Excel is running. The disadvantage is that if you switch or upgrade computers, you may forget to take a backup of the Personal Macro Workbook and thus lose your macros. In this post, we’ll talk about an alternative to the Personal Macro Workbook, which I like to call the Macro Toolbox.
Objective
When using the macro recorder to create a new macro, you are given an option to save the new macro in the Personal Macro Workbook. The Personal Macro Workbook is a hidden workbook that opens when Excel opens. Any macros saved there are available to run as needed.
The Personal Macro Workbook is typically stored on the same drive as Windows and Microsoft Office, probably your C drive. When you do some type of upgrade, such as upgrading to a new computer, new operating system, or new version of Office, sometimes the Personal Macro Workbook, and all of your hard work, is left behind.
It would be great if we could kind-of simulate the features of the Personal Macro Workbook, but, save it wherever we want. We could choose to save our macro workbook on a network drive that is backed up every night. And when we switch computers, or upgrade, we would still have access to our macro workbook because it is still out on the shared drive.
Our objective then is to simulate the functionality of the Personal Macro Workbook, but, save it to a location of our choosing.
Details
The Personal Macro Workbook is hidden and is opened when Excel is opened. So, our Macro Toolbox should exhibit these same properties. We’ll proceed using the following steps.
- Create the Macro Toolbox
- Save it
- Hide it
- Open it whenever Excel opens
Create the Macro Toolbox
First up, the Macro Toolbox isn’t some type of built-in Excel thing. It is just a standard workbook. I use the name Macro Toolbox because it suggests a collection of macros. You can name yours anything else you like, it is really just a matter of personal preference.
Let’s begin by creating a new blank workbook that will eventually become our Macro Toolbox. Next, we’ll put a macro in it, and there are a couple ways you could do that. For example, you could paste existing macros from your Personal Macro Workbook, or just record a new macro. For completeness, we’ll do both, but, keep in mind your goal is just to get a macro into the new workbook, however you’d like.
What we’ll do is record a macro to the Personal Macro Workbook, and then copy it into the new blank workbook. We begin by using the following Ribbon command to start the macro recorder:
- View > Macros > Record Macro
In the resulting Record Macro dialog, we give the macro a name, such as BoldCenterUnderline and choose to store it in the Personal Macro Workbook, as shown below.
Click OK to start the recorder.
Now, without doing anything else or clicking anywhere else, format the active cell with Bold, Underline, and Center using either the Ribbon icons, keyboard shortcuts, or Format Cells dialog box.
Once formatted, immediately stop the recorder (without clicking anywhere else) by using the following Ribbon command:
- View > Macros > Stop Recording
At this point, you have a new macro in your Personal Macro Workbook.
Next, we need to move it into the new blank workbook. To do this, we have to first unhide the Personal Macro Workbook so we can see it and its macros.
We use the following Ribbon icon to show the Personal Macro Workbook:
- View > Unhide
In the resulting Unhide dialog box, we select PERSONAL.XLSB as shown below.
After clicking OK, we can now see and access the Personal Macro Workbook (PERSONAL.XLSB) and its macros.
We now want to view the macro by clicking the following Ribbon command:
- View > Macros > View Macros
This opens the Macro dialog box. We select our newly recorded macro, named BoldCenterUnderline, and click the Edit button, as shown below.
This opens the Visual Basic Editor, which is a great utility for managing, creating, editing, and deleting macros.
At this point, the Visual Basic Editor should have opened directly to the selected macro, in our case, the BoldCenterUnderline macro. We now need to move or copy it into our new blank workbook. To do so, we select the macro text, just like using a word processor. This is shown below.
Then we do a standard Copy (or Cut), using the Edit menu or Ctrl+C (or Ctrl+X).
Then we navigate to the destination, which is of course the new blank workbook. You’ll notice in the Project Explorer, the pane on the left of the screenshot above, that there is one VBA Project for each open workbook. In our case, we have Book1 (our new blank workbook) and PERSONAL.XLSB (our Personal Macro Workbook).
Note: if you don’t see the pane on the left, which is called the Project Explorer, you can use the View > Project Explorer menu command to turn it on.
You’ll notice that the PERSONAL.XLXB project has two folders, Microsoft Excel Objects and Modules. Our BoldCenterUnderline macro is stored in Module1 in the Modules folder. The new blank workbook doesn’t have a Module1 destination, but fortunately, it is easy to create.
We select the new blank workbook “VBAProject (Book1)” in the Project Explorer pane, and then use the following menu command:
- Insert > Module
Ah, yes, now we have a Modules folder with Module1, and Module1 is active. So, we can simply paste in our macro by doing a standard Edit > Paste or Ctrl+V.
Congratulations…you just added your macro to your new blank workbook! You can continue copying and pasting any other macros that you want.
Please note that each macro does NOT need its own Module, you can just place them all together in one Module if you’d like.
After you’ve moved any remaining macros over, you can close the Visual Basic Editor with:
- File > Close and Return to Excel
At this point, we have a new workbook that includes a macro. So far so good? Let’s keep moving.
Save It
Now, we want to save our new workbook. We make it the active workbook, using the Switch Windows command if needed:
- View > Switch Windows
Once our new workbook is active, we just need to save it. We can click the Save icon, use File>Save, or Ctrl+S. Excel displays the familiar Save As dialog. There are three key points, the location, the name, and the type.
The location. We can pick any location that we want to save the workbook. It is often really nice to save it to a location that is regularly backed up. It is also nice to have it stored in a location that is accessible if you switch computers, or that you will be sure to take with you if you move to a new computer.
Rather than save the workbook in a folder that has other files, we want to save it in a new empty folder. So, after navigating to the desired location, we add a new folder and name it something like Macro Toolbox. You can create a new folder by clicking the New folder button in the Save As dialog or with the Ctrl+Shift+N equivalent. Once you have your new empty folder, navigate into it so you save your file there.
The name. You can name the file anything you want. I went with Macro Toolbox.
The type. Finally, we need to update the file type. We can’t store macros in an ordinary XLSX file type, but, we can save macros in the XLSM file type, which represents an Excel Macro-Enabled Workbook.
Hit save, and we are ready for the next step.
Hide It
The Personal Macro Workbook is sort of mysterious because we typically don’t see it. That is, it opens Hidden. We had to temporarily unhide it in order to get access to any macros it stored.
We can hide other workbooks as well, and, it is pretty easy to do. To hide the active workbook, our Macro Toolbox workbook, we just click the following Ribbon command.
- View > Hide
Now, our Macro Toolbox workbook is hidden, and that was one of our objectives in simulating the functionality of the Personal Macro Workbook. The other objective was to have our Macro Toolbox open whenever Excel opens. This is our final step.
Open it When Excel Opens
We want the Macro Toolbox to open whenever Excel opens.
We’ll discuss two options for accomplishing this. You can use either method, depending on your preference.
Method 1: Excel Options
We can tell Excel to open the Excel files found in any given folder. To do this, we open the Excel Options dialog with the following command:
- File > Options
In the Excel Options dialog, we click on the Advanced category on the left side, and then scroll down the right side to the General commands, where we see a field named “At startup, open all files in” as shown below.
Here, we can type or paste in the full path to our Macro Toolbox folder. We click OK to save the change. That was easy. Now, any Excel files we place into that folder will open when Excel opens.
Method 2: Create a Link in XLSTART
Another option is to create a link to the new Macro Toolbox.xlsm file inside of a special folder called XLSTART. The XLSTART folder does just what its name indicates…any files in there are opened by Excel when Excel opens. It opens Excel files as well as links to Excel files. Fortunately, it is pretty easy to create a link.
First, we want to use Windows Explorer to navigate to the XLSTART folder. The exact location of this folder depends on your operating system and office version. There are many clever ways to find it.
One way to find it is to navigate to your C drive and use Windows Explorer to search for it.
Another way is to use Excel’s Trust Center. To do this, open the Excel Options dialog box (File > Options), and then click on the Trust Center category on the left, and then the Trust Center Settings button on the right as shown below.
This opens the Trust Center, and you can click on the Trusted Locations category on the left and view the various trusted folders. You should see XLSTART listed. You may have multiple XLSTART locations, for example, you may have one with a description of User StartUp and you may have one with a description Excel StartUp. You’ll want to work in the User StartUp version so that you have permissions to add a link to it. This is shown in the dialog below for reference.
Double clicking it opens a dialog where you can copy the full path and paste it into another Windows Explorer window to navigate quickly.
Note: be careful not to accidentally browse to a new location which would result in changing the trusted location path.
Once you’ve opened the XLSTART folder in Windows Explorer, you create a new link to your Macro Toolbox file. You can do this by right-clicking in the folder and selecting New > Shortcut. In the Create Shortcut dialog, browse to the location of your Macro Toolbox.xlsm file. Now, your XLSTART folder contains a link to the Macro Toolbox workbook, and, when you open Excel, your macro toolbox file is opened as well.
Note: if you don’t see the option to create a new Shortcut when you right-click, it is probably because you are in the Excel StartUp XLSTART instead of the User StartUp XLSTART. If so, open the Trust Center up again and find the User StartUp.
Wrap-Up
Regardless of which method you use to get Excel to open the Macro Toolbox, we need to save our Macro Toolbox file. Our Macro Toolbox.xlsm file was hidden after we saved it. In order to get it saved as a hidden file, we can simply close the Excel application. Doing so will cause Excel to display a dialog asking if you want to save changes, which you want to do, so click Save.
Then next time you open Excel, the Macro Toolbox workbook will open. Also, depending on your Excel security settings, you may receive a security notice, and you’ll want to of course click Enable Macros.
And, with that, we have created a portable alternative to the Personal Macro Workbook. We specifically define the location, and if we save it to a network drive, it will be available as we upgrade computers, Windows, or Office.
If you have any other suggestions or macro tips, please share by posting a comment below…thanks!
Excel is not what it used to be.
You need the Excel Proficiency Roadmap now. Includes 6 steps for a successful journey, 3 things to avoid, and weekly Excel tips.
Want to learn Excel?
Our training programs start at $29 and will help you learn Excel quickly.
This is AWESOME! I have some pretty involved macros (at least *I* think so!) in my Personal Macros workbook. When I got a new computer, I didn’t realize that they didn’t transfer over! BOY, was that not fun having to re-create the wheel!
I’m definitely going to do this SOON!
Ah yes…that same thing happened to me 🙁
That’s what led to this approach 🙂
Thanks – I really appreciate know about this.
David
A Heading area includes the macro name, who would use it, and whether it is a macro used in edit or an execute used standalone.
Hi Jeff
I don’t see the advantage of creating a separate workbook when you can just store your PMW (Personal.xlsb) in your desired network location. There is no requirement for this file to be stored on the same drive as Windows and Microsoft Office – that is just the DEFAULT location. I have used this technique for years – in recent times even using Dropbox so that I always have access to the latest version no matter where I am.
Col… storing your macro workbook in a different location is great, and I never thought about DropBox … thanks for sharing this idea 🙂
Thanks
Jeff
Necessity (being messed around by others!) is the mother of innovation!
One potential gotcha to watch out for (at least in XL 2007, 2010):
If you assign custom ribbon buttons, or custom QAT buttons (“for all documents” rather than just the active workbook), to macros in your Personal.xlsb or any other general “toolbox” like workbook, subsequently changing the stored location of that source workbook (even if you do it via File Save As from within Excel) does NOT change the button links to the file! (Go figure how MS didn’t cater for that!).
The somewhat messy work-around is to (1) export the ribbon/QAT customisations to a file (2) open the export file in Notepad or similar text editor (3) search for the old file paths (there are TWO slightly different records for each button link) and replace with the new paths (4) save (5) import back into Excel.
The best solution is to put all these tools in an Add-in, but that’s a bit of work and a little bit technical for most. (I’ll get around to doing this myself one day!!)
This is great info, and I appreciate you sharing and helping others avoid such Excel pitfalls 🙂
Jeff,
I’m unsure as to why you created the macro in the PERSONAL MACRO WORKBOOK first. and then copied it to a module in the new workbook. Could you not have simply create the macro in THIS WORKBOOK and continued from there?