Excel VBA Macro Functions: What They Are & How to Use

VBA & macros · Updated November 22, 2026

Excel VBA macro functions are custom procedures written in Visual Basic for Applications (VBA) that you can call from a worksheet cell (like a built-in function) or use to automate repetitive tasks. The two main types are Sub procedures (macros that perform actions) and Function procedures (user-defined functions, or UDFs, that return values). This article explains both, how to create them, and how to decide which one you need.

What Exactly Is a VBA Macro Function?

In Excel, a “macro” usually refers to a Sub procedure—a block of code that runs when you trigger it (via a button, keyboard shortcut, or the Macros dialog). A “function” in VBA is a separate type of procedure that returns a value and can be used directly in worksheet formulas, just like SUM or VLOOKUP. For example, you could write a function called SalesTax that calculates tax on an amount, then type =SalesTax(A1) in a cell. Both are stored in the Visual Basic Editor (VBE) inside a module.

Key difference: a Sub performs actions (like formatting cells or copying data) but does not return a value; a Function returns a value but cannot directly modify the worksheet (it can only return a result). This distinction matters when you decide what to build.

How to Open the VBA Editor and Insert a Module

To write any VBA code, you first open the VBE. Press Alt+F11 on Windows (or Fn+Option+F11 on Mac). In the VBE, go to Insert > Module to create a new module where you can paste or type your code. Then close the VBE and return to Excel.

Before running any macro, always save a copy of your workbook (File > Save As) because macros cannot be undone with Ctrl+Z. If you plan to keep the macro, save the file as a macro-enabled workbook (.xlsm).

How to Create a Simple Sub Macro

A Sub macro is the most common type. Here’s an example that formats the selected cells with a yellow fill and bold font:

Sub FormatHighlight()
    ' Declare a range variable
    Dim rng As Range
    Set rng = Selection
    
    ' Apply formatting
    rng.Interior.Color = vbYellow
    rng.Font.Bold = True
End Sub

To run it, press Alt+F8, select FormatHighlight, and click Run. Or assign it to a button or shape. This macro acts on whatever cells you have selected when you run it.

How to Create a User-Defined Function (UDF)

If you want a custom function that you can call from a cell, create a Function procedure. Here’s a simple function that adds a 10% sales tax:

Function SalesTax(amount As Double) As Double
    ' Returns amount plus 10% tax
    SalesTax = amount * 1.1
End Function

After adding this to a module, you can type =SalesTax(100) in any cell and it returns 110. UDFs appear in the Insert Function dialog under the “User Defined” category. Note: UDFs cannot change the worksheet—they only return a value. To modify cells, you need a Sub.

Choosing Between a Sub and a Function

Use a Sub when you need to automate actions: formatting, copying, deleting, or interacting with other Office apps. Use a Function when you need a custom calculation that you want to use in a formula, especially if you’ll reuse it across many cells. Functions are also useful for array formulas (e.g., returning multiple values) but that’s advanced.

Common mistakes: trying to change a cell from a function (won’t work), or forgetting to declare the return value (the function name itself must be assigned a value). Also, UDFs are volatile if they use Volatile or certain functions like NOW(), which recalculate often—avoid if not needed.

Common Problems and How to Fix Them

Here are frequent issues beginners hit:

  • Macros disabled: Excel shows a security warning. Enable macros by clicking “Enable Content” if you trust the file, or set the macro security level to “Disable all macros with notification” (File > Options > Trust Center > Trust Center Settings).
  • “Compile error: Sub or Function not defined”: Usually a typo in the name or the code is in the wrong module. Check spelling and ensure the code is in a standard module (not a sheet module) unless it’s an event macro.
  • Function returns #VALUE! error: Often because the function expects a number but gets text, or the function name conflicts with a built-in name. Rename your function to something unique.
  • Macro runs slowly: Turn off screen updating with Application.ScreenUpdating = False at the start and set it back to True at the end.

Always test on a copy of your data first, because macros can modify many cells at once and there is no undo.

Security and Safety When Using VBA

VBA macros can contain malicious code, so only run macros from sources you trust. Excel’s default security settings block macros from unknown sources. If you download a workbook with macros, be cautious. To protect your own macros, you can password-protect the VBA project (Tools > VBAProject Properties > Protection) but that is not strong encryption.

Also, remember that macros are stored in the workbook file. If you share the file, recipients may see the code. For sensitive logic, consider an add-in (.xlam) but that still exposes code to anyone who opens the VBE.

Alternatives to VBA Macros

If you don’t want to write code, Excel offers built-in automation:

  • Excel Tables and Formulas: Many tasks can be done with formulas like SUMIFS, XLOOKUP (Excel 2021/365), or dynamic arrays.
  • Power Query: For data cleaning and transformation, Power Query (Data > Get Data) provides a no-code interface.
  • Conditional Formatting: For visual changes based on rules, no macro needed.
  • Office Scripts: In Microsoft 365, Office Scripts (Automate tab) allow you to record and run scripts in the web version, but they are limited compared to VBA.

VBA is still powerful for complex automation that these tools can’t handle, but learning basic formulas first can reduce the need for macros.

Frequently Asked Questions

Can I use a VBA function in Excel for the web?

No. Excel for the web does not run VBA. It supports Office Scripts instead. If you need to share a workbook with UDFs, users must open it in the desktop Excel app.

How do I make a macro run automatically when a workbook opens?

You can name a Sub macro Auto_Open or use the Workbook_Open event in the ThisWorkbook module. For example, in the ThisWorkbook code pane, add:

Private Sub Workbook_Open()
    MsgBox "Welcome"
End Sub

This runs when the file opens, but be careful—it will run for anyone opening the file.

Why does my UDF not recalculate when I change input cells?

UDFs recalculate when their input cells change, but if they depend on cells not passed as arguments, they may not. You can force recalculation by adding Application.Volatile at the top of the function, but use it sparingly because it slows down calculation.

Can I create a macro that works on multiple sheets?

Yes, you can loop through sheets. For example, to bold all cells in column A on every sheet:

Sub BoldAllSheets()
    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
        ws.Range("A:A").Font.Bold = True
    Next ws
End Sub

Just make sure you save a copy before running.

What is the difference between a macro and an add-in?

A macro is code stored in a workbook. An add-in (.xlam) is a special workbook that loads in the background and makes its functions available across all workbooks. Add-ins are useful for distributing custom functions to other users.

Related

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.