Claude Code for VBA Macro Development 2026

Written by Michael Lip · Solo founder of Zovo · $400K+ on Upwork · 100% JSS Join 50+ builders · More at zovo.one

The Workflow

Use Claude Code to generate, debug, and refactor VBA macros for Microsoft Office applications. This covers Excel automation, Access database operations, and Outlook email processing, with proper error handling and testable module structure.

Expected time: 10-20 minutes per macro Prerequisites: Microsoft Office (Excel, Access, or Outlook), Claude Code installed, VBA editor access (Alt+F11)

Setup

1. Create a VBA Project Directory

mkdir -p ~/vba-macros/{excel,access,outlook,tests}

Store VBA source files as .bas and .cls files for version control outside Office.

2. Configure CLAUDE.md for VBA Work

# CLAUDE.md

## VBA Code Standards
- Always include Option Explicit at module top
- Error handling: On Error GoTo ErrHandler pattern in every public sub/function
- Variables: Hungarian notation (str, lng, rng, ws, wb prefixes)
- Max 60 lines per procedure — split into helper functions
- All public subs must have a header comment with purpose and parameters
- Use early binding with explicit references where possible
- Never use Select/Activate — work with objects directly

3. Verify Export Setup

# Install a VBA export tool (optional, for CI integration)
pip install oletools
# Verify
olevba --version
# Expected output:
# olevba 0.60.2

Usage Example

Generate an Excel macro that processes a data range, applies conditional formatting, and exports results to a new workbook:

Option Explicit

'=============================================================
' Module: DataProcessor
' Purpose: Process sales data, apply formatting, export summary
' Author: Generated by Claude Code
' Date: 2026-04-21
'=============================================================

Private Const MODULE_NAME As String = "DataProcessor"

Public Sub ProcessSalesData()
    Dim wsSource As Worksheet
    Dim wbExport As Workbook
    Dim rngData As Range
    Dim lngLastRow As Long
    Dim lngLastCol As Long

    On Error GoTo ErrHandler

    Set wsSource = ThisWorkbook.Sheets("Sales")
    lngLastRow = wsSource.Cells(wsSource.Rows.Count, "A").End(xlUp).Row
    lngLastCol = wsSource.Cells(1, wsSource.Columns.Count).End(xlToLeft).Column

    If lngLastRow < 2 Then
        MsgBox "No data found in Sales sheet.", vbExclamation
        Exit Sub
    End If

    Set rngData = wsSource.Range(wsSource.Cells(1, 1), _
                                 wsSource.Cells(lngLastRow, lngLastCol))

    ' Apply conditional formatting
    Call ApplyConditionalFormatting(rngData)

    ' Generate summary
    Call ExportSummary(wsSource, lngLastRow)

    MsgBox "Processing complete. " & (lngLastRow - 1) & " rows processed.", vbInformation

    Exit Sub
ErrHandler:
    MsgBox "Error in " & MODULE_NAME & ".ProcessSalesData: " & _
           Err.Number & " - " & Err.Description, vbCritical
End Sub

Private Sub ApplyConditionalFormatting(ByRef rngData As Range)
    Dim rngRevenue As Range
    Dim lngRevenueCol As Long

    On Error GoTo ErrHandler

    ' Find Revenue column
    lngRevenueCol = FindColumnByHeader(rngData, "Revenue")
    If lngRevenueCol = 0 Then Exit Sub

    Set rngRevenue = rngData.Offset(1, lngRevenueCol - 1) _
                           .Resize(rngData.Rows.Count - 1, 1)

    ' Clear existing rules
    rngRevenue.FormatConditions.Delete

    ' Red for below target
    With rngRevenue.FormatConditions.Add(Type:=xlCellValue, _
         Operator:=xlLess, Formula1:="=10000")
        .Interior.Color = RGB(255, 199, 206)
        .Font.Color = RGB(156, 0, 6)
    End With

    ' Green for above target
    With rngRevenue.FormatConditions.Add(Type:=xlCellValue, _
         Operator:=xlGreaterEqual, Formula1:="=50000")
        .Interior.Color = RGB(198, 239, 206)
        .Font.Color = RGB(0, 97, 0)
    End With

    Exit Sub
ErrHandler:
    Debug.Print "Error in ApplyConditionalFormatting: " & Err.Description
End Sub

Private Sub ExportSummary(ByRef wsSource As Worksheet, _
                          ByVal lngLastRow As Long)
    Dim wbExport As Workbook
    Dim wsExport As Worksheet
    Dim strPath As String

    On Error GoTo ErrHandler

    Set wbExport = Workbooks.Add(xlWBATWorksheet)
    Set wsExport = wbExport.Sheets(1)
    wsExport.Name = "Summary"

    ' Write headers
    wsExport.Range("A1").Value = "Metric"
    wsExport.Range("B1").Value = "Value"
    wsExport.Range("A1:B1").Font.Bold = True

    ' Calculate metrics
    wsExport.Range("A2").Value = "Total Rows"
    wsExport.Range("B2").Value = lngLastRow - 1

    wsExport.Range("A3").Value = "Total Revenue"
    wsExport.Range("B3").Value = Application.WorksheetFunction.Sum( _
        wsSource.Range("C2:C" & lngLastRow))
    wsExport.Range("B3").NumberFormat = "$#,##0.00"

    wsExport.Range("A4").Value = "Average Revenue"
    wsExport.Range("B4").Value = Application.WorksheetFunction.Average( _
        wsSource.Range("C2:C" & lngLastRow))
    wsExport.Range("B4").NumberFormat = "$#,##0.00"

    wsExport.Columns("A:B").AutoFit

    ' Save export
    strPath = ThisWorkbook.Path & "\Sales_Summary_" & _
              Format(Now, "yyyymmdd_HHmmss") & ".xlsx"
    wbExport.SaveAs strPath, xlOpenXMLWorkbook
    wbExport.Close SaveChanges:=False

    Debug.Print "Summary exported to: " & strPath

    Exit Sub
ErrHandler:
    If Not wbExport Is Nothing Then wbExport.Close SaveChanges:=False
    Debug.Print "Error in ExportSummary: " & Err.Description
End Sub

Private Function FindColumnByHeader(ByRef rngData As Range, _
                                     ByVal strHeader As String) As Long
    Dim cell As Range

    For Each cell In rngData.Rows(1).Cells
        If UCase(Trim(cell.Value)) = UCase(strHeader) Then
            FindColumnByHeader = cell.Column - rngData.Column + 1
            Exit Function
        End If
    Next cell

    FindColumnByHeader = 0
End Function

Export VBA modules for version control:

# Extract VBA from workbook using oletools
olevba --decode sales-processor.xlsm > ~/vba-macros/excel/DataProcessor.bas

# Or use a simple export script
cat > ~/vba-macros/export-vba.vbs << 'EOF'
Dim wb As Workbook
Set wb = Workbooks.Open("C:\path\to\workbook.xlsm")
Dim comp As VBComponent
For Each comp In wb.VBProject.VBComponents
    comp.Export "C:\vba-macros\" & comp.Name & ".bas"
Next comp
wb.Close False
EOF

Common Issues

Why This Matters

VBA remains embedded in millions of enterprise workflows. Claude Code generates production-quality macros with error handling in minutes instead of hours of manual debugging in the VBA editor.