Excel VBA Terminology: Macro vs Module vs Procedure

 

Excel Visual Basic for Applications (VBA) is a powerful programming language. VBA allows you to automate tasks, create custom functions, and enhance spreadsheet functionality. If you’re starting with VBA, understanding key terms like Macro, Module, and Procedure is essential. These concepts are interconnected but serve different purposes.

In this tutorial, we will discuss Excel VBA terminology: Macro vs Module vs Procedure. We’ll break them down with explanations, examples, and practical tips to help you get started.

To follow along, you’ll need Excel (any version from 2007 onward) with the Developer tab enabled.

• If it’s not visible, go to the File tab >> select Options >> select Customize Ribbon >> select Developer on the right side

1. What is a Macro?

A Macro is the most user-friendly entry point into VBA. It’s essentially a recorded or written set of instructions that automates repetitive tasks in Excel. Macros can be created without deep programming knowledge by using Excel’s Macro Recorder. It captures your actions, like formatting cells or inserting formulas, and converts them into VBA code.

In practical terms, when people say “I recorded a macro” or “I wrote a macro,” they’re usually talking about a Sub procedure stored in a module. Excel’s Macro Recorder creates Sub procedures automatically.

Key Characteristics:

• Purpose: Automate tasks to save time. For example, a macro could sort data, apply filters, or generate reports with one click.
• Way to Create:
  • Recorded: Use the Macro Recorder for simple tasks.
  • Written: Edit or write code manually in the VBA editor for more complex logic.
• Scope: Macros are typically stored in workbooks and can be run from buttons, shortcuts, or the Macros dialog.
• Limitations: Recorded macros often include unnecessary code (e.g., selecting cells), and cell references can look complex, so they’re often cleaned up manually.

Macro ≠ VBA: VBA is a programming language. A macro is runnable automation built using VBA (or recorded into VBA).

Example: Creating and Running a Simple Macro

• Go to the Developer tab >> select Record Macro
• Name it ApplyFormat (no spaces) >> assign a shortcut if desired >> click OK

• Perform actions:
  • Select the headers
  • Go to the Home tab >> select Bold
  • Apply Fill color and Font color
• Stop recording: Go to the Developer tab >> click Stop Recording

Behind the scenes, Excel generates VBA code like this:

Sub ApplyFormat()
'
' ApplyFormat Macro
'
' Keyboard Shortcut: Ctrl+Shift+F
'
    Range("A1:G1").Select
    Selection.Font.Bold = True
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent6
        .TintAndShade = -0.249977111117893
        .PatternTintAndShade = 0
    End With
    With Selection.Font
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
    End With
End Sub

• This is a Sub (short for Subroutine), which is a type of procedure

Run the Macro:

• Go to the Developer tab >> select Macros
• Select ApplyFormat >> select Run
• Or press Ctrl+Shift+F

Now you can create and use a macro for quick automation without coding expertise.

2. What is a Procedure?

A Procedure is the most fundamental unit; it is a block of VBA code that performs a specific task. Think of it as a set of instructions that Excel follows when you run it. Procedures are where the actual logic lives; think of them as functions or methods in other languages.

Key Characteristics:

• Types:
  • Sub Procedure (Sub): Performs actions but doesn’t return a value. This is what most people mean by “macro” in the VBA context. It starts with Sub and ends with End Sub.
  • Function Procedure (Function): Performs calculations and returns a value. It starts with Function and ends with End Function. Useful for custom formulas in worksheets.
• Purpose: Creates reusable code. Procedures can include loops, conditions, variables, and interactions with Excel objects (like cells or charts).
• Scope: Can be public (accessible from anywhere) or private (limited to the module).
• Parameters: Can accept inputs (arguments) for flexibility.

Example: A Sub Procedure vs. a Function Procedure

• Go to the Developer tab >> select Visual Basic (or press Alt+F11)
• Click Insert >> select Module

Sub Procedure (like a Macro):

Sub ClearFormatting()
    Range("A1:G1").ClearFormats
    MsgBox "Formatting is cleared"
End Sub

• Run the code to clear the formatting, then display a message stating “Formatting is cleared”

Function Procedure:

Function Multiply(num1 As Integer, num2 As Integer) As Integer
    Multiply = num1 * num2
End Function

• Use it in a cell like =Multiply(E2,F2) to multiply values

Relationship to Macros: All macros are Sub procedures, but not all Sub procedures are recorded macros. You can write advanced Sub procedures that go beyond simple recordings.

Use procedures when you need structured, reusable code. Split complex tasks into multiple procedures for better organization.

3. What is a Module?

A Module is a container where we store one or more procedures. It is a file in the VBA editor where you write and organize your VBA code. It’s like a folder that organizes your code, making it easier to manage and reuse.

Types of Modules:

• Standard Module: Most common type, where you write general-purpose code.
  • These appear in your VBA Project Explorer as “Module1”, “Module2”, etc.
  • Open the VBA editor >> click Insert >> select Module.
• Workbook Module (ThisWorkbook): Holds code tied to workbook events (open, before close, etc.).
  • Event procedures that respond to actions like opening or closing a workbook
• Worksheet Module (Sheet1, Sheet2, etc): These are special modules attached to specific sheets.
  • They contain event procedures that respond to actions like clicking a sheet, and code runs when a sheet changes.
• Class Module: These modules are advanced modules used for creating custom objects, but you won’t need them when starting. This is used for object-oriented programming, like creating custom classes.
• UserForm Module: For designing custom dialog boxes.

Example: Working with Modules

• Go to the Developer tab >> select Visual Basic (or press Alt+F11)
• In the Project Explorer (left pane),
  • Right-click your workbook >> click Insert >> select Module

• Copy-paste a procedure into it:

Sub FormatSalesReport()
    Dim ws As Worksheet
    Set ws = ActiveSheet
    Dim lastRow As Long
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    ws.Range("A1:H1").Font.Bold = True
    ws.Range("A1:H" & lastRow).Borders.LineStyle = xlContinuous
    Dim i As Long
    For i = 2 To lastRow
        If ws.Cells(i, "H").Value > 100 Then ws.Cells(i, "H").Interior.Color = RGB(0, 255, 0)
    Next i
End Sub

• This VBA code formats the data and highlights values greater than 100 in column H
• Save the workbook
• Now, this procedure is available as a macro
• Press F5 or click Run

Relationship with Macros and Procedures:

• Modules hold procedures
• Macros are Sub procedures stored in modules
• A macro without a module? Not possible, because all VBA code lives inside some type of module.

When to Use: For any VBA project. Start with one module for small tasks; use multiple for larger ones to avoid clutter.

How They Fit Together: Macro → Procedure → Module

A Macro is a user-facing term for a Sub procedure. Procedures are the code blocks. Modules group procedures for organization.

Workflow Example:

• Record a macro → It creates a Sub procedure in a new module (often “Module1”)
• Edit the procedure to refine it
• Add more procedures to the same module or create new modules

Best Practices and Tips

• Naming Conventions: Use descriptive names like Sub CalculateTotalSales() instead of Sub Macro1(). Avoid spaces; use CamelCase.
• Error Handling: Add On Error Resume Next or On Error GoTo ErrorHandler in procedures to make them more robust.
• Security: Macros can run code, so enable them only from trusted sources (File >> Options >> Trust Center >> Macro Settings).
• Debugging: Use F8 to step through code in the VBA editor.
• Advanced Tip: Export modules (.bas files) to reuse code across workbooks.
• Common Pitfalls: Don’t confuse Excel macros with Office Scripts (JavaScript-based in newer Excel versions). VBA is more powerful for desktop automation.

Conclusion

By following this tutorial, you can understand the Excel VBA terminology: Macro vs Module vs Procedure. Understanding these terms will help you use VBA efficiently. It will help you navigate VBA documentation, communicate with other Excel users, and organize your code more effectively. Start by recording macros to see procedures in action, then organize them into modules as your projects grow. Practice by automating a simple task in your own spreadsheet, and you’ll quickly see the power of VBA.