Excel VBA Module: How It Works, Types, and When to Use One

An Excel VBA module is a container for Visual Basic for Applications code, such as macros, helper procedures, and functions. In desktop Excel for Microsoft 365 on Windows, create a standard module by pressing Alt+F11 to open the Visual Basic Editor, then choosing Insert > Module; put a procedure such as a Sub in it to make a macro available to run.

What an Excel VBA module is

VBA is Excel’s built-in programming language for automating tasks and interacting with workbook objects. A module is one place in a VBA project where code is organized. It is comparable to a code file: it can hold one or many procedures, and those procedures can refer to worksheets, ranges, charts, and other parts of the workbook.

A module is not itself a macro, a worksheet, or a separate Excel file. A macro is usually a procedure beginning with Sub and ending with End Sub. The module stores that procedure and may also store related code. For example, one module could contain macros for importing data and helper routines those macros call.

Every macro-enabled workbook has a VBA project. In the Visual Basic Editor (VBE), the Project Explorer shows that project and its components. A standard module appears under a folder named Modules, usually with a name such as Module1. The module is saved as part of the workbook’s VBA project.

How to add a standard module

Open the workbook in the desktop version of Excel. If the Developer tab is missing, use File > Options > Customize Ribbon, select Developer in the main tabs list, and choose OK.
Press Alt+F11, or choose Developer > Visual Basic, to open the VBE.
If the Project Explorer is not visible, press Ctrl+R in the VBE or select View > Project Explorer.
Select the project for the workbook where the code should live. Choose Insert > Module. Excel adds a standard module, commonly named Module1.
Click in the module’s code window and type or paste the VBA procedure. Save the workbook in a macro-enabled format if it needs to retain VBA.

On Mac, VBA is available in desktop Excel, but ribbon and keyboard details can vary with the version and keyboard settings. Use Developer > Visual Basic to open the editor if the Developer tab is shown; on some keyboards, the function-key shortcut also requires Fn. Excel for the web does not provide the VBE and cannot create, edit, or run VBA macros. Open the file in desktop Excel for those tasks.

A small example of code in a module

This example writes a short message into cell A1 of the currently active worksheet. It demonstrates the basic shape of a macro: a named procedure, a declared variable, a reference to an Excel object, and an action.

Option Explicit

Sub WriteGreeting()
    Dim targetSheet As Worksheet

    ' Write a sample message to the active worksheet.
    Set targetSheet = ActiveSheet
    targetSheet.Range("A1").Value = "Hello from VBA"
End Sub

To try it, first save a copy of the workbook. In the VBE, click inside WriteGreeting and press F5, or return to Excel and use Developer > Macros, select WriteGreeting, and choose Run. The macro changes A1 on whichever sheet is active when it runs. That is convenient for a demonstration, but production code should identify its intended sheet explicitly if the active sheet could be wrong.

Macros cannot be undone with Ctrl+Z. Excel generally clears the normal undo history when VBA changes cells, so save a separate copy before running unfamiliar code and inspect what it will change.

Types of modules and where code belongs

“Module” can refer to several different components in a VBA project. Choosing the right location helps code behave as expected, especially when it responds to events.

Standard module: The general-purpose place for reusable macros and procedures that can be called from elsewhere. It is usually the right choice for a macro that you intend to run from the Macros dialog or assign to a button.
Worksheet module: Each worksheet has its own code module. Put worksheet-specific event procedures here, such as code that responds when a cell changes on that sheet.
ThisWorkbook module: Holds code associated with workbook-level events, such as opening or closing the workbook. It is not the same as a standard module.
Class module: Defines a custom object with its own properties and methods. It is useful for more structured VBA projects, but unnecessary for many simple macros.
UserForm: A visual form component with controls and associated code, used to build a custom dialog or input interface.

A frequent beginner mistake is placing an event procedure in a standard module. For example, a worksheet change event must be placed in the relevant worksheet’s code module to respond to edits on that sheet. A normal macro that you start yourself generally belongs in a standard module instead.

How procedures in modules work

A standard module can contain several procedures. A Sub performs actions and does not return a value in the same way a function does. A Function returns a value and can sometimes be called from a worksheet formula, provided it is written for that purpose and does not try to perform unsupported worksheet changes as a cell function.

Procedures can call other procedures. That makes it possible to split a larger task into a main macro and smaller helper routines, rather than putting every instruction into one long block. Descriptive names such as ImportMonthlyData are easier to understand later than generic names such as Macro2.

Visibility affects which code can call a procedure. A public procedure in a standard module is generally available to other code in the project and may appear in Excel’s Macros dialog when it meets the dialog’s requirements. A procedure marked Private is limited to its module. Variables declared at the top of a module can be shared by procedures in that module, while variables declared inside a procedure normally exist only while that procedure runs. Use the narrowest scope that suits the task to reduce unintended interactions.

Adding Option Explicit at the top of a module requires variables to be declared before use. This catches many spelling mistakes and accidental variable names. It is a useful default in new code, although it does not guarantee that a macro is logically correct or safe.

Running, assigning, and saving a macro

There are several ways to run a macro stored in a standard module. In Excel for Windows, Developer > Macros opens the macro list; select a compatible public macro and choose Run. You can also run a procedure from the VBE with the cursor inside it and F5. For repeated use, a macro can be assigned to a worksheet button or another supported interface, which lets users start it without opening the editor.

To retain VBA, save the workbook as an Excel Macro-Enabled Workbook (.xlsm) using File > Save As and the file type list. A regular .xlsx workbook does not store VBA code; Excel warns that VBA will be removed if you save a workbook containing macros in a macro-free format. An Excel Binary Workbook (.xlsb) can also store VBA, but its binary format may be less convenient for sharing or inspecting than .xlsm.

A standard module belongs to the project in which it was created. If you want a macro available across workbooks on one computer, Excel’s Personal Macro Workbook, commonly named PERSONAL.XLSB, is one option. It opens in the background when Excel starts for that user. For code that needs to be distributed and maintained across users, an Excel add-in may be more appropriate. These options involve different installation and trust considerations, so they are not interchangeable with simply saving a macro in one workbook.

Security and safe use

VBA can make substantial changes: it can edit or delete workbook data, create files, and interact with other Office features. Treat a macro-enabled file from an unknown sender as executable code rather than as a passive spreadsheet. Do not enable macros just because a document asks you to; first consider whether you trust the source and whether the macro is necessary.

Excel’s macro controls are available in File > Options > Trust Center > Trust Center Settings > Macro Settings on Windows. The exact choices can be affected by an organization’s policies. Avoid setting Excel to enable all macros as a general convenience. If a business relies on macros, its administrator may use digitally signed code or trusted locations under an appropriate security policy. A digital signature can help identify a publisher and whether code has changed, but it does not prove that the code is harmless.

Before running code, review its scope: which workbook and sheets does it access, what cells or files can it change, and does it use external connections? Test on a copy with representative data. If a macro is shared, explain what it does and what permissions or file locations it needs.

Common problems and practical fixes

The Macros dialog is empty: Check that the procedure is a public, parameter-free Sub in a standard module, that the correct workbook is open, and that the file has not been saved as .xlsx.
“Sub or Function not defined” appears: Check the procedure name and spelling, confirm that the code is in a project the calling code can access, and make sure the procedure is not private when it needs to be called from outside its module.
The code does nothing or changes the wrong sheet: Look for references such as ActiveSheet or Selection. They depend on what is active at runtime. Use explicit workbook and worksheet references when the target must not vary.
Excel blocks the macro: Check the file’s source and the Trust Center or organization policy. Do not lower security settings indiscriminately to get an unknown macro running.
Code fails after a worksheet is renamed: Review references that use a worksheet’s tab name. Some VBA references use the sheet’s code name instead, but either way, understand which name the code expects before renaming or modifying it.

When an error occurs, use the VBE’s debugging tools to identify the line that failed. A compile error points to invalid or unresolved code; a runtime error happens while the procedure is executing. Read the error message and inspect the highlighted line before making changes. Small, focused procedures and meaningful variable names make these problems easier to diagnose.

When a VBA module is the right tool

A module is useful when a task involves a repeatable sequence that ordinary worksheet features do not handle conveniently. Examples include applying a consistent format to many sheets, preparing a report from workbook data, or prompting for input before updating a workbook. VBA can also connect actions across sheets and automate parts of a desktop Excel workflow.

It is not always the best solution. For simple calculations, normal worksheet formulas are usually easier for other people to inspect. Power Query can be a better fit for repeatable data import and transformation, while Office Scripts may suit some cloud-based automation scenarios. Excel for the web cannot execute VBA, so a VBA-dependent workflow requires desktop Excel. The right choice depends on who must run the process, where the workbook is used, and how the solution will be maintained.

Frequently Asked Questions

Is a VBA module the same as a macro?
No. A module is a container for VBA code. A macro is commonly a runnable Sub procedure stored in a module, although code modules can also contain helper procedures, functions, declarations, and event code.

Can I use VBA modules in Excel for the web?
No. Excel for the web does not let you create, edit, or run VBA. Use desktop Excel to work with VBA; a workbook may be opened in the web app, but its VBA macros will not run there.

Why did my VBA code disappear after saving?
The workbook may have been saved as .xlsx, a format that does not retain VBA. Save a workbook that must keep its macros as .xlsm or another VBA-supporting format, and keep a backup before changing formats.

Where should I put a macro I want to run from Excel?
For an ordinary manually run macro, put a public, parameter-free Sub in a standard module. Put worksheet or workbook event procedures in the corresponding worksheet or ThisWorkbook module instead.

Can I undo a VBA macro with Ctrl+Z?
Usually not. Excel’s normal undo history does not provide a reliable way to reverse changes made by a VBA macro. Save a copy before running code, especially if it edits or deletes data.

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.