The Personal Macro Workbook

1. Creating Personal Macro Workbook
2. Using Personal Macro Workbook
3. Sharing Personal Macro Workbook
4. Unhiding Personal Macro Workbook
5. Deleting Personal Macro Workbook
6. Stop Excel from Asking to Save Nonexistent Changes

A macro is a set of commands that you can run with a single click. By default, a macro is saved in the same workbook where you created it. However, this means that you can only use the macro in that workbook.

If you want to access the same macro from different workbooks, you have to copy and paste it every time. A better solution is to store your frequently used macros in the Personal Macro Workbook.

The Personal Macro Workbook is a hidden file, named personal.xlsb, that is loaded automatically when you launch Excel. You can save any macro in it and use it in any other workbook.

Creating Personal Macro Workbook

To create a personal macro workbook, you need to record at least one macro and store it in the Personal Macro Workbook option. Follow these steps:

1. In any workbook, follow one of these steps:
   • go to the View tab > Macros group, and click Macros > Record Macro...
   • go to the Developer tab > Code group, and click Record Macro.
2. The Record Macro dialog box will appear.
3. In the “Store Macro in” drop-down list, select Personal Macro Workbook and click OK. You can accept the default name for the macro or give it a descriptive name.
4. Now stop recording by following one of these steps:
   • go to the View tab > Macros group, and click Macros > Stop Recording.
   • go to the Developer tab > Code group, and click Stop Recording.

This will create a personal macro workbook named Personal.xlsb (or Personal.xls in earlier versions) and store it in the XLSTART folder on your computer.

When you close Excel, you will be prompted to save both the current workbook and the personal macro workbook. Click Save for both.

Using Personal Macro Workbook

Once you have created a personal macro workbook, you can:

• add/record any macros to it and that macro can be RUN from any opened workbook.
• write VBA code by using the Visual Basic Editor (VBE), any workbook can use/access that code.

To access VBE (Visual Basic Editor), press ALT+F11, you can find the personal macro workbook personal.xlsb in the Project Explorer pane on the left-hand side:

To run a macro from your personal macro workbook, you can use any of these methods:

• Go to: View > Macros or Developer > Macros and
  select the macro name and click Run.
• Use the keyboard shortcut assigned to the macro.
• Use a button or shape assigned to the macro.

Sharing Personal Macro Workbook

To share a personal macro workbook PERSONAL.XLSB with others, you need to follow these steps:

1. Close the Excel application, you will be prompted to save both the current workbook and the personal macro workbook. Click Save for both.
2. Locate the personal macro workbook file on your computer. It is usually stored in the XLSTART folder under the AppData folder. The file name is PERSONAL.XLSB. For more info, visit Locate the XLStart folder.
3. Copy the file to a location that is accessible by the people you want to share it with, such as a shared network drive or a cloud storage service.
4. Instruct the other users to copy the file to their XLSTART folder on their computers and restart the Excel application.
5. The personal macro workbook should load automatically and the macros should be available in the Macro dialog box.

Caution:

If the other users have their own PERSONAL.XLSB file in the XLSTART folder, this method will override that file with your provided file which causes the removal of their own macros collection.

To prevent this situation, you can rename the PERSONAL.XLSB file and then share it with other users and instruct them to copy the file to their XLSTART folder to open it automatically when Excel starts. For more information see How to Open Workbooks Automatically When Excel Starts.

Unhiding Personal Macro Workbook

The personal macro workbook PERSONAL.XLSB is a hidden workbook that opens when you start Excel and contains any macros that you want to use across all your workbooks. To unhide it, follow these steps:

1. On the View tab, in the Window group, click Unhide.
2. In the Unhide dialog box, select PERSONAL.XLSB and click OK.
3. The personal macro workbook should now be visible on the desktop.
4. You can hide the personal macro workbook again by clicking Hide on the View tab, in the Window group.

Deleting Personal Macro Workbook

If you want to delete your Personal Macro Workbook, you need to follow these steps:

1. Close the Excel application, you will be prompted to save both the current workbook and the personal macro workbook. Click Save for both.
2. Locate the personal macro workbook file on your computer. It is usually stored in the XLSTART folder under the AppData folder. The file name is PERSONAL.XLSB. For more info, visit Locate the XLStart folder.
3. Select the PERSONAL.XLSB and press the delete button.
4. Click Yes to confirm the deletion.

Stop Excel Prompting to Save Nonexistent Changes

You might have noticed that sometimes simply opening a workbook and taking a look around is enough to trigger Excel to prompt you to save changes to your personal macro workbook – despite the fact that you’ve made no changes whatsoever. Whether you know it or not, you most likely have a volatile function within your personal macro workbook.

A volatile function (or formula) is one that automatically recalculates each time you do almost anything in Excel, including opening and closing either the workbook or the entire application. Two of the most common volatile functions are the Today( ) and Now( ) functions.

To avoid this, you can either use a nonvolatile function that does not update automatically or you can use a VBA code snippet in the personal macro workbook that saves the workbook when it opens:

Private Sub workbook_Open( )
 Me.Saved = True
End Sub

This macro will save your personal macro workbook automatically each time it is opened.

---

Understanding Workbooks and Worksheets: