Personal Macro Workbook VBA- Create and Use

In the previous article, we learned how to use Sub Procedures in VBA. Today we will learn to use the Personal.xlsb file which is the Personal Macro Workbook in Excel. Normally when we write a macro using a module in the workbook, it is accessible from that workbook only. If we want the macro to work for multiple workbooks, copying the code to all workbooks is not an efficient solution.

So let us learn the usage of the Personal Macro Workbook in Excel.

What is Personal Macro Workbook?

Personal Macro Workbook is a macro enabled workbook, one in every version of excel. Whenever we open Microsoft Excel, the Personal.xlsb file is also opened but hidden from us. Personal Macro Workbook is hidden by default but It can contain the VBA code that we wish to access in different workbooks in our version of Excel.

Along with Macros. Personal.xlsb file can contain User Defined Functions ( UDF ).

The thing is that Personal.xlsb file does not exist in your excel by default and you need to create it yourself once to use it in the future as well.

Personal Macro Workbook on Your System

First, you need to check if the Personal.xlsb file already exists on your System or not. The Personal.xlsb file is stored at the following path on your PC. You can check if the file is present at the following path.

On Windows 7, Windows 10, and Windows Vista:-

C:\Users\User Name\AppData\Roaming\Microsoft\Excel\XLSTART

The User Name here is specific to your PC.

If the file is already present, then you will see the file on the path like this. You can also share or delete the Persona.xlsb file from here.

If there is no file present, then you need to create the Personal.xlsb file to use it.

Create Personal Macro Enabled Workbook

To create the Personal.xlsb file, you need to record a macro and then store this macro in Personal.xlsb file. Excel will automatically create Personal.xlsb file to perform this operation.

Follow the given steps to create Personal Macro Workbook in your version of Excel.

• Enable the Developer Tab.
• Go to the Developer tab and click on Record Macro button in the Code Group.

• This opens Create Macro dialog box. Select Personal Macro Workbook in the Store macro in the field and then click Ok.

• This started recording a new macro in Personal.xlsb and the file is created now. You can now stop recording the macro from the Developer tab.

• When you try to close excel it asks for saving the changes made in Personal Macro Workbook. This is so because. whenever you record the macro and store its code in Personal.xlsb file it needs to be saved to be used next time when you open workbook in excel.

How to Use Personal Macro File?

The macros stored in Personal.xlsb file can be used in all of the workbooks that you open in excel on your system.

Here we have a macro that asks for the zoom-in value and then zooms in all the worksheets of the workbook to the same value of zoom-in percentage.

Sub zoom()
'zoom to fixed value for all worksheets of workbook
Dim x As Integer
x = InputBox("Enter zoom value")
Dim ws As Worksheet, Act_Sheet As Worksheet
Set Act_Sheet = ActiveSheet
For Each ws In Sheets
ws.Activate
ActiveWindow.zoom = x
Next ws
Act_Sheet.Activate
End Sub

To use this sub procedure to all your workbooks, you need to paste the above code in a new module in Personal.xlsb file. Follow these steps to do it.

• Press Alt and F11 keys to open the Visual Basic Editor.
• In the Project Explorer at the left, click on VBA Project ( Personal.xlsb ).

• Go to the Insert tab and insert a new module in Personal.xlsb file and paste the code for zoom in the new module.

Now you can close the Visual Basic Editor and press Alt and F8 keys to see the list of available macros. You can run this macro in any opened workbook in your Excel.

How to Stop Personal Macro Workbook from Opening?

Personal Macro Workbook is hidden by default. If the file keeps opening whenever using macros then you can hide it to stop it from opening.

• Open the Personal.xlsb file and go to View tab on the ribbon.
• In the Windows tab, click on Hide button and then the Personal.xlsb file will be hidden.

You can unhide the Personal.xlsb file from the Unhide button in the View tab on the ribbon.

This brings us to an end.

Thank you for reading. ❤