Claude Code for VBA Macro Development (2026)

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

  • Compile error: Variable not defined: You forgot Dim for a variable. This happens when Option Explicit is active (which it should be). Add the missing declaration with proper type.
  • Runtime error 1004 “Select method of Range class failed”: You are using .Select on a range in an inactive sheet. Remove Select/Activate calls and reference objects directly: wsSource.Range("A1").Value not Sheets("Sales").Select: Range("A1").Select.
  • Macros disabled on open: The workbook must be saved as .xlsm (macro-enabled). File > Save As > Excel Macro-Enabled Workbook. Also check Trust Center settings: File > Options > Trust Center > Macro Settings.

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. Legacy Excel workbooks with thousands of lines of VBA are particularly good candidates for Claude Code assistance because the language’s verbose syntax makes manual refactoring tedious and error-prone.

Try it: Paste your error into our Error Diagnostic for an instant fix.