How to Create a VBA Macro in Excel (Beginner Guide for Accountants)

If you are comfortable with formulas, pivot tables, and cleaning up ugly exports, you are already closer to Excel VBA than you think. VBA is not reserved for developers. For accountants, it is often just a way to turn a repeated set of clicks into a button you can run again next week.

This guide shows how to create a VBA macro in Excel without making the process feel technical. You will learn what VBA is in plain English, how to open the editor with Alt + F11, and how to build two practical macros: one that formats headers and amounts, and another that highlights transactions over $1,000. If you want more copy-paste examples after this, the free VBA cheat sheet is a good next stop.

What VBA Is, in Plain English

VBA stands for Visual Basic for Applications. In practice, that means Excel has a built-in automation language that can follow instructions you write once and run whenever you need them. Think of a macro as a saved procedure: format this row, loop through these transactions, highlight anything above a threshold, then stop.

For accounting work, VBA is useful because so much of the job is repeatable. Bank imports arrive in the same rough shape. Review sheets need the same checks. Month-end reports need the same formatting. A macro helps Excel do those steps for you instead of relying on memory and manual effort.

How to Open the VBA Editor

On Windows Excel, press Alt + F11. That opens the VBA Editor. If nothing happens, check that you are in the desktop version of Excel rather than the browser version.

1. Open the workbook where you want the macro to live.
2. Press Alt + F11.
3. In the editor, click Insert and then Module.
4. Paste your code into the blank window and press F5 to run it.

That is the whole workflow for most beginner macros. You do not need to build an app. You open the editor, insert a module, paste code, and run it on a workbook you already know well.

Your First Simple Macro

Let's start with something useful and low-risk. The macro below assumes row 1 contains headers and column C contains amounts. It makes the header row bold, shades it lightly, and applies Excel's Accounting number format to the amount column.

Sub FormatHeadersAndAmounts()
    Dim ws As Worksheet
    Dim lastRow As Long

    Set ws = ActiveSheet
    lastRow = ws.Cells(ws.Rows.Count, "C").End(xlUp).Row

    ws.Rows(1).Font.Bold = True
    ws.Rows(1).Interior.Color = RGB(217, 225, 242)
    ws.Columns("A:C").AutoFit

    ws.Range("C2:C" & lastRow).NumberFormat = _
        "_(* #,##0.00_);_(* (#,##0.00);_(* ""-""??_);_(@_)"
End Sub

Run it on a copy of a transactions sheet and you will immediately see why VBA is worth learning. A task that normally takes a minute or two becomes a repeatable shortcut. Once you are comfortable with this pattern, you can change the column letters or add extra steps without starting from scratch.

Practical Example: Highlight Transactions Over $1,000

Now let's move from formatting to review work. This macro scans the amount column and highlights any transaction above $1,000. It is a simple example, but it mirrors real accounting use cases such as spotting unusual expenses, large reimbursements, or exceptions worth a second look.

Sub HighlightLargeTransactions()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim cell As Range

    Set ws = ActiveSheet
    lastRow = ws.Cells(ws.Rows.Count, "C").End(xlUp).Row

    For Each cell In ws.Range("C2:C" & lastRow)
        If IsNumeric(cell.Value) Then
            If cell.Value > 1000 Then
                cell.Interior.Color = RGB(255, 235, 156)
                cell.Font.Bold = True
            End If
        End If
    Next cell
End Sub

If your amounts are in a different column, replace every `"C"` with the correct column letter. That is one of the first useful lessons in VBA: most beginner edits are small and readable. You are not rebuilding the whole macro. You are changing one assumption to match your sheet.

When you are ready for more advanced automation, these related guides show what the same approach looks like in bigger monthly workflows: automating bank reconciliation and automating multi-sheet consolidation.

Common Beginner Mistakes

• Running a macro on the wrong sheet. Test on a copy first until you trust the code.
• Assuming the amount column is always the same. If your workbook changes often, note which parts of the code refer to row numbers or column letters.
• Forgetting to save as a macro-enabled workbook. Use .xlsm if you want the macro to stay with the file.
• Expecting perfection on the first run. VBA is learned by testing one small change at a time, not by writing a huge script from memory.

When to Use Recorded Macros vs. Edited VBA

Recorded macros are useful when you do not know the VBA syntax yet. Excel can watch you perform a sequence of actions and turn those clicks into code. That makes recording a good way to create a first draft or learn the names Excel uses behind the scenes.

Edited VBA is better when you want flexibility. Recorded macros tend to be literal. They might select cells one by one or assume the data always sits in the exact same location. Once you start editing the code directly, you can loop through variable-length data, handle exceptions, and make the macro usable month after month. A practical path for beginners is to record simple formatting steps, then tidy the code manually as you become more confident.

If you want more examples before building your own from scratch, take a look at five VBA macros accountants use all the time. Seeing a few more real examples makes the editor feel much less intimidating.