Personal Macro Books in Excel
Macros are great feature of Excel. They enhances productivity and help automating repeating tasks. Most of the macros we use are often written in a specific excel workbook only and we need to copy them if we want to use them in another workbook. But there is a solution; using Personal macro book we can make macros available across multiple workbooks. There are other solution to it as well, but this is one of the simpler one to use.
Personal macro-book is an .XLSB file which gets open when you open the excel and it stays hidden but all the macros written in it are available across different excel workbooks.
How to create Personal Macro books?
Its easy just open a new excel and save it with .XLSB (Excel Binary Workbook) extension. But to get it open each time excel open you need to save it at following location
Windows 7 and up:
C:\Users\AppData\Roaming\Microsoft\Excel\XLSTART\PERSONAL.XLSB
Mac - I use a different way to put my personal macro-book. Go to preferences (CMD + ,) then go to General section. Here you can specify the path of start-up folder under "At start up, open all files in:"
Alternate way to get the path of startup folder is, go to VB editor and open the immediate window and type following command and press enter. It will give the path of startup folder based on your OS and Excel version
? application.StartupPath
Once Personal macro book is saved under this folder, then whenever you open any excel file and it will get open along with it. But you dont' want it to show all the time, so you can hide it via menu Window -> hide. By doing this Personal macro-book will be hidden still you will be able to use any macros written in it.
Adding macros to Personal macro-book is same as writing macros in any of your excel files. Just make sure when you go to VB editor you choose a module under Personal macro book and not in your excel file. Also while recording macros, after giving macro name you can choose Personal Macro Book under section "Store Macro in". That will record the macro directly into Personal Macro Book.
