How to save VBA in a in or as Macro

Saving VBA Code: Understanding Modules, Workbooks, and Add-ins

Learn the essential methods for saving your VBA macros in Excel, from embedding them directly in a workbook to creating reusable add-ins.

VBA (Visual Basic for Applications) is a powerful tool for automating tasks in Microsoft Excel. Once you've written a macro, the next crucial step is to save it correctly so you can use it again. The method you choose depends on how you intend to use the macro: whether it's for a specific workbook, for all your workbooks, or to be shared with others. This article will guide you through the different ways to save your VBA code, ensuring your hard work is preserved and accessible.

Understanding Where VBA Code Resides

Before saving, it's important to understand the structure of VBA projects within Excel. Your VBA code is stored in modules, which are then contained within a VBA project. This project is typically associated with an Excel workbook. There are three primary locations where you can save your VBA code:

flowchart TD
    A[VBA Project] --> B[Workbook Object (e.g., ThisWorkbook)]
    A --> C[Standard Module]
    A --> D[Class Module]
    A --> E[UserForm]
    A --> F[Sheet Object (e.g., Sheet1)]
    B --> G["Event Procedures (e.g., Workbook_Open)"]
    F --> H["Event Procedures (e.g., Worksheet_Change)"]
    C --> I[General Procedures (Sub/Function)]
    D --> J[Custom Objects/Classes]
    E --> K[Custom Dialogs]

Structure of a VBA Project in Excel

Saving VBA in a Specific Workbook

The most common way to save VBA code is directly within the Excel workbook where it will be used. This makes the macro available only when that specific workbook is open. This is ideal for macros that are tailored to a particular report, data set, or task within a single file.

1. Step 1: Open the VBA Editor

Press Alt + F11 to open the Visual Basic for Applications editor (VBE).

2. Step 2: Insert a Module

In the VBE, go to Insert > Module. This creates a new standard module where you can write your code. If your code is already in a sheet object or ThisWorkbook, you can skip this step.

3. Step 3: Write or Paste Your Code

Enter your VBA code into the module window.

4. Step 4: Save the Workbook

Go back to Excel (you can close the VBE or just switch windows). When saving the Excel file, you must save it as an 'Excel Macro-Enabled Workbook' (.xlsm extension). If you save it as a regular .xlsx file, your VBA code will be lost.

Sub MyWorkbookSpecificMacro()
    MsgBox "This macro is saved within the current workbook."
End Sub

Example of a macro saved in a standard module within a workbook.

Saving VBA for All Workbooks (Personal Macro Workbook)

If you have macros you use frequently across many different Excel files, saving them in your Personal Macro Workbook (PERSONAL.XLSB) is the best approach. This workbook is a hidden file that opens automatically every time you start Excel, making its macros available to any open workbook.

1. Step 1: Record a Dummy Macro (if PERSONAL.XLSB doesn't exist)

If you've never used the Personal Macro Workbook before, it won't exist. To create it, go to the Developer tab in Excel, click Record Macro. In the 'Store macro in:' dropdown, select 'Personal Macro Workbook'. Click 'OK', then immediately click Stop Recording. This action creates and saves PERSONAL.XLSB.

2. Step 2: Open the VBA Editor

Press Alt + F11 to open the VBE.

3. Step 3: Locate PERSONAL.XLSB

In the Project Explorer pane (usually on the left), expand VBAProject (PERSONAL.XLSB). You'll find a Modules folder within it.

4. Step 4: Insert or Move Your Code

You can either insert a new module (Insert > Module) into PERSONAL.XLSB and paste your code, or you can drag and drop an existing module from another workbook's project into the PERSONAL.XLSB project.

5. Step 5: Save PERSONAL.XLSB

Close the VBE. When you close Excel, it will prompt you to save changes to PERSONAL.XLSB. Click 'Save'. From now on, these macros will be available in all your Excel sessions.

Saving VBA as an Excel Add-in (.xlam)

For more advanced scenarios, especially when you want to distribute your macros to other users or create a robust set of custom functions, saving your VBA project as an Excel Add-in (.xlam) is the professional choice. Add-ins are hidden workbooks that load automatically when enabled, providing custom functions, tools, and macros without exposing the underlying code directly to the user.

1. Step 1: Prepare Your Workbook

Start with a regular macro-enabled workbook (.xlsm) containing all the VBA code you want in your add-in. Ensure all code is in standard modules, class modules, or userforms, and not directly in sheet objects or ThisWorkbook unless it's specifically for add-in events.

2. Step 2: Save As an Add-in

Go to File > Save As. In the 'Save as type:' dropdown, select 'Excel Add-In (*.xlam)'. Excel will automatically suggest saving it in the default Add-Ins folder, which is usually the best location.

3. Step 3: Install the Add-in

To use the add-in, go to File > Options > Add-Ins. In the 'Manage:' dropdown at the bottom, select 'Excel Add-ins' and click 'Go...'. In the Add-Ins dialog box, check the box next to your newly created add-in and click 'OK'.

' In a standard module within your .xlam file
Public Function MyCustomFunction(inputVal As Double) As Double
    MyCustomFunction = inputVal * 1.05 ' Example: Add 5%
End Function

Example of a custom function within an Excel Add-in, callable from any worksheet.