Excel VBA Macro Not Working? Common Errors and Solutions

A macro that ran fine last month now does nothing when you click the button. Or Excel shows a red banner saying macros are blocked, or a dialog appears with “Run-time error ‘1004’” and a Debug button. Sometimes the macro runs but leaves Excel in an odd state: totals stop recalculating or other event macros quietly stop firing.

These symptoms have different causes. Some are security features working as designed, some are environment problems (a missing library, 64-bit Office, Excel for the web), and some are bugs in the code. This guide shows how to tell them apart and fix each one without weakening security more than necessary.

Quick diagnostic checklist

• Is the file macro-enabled (.xlsm, .xlsb, or .xlam) rather than .xlsx?
• Are you in the Excel desktop app? Excel for the web cannot run VBA.
• Is there a red “Security Risk” banner or a yellow bar with Enable Content?
• Does Debug > Compile VBAProject finish without an error?
• Does Tools > References show anything marked MISSING:?
• What is the exact error number, and which line is highlighted after you click Debug?
• What changed recently: a renamed sheet, a moved file, a new PC, 64-bit Office, a different user?

Common causes of VBA macros not working

1. Macros blocked because the file came from the internet

Microsoft 365 apps on Windows block VBA macros by default in files that carry the Mark of the Web, a Windows attribute added to downloads, email attachments, and files from some network shares. You see a red banner reading “Microsoft blocked macros from running because the source of the file is untrusted,” with no button to override it. Microsoft explains the zones and exceptions in Macros from the internet are blocked by default in Office. If the banner says your administrator has restricted macros, the block comes from organizational policy and your IT team controls it.

2. Trust Center settings disable macros

Under File > Options > Trust Center > Trust Center Settings > Macro Settings, the option “Disable VBA macros without notification” makes macros silently do nothing, with no prompt. “Disable VBA macros with notification” shows the yellow bar so you can choose. Microsoft lists every option in Enable or disable macros in Microsoft 365 files.

3. The file format or platform cannot run VBA

An .xlsx workbook cannot store a VBA project, so saving as .xlsx leaves the code behind. Excel for the web keeps macros in the file but cannot create, run, or edit them, according to Work with VBA macros in Excel for the web. Teams often discover this after moving workbooks to SharePoint or Teams.

4. A missing library reference

If the project references a library that is not installed on the current machine, VBA reports “Can’t find project or library,” and Tools > References shows the entry with a MISSING: prefix. Until it is fixed, unrelated code may also fail to compile. This is common when a workbook moves between PCs with different Office versions.

5. 64-bit Office and old Declare statements

Windows API declarations written for 32-bit Office need the PtrSafe keyword in 64-bit Office, and pointers or handles need the LongPtr type. Without PtrSafe the project will not compile. Microsoft’s 64-bit VBA overview stresses that adding the keyword alone is not enough; the data types must also hold 64-bit values.

6. Run-time errors in the code

When the macro starts but stops partway, the error number narrows the search:

Microsoft’s pages for error 9, error 13, error 91, and error 438 list further causes.

7. Settings left off after an earlier crash

Macros often turn off ScreenUpdating, EnableEvents, or automatic calculation for speed. If the code fails before restoring them, Excel stays that way for the session, and the next symptom looks unrelated: Worksheet_Change macros stop firing or totals stop updating. The EnableEvents and ScreenUpdating references both say to set them back.

Step-by-step troubleshooting

1. Work on a copy. Back up the workbook before changing code or settings, especially if the macro deletes rows or sends email.
2. Read the banner exactly. Red points to the Mark of the Web; yellow points to Trust Center settings. No reaction at all may mean silent disabling, or a button assigned to a different macro. Right-click a Form Control button and choose Assign Macro to confirm.
3. Unblock only trusted files. Close the file, right-click it in File Explorer, choose Properties, check Unblock, and click OK. For files your team opens daily, a folder under Trusted Locations works, but Microsoft advises using them sparingly. Do not switch on “Enable VBA macros” globally.
4. Compile. In the Visual Basic Editor (Alt+F11), choose Debug > Compile VBAProject. A compile error stops every macro in the project, and the editor highlights the cause.
5. Fix references. In Tools > References, point each MISSING: entry to the installed version or clear it if unused. For workbooks shared across Office versions, late binding (As Object with CreateObject) avoids version-specific references.
6. Step through the failure. Click Debug on the error, set a breakpoint a few lines earlier with F9, rerun, and press F8 to run one line at a time.
7. Inspect values. Open the Immediate window with Ctrl+G and type ? TypeName(rng.Value) or ? ws.Name. Debug.Print lines write values there during loops, and Debug > Add Watch can break when an expression, such as lastRow = 0, becomes true.
8. Remove fragile assumptions. Most 1004, 9, and 91 errors come from assuming what is active or present. Qualify every range with its worksheet, avoid Select, test Find results for Nothing, and confirm sheets exist before using them.
9. Reset Excel if needed. Type Application.EnableEvents = True in the Immediate window and set Formulas > Calculation Options back to Automatic. Then add cleanup code so it does not recur.

Platform-specific guidance for Excel

Select only works on the active sheet. Microsoft’s selecting and activating cells article confirms this and notes that you rarely need to select cells to change them. Recorded macros are full of Select, which is why they break when run from another sheet.

Range.Find remembers settings. Per the Range.Find reference, LookIn, LookAt, SearchOrder, and MatchByte persist between calls and are shared with the Find dialog. Pass them explicitly every time.

Error handlers have rules. The On Error reference notes that an error raised inside an active handler is not caught by it. Put Exit Sub before the handler label and read Err.Number before calling anything else.

Browser or scheduled runs. Office Scripts run in Excel on the web and can be called from Power Automate, but Microsoft’s Office Scripts vs. VBA comparison notes they need a qualifying commercial or education license, do not support workbook events, and cannot use desktop features such as COM. Moving from VBA means rewriting the logic.

A more robust macro skeleton

This pattern saves Excel’s settings, speeds up processing, checks that the sheet exists, and always restores the original state, whether the macro succeeds or fails.

Option Explicit

Public Sub UpdateSalesSummary()
    Dim prevCalc As XlCalculation
    Dim prevEvents As Boolean, prevScreen As Boolean
    Dim wsData As Worksheet
    Dim lastRow As Long

    prevCalc = Application.Calculation
    prevEvents = Application.EnableEvents
    prevScreen = Application.ScreenUpdating

    On Error GoTo ErrHandler
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual

    Set wsData = GetSheet(ThisWorkbook, "Data")
    If wsData Is Nothing Then
        Err.Raise vbObjectError + 513, "UpdateSalesSummary", _
                  "The worksheet 'Data' was not found."
    End If

    lastRow = wsData.Cells(wsData.Rows.Count, "A").End(xlUp).Row
    If lastRow < 2 Then GoTo CleanExit   ' header only

    ' Fully qualified references, no Select
    wsData.Range("F2:F" & lastRow).Formula = "=D2*E2"

CleanExit:
    Application.Calculation = prevCalc
    Application.EnableEvents = prevEvents
    Application.ScreenUpdating = prevScreen
    Exit Sub

ErrHandler:
    Debug.Print Now, "Error " & Err.Number & ": " & Err.Description
    MsgBox "The macro stopped: " & Err.Description, vbExclamation
    Resume CleanExit
End Sub

Private Function GetSheet(wb As Workbook, sheetName As String) As Worksheet
    On Error Resume Next   ' return Nothing instead of error 9
    Set GetSheet = wb.Worksheets(sheetName)
    On Error GoTo 0
End Function

Resume CleanExit clears the error and leaves the active handler, so cleanup runs normally. Keep the cleanup block simple so it cannot fail. The custom error number stays out of the 0 to 512 range reserved for system errors, per the Raise method reference.

How to confirm the issue is fixed

• Reopen the file from its normal location: no red banner, or only the prompt your policy requires.
• Compile finishes cleanly and Tools > References shows no MISSING: entries.
• The macro completes on a test copy, including edge cases such as an empty sheet.
• After a forced error (rename the sheet in your copy), Excel still recalculates and event macros still fire, proving cleanup works.
• A colleague on another PC can run it from the shared location.

Common mistakes to avoid

• Enabling all macros globally to get past one blocked file.
• Leaving On Error Resume Next on for a whole procedure, which hides errors and processes bad data.
• Relying on ActiveSheet, ActiveCell, and Selection in shared code.
• Hard-coding personal paths like a Desktop or Downloads folder.
• Adding PtrSafe without changing handle and pointer types to LongPtr.

Prevention and monitoring

• Use Option Explicit in every module so typos become compile errors.
• Log each run’s time, user, row count, and any error number to a sheet or text file.
• Keep shared macro workbooks in one controlled Trusted Location, or sign the VBA project, instead of having everyone unblock downloads.
• Export modules to .bas files and keep version history so you can compare changes.
• Retest after Office updates, a move to 64-bit Office, or changes to source system exports.

Our Excel VBA automation services cover repairing fragile macros and rebuilding them. For a browser-based approach, see how to automate Google Sheets reporting with Apps Script, or Google Apps Script trigger not working if a Google script is the problem.

FAQ

Why is there no Enable Content button on the macro warning?

The red banner means the file has the Mark of the Web. If you trust it, close it, check Unblock in its File Explorer Properties, and reopen it. If an administrator policy applies, IT controls the outcome.

Can I run VBA macros in Excel for the web or Teams?

No. Use Open in Desktop App, or rebuild the automation with Office Scripts if your license includes them.

What does run-time error 1004 mean?

An Excel method or property call failed. Click Debug and check each object reference on the highlighted line: inactive sheets, invalid cells, and protected sheets are common causes.

Sources

• Microsoft Learn: Macros from the internet are blocked by default in Office
• Microsoft Support: A potentially dangerous macro has been blocked
• Microsoft Support: Enable or disable macros in Microsoft 365 files
• Microsoft Support: Work with VBA macros in Excel for the web
• Microsoft Learn: Differences between Office Scripts and VBA macros
• Microsoft Learn: 64-bit Visual Basic for Applications overview
• Microsoft Learn: Can’t find project or library
• Microsoft Learn: Check or add an object library reference
• Microsoft Learn: Subscript out of range (Error 9)
• Microsoft Learn: Type mismatch (Error 13)
• Microsoft Learn: Object variable not set (Error 91)
• Microsoft Learn: Object doesn’t support this property or method (Error 438)
• Microsoft Learn: On Error statement
• Microsoft Learn: Raise method
• Microsoft Learn: Debug menu
• Microsoft Learn: Use the Immediate window
• Microsoft Learn: Selecting and activating cells
• Microsoft Learn: Range.Find method
• Microsoft Learn: Application.ScreenUpdating property
• Microsoft Learn: Application.EnableEvents property
• Microsoft Learn: Application.Calculation property