Personal Macros – Personal.xlsb or Add-In.xlam?
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.
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 .
If you need to distribute and maintain macros to a group of users then an Add-In 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. 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 a personal choice depending on your role and doesn’t make too much difference if the macros are just for you. But for me I like Add-Ins.