MrExcel.com - Your One Stop for Excel Tips & Solutions

Create a Personal Macro Workbook

November 05, 2021 - by

Create a Personal Macro Workbook

Problem: There are a few macros in this workbook that might be useful in a Personal Macro Workbook. If you’ve never used macros before, you may not have a personal macro workbook. It is easy to create one.

Strategy: This is an easy way to create Personal.xlsb and save it in the correct location.

  • 1. Open a blank workbook.

  • 2. View, Macros, Record New Macro.

  • 3. In the Record Macro dialog, change Store Macro In to Personal Macro Workbook.

Record a macro and choose to Store Macro In the Personal Macro Workbook. This will automatically create Personal.xlsm
Figure 147. Save the recorded macro in your Personal Macro Workbook.

  • 4. Click OK to begin recording.

  • 5. In Excel, type Hello and press Ctrl+Enter.

  • 6. Stop the macro recorder using View, Macros, Stop Recording.

  • 7. Press Alt+F11 to open VBA.

  • 8. Press Ctrl+R to display the Project Explorer in VBA.

  • 9. In the Project pane, click on VBAProject (PERSONAL.XLSB).

  • 10. Use the + icon to expand PERSONAL.XLSB.

  • 11. Use the + icon to expand Modules.

  • 12. Double-click on Module1.

  • 13. You will see your Macro1 code on the right. This is where you will type new macro code in the future.

  • 14. Click the Save icon in the toolbar. If you skip this step, I can predict what will happen. At 5 o’clock, when you close Excel, you will see and answer “Do you want to Save?” questions about open workbooks. You might be in a mood to close everything without saving. Personal.xlsb is a hidden workbook and will be the last “Do you want to Save” message that you see. Since you are not seeing any workbook on the screen, there is a tendency to say No and lose the macros you added.

Inside of Personal.xlsb, there are three modules. One is created on each day that you record a new macro and store it in the Personal Macro Workbook.
Figure 148. You can type future macros in the code pane on the right.

Note: This Macro1 is not meant to be useful. You can actually delete the lines Sub Macro1() through End Sub. The goal here is to allow Excel to create Personal.xlsb in the correct location so you can use the macros that follow.

This article is an excerpt from Power Excel With MrExcel

Title photo by Kid Circus on Unsplash