Excel – Create a PERSONAL.XLSB File to Store Macros
If you are using Excel VBA you might know this already. Storing macros that work in any Excel file can be done in 2 ways:
- Creating a macro enabled binary file (PERSONAL.XLSB) that loads when you open any Excel file. Normally Excel doesn’t create that file.
- Creating an Excel add-in (.XLAM, .XLA for older Excel). This is good when you want to personalize existing Ribbons or create a special Ribbon for yourself.
In this tutorial we are going to cover the first way since it’s the easiest. I might cover the 2nd in another guide.
You must record a Macro to create PERSONAL.XLSB. You don’t have to do anything in the Macro (it can be empty) but you must initialize a macro recording process, then stop it.
a. in Excel go to File – Options
b. Customize Ribbon
c. check the Developer Ribbon on the right hand side
d. click OK
e. go to the Developer Ribbon and click the Record Macro button
f. in the box that appears, under Store macro in, choose Personal Macro Workbook, then click OK
g. on the Developer Ribbon click Stop Recording
h. on the Developer Ribbon click on Visual Basic
i. on the left hand side tree-style Project Explorer you should notice VBAProject(PERSONAL.XLSB), which you can right click and insert Userform, Module or Class Module
j. if the tree-style Project Explorer does not appear, go to View – Project Explorer to enable it