Find Personal Macro Workbook Excel
Unhide Workbook Excel Macro
Open Excel Workbook Macro
Mac Excell 2011 Creating Macro For All Workbooks Using

The tutorial shows how to create and use the Personal.xlsb file to make Excel macro available in all workbooks.

In Excel for Mac 2011 Essential Training, author Curt Frye gives a comprehensive overview of Excel, the full-featured spreadsheet software from Microsoft. The course covers key skills such as manipulating workbook and cell data, using functions, automating actions, printing worksheets, and collaborating with others. The Personal Macro Workbook is used to store macros that you want to make available to any file in Excel. This tutorial explains why you might want to store. I'm creating a macro in Excel 2011 for Mac to create reports. Part of the macro wants to open a file (99% of the time), edit the file, and then close the file (saving changes). On a rare occasion, if a file does not exist, it wants to create a new excel file, edit it, and then close the file (saving changes). To create functions and macros, you work with the Visual Basic Editor (VBE), which opens in a new window separate from Excel. Suppose your company offers a quantity discount of 10 percent on the sale of a product, provided the order is for more than 100 units.

Normally, when you write a macro in Excel, it can only be accessed from that specific workbook. But what if you want to use it in other files too? Recreating the same macro for each and every workbook does not sound like a good idea. Instead, store your popular VBA codes in the Personal Macro Workbook. This will make the macros accessible every time you open Excel, in any workbook!

What is Excel Personal Macro Workbook?

Here is the easiest way: Open a new Excel file and click on Tools and Record New Macro, which opens the Record Macro screen. Under Macro name, label it something innocuous (such as Test ) and in the box under Store macro in, click on the down arrow to show Personal Macro Workbook and then click on OK (see screenshot below).

Personal Macro Workbook is a special file named Personal.xlsb in Excel 2007 – 2019 or Personal.xls in earlier versions. Any macros stored in this file are available to you whenever you start Excel. A great thing about the personal workbook is that it's hidden by default, so you won't ever notice it opening in the backend. No actions on your side are required. Microsoft Excel just knows the location where this file lives in your system and looks for it automatically on every launch.

Aside from macros, the Personal Macro Workbook can store your custom functions (user-defined functions) written in VBA.

A fly in the ointment is that the Excel Personal Macro Workbook does not exist by default. To create it, you will need to record at least one macro. Once the Personal.xlsb file is created, you can add any other macros to it by using the VB Editor.

Find Personal Macro Workbook Excel

Where is Personal Macro Workbook?

The Personal.xlsb file is stored in the XLSTART folder in the following locations.

On Windows 10, Windows 7, and Windows Vista:

C:UsersUser NameAppDataRoamingMicrosoftExcelXLSTART

On Windows XP:

C:Documents and SettingsUser NameApplication DataMicrosoftExcelXLSTART

Please note that the AppData folder is hidden by default. So, to get to the XLSTART folder in Windows Explorer, check the Hidden items box on the View tab.

Note. If the XLSTART folder does not exist on your computer, that means your personal macro workbook is not created yet. Please follow the below steps to make one.

How to create Personal Macro Workbook in Excel

Unhide Workbook Excel Macro

To make your personal macro workbook, you need to record any macro, which is done via the Developer tab. So, before we get started, please make sure the Developer tab is activated in your Excel.

To create the Excel Personal Macro Workbook, carry out these steps:

In any workbook, go to the Developer tab > Code group, and click Record Macro.
The Record Macro dialog box will show up. In the Store Macro in drop-down list, select Personal Macro Workbook and click OK. Optionally, you can change the default name such as Macro1 to a more meaningful one.
If you want to record a specific macro, perform the desired actions. If your goal is just to create a personal workbook, continue with the next step.
On the Developer tab or the Status bar, click Stop Recording.
Save and close all open workbooks. Doing this display a message prompting you to save the changes you've made to the Personal Macro Workbook, and you click Save:
This message will appear every time you create a new macro or update an existing macro in your personal workbook.

Done! The Personal.xlsb file is created and will be automatically opened in the background every time you start Excel.

Now that you have a personal macro workbook, how do you place macros into it? There are four different ways to do this:

Record a macro and save it to Personal Macro Workbook as shown above.
Write a macro in the VB Editor.
Copy VBA code from another workbook.
Import macros from a .bas file.

How to view, create, copy and edit macros in Personal Macro Workbook

Once the Personal.xlsb file is saved on your computer, you can view, create and edit the macros the Visual Basic Editor. Also, you can copy the code you wish to reuse from another workbook.

Mac Excell 2011 Creating Macro For All Workbooks

To create or edit a macro in your personal workbook, this is what you need to do:

Open the VB Editor. For this, press ALT + F11 or click the Visual Basic button on the Developer tab, in the Code group.
In the Project Explorer window, find the PERSONAL.XLSB object, expand it, and double-click on Module1 to open its Code window.
In the Code window, write, copy/paste or edit the VBA code.
Close the VB Editor.
Close Excel and save the changes you've made to the Personal Macro Workbook when prompted.

How to use macros stored in Personal Workbook

Macros stored in your personal workbook can be used in any Excel file. For this, just open the worksheet in which you want to run the macro, and do the following:

On the Developer tab, in the Code group, click the Macros button or press the Alt + 8 shortcut.
In the Macro dialog box, you will see the list of macros available for use in all open workbooks, including the ones stored in the Personal.xlsb file. Select the target macro and click Run.

You can also assign a custom shortcut to a macro or run it by clicking a special macro button.

How to export Personal Macro Workbook

Like any other VBA code, the macros stored in Personal.xlsb can be exported to a .bas file.

To export your personal macro workbook, here's what you need to do:

Open the Visual Basic Editor.
In the Project Explorer, right-click Module1 under PERSONAL.XLSB and select Export File.
Navigate to the folder where you want to save your personal macro workbook, give the file any name you want, and click Save.

To import a .bas file that someone shared with you, right-click the module under PERSONAL.XLSB into which you want to place the macros, and select Import File.

How to share Personal Macro Workbook in Excel

In case you'd like to share your per

Mac Excell 2011 Creating Macro For All Workbooks