My last blog was on accessing personal macros from my custom ribbon.
This blog is about where to store these personal macros. These personal macros are the ones that save you time and tedious work. There are basically two ways to go.
Some save them to their Personal.xlsb file that loads on opening Excel. Others including me prefer the Add-In method which has a .xlam extension and still loads on opening Excel is ticked as an active Add-in.
The Add-In is essentially a hard wired hidden workbook unless you go into the VBE (see left) and select the Workbook object and and then change the highlighted setting IsAddIn True to False. But to save it you will have to change this setting back to True if you want to continue with this Workbook being an Add-In. To create one you start with a new workbook, get it the way you want it including the VBA code you need for the macros you want to access then save as an .xlam extension and the workbook will disappear from view.
For it to load with each Excel session you will need to save it to the Add-In directory which on my machine is C:\Users\John\AppData\Roaming\Microsoft\AddIns .
To edit the Add-In you have to got to the VBE and as above select the Workbook object and and then change the highlighted setting IsAddIn True to False. Do your changes then back to the highlighted setting IsAddIn True to False and hit save in the VBE. If you forget to save Excel will ask if you want to save as you close down your Excel session but hey that’s leaving things to chance and your changes may not survive an Excel crash.
The Personal.xlsb workbook is a hidden workbook but can be unhidden like any hidden workbook conventionally using the ribbon command like below. To create one you need to record a macro and then choose Personal.xlsb in the store to field. This will automatically create a Personal.xlsb file in the XLStart directory which for my PC is: C:\Users\John\AppData\Roaming\Microsoft\Excel\XLSTART . This method is actually great for those who don’t know VBA but want to use macros to save time so store recorded macros and don’t need to share them with others. For instance, say with a regularly downloaded bank csv report, you need a macro to just clean it up – maybe delete columns you don’t need and then add some headings and add a pivot table 3 columns to the right. You record the macro, give it a meaningful name like CleanupBankDump, save it to Personal.xlsb and then it’s there to act upon which ever sheet they have opening the future so note you have to be mindful that you have the right sheet in front of you. Even so, a user with good VBA skills can still use the Personal.xlsb method as long as they write the VBA code to an object of module within this Personal.xlsb file.
If you need to distribute and maintain macros to a group of users then an Add-In is the better method and can be located on a server and then only have to manage the one file in theory. But users can accidentally save a copy to their local drive when prompted by Excel and not pick up changes you make to the server located .xlam file so be mindful of this. But if you explain to users not to be sloppy with this issue, it can work well.
Note another small issue is if you have created a Personal.xlsb file then all recorded macros default to this rather than the active file which is a very slight inconvenience as usually the VBA code is intended for the active workbook.
Whether you use Personal.xlsb or an Add-In.xlam is therefore a personal choice depending on your knowledge of VBA and job role. It doesn’t make too much difference which you use if the macros are just for you. But for me, I like Add-Ins because I have learned to use them and they are powerful when you need roll out macros to others.