Using ChatGPT to Write Excel VBA Macros

Getting Started: Your First AI-Generated VBA Macro

ChatGPT can generate complete, functional VBA macros from plain-English descriptions. To run a macro, open Excel, press Alt + F11 to open the VBA editor, insert a new module (Insert > Module), paste the code, and run it with F5. Your first prompt should be simple and testable — something like "Write a VBA macro that takes the selected range and changes the background color to light blue." This verifies that the generated code runs in your environment before tackling complex automation.

Always save your workbook as a macro-enabled file (.xlsm), not a regular .xlsx. Standard workbooks strip all VBA code on save. If you see a security warning bar below the ribbon, click "Enable Content" to allow macros to run. For ChatGPT-generated code, start in a test copy of your workbook until you are confident the macro behaves as expected.

Productivity Macros: Real Examples You Can Use Today

Macro 1: Auto-Format a Data Table

Prompt: "Write a VBA macro that takes the current selection, applies bold to the header row, adds bottom borders to all data cells, auto-fits column widths, and freezes the top row."

Generated code:

Sub FormatDataTable()
    Dim rng As Range
    Set rng = Selection
    
    ' Header row formatting
    With rng.Rows(1)
        .Font.Bold = True
        .Interior.Color = RGB(68, 114, 196)
        .Font.Color = RGB(255, 255, 255)
    End With
    
    ' Add borders to all cells
    With rng.Borders
        .LineStyle = xlContinuous
        .Weight = xlThin
        .Color = RGB(180, 180, 180)
    End With
    
    ' Auto-fit columns
    rng.EntireColumn.AutoFit
    
    ' Freeze top row
    ActiveWindow.SplitRow = 1
    ActiveWindow.FreezePanes = True
    
    MsgBox "Table formatted successfully!", vbInformation
End Sub

This macro is safe, reversible (use Ctrl+Z after running), and demonstrates essential VBA patterns: Range objects, With blocks for efficient property setting, and user feedback via MsgBox.

Macro 2: Export Each Worksheet as a Separate PDF

Prompt: "Write a VBA macro that exports every worksheet in the active workbook as a separate PDF file, saved to the same folder as the workbook, with filenames in the format 'WorkbookName_SheetName.pdf'. Skip hidden sheets."

Generated code:

Sub ExportSheetsAsPDF()
    Dim ws As Worksheet
    Dim folderPath As String
    Dim pdfName As String
    
    folderPath = ThisWorkbook.Path & ""
    
    For Each ws In ThisWorkbook.Worksheets
        If ws.Visible = xlSheetVisible Then
            pdfName = folderPath & _
                      Replace(ThisWorkbook.Name, ".xlsm", "") & _
                      "_" & ws.Name & ".pdf"
            
            ws.ExportAsFixedFormat _
                Type:=xlTypePDF, _
                Filename:=pdfName, _
                Quality:=xlQualityStandard, _
                IncludeDocProperties:=True
            
            Debug.Print "Exported: " & pdfName
        End If
    Next ws
    
    MsgBox "Export complete! Check the Immediate Window (Ctrl+G) for details.", vbInformation
End Sub

This macro uses ExportAsFixedFormat, the same engine Excel uses for File > Save As > PDF. It loops through the Worksheets collection, skipping hidden sheets, and builds filenames dynamically with string concatenation. The Debug.Print statements let you verify output in the Immediate Window.

Macro 3: Send Email with Outlook from Excel Data

Prompt: "Write a VBA macro that reads email addresses from column A (starting at A2), subject lines from column B, and body text from column C. For each row, send an email via Outlook. Stop when column A is empty. Add a 2-second delay between sends."

Generated code:

Sub SendBulkEmails()
    Dim OutApp As Object
    Dim OutMail As Object
    Dim i As Long
    Dim lastRow As Long
    
    On Error Resume Next
    Set OutApp = GetObject(, "Outlook.Application")
    If OutApp Is Nothing Then
        Set OutApp = CreateObject("Outlook.Application")
    End If
    On Error GoTo 0
    
    lastRow = Cells(Rows.Count, 1).End(xlUp).Row
    
    For i = 2 To lastRow
        If Cells(i, 1).Value = "" Then Exit For
        
        Set OutMail = OutApp.CreateItem(0)
        With OutMail
            .To = Cells(i, 1).Value
            .Subject = Cells(i, 2).Value
            .Body = Cells(i, 3).Value
            .Send
        End With
        
        Application.Wait Now + TimeValue("00:00:02")
    Next i
    
    Set OutMail = Nothing
    Set OutApp = Nothing
    MsgBox "All emails sent!", vbInformation
End Sub

The Application.Wait line enforces a 2-second delay between sends, which helps avoid triggering Outlook's rate-limiting or spam filters when sending bulk emails. The error handling with On Error Resume Next and the Nothing check gracefully handles the case where Outlook is not already running.

Prompt Engineering for Better VBA Code

The quality of ChatGPT-generated VBA depends heavily on how you frame the request. Use this structured approach:

Define the trigger: "Run when a button is clicked" vs "Run automatically when the workbook opens" vs "Run on the selected range."
Specify data locations: "Column A has customer names starting at A2, Column B has email addresses."
Describe the exact outcome: "For each row, create a new worksheet named after the customer and copy their data into it."
State error handling requirements: "Skip rows with missing email addresses instead of showing an error."
Mention constraints: "The workbook has 50,000 rows, so optimize for speed" or "This must work in Excel 2016 on Windows."

Pro tip: For complex macros, break the request into smaller pieces. First ask for the main loop structure, then ask for each subroutine separately. Ask ChatGPT to add comments as it writes; this makes debugging significantly easier.

Debugging AI-Generated VBA Code

AI-generated macros rarely work perfectly on the first run. Here is a systematic debugging workflow:

Compile first: In the VBA editor, go to Debug > Compile VBAProject. This catches syntax errors, undeclared variables, and missing references before runtime.
Add Option Explicit: If the generated code is missing Option Explicit at the top of the module, add it. This forces variable declaration and catches typos in variable names.
Use breakpoints: Click in the left margin next to a line to set a breakpoint, then press F8 to step through line by line. Hover over variables to inspect their current values.
Add Debug.Print statements: Insert Debug.Print "Row: " & i & " Value: " & Cells(i,1).Value at strategic points to trace execution flow.
Paste errors back to ChatGPT: Copy the exact error message and line number, then ask "I got 'Runtime error 1004: Application-defined or object-defined error' on line 12. Here is the full code. What is causing this?" The AI can often self-correct when given specific error feedback.

Security Considerations for AI-Generated Macros

VBA macros have full access to your file system, registry, and network. Treat AI-generated code with the same caution as code downloaded from the internet:

Never run macros that delete files or modify system settings without reviewing every line first. Look for keywords: Kill, RmDir, DeleteFile, Shell, WScript.Shell, RegWrite.
Avoid macros that send data over the network unless you fully understand the destination URL and the data being transmitted. Watch for XMLHTTP, WinHttp.WinHttpRequest, MSXML2.ServerXMLHTTP.
Check for auto-execution triggers: Macros named Auto_Open or Workbook_Open run automatically when the workbook opens. Review these carefully.
Use digital signatures: For macros you plan to distribute, sign them with a digital certificate (File > Info > Protect Workbook > Add a Digital Signature).
Corporate environments: If you are on a managed device, your IT department may have Group Policy settings that block unsigned macros. Check with them before investing time in macro-based solutions.

Building a Reusable VBA Toolkit

Save your most reliable AI-generated macros in a Personal Macro Workbook (Personal.xlsb). This hidden workbook loads every time you open Excel, making your macros available across all workbooks. To create it, record any simple macro and choose "Personal Macro Workbook" as the storage location. Then open the VBA editor, find the VBAProject (PERSONAL.XLSB), and add modules containing your curated macros.

Organize macros into modules by function: one for formatting, one for data export, one for email automation, and one for worksheet management. Add a custom ribbon tab (File > Options > Customize Ribbon) with buttons mapped to your most-used macros for one-click access. Over time, this toolkit replaces dozens of manual steps and ensures consistency across your Excel projects.

Have questions or found an error in this article? Submit Feedback