Where Do Excel Macros Get Stored
Explore where Excel macros live, from workbook modules to Personal Macro Workbook and add ins. Learn how to move, export, and secure VBA code for reliable automation across files.
Excel macros storage is a type of macro storage that houses VBA code in workbooks, the Personal Macro Workbook, or an add-in. This determines availability and portability of the macros.
Why understanding where do Excel macros get stored matters
In Excel, understanding where your macros live is not just a bookkeeping exercise; it directly impacts how portable, shareable, and secure your automation will be. According to XLS Library, the storage location determines whether a macro runs only in a single workbook or everywhere you work. This knowledge helps you plan distribution, maintenance, and future updates, especially in team environments. The choice also affects how you back up your VBA projects and how easily you can migrate them to another computer or account. By knowing where do Excel macros get stored, you can design a workflow that minimizes duplication and reduces the risk of legacy code becoming inaccessible when a file is moved or renamed.
More practically, the storage decision influences how macros are loaded when Excel starts, how they are shared with colleagues, and how updates are rolled out. If you rely on a macro to streamline a critical process, you want a storage location that aligns with your workflow, security, and deployment needs. This is the foundation of robust automation that scales across teams and devices.
The three main storage locations for Excel macros
There are three primary storage locations for VBA macros in Excel: This Workbook, the Personal Macro Workbook, and Excel Add-Ins (.xlam). Each location has distinct implications for accessibility, portability, and security.
This Workbook storage
Macros stored in This Workbook travel with the file. They reside in a module within the workbook or in the ThisWorkbook object itself. This makes the macros ideal for project specific automation but requires the file to be open to run. Save the workbook in a macro enabled format such as .xlsm or .xlsb to preserve the code. This location ensures the macro is tied to that file and won’t automatically appear in other files unless copied.
Personal Macro Workbook (PERSONAL.XLSB)
The Personal Macro Workbook is a hidden global store for macros you want available across all workbooks on your device. It is not tied to any single file, so it is ideal for utilities you reuse daily. When you record a macro and choose Personal Macro Workbook, Excel creates or updates PERSONAL.XLSB in your user profile. This approach makes macros portable across projects without duplicating code, so you can run common routines anywhere Excel is installed on your machine.
Add-Ins (.xlam)
Excel Add-Ins hold reusable tools you want to share in a controlled way. Macros stored in an add-in load automatically when Excel starts and are accessible from any workbook. This is the recommended path for distribution in teams and organizations because it centralizes management and updates. Add-Ins also make it easier to version and audit macro code, while keeping workbooks lean and portable.
People Also Ask
What is the Personal Macro Workbook and where is it stored?
The Personal Macro Workbook, named PERSONAL.XLSB, is a hidden workbook that stores macros you want available across all Excel workbooks. It lives in your user profile and does not open like a regular file unless you enable its visibility. This makes it ideal for universal utilities.
The Personal Macro Workbook is a hidden file called PERSONAL.XLSB that holds macros you want accessible in every Excel file. It sits in your user profile and only shows up when needed.
Can I store a macro only in a workbook?
Yes. Macros saved this way reside in the specific workbook and travel with that file. You must distribute the workbook itself if you want others to use the macros, and the file must be macro enabled (.xlsm or .xlsb).
Yes. Macros can stay in the workbook they were created in, but others need that workbook to use them.
How do I move a macro from a workbook to PERSONAL.XLSB?
Open the Visual Basic Editor, export the module containing the macro, then open PERSONAL.XLSB and import the module. Save and restart Excel to ensure the macro loads from the Personal Macro Workbook.
Export the module from the workbook, import it into PERSONAL.XLSB, and save. Restart Excel to load it from the personal store.
Can macros in an Add-In run in any workbook?
Yes. Macros stored in an Add-In (.xlam) are loaded when Excel starts and are available in all workbooks. They are a stable distribution method for shared functionality across teams.
Add-Ins load when Excel starts, making their macros available in any workbook.
How can I share macros with teammates efficiently?
A common approach is to place macros in an Add-In and distribute the .xlam file, or provide a compiled module and document its usage. This keeps code centralized and easier to update.
Share your macros by using an Add-In so everyone gets the same tools from a single source.
Is there a security risk in storing macros in Personal Macro Workbook?
Macros in any location can pose security risks if sourced from untrusted files. Ensure macro security settings are appropriate and sign code when possible. Regular backups and using trusted locations help mitigate risk.
Yes, like any macro, those in PERSONAL.XLSB should come from trusted sources and be secured properly.
The Essentials
- Identify where your macro currently resides.
- Store commonly used macros in an add-in for easy reuse.
- Use Personal Macro Workbook to share across workbooks.
- Export modules before moving storage locations.