How to Use Claude AI to Write Excel Macros and VBA Code

VBA (Visual Basic for Applications) macros can automate virtually anything in Excel — formatting reports, processing data, sending emails, generating files. The problem? Most people have never written a line of code. Claude AI solves this by generating complete, ready-to-run VBA code from a plain English description of what you want to happen.

This tutorial walks you through the full cycle: how to describe a task so Claude understands it, how to paste the code into Excel's VBA editor, how to run it safely, and — crucially — how to hand an error message back to Claude when something goes wrong. You will also find five copy-paste macro templates covering the most common Excel automation jobs.

Follow me on Instagram@sagnikteaches

No coding background required. If you can describe what you do manually in Excel, Claude can automate it.

Connect on LinkedInSagnik Bhattacharya Subscribe on YouTube@codingliquids

Prerequisites

• Excel 2016 or newer on Windows — the VBA editor is available on Mac too, but a few automation APIs (Outlook integration, Shell commands) are Windows-only. Tell Claude your platform if you are on Mac.
• A Claude account — free tier at claude.ai is enough for all the examples in this tutorial.
• Save as .xlsm before you start — Excel's standard .xlsx format strips all macro code on save. File → Save As → Excel Macro-Enabled Workbook (.xlsm) before pasting any VBA.

Why Claude for VBA?

Claude understands Excel's object model deeply. It knows the difference between ActiveSheet and ThisWorkbook.Sheets("Data"), it adds On Error GoTo handlers without being asked, and it writes self-documenting comments so you can read the code later. Other AI tools often produce syntactically correct but logically broken macros; Claude tends to ask clarifying questions or flag assumptions inline as code comments.

• Generates complete, runnable Sub and Function procedures
• Handles edge cases (empty rows, merged cells, protected sheets) when you describe them
• Fixes its own mistakes when you paste an error message back
• Explains every section so you learn as you go

The Prompt → Code → Paste Cycle

Every Claude VBA session follows the same five steps.

Step 1: Describe the task precisely

Vague prompts produce vague macros. Instead of "automate my report", write something like:

"Write a VBA macro that loops through all rows in column A of Sheet1. For each cell, if the value is 'Completed', colour the entire row green. If it is 'Pending', colour it yellow. If it is 'Overdue', colour it red. Data starts in row 2; row 1 is headers. Add error handling in case a cell is blank."

The more context you give — sheet names, column layout, starting row, edge cases — the better the output.

Step 2: Copy Claude's code

Claude will return a complete Sub with comments. Example output for the prompt above:

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

    On Error GoTo ErrorHandler

    Set ws = ThisWorkbook.Sheets("Sheet1")
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    For i = 2 To lastRow
        If IsEmpty(ws.Cells(i, 1)) Then GoTo NextRow
        Select Case ws.Cells(i, 1).Value
            Case "Completed"
                ws.Rows(i).Interior.Color = RGB(198, 239, 206)
            Case "Pending"
                ws.Rows(i).Interior.Color = RGB(255, 235, 156)
            Case "Overdue"
                ws.Rows(i).Interior.Color = RGB(255, 199, 206)
        End Select
NextRow:
    Next i

    MsgBox "Formatting complete!", vbInformation
    Exit Sub

ErrorHandler:
    MsgBox "Error " & Err.Number & ": " & Err.Description, vbCritical
End Sub

Step 3: Open the VBA Editor

1. Press Alt + F11 — the VBA Editor opens.
2. In the Project panel on the left, right-click your workbook name → Insert → Module.
3. Paste the code into the blank module window.

Step 4: Run the macro

1. Click anywhere inside the Sub procedure.
2. Press F5 to run it directly, or close the editor and use Alt + F8 to pick it from the Macros list.
3. Save the file again as .xlsm to preserve the code.

Step 5: Iterate with Claude

If Excel shows an error, copy the exact error message and line number, then send both back to Claude: "I got Runtime Error 9 on line 8. Here is the code: [paste]. The sheet tab is actually called 'Status Tracker'. Please fix it." Claude will correct the sheet name reference and return updated code instantly.

5 Ready-to-Use Macro Templates

Ask Claude to generate these by pasting the prompt shown. Adapt sheet names and column letters to match your own workbook.

Template 1: Auto-format a report header row

Prompt to Claude: "Write a VBA macro that on Sheet1 makes row 1 bold, sets the font to 12pt, adds a thick bottom border, fills the row with a dark blue background (hex #1F3864) with white text, and auto-fits all column widths."

Sub FormatReportHeaders()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1")

    With ws.Rows(1)
        .Font.Bold = True
        .Font.Size = 12
        .Font.Color = RGB(255, 255, 255)
        .Interior.Color = RGB(31, 56, 100)
        .Borders(xlEdgeBottom).LineStyle = xlContinuous
        .Borders(xlEdgeBottom).Weight = xlThick
    End With

    ws.Columns.AutoFit
    ws.Rows(1).RowHeight = 28

    MsgBox "Headers formatted.", vbInformation
End Sub

Beginner pitfall: If you have merged cells in row 1, AutoFit may not resize correctly. Ask Claude to "skip merged cells when auto-fitting".

Template 2: Delete all blank rows in a dataset

Prompt to Claude: "Write a VBA macro that deletes every row in Sheet1 where column A is completely empty. Data starts in row 2. Loop from the bottom up so row numbers don't shift during deletion."

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

    Set ws = ThisWorkbook.Sheets("Sheet1")
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    ' Loop from bottom to top to avoid row-shift errors
    For i = lastRow To 2 Step -1
        If IsEmpty(ws.Cells(i, 1).Value) Or ws.Cells(i, 1).Value = "" Then
            ws.Rows(i).Delete
        End If
    Next i

    MsgBox "Blank rows removed. " & lastRow - ws.Cells(ws.Rows.Count, "A").End(xlUp).Row & " rows deleted.", vbInformation
End Sub

Beginner pitfall: Looping top-to-bottom and deleting rows shifts all subsequent row numbers by one, causing the macro to skip rows. Always loop from the bottom up when deleting.

Template 3: Copy rows matching a criterion to a new sheet

Prompt to Claude: "Write a VBA macro that copies all rows from Sheet1 where column C equals 'Approved' to a new sheet called 'Approved Items'. Include the header row. If the sheet already exists, clear it first."

Sub CopyApprovedRows()
    Dim wsSource As Worksheet
    Dim wsDest As Worksheet
    Dim lastRow As Long
    Dim destRow As Long
    Dim i As Long

    Set wsSource = ThisWorkbook.Sheets("Sheet1")

    ' Create or clear destination sheet
    On Error Resume Next
    Set wsDest = ThisWorkbook.Sheets("Approved Items")
    On Error GoTo 0
    If wsDest Is Nothing Then
        Set wsDest = ThisWorkbook.Sheets.Add(After:=wsSource)
        wsDest.Name = "Approved Items"
    Else
        wsDest.Cells.Clear
    End If

    ' Copy header row
    wsSource.Rows(1).Copy Destination:=wsDest.Rows(1)
    destRow = 2

    lastRow = wsSource.Cells(wsSource.Rows.Count, "A").End(xlUp).Row
    For i = 2 To lastRow
        If wsSource.Cells(i, 3).Value = "Approved" Then
            wsSource.Rows(i).Copy Destination:=wsDest.Rows(destRow)
            destRow = destRow + 1
        End If
    Next i

    MsgBox destRow - 2 & " rows copied to 'Approved Items'.", vbInformation
End Sub

Template 4: Split data by unique value onto separate sheets

Prompt to Claude: "Write a VBA macro that reads all unique values in column B of Sheet1 (starting row 2) and creates a separate worksheet for each unique value, named after that value, containing all rows with that value plus the header row."

Sub SplitByDepartment()
    Dim wsSource As Worksheet
    Dim wsDest As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Dim dept As String
    Dim dict As Object

    Set wsSource = ThisWorkbook.Sheets("Sheet1")
    Set dict = CreateObject("Scripting.Dictionary")
    lastRow = wsSource.Cells(wsSource.Rows.Count, "A").End(xlUp).Row

    ' Collect unique department names
    For i = 2 To lastRow
        dept = CStr(wsSource.Cells(i, 2).Value)
        If dept <> "" And Not dict.Exists(dept) Then dict.Add dept, True
    Next i

    ' Create/clear a sheet per department and copy matching rows
    Dim key As Variant
    For Each key In dict.Keys
        On Error Resume Next
        Set wsDest = ThisWorkbook.Sheets(CStr(key))
        On Error GoTo 0
        If wsDest Is Nothing Then
            Set wsDest = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
            wsDest.Name = Left(CStr(key), 31) ' Sheet names max 31 chars
        Else
            wsDest.Cells.Clear
        End If

        wsSource.Rows(1).Copy Destination:=wsDest.Rows(1) ' Header
        Dim destRow As Long
        destRow = 2
        For i = 2 To lastRow
            If CStr(wsSource.Cells(i, 2).Value) = CStr(key) Then
                wsSource.Rows(i).Copy Destination:=wsDest.Rows(destRow)
                destRow = destRow + 1
            End If
        Next i
        Set wsDest = Nothing
    Next key

    MsgBox "Split complete. " & dict.Count & " sheets created.", vbInformation
End Sub

Beginner pitfall: Excel sheet names cannot exceed 31 characters and cannot contain / \ ? * [ ]. The template trims names to 31 chars; ask Claude to also strip illegal characters if your data might contain them.

Template 5: Draft Outlook emails from a row-by-row list

Prompt to Claude: "Write a VBA macro that reads Sheet1 where column A is recipient name, column B is email address, and column C is a custom message. For each row, create a draft Outlook email with subject 'Follow-up from Sagnik' and body combining the name and message. Leave them as drafts — don't send."

Sub DraftOutlookEmails()
    ' Requires reference: Tools → References → Microsoft Outlook XX.0 Object Library
    Dim ws As Worksheet
    Dim outlookApp As Object
    Dim mail As Object
    Dim lastRow As Long
    Dim i As Long

    Set ws = ThisWorkbook.Sheets("Sheet1")
    Set outlookApp = CreateObject("Outlook.Application")
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    For i = 2 To lastRow
        If ws.Cells(i, 2).Value = "" Then GoTo NextRow
        Set mail = outlookApp.CreateItem(0) ' olMailItem
        With mail
            .To = ws.Cells(i, 2).Value
            .Subject = "Follow-up from Sagnik"
            .Body = "Hi " & ws.Cells(i, 1).Value & "," & vbCrLf & vbCrLf & _
                    ws.Cells(i, 3).Value & vbCrLf & vbCrLf & "Best regards," & vbCrLf & "Sagnik"
            .Save ' Saves to Drafts — does NOT send
        End With
        Set mail = Nothing
NextRow:
    Next i

    MsgBox lastRow - 1 & " draft emails saved to Outlook Drafts.", vbInformation
End Sub

Beginner pitfall: This macro requires Outlook to be installed and the Outlook Object Library reference enabled (Tools → References in the VBA Editor). It is Windows-only; on Mac, ask Claude for an AppleScript version instead.

Tips for Better Prompts

1. Name the sheet exactly — Claude will use whatever name you give; if the tab is "Data Entry" (with a space), say so.
2. State the starting row — "data starts in row 2, row 1 is headers" prevents off-by-one errors.
3. Describe edge cases — "some cells in column C may be blank; skip those rows".
4. Ask for a progress indicator on long macros — "show a status bar message for every 100 rows processed".
5. Ask for a confirmation prompt — "before running, ask the user to confirm with a Yes/No dialog"; useful for destructive operations like row deletion.
6. Request a dry-run mode — "first run in read-only mode and print what would be deleted to the Immediate Window, then ask for confirmation before actually deleting".

How to Debug a Broken Macro with Claude

When Excel throws an error, copy three things and paste them into Claude:

1. The full error message — e.g. "Runtime Error 9: Subscript out of range".
2. The line number Excel highlighted (shown in yellow in the VBA editor after clicking Debug).
3. The complete macro code.

Then describe the actual sheet tab name and column layout. Claude will rewrite the broken section and explain what was wrong. You can also ask Claude to add debug MsgBox statements to print variable values at key points — a faster way to trace logic errors than reading the code line by line.

For errors that involve formulas inside a macro (e.g. WorksheetFunction.VLookup), see Claude AI to debug Excel formulas — the same diagnostic approach applies.

Safety Notes

• Test on a copy of your data first — macros that delete rows or overwrite cells cannot be undone with Ctrl+Z after the macro finishes.
• Read the code before running it — Claude adds comments; spend 60 seconds reading them.
• Never run macros from untrusted sources — VBA has full access to your file system and can delete files or send emails without your knowledge if misused.
• Save as .xlsm before and after — accidental Save As .xlsx silently strips all macro code.
• Re-restrict macro security after testing — if you enabled all macros to get started, go back to File → Options → Trust Center → Macro Settings and select "Disable all macros with notification" when done.

Troubleshooting

1. "Cannot run the macro" / macro blocked by security
   Excel's default setting disables all macros. Go to File → Options → Trust Center → Trust Center Settings → Macro Settings → select "Enable all macros" (testing only). Reset to "Disable all macros with notification" once you're done testing. Permanently, add your working folder to Trusted Locations instead of enabling all macros globally.
2. Runtime Error 9 "Subscript out of range"
   The sheet name in the code doesn't match the actual tab name — it is case-sensitive and space-sensitive. Open the VBA editor, find Sheets("SheetName"), and replace the name with the exact text shown on your tab. If the name contains spaces (e.g. "Sales Data"), both the tab and the code must match exactly.
3. Runtime Error 1004 "Application-defined or object-defined error"
   Usually means the macro tried to write to a protected sheet, or referenced a range that doesn't exist (e.g. a column letter that has no data). Check: is the sheet protected? (Review → Unprotect Sheet). Does the referenced column/range actually contain data?
4. Macro code disappears after saving and reopening
   The file was saved as .xlsx, which strips all VBA. File → Save As → Excel Macro-Enabled Workbook (.xlsm). You will need to re-paste the code if you didn't save a backup.
5. Security yellow bar appears every time you open the file
   Excel is warning you about macros in the file. Click "Enable Content" once, or add the folder to Trusted Locations (File → Options → Trust Center → Trusted Locations → Add new location) so files there run without prompting.

FAQ

Can Claude write VBA for Mac Excel?

Yes, most macros work on Mac. The exceptions are Windows-only APIs: Shell commands, SendKeys, and Outlook COM automation (Outlook is not on Mac). Tell Claude "I am on Mac" at the start of your prompt and it will avoid those APIs automatically.

Is there a complexity limit for macros Claude can generate?

No hard limit, but for very large automations (50+ steps, multiple sheets, file-system operations) break the request into smaller macros that call each other. Claude can also write a "main" Sub that calls helper Subs — ask for that structure explicitly.

Can I ask Claude to fix a broken macro?

Yes — paste the full code plus the exact error message and line number. Claude diagnoses and rewrites the problem section. For Runtime Error 9 always include your actual sheet tab name, since that is the most frequent cause.

Will Claude's VBA work in Excel 2019 vs Excel 365?

Most VBA works in both. Newer worksheet functions like FILTER, UNIQUE, and XLOOKUP referenced inside VBA need Excel 365. If you are on Excel 2019, tell Claude and it will use legacy equivalents (INDEX/MATCH, helper columns, etc.).

Do I need to enable macros every time I open the file?

Only if the file's folder is not in Excel's Trusted Locations. Go to File → Options → Trust Center → Trust Center Settings → Trusted Locations → Add new location, and point it at your working folder. Files there run macros without any prompt.

Sources

• Microsoft — Getting Started with VBA in Office
• Anthropic Help Centre — Use Claude for Excel
• Microsoft — Excel VBA Object Model reference

Related tutorials

• Excel Formulas & AI Hub — the canonical Excel + AI reference on this site
• Using Claude AI to Debug and Fix Excel Formulas
• How to Use Claude AI to Write Excel Formulas Instantly
• Automating Repetitive Excel Tasks with Copilot
• Run a Free Local AI in VS Code with Gemma 4 — for developers who want to extend automation beyond Excel