Personal Macro Workbook in Excel - make macros available in all workbooks

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

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?

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.

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:\Users\User Name\AppData\Roaming\Microsoft\Excel\XLSTART

On Windows XP:

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

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.
The location of the Personal Macro Workbook

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

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:

  1. In any workbook, go to the Developer tab > Code group, and click Record Macro.
    Recording a macro in Excel
  2. 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.
    Creating Personal Macro Workbook
  3. 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.
  4. On the Developer tab or the Status bar, click Stop Recording.
    Stop recording the macro
  5. 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:
    Save the changes you've made to the Personal Macro Workbook.

    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:

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.

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

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

Adding a macro to the Personal.xlsb file

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:

  1. On the Developer tab, in the Code group, click the Macros button or press the Alt + F8 shortcut.
  2. 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.
    Run a macro stored in a personal workbook.

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:

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

Exporting Personal Macro Workbook

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 personal workbook with other people, proceed in one of the following ways.

Share the Personal.xlsb file

The easiest way to share a personal macro workbook with someone is to send them a copy of your Personal.xlsb file as attachment, or save it to a cloud storage or your local network. And then, other persons can put your Personal.xlsb file in their XLSTART folder. See where Personal.xlsb is located.

This simple approach, however, can be dangerous in case the other person already has his/her own macros in the Personal.xlsb file. So, I'd recommend replacing this file only on your own devices when you are absolutely sure you won't mess up anything.

Copy macros to another workbook

If you want to share just a few macros from your Personal.xlsb file, you can copy those macros to another workbook, and email that workbook to other users. For the detailed steps, please see How to copy macros to another workbook.

Export macros to a .bas file

A professional-like way is to move all the macros you wish to share into one module and export that module as a .bas file. Send the .bas file to other users by email, and they will be able import it as a new module into their Personal.xls file.

Note. Please be aware that some macros from your personal workbook may not work for other users, e.g. VBA code that refers to a specific sheet.

How to enable Personal Macro Workbook in Excel

If one day you open Excel and find out that your favorite macros are no longer there, don't panic! Most likely, the Personal.xlsb file was disabled by Excel after a crash, update, or for whatever reason.

Here are the steps to get your personal workbook up and running again:

  1. In your Excel, click File > Options.
  2. In the Excel Options dialog box, select Add-ins on the left-hand side.
  3. In the Manage drop-down list, pick Disabled Items and click Go.
  4. In the Disabled Items dialog box, select Personal Macro Workbook and click Enable.
  5. Restart your Excel.

Enable Personal Macro Workbook.

How to prevent Personal.xlsb from opening but keep macros running

By default, the Personal Macro Workbook should not be visible in Excel. If the Personal.xlsb file keeps opening whenever you use macros and this really bugs you, you can hide it in this way:

  1. Navigate to the PERSONAL.XLSB workbook.
  2. On the View tab, in the Window group, click Hide.
    Hide Personal.xlsb to prevent it from opening.

That's it! From now on, the personal workbook will be hidden from view but still launch in the background, so you could access the macros stored there.

How to unhide a personal workbook in Excel

To make your personal macro workbook visible, this is what you need to do:

  1. On the View tab, in the Window group, click Unhide.
  2. In the Unhide dialog box, select PERSONAL.XLSB and click OK.
    Unhide the Personal.xlsb file.

How to remove Personal.xlsb file

If you'd like to completely remove the Personal Macro Workbook from your computer, delete the Personal.xlsb file from the XLSTART folder. Here's how:

  1. Close all Excel workbooks.
  2. Open Windows Explorer.
  3. On the View tab, in the Show/Hide group, tick the Hidden Items checkbox.
  4. Navigate to the XLSTART folder. On Windows 7 – 10, it is located here:

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

  5. Select PERSONAL.XLSB and press the Delete key, or right-click the file and choose Delete from the context menu.
    Delete the Personal.xlsb file.

Note. To remove Personal.xlsb, you must close the Excel application first. If the file does not delete, that means it's still running in the background. To fix this, open the Task Manager and kill all running Excel processes.

That's how to make your favorite Excel macros available in all workbooks. I thank you for reading and hope to see you on our blog next week!

Table of contents

25 comments

  1. Thank you!

  2. Very helpful and insightful indeed.
    Kudos Svetlana Cheusheva, and Thank you very much!
    I am sure you must've made many people happy by sharing this information :) :) :)

  3. I am so glad that I was finally able to fix my problem using your guidelines. Thank you for your practical and user-friendly explanation.

  4. Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    ActiveCell.Calculate
    End Sub

    Put this code in "ThisWorkBook" under Personal.xlsb but didn't work.

  5. I recently set up a Personal macro which is used each time I open my .xlsx files and press: CTRL + SHIFT + C (which is in the OPTIONS of this macro). It works find but when I get to the end of the macro, I am constantly being asked if I want to save the changes to the Personal.XLSB file. How do I avoid this? Thanks.

  6. Merci beaucoup.
    Tutoriel très bien fait et efficace !

  7. Thank you for this tutorial. I have been trying to remove a faulty macro for months and now it is gone.

  8. This article is incredibly helpful for me to learn this handy trick. Thank you Svetlana!

  9. Thank you so much!

  10. i am too thankful for this information

  11. Hi,
    My personal workbook seems to have disappeared. I can't unhide it because unhide is greyed out, and I did the enable disabled items in add-ins but still no personal workbook. HALP. How do I get it back??

  12. Is there a way to store the Personal Macro Workbook in the cloud, so that I can use it at home and at work?
    Thanks

  13. I saved a macro that is headed "Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    ' Colour in GC and AC entries"
    to PERSONAL.XLSB, but it does not appear when I open a worksheet and press the Macros button.
    Should I be adding a title for the macro in some way?

    1. Hello!
      When you press Alt+F8 in your workbook to run a macro, Private Subs don’t show up in the dialog box. Don't use the "Private" option.

  14. there's a typo:
    "On the Developer tab, in the Code group, click the Macros button or press the Alt + 8 shortcut"
    should be:
    "On the Developer tab, in the Code group, click the Macros button or press the Alt + F8 shortcut"

  15. Thank you so much! I couldn't find a way to import macros from a colleague but now it works. Appreciate it.

  16. How to edit ms Excel online?

  17. Hello Svetlana,

    I have created macro enabled file. Created a button and attached this macro to it.

    Now i want to share this excel file with users.

    But I dont want to restrict users to save files on specific paths like -
    C:\Users\usrname\AppData\Roaming\Microsoft\Excel\XLSTART

    Is there any way to avoid this step.

    As a user when I received macro enabled file and xlsb file. I would simply download attachments and save them to any random path I wish to. And start using macro functionalities.

    Basically, end user should not bother about the technicality of xlsb.

  18. But I tried something and editing macros also works in a shared file (any) !!! Cancel sharing / open the file in the archiver (winrar, 7-zip, etc ...) / folder xl, file vbaProject.bin -> extract file (for example to the Desktop) / share the excel file again / reopen the excel file with the archiver and insert the original vbaProject.bin --> VBA can be edited in the shared file ;)

  19. I have one knowledge about Personal.xlsb If I want to open 2 instances of Excel in Office2010, the second of them blocked Personal.xlsb because it is used by the first instance. Thus, either Personal must be checked "read-only" or Personal must be listed as shared. However, in both cases, macros cannot be edited. So this is a bit ill-conceived (MS) :(((

  20. I created personal macro for Pivot Table creation but when I run this on other files, it gives me runtime error. Any suggest to fix it ?
    Thanks

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)