Excel Macros & VBA for Accountants: Automate Repetitive Tasks & Build Finance Tools

Macros vs VBA: Core Concepts

A Macro is a saved sequence of Excel commands that can be replayed on demand. Think of it as a recording of everything you do in Excel — every click, every keystroke, every format change — stored and repeatable. The Developer tab is the gateway to all macro and VBA functionality.

Enabling the Developer Tab

File → Options → Customize Ribbon → tick Developer → OK. The Developer tab now appears in the ribbon with buttons for Record Macro, Visual Basic editor, Insert Form Controls, and Macro Security.

Recording Your First Macro — Month-End Close Task

Let's record a macro that formats a trial balance worksheet the same way every month:

1. Developer → Record Macro. Give it a name (no spaces): FormatTrialBalance. Assign a shortcut key: Ctrl+Shift+T. Store in: This Workbook. Click OK.
2. Now perform the actions you want to record: Select row 1, make it bold, change fill to dark blue, change font colour to white. Set column widths. Freeze the top row. Apply number format to the debit/credit columns.
3. Developer → Stop Recording.
4. To run: press Ctrl+Shift+T, or Developer → Macros → select FormatTrialBalance → Run.

Running Macros: Three Methods

Method	Best For
Keyboard shortcut (Ctrl+Shift+letter)	Personal power users, frequent repetitive tasks
Form Control Button on worksheet	Shared workbooks, non-technical users
Developer → Macros → Run	One-off runs, testing
Shape or image with assigned macro	Polished finance tools for management

Macro Security Settings

Developer → Macro Security. For accounting workbooks shared internally: use "Disable all macros with notification" (user clicks Enable Macros when opening). Never use "Enable all macros" as it creates a security risk. For trusted internal tools, place them in a trusted folder (File → Options → Trust Center → Trusted Locations).

VBA Basics for Accountants

Open the VBA Editor with Alt+F11. You'll see the Project Explorer (left) listing all open workbooks, and the Code window (right) where you write code.

Sub vs Function

' Sub: performs actions, returns nothing
' Run from a macro button or shortcut
Sub GenerateMonthlyReport()
    ' Code here
End Sub

' Function: calculates and returns a value
' Can be used as a worksheet formula
Function DaysOverdue(DueDate As Date) As Integer
    DaysOverdue = DateDiff("d", DueDate, Now())
    If DaysOverdue < 0 Then DaysOverdue = 0
End Function

Variables — The Core Data Types for Accountants

Sub VariableExamples()
    Dim invoiceCount  As Integer      ' Whole numbers up to 32,767
    Dim invoiceAmount As Double       ' Decimal numbers (monetary values)
    Dim vendorName    As String       ' Text
    Dim invoiceDate   As Date         ' Dates
    Dim isOverdue     As Boolean      ' True/False
    Dim ws            As Worksheet    ' Reference to a worksheet
    Dim rng           As Range        ' Reference to a cell range
    Dim wb            As Workbook     ' Reference to a workbook

    invoiceCount  = 150
    invoiceAmount = 45250.75
    vendorName    = "Infosys Limited"
    invoiceDate   = #3/31/2025#
    isOverdue     = (Now() > invoiceDate)

    Set ws  = ThisWorkbook.Worksheets("InvoiceData")
    Set rng = ws.Range("A1:D100")
End Sub

Loops — Essential for Processing Accounting Data

' For Next: known number of iterations (12 months)
Sub ProcessAllMonths()
    Dim i As Integer
    For i = 1 To 12
        ' Process month i
        Debug.Print "Processing month: " & i
    Next i
End Sub

' For Each: iterate over a collection (all worksheets)
Sub FormatAllSheets()
    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
        ws.Range("A1").Font.Bold = True
    Next ws
End Sub

' Do While: loop until condition is false (find last row)
Sub FindLastEntry()
    Dim lastRow As Long
    lastRow = 2
    Do While Cells(lastRow, 1).Value <> ""
        lastRow = lastRow + 1
    Loop
    MsgBox "Data ends at row: " & (lastRow - 1)
End Sub

If-Then-Else, With Statement, and Error Handling

' If-Then-Else: classify AR aging
Function AgingBucket(daysOverdue As Integer) As String
    If daysOverdue <= 0 Then
        AgingBucket = "Current"
    ElseIf daysOverdue <= 30 Then
        AgingBucket = "1-30 Days"
    ElseIf daysOverdue <= 60 Then
        AgingBucket = "31-60 Days"
    ElseIf daysOverdue <= 90 Then
        AgingBucket = "61-90 Days"
    Else
        AgingBucket = "90+ Days"
    End If
End Function

' With statement: apply multiple properties to same object
Sub FormatReportHeader()
    With ActiveSheet.Range("A1")
        .Value         = "Monthly P&L Report - March 2025"
        .Font.Bold     = True
        .Font.Size     = 14
        .Font.Color    = RGB(0, 32, 96)
        .Interior.Color = RGB(0, 112, 192)
    End With
End Sub

' Error handling: essential for production VBA tools
Sub SafeMacro()
    On Error GoTo ErrorHandler
    ' ... code that might fail ...
    Exit Sub
ErrorHandler:
    MsgBox "Error " & Err.Number & ": " & Err.Description, vbCritical
    ' Log error or clean up
End Sub

5 Practical VBA Accounting Tools with Code

Tool 1: Monthly Report Generator

Sub GenerateMonthlyReports()
    Dim ws       As Worksheet
    Dim months   As Variant
    Dim i        As Integer
    months = Array("Jan","Feb","Mar","Apr","May","Jun", _
                   "Jul","Aug","Sep","Oct","Nov","Dec")

    For i = 0 To 11
        ' Add new sheet for each month if it doesn't exist
        On Error Resume Next
        Set ws = ThisWorkbook.Worksheets(months(i))
        On Error GoTo 0
        If ws Is Nothing Then
            Set ws = ThisWorkbook.Worksheets.Add(After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count))
            ws.Name = months(i)
        End If

        ' Copy template from "Template" sheet
        ThisWorkbook.Worksheets("Template").Cells.Copy ws.Cells(1, 1)

        ' Update header with month name
        ws.Range("B2").Value = "Report for: " & months(i) & " 2025"

        Set ws = Nothing
    Next i
    MsgBox "All 12 monthly report sheets created.", vbInformation
End Sub

Tool 2: Bank Reconciliation Macro — Highlight Matched/Unmatched

Sub BankReconciliation()
    Dim wsLedger  As Worksheet
    Dim wsBank    As Worksheet
    Dim ledgerAmt As Double
    Dim bankAmt   As Double
    Dim i         As Long
    Dim j         As Long
    Dim lastRowL  As Long
    Dim lastRowB  As Long
    Dim matched   As Boolean

    Set wsLedger = ThisWorkbook.Worksheets("GL_Ledger")
    Set wsBank   = ThisWorkbook.Worksheets("Bank_Statement")

    lastRowL = wsLedger.Cells(wsLedger.Rows.Count, 1).End(xlUp).Row
    lastRowB = wsBank.Cells(wsBank.Rows.Count, 1).End(xlUp).Row

    ' Clear previous highlighting
    wsLedger.Range("A2:D" & lastRowL).Interior.ColorIndex = xlNone
    wsBank.Range("A2:D" & lastRowB).Interior.ColorIndex = xlNone

    ' Match on Amount (column C) — highlight green if matched
    For i = 2 To lastRowL
        ledgerAmt = wsLedger.Cells(i, 3).Value
        matched   = False
        For j = 2 To lastRowB
            bankAmt = wsBank.Cells(j, 3).Value
            If Abs(ledgerAmt - bankAmt) < 0.01 Then
                wsLedger.Rows(i).Interior.Color = RGB(198, 239, 206) ' Green
                wsBank.Rows(j).Interior.Color   = RGB(198, 239, 206)
                matched = True
                Exit For
            End If
        Next j
        If Not matched Then
            wsLedger.Rows(i).Interior.Color = RGB(255, 199, 206) ' Red = unmatched
        End If
    Next i
    MsgBox "Reconciliation complete. Green = matched, Red = unmatched.", vbInformation
End Sub

Tool 3: AR Aging Report Generator

Sub GenerateAgingReport()
    Dim wsData   As Worksheet
    Dim wsReport As Worksheet
    Dim i        As Long
    Dim lastRow  As Long
    Dim dueDate  As Date
    Dim daysOver As Long
    Dim bucket   As String

    Set wsData   = ThisWorkbook.Worksheets("AR_Data")
    Set wsReport = ThisWorkbook.Worksheets("Aging_Report")

    lastRow = wsData.Cells(wsData.Rows.Count, 1).End(xlUp).Row
    wsReport.Cells.Clear

    ' Write headers
    wsReport.Range("A1:F1").Value = Array("Customer", "Invoice No", "Amount", "Due Date", "Days Overdue", "Aging Bucket")
    wsReport.Range("A1:F1").Font.Bold = True

    ' Process each invoice
    For i = 2 To lastRow
        dueDate  = wsData.Cells(i, 4).Value
        daysOver = DateDiff("d", dueDate, Now())
        If daysOver < 0 Then daysOver = 0

        Select Case daysOver
            Case 0          : bucket = "Current"
            Case 1 To 30    : bucket = "1-30 Days"
            Case 31 To 60   : bucket = "31-60 Days"
            Case 61 To 90   : bucket = "61-90 Days"
            Case Else       : bucket = "90+ Days"
        End Select

        wsReport.Cells(i, 1).Value = wsData.Cells(i, 1).Value  ' Customer
        wsReport.Cells(i, 2).Value = wsData.Cells(i, 2).Value  ' Invoice No
        wsReport.Cells(i, 3).Value = wsData.Cells(i, 3).Value  ' Amount
        wsReport.Cells(i, 4).Value = dueDate
        wsReport.Cells(i, 5).Value = daysOver
        wsReport.Cells(i, 6).Value = bucket

        ' Colour-code by bucket
        If daysOver = 0 Then
            wsReport.Rows(i).Interior.Color = RGB(198, 239, 206)
        ElseIf daysOver <= 60 Then
            wsReport.Rows(i).Interior.Color = RGB(255, 235, 156)
        Else
            wsReport.Rows(i).Interior.Color = RGB(255, 199, 206)
        End If
    Next i
    MsgBox "AR Aging Report generated on sheet 'Aging_Report'.", vbInformation
End Sub

Tool 4: GST Return Formatter

Sub CleanGSTR2A()
    Dim ws      As Worksheet
    Dim lastRow As Long
    Dim i       As Long

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

    Application.ScreenUpdating = False  ' Speed up processing

    For i = lastRow To 2 Step -1  ' Loop bottom-up for row deletion
        ' Delete blank rows
        If ws.Cells(i, 1).Value = "" And ws.Cells(i, 2).Value = "" Then
            ws.Rows(i).Delete
        End If
    Next i

    ' Re-find last row after deletions
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row

    ' Trim and clean all text in columns A to H
    Dim cell As Range
    For Each cell In ws.Range("A2:H" & lastRow)
        If cell.Value <> "" Then
            cell.Value = Trim(cell.Value)
        End If
    Next cell

    ' Fix date format in column D (assume DD-MM-YYYY text)
    For i = 2 To lastRow
        If ws.Cells(i, 4).Value <> "" Then
            On Error Resume Next
            ws.Cells(i, 4).Value = CDate(ws.Cells(i, 4).Value)
            ws.Cells(i, 4).NumberFormat = "DD/MM/YYYY"
            On Error GoTo 0
        End If
    Next i

    Application.ScreenUpdating = True
    MsgBox "GSTR-2A cleaning complete. " & (lastRow - 1) & " records processed.", vbInformation
End Sub

Tool 5: Email Dispatcher — Send Reports via Outlook

' Requires: Tools > References > Microsoft Outlook Object Library
Sub SendMonthlyReport()
    Dim outlookApp  As Object
    Dim mailItem    As Object
    Dim reportPath  As String

    reportPath = ThisWorkbook.FullName  ' Send current workbook

    Set outlookApp = CreateObject("Outlook.Application")
    Set mailItem   = outlookApp.CreateItem(0)  ' 0 = olMailItem

    With mailItem
        .To      = "[email protected]"
        .CC      = "[email protected]"
        .Subject = "Monthly Finance Report — " & Format(Now(), "MMMM YYYY")
        .Body    = "Dear Sir/Ma'am," & vbCrLf & vbCrLf & _
                   "Please find attached the monthly finance report." & vbCrLf & vbCrLf & _
                   "Best regards," & vbCrLf & "Finance Team"
        .Attachments.Add reportPath
        .Display  ' Use .Send to send directly without preview
    End With

    Set mailItem   = Nothing
    Set outlookApp = Nothing
End Sub

Debugging Tools, File Formats & Security

Three Essential Debugging Tools

F8 (Step Through): Execute code one line at a time. The yellow highlighted line shows where execution is paused. Hover over a variable to see its current value in a tooltip. This is the most important debugging tool — step through your accounting macro line by line to see exactly what it's doing.

Immediate Window (Ctrl+G): Type expressions to evaluate them instantly. Type ?Cells(2,3).Value to see what's in cell C2. Type ?Format(Now(),"DD/MM/YYYY") to test a date format. Use Debug.Print variableName in your code to output variable values to the Immediate Window during execution.

Watch Window: Debug → Add Watch. Monitor specific variables throughout execution. Useful for loop counters and running totals in large accounting macros to verify calculations at each iteration.

Macro-Enabled Files (.xlsm) and Security

Always save VBA-containing workbooks as .xlsm (Excel Macro-Enabled Workbook). Saving as .xlsx permanently deletes all VBA code without warning. When distributing internal finance tools:

• Document what the macros do in a "ReadMe" sheet
• Use digital certificates for workbooks distributed company-wide (prevents "Unknown Publisher" warnings)
• Never hardcode file paths — use ThisWorkbook.Path and relative paths instead
• Password-protect the VBA project (Tools → VBAProject Properties → Protection) to prevent unauthorised modification

When NOT to Use VBA (Use Power Query or Python Instead)

VBA is powerful but not always the right tool. Knowing when to use alternatives is a mark of a mature Excel practitioner:

Task	Better Tool	Why
Data cleaning and transformation	Power Query	Visual, auditable, auto-refreshing without code
Combining multiple CSV/Excel files	Power Query	Folder connector handles this natively
Large dataset analysis (>500K rows)	Python (pandas)	VBA is slow on large data; Python is 10-100x faster
Machine learning or forecasting	Python or R	VBA has no statistical libraries
Interactive dashboards for management	Power BI	Better visuals, sharing, and mobile access
UserForms and data entry validation	VBA	VBA excels at this — it's the right choice here
Outlook email automation	VBA	Native Outlook integration via object library
Complex conditional formatting	VBA	More powerful than the UI for dynamic conditions

VBA Learning Resources

Recommended progression path for accountants learning VBA:

1. Chandoo.org — India-focused Excel and VBA tutorials with accounting examples, free blog content, and paid courses
2. Excel VBA Programming For Dummies (John Walkenbach) — the classic reference book, covers all fundamentals systematically
3. Microsoft Learn (free) — official VBA documentation at learn.microsoft.com
4. MrExcel.com forum — ask specific VBA questions, very active community
5. YouTube: Excel Campus (Jon Acampora) — high-quality accounting-specific VBA tutorials