Image by Editor

Artificial intelligence (AI) can help Excel users automate their work. Tasks that once required deep familiarity with Visual Basic for Applications (VBA) can now be done with some prompting. Whether you want to clean a dataset, build a custom function, or automate an entire report, AI tools can draft the macro for you. But the convenience comes with the responsibility to use the code safely. Working with AI-generated code requires understanding both its capabilities and limitations. In this article, we will show how to generate VBA code safely with AI. Let’s go from prompt to macro.

Understanding What AI Can and Cannot Do with VBA

AI can translate a plain description of a task into code that Excel understands. It can create procedures, custom functions, event-based macros, and logic involving loops, conditions, or worksheet manipulation.

However, AI tools can’t directly read your workbook unless you give them the context of it. They cannot see your sheet names, your data layout, or the specific quirks of your workflow. As a result, AI-generated code often works as a starting point, not a finished product. It still requires human review, adjustments, and testing. The safest mindset is to treat AI as a coding assistant rather than a code executor.

Writing a Clear Prompt

An unclear, incomplete prompt leads to ambiguous code. A contextual, concise, structured prompt will return macros that match your intent. Before writing your request, decide exactly what the macro should do. List all the requirements, then describe the steps as if you were explaining them to a colleague. The more specific your description, the safer the output.

You can use a three-part prompt structure that works consistently well: the context, the inputs, and the expected actions. 

• The context tells the AI where the macro will run, such as in a sheet called Report or a table named Sales
• The inputs clarify what data the macro will read
• Finally, the expected actions describe what you hope to happen when you run the macro

Even though you are not writing the macro yourself, structuring your instructions this way gives AI the information it needs to produce clean code.

Open any AI tool (ChatGPT, Claude, Grok, Gemini, etc.) and use the following prompt to generate VBA code.

“Create a VBA macro for Excel that loops through column A (starting at row 2) and copies any cells containing the word ‘Urgent’ to a new sheet called ‘Urgent Orders’. Include error handling.”

VBA Code:

Sub CopyUrgentOrders()
    On Error GoTo ErrHandler
    
    Dim wsSource As Worksheet
    Dim wsTarget As Worksheet
    Dim lastRow As Long
    Dim nextRow As Long
    Dim i As Long
    
    '--- Set source sheet (active sheet)
    Set wsSource = ActiveSheet
    
    '--- Create or clear "Urgent Orders" sheet
    On Error Resume Next
    Set wsTarget = ThisWorkbook.Worksheets("Urgent Orders")
    On Error GoTo ErrHandler
    
    If wsTarget Is Nothing Then
        Set wsTarget = ThisWorkbook.Worksheets.Add
        wsTarget.Name = "Urgent Orders"
    Else
        wsTarget.Cells.ClearContents
    End If
    
    '--- Add header
    wsSource.Rows(1).Copy wsTarget.Rows(1)
    
    '--- Determine last row in column A
    lastRow = wsSource.Cells(wsSource.Rows.Count, "A").End(xlUp).Row
    nextRow = 2
    
    '--- Loop through column A starting from row 2
    For i = 2 To lastRow
        If InStr(1, wsSource.Cells(i, "A").Value, "Urgent", vbTextCompare) > 0 Then
            wsSource.Rows(i).Copy wsTarget.Rows(nextRow)
            nextRow = nextRow + 1
        End If
    Next i
    
    MsgBox "Urgent orders copied successfully!", vbInformation
    Exit Sub

'===== Error Handler =====
ErrHandler:
    MsgBox "An error occurred: " & Err.Description, vbCritical
End Sub

Review AI-Generated VBA Before You Run It

AI code often looks clean and well-structured with proper comments and indentation, yet it should never be executed immediately. Reviewing it is an essential safety step. First, check for the correct sheet names and ranges, because AI tools often guess default values. Second, verify that the macro avoids dangerous actions such as selecting cells unnecessarily or clearing data in the wrong place. Third, look for infinite loops, improper variable declarations, and references to objects that do not exist in your workbook. The goal is not to rewrite the code but to ensure every action matches your intent.

Based on our prompt, the AI-generated VBA code assumed the Urgent values are in column A, which is not true. We mentioned column A to loop through but didn’t specify the column that contains the word “Urgent.”

wsSource.Cells(i, "A").Value

This code looks at the value in column A of each row.

Now, you can change the column from A to D. Either update it yourself, or regenerate the code via another prompt.

wsSource.Cells(i, "D").Value

Running VBA without a review can overwrite data, delete sheets, or create hard-to-diagnose errors. Especially in Excel, you can’t undo a VBA action. A brief visual inspection prevents most problems. Don’t be intimidated; you don’t need to be an expert in VBA to recognize obvious issues—you only need to confirm that the operations correspond to the workflow you described.

Testing Macro in a Safe Environment

Once you run VBA, it can’t be undone. So, before using the macro in a production workbook, run it in a test copy. Saving a backup and disabling auto-run events is a simple but effective precaution. Create a copy of the workbook and save it with a test name. Use a small dataset to see whether the logic works, whether the right rows are copied, and whether the formatting is preserved.

Let’s save a copy like Test_VBA.xlsm and run the macro there first. Keep the original untouched.

Go to the Developer tab >> select Visual Basic. Click Insert >> select Module to insert a new module. Now paste the code there. This makes it easier to remove or disable later.

VBA Code:

Sub CopyUrgentOrders()
    On Error GoTo ErrHandler
    
    Dim wsSource As Worksheet
    Dim wsTarget As Worksheet
    Dim lastRow As Long
    Dim nextRow As Long
    Dim i As Long
    
    '--- Set source sheet (active sheet)
    Set wsSource = ActiveSheet
    
    '--- Create or clear "Urgent Orders" sheet
    On Error Resume Next
    Set wsTarget = ThisWorkbook.Worksheets("Urgent Orders")
    On Error GoTo ErrHandler
    
    If wsTarget Is Nothing Then
        Set wsTarget = ThisWorkbook.Worksheets.Add
        wsTarget.Name = "Urgent Orders"
    Else
        wsTarget.Cells.ClearContents
    End If
    
    '--- Add header
    wsSource.Rows(1).Copy wsTarget.Rows(1)
    
    '--- Determine last row in column D
    lastRow = wsSource.Cells(wsSource.Rows.Count, "D").End(xlUp).Row
    nextRow = 2
    
    '--- Loop through column D starting from row 2
    For i = 2 To lastRow
        If InStr(1, wsSource.Cells(i, "D").Value, "Urgent", vbTextCompare) > 0 Then
            wsSource.Rows(i).Copy wsTarget.Rows(nextRow)
            nextRow = nextRow + 1
        End If
    Next i
    
    MsgBox "Urgent orders copied successfully!", vbInformation
    Exit Sub

'===== Error Handler =====
ErrHandler:
    MsgBox "An error occurred: " & Err.Description, vbCritical
End Sub

Click Run or press F5 to execute the code.

Testing the macro in a duplicate environment allows you to confirm that it performs the correct steps without harming real data. If the macro does not behave as expected, revise your prompt to regenerate the code. This iterative loop is the safest way to refine AI-generated VBA. Over time, you will learn which types of instructions the AI interprets cleanly and which require more detail.

Iterating with AI: Fixing Errors and Security Considerations

In practice, the first version of the macro might not work perfectly. Instead of giving up, treat it as a draft and iterate on it with your AI assistant. AI attempts to follow your initial request, but you can guide it toward better code by refining your instructions.

You can ask for a clearer structure or better practices. Refactor the code to move repeated logic into a separate subroutine. You can ask it to add comments explaining each block as if you were teaching a beginner. If the macro uses unnecessary Select statements, you can ask for a version that avoids selection. If the code does not use With blocks or does not declare variables explicitly, ask for improved structure. AI can produce clean, modern VBA; it only needs guidance.

Prompt refinement is especially helpful when building complex macros that combine filtering, looping, calculations, and formatting. In this step, you can ask AI for performance optimization — for example, using arrays instead of cell-by-cell loops.

It is far easier to generate the first draft with AI and then re-prompt for improvements than to write everything manually from scratch.

Protecting Privacy for Safety

If you work with sensitive or confidential data, you must be extra careful. Never directly paste raw sensitive data into the prompt. Instead of actual names or account numbers, describe the structure: Column A contains customer IDs (text). Column B contains account balances (currency). Remove business-specific logic from prompts.

VBA can modify files, delete sheets, access external directories, and even run shell commands. That means AI-generated code must be handled with the same caution as any script downloaded from the internet. Avoid running macros that interact with unknown file paths or Windows commands. If the generated code references locations outside Excel, review these lines carefully. Only enable macros in workbooks from trusted sources and always keep Excel’s macro security settings at their default levels unless you have a clear need to change them.

You must follow your organization’s AI policy for safety purposes. Many companies have rules about what you can share with external tools. If in doubt, ask your IT or data protection team. Prefer on-device or enterprise AI where available. Some tools (like certain versions of Copilot integrated in Office or enterprise AI deployments) are designed to handle sensitive content locally or within a governed environment. Use those when possible.

Conclusion

By following the above steps, you can generate VBA code safely with AI. AI can dramatically accelerate VBA development, but safe implementation requires careful review, testing, and iteration. Clear prompts, careful reviews, and controlled testing environments transform AI from a risk into a powerful helper. As you start using AI, you will learn to design prompts that generate the expected code within one or two iterations. AI is a tool to assist you, not replace your judgment and testing. 

Remember, blindly pasting AI-generated code into your workbook can break formulas, corrupt data, leak sensitive information, or even open up security holes if you’re not careful.