Excel Personal Macro Workbook: how to create, use and share

This tutorial explains how to create and utilize the Personal.xlsb file to make Excel macros accessible across all workbooks. Normally, Excel macros are workbook-specific. This guide demonstrates how to centralize commonly used VBA code in the Personal Macro Workbook for universal access within Excel.

- What is the Personal Macro Workbook?

• Locating your Personal.xlsb file
• Creating a Personal Macro Workbook
• Editing the Personal Workbook
• Running macros from the Personal Workbook
• Exporting your Personal Macro Workbook
• Sharing your Personal Macro Workbook
• Enabling the Personal Macro Workbook
• Preventing Personal.xlsb from automatically opening
• Unhiding the Personal Workbook
• Deleting the Personal.xlsb file

Understanding the Excel Personal Macro Workbook

The Personal Macro Workbook is a special file, Personal.xlsb (Excel 2007-2019) or Personal.xls (earlier versions). Macros within this file are available whenever Excel is launched. It's cleverly hidden by default, seamlessly integrating into the background. Excel automatically detects its location and loads it on startup. Beyond macros, it can also store custom VBA functions.

Note that the Personal Macro Workbook isn't created automatically. You must record at least one macro to generate it. Once created, you can add more macros via the VB Editor.

Locating the Personal Macro Workbook

The Personal.xlsb file resides in the XLSTART folder:

Windows 10, 7, Vista: C:\Users\User Name\AppData\Roaming\Microsoft\Excel\XLSTART

Windows XP: C:\Documents and Settings\User Name\Application Data\Microsoft\Excel\XLSTART\

Remember that the AppData folder is hidden. Enable "Hidden items" in the View tab of Windows Explorer to locate it.

The absence of the XLSTART folder indicates that the Personal Macro Workbook hasn't been created yet. Follow the steps below to create one.

Creating the Personal Macro Workbook

Creating the Personal Macro Workbook requires recording a macro, accessible through the Developer tab. Ensure the Developer tab is enabled in Excel.

1. In any workbook, navigate to the Developer tab > Code group, and click Record Macro.
2. The Record Macro dialog appears. Select Personal Macro Workbook from the "Store Macro in" dropdown and click OK. You can optionally rename the default macro name (e.g., Macro1).
3. Perform the desired actions to record a macro (or skip this step if your sole aim is creating the workbook).
4. Click Stop Recording (on the Developer tab or Status bar).
5. Save and close all open workbooks. A prompt will appear to save changes to the Personal Macro Workbook; click Save. This prompt will reappear whenever you create or update macros.

The Personal.xlsb file is now created and will automatically load in the background with each Excel launch. You can add macros via recording, the VB Editor, copying from other workbooks, or importing from .bas files.

Managing Macros in the Personal Macro Workbook

Access, create, and edit macros within the Personal.xlsb file using the Visual Basic Editor (VBE). You can also copy code from other workbooks.

1. Open the VBE (ALT F11 or the Visual Basic button on the Developer tab).
2. In the Project Explorer, locate PERSONAL.XLSB, expand it, and double-click Module1 to open its code window.
3. Write, paste, or edit VBA code.
4. Close the VBE and Excel; save changes when prompted.

Using Macros from the Personal Workbook

Access macros from the Personal Workbook in any Excel file:

1. Developer tab > Code group > Macros (or Alt F8).
2. Select the desired macro and click Run.

Alternatively, assign custom shortcuts or create macro buttons.

Exporting and Sharing the Personal Macro Workbook

Exporting:

1. Open the VBE.
2. Right-click Module1 under PERSONAL.XLSB and select Export File.
3. Save the .bas file.

Importing: Right-click the module and select Import File.

Sharing: Share the Personal.xlsb file directly (use caution if recipients already have their own Personal.xlsb), copy individual macros to another workbook, or export macros to a .bas file for others to import. Be aware that some macros might not function correctly on other systems due to workbook-specific references.

Enabling the Personal Macro Workbook

If macros disappear, the Personal.xlsb file might be disabled. Re-enable it:

1. File > Options.
2. Select Add-ins.
3. In the "Manage" dropdown, choose Disabled Items and click Go.
4. Select Personal Macro Workbook, click Enable, and restart Excel.

Hiding and Unhiding the Personal Workbook

Hiding: To prevent the Personal.xlsb file from being visible while retaining macro functionality:

1. Navigate to the PERSONAL.XLSB workbook.
2. View tab > Window group > Hide.

Unhiding:

1. View tab > Window group > Unhide.
2. Select PERSONAL.XLSB and click OK.

Removing the Personal.xlsb File

To completely remove the Personal Macro Workbook:

1. Close all Excel workbooks.
2. Open Windows Explorer, show hidden items, navigate to the XLSTART folder, and delete PERSONAL.XLSB. Ensure all Excel processes are closed before deleting.

This concludes the guide on making Excel macros accessible across all workbooks.

The above is the detailed content of Excel Personal Macro Workbook: how to create, use and share. For more information, please follow other related articles on the PHP Chinese website!