Excel Personal Macro Workbook

Brad Arnold

I have a folder of Macros that I use from time to time. Until recently I would open my new spreadsheet, go into Visual Basic mode and copy/paste the macro into the spreadsheet. Save it and then use my macro. Which was fine, but if I had several spreadsheets I needed to run the same macros on it was tedious.

I recently learned about the Personal Macro Workbook which saves your macros in a special workbook on your computer and then they are available to any Excel file you open.

How does this work?

The personal workbook (Personal.xlsb) is opened as a hidden workbook every time you start Excel. Excel saves the personal workbook as Personal.xlsb, in the following folder on Windows 7 and Windows Vista computers:

C:Users<username>AppDataRoamingMicrosoftExcelXLSTART

Make the Developer tab visible in Excel 2010

  1. On the File tab, click Options, and then click Customize Ribbon
  2. Under Customize Ribbon, in the Main Tabs box, make sure that the Developer check box is selected.
  3. Click OK.

Make the Developer tab visible in Excel 2007

  1. Click the Microsoft Office Button Office button image, and then click Excel Options.
  2. Click Popular, and then select the Show Developer tab in the Ribbon check box.
  3. Click OK.

Now you’re ready to record a macro and save it in the Personal Macro Workbook. In this example, you will create a simple macro by using the Macro Recorder.

  1. On the Developer tab, in the Code group, click Record Macro.
    Record Macro
  2. In the Record Macro dialog box, type a name for the macro in the Macro name box. Make sure you don’t use any spaces in the name.
  3. In the Store macro in box, select Personal Macro Workbook.
    Record Macro dialog box
  4. Click OK.
  5. On the Developer tab, in the Code group, click Stop Recording.
    Stop Recording
  6. Click Visual Basic button to enter Visual Basic mode
  7. You will now see VBAProject (PERSONAL.XLSB)

8.       Double-Click on Module1 to see the Macro you just “recorded”. It will be empty since we didn’t actually perform any steps while recording.

9.       Add whatever VB code you want.

10.   Close Visual Basic and return to Excel. If you click on Macros you’ll see your newly created Macro

11.   Run this Macro just like you would any other Macro.

  1. Close any open workbooks and then exit Excel.
    A message appears that prompts you to save the changes that you made to the Personal Macro Workbook.

13.   Open Excel and open a new (different) spreadsheet. If you go to the Developer Tab and then click on Macros you’ll see your macro you created above even though you’re in a brand new file!

Make sure you’re backing up your Personal.xlsb so that if your computer crashes you don’t have to re-do all your macros!

Leave a thought