Updated October 2026 · 7 minute read
A macro that worked for years suddenly does nothing, shows a red banner or throws an error. Most of the time the code is fine and something around it changed: a security setting, the file type, Excel’s version or a renamed sheet. Work down this list in order; the first three are the quickest to check.
- Red “SECURITY RISK” banner: the file came from the internet or email. Properties → Unblock, then reopen.1
- Macros gone completely: the file was saved as .xlsx. Macros only live in .xlsm or .xlsb files.
- “Must be updated for 64-bit”: add PtrSafe and use LongPtr in Declare statements.2
- Excel for the web can’t run VBA at all; use the desktop app.3
- Buttons do nothing: re-assign the macro and reset
Application.EnableEvents.
1. Blocked because the file came from the internet
Since 2022, Excel on Windows blocks macros by default in files that carry the Mark of the Web, which Windows adds to downloads and email attachments. You see a red SECURITY RISK banner with no Enable Content button.1
- Only do this for a file from a source you trust.
- Close the file. Right-click it in File Explorer and choose Properties.
- On the General tab, tick Unblock and click OK.
- Open the file again.
If there is no Unblock box, the file may be on a network share; copy it to your computer first. Saving files you use often to a Trusted Location also skips this check, but Microsoft advises using trusted locations sparingly.1
2. The file was saved as .xlsx
A normal .xlsx workbook cannot hold VBA. If someone used Save As and kept the default type, Excel warned them and saved the file without its macros. Check the extension: macros need .xlsm (or .xlsb). If you only have the .xlsx now, look for an older .xlsm copy or a backup; the code is not in the .xlsx.
3. Trust Center settings disable macros
Go to File → Options → Trust Center → Trust Center Settings → Macro Settings. “Disable VBA macros with notification” is the usual choice: macros stay off until you click Enable Content for files you trust. On a work computer these settings may be set by your IT team and greyed out; ask them rather than working around it.
4. 64-bit Office and Declare statements
Macros written for 32-bit Office that call Windows functions fail in 64-bit Office with an error saying the Declare statement needs the PtrSafe qualifier. Adding PtrSafe is step one; pointers and handles also need the LongPtr type.2 Wrapping both versions in #If VBA7 keeps the file working in older Excel:
#If VBA7 Then
Declare PtrSafe Function GetActiveWindow Lib "user32" () As LongPtr
#Else
Declare Function GetActiveWindow Lib "user32" () As Long
#End If5. Missing references
“Compile error: Can’t find project or library” usually means the macro uses a library that isn’t on this computer. In the VBA editor (Alt + F11), open Tools → References and look for an entry marked MISSING:. Untick it, or tick the version installed on this PC, then run Debug → Compile VBAProject.
6. Events left switched off
Many macros turn off events or screen updating while they run. If one crashed halfway, those settings stay off and event-driven macros, such as ones that run when a cell changes, stop firing until Excel restarts. Paste this into a module and run it once:
Sub ResetExcel()
Application.EnableEvents = True
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.Calculation = xlCalculationAutomatic
End SubThen fix the original macro so it switches settings back on even when it hits an error, with an error handler that runs the same lines before it exits.
7. Renamed sheets, ranges or files
Code like Worksheets("Data") breaks the moment someone renames the tab to “Data 2026”. The same goes for named ranges, table names and file paths. The error is usually Run-time error 9: Subscript out of range. Search the code (Ctrl + F in the editor) for the old name and update it, or refer to sheets by their code name, which doesn’t change when the tab is renamed.
8. Excel for the web or Mac
- Excel for the web can’t create, run or edit VBA macros. You can open and edit the workbook, but run macros in the desktop app.3
- Excel for Mac runs VBA, but code that calls Windows features (Declare statements, ActiveX controls, some file dialogs) won’t work there.
- Google Sheets doesn’t run VBA; it uses Apps Script instead.
Common error messages
| Message | Usual cause | First thing to check |
|---|---|---|
| Run-time error 9: Subscript out of range | Sheet, workbook or array name not found | Renamed or deleted tab |
| Run-time error 1004 | Excel couldn’t do the action on that range or sheet | Protected sheet, merged cells, wrong range |
| Run-time error 13: Type mismatch | Text where a number or date was expected | Blank or text cells in the data |
| Run-time error 424: Object required | A variable or control the code expects isn’t there | Renamed control or missing Set |
| Compile error: Can’t find project or library | Missing reference | Tools → References, MISSING: |
When the error box appears, click Debug. The yellow line is where it failed, which is usually enough to see which name or range changed.
Still stuck? We fix broken macros
Send the file and a screenshot of the error. We find the cause, fix it, test it on your Excel version and explain what changed, for a fixed price agreed before we start.
Questions
Why did my Excel macros suddenly stop working?
A very common cause on Windows is that, since 2022, Microsoft blocks macros in files that came from the internet or email. You will see a red SECURITY RISK banner. If the file is from a source you trust, right-click it, choose Properties, tick Unblock and reopen it.
How do I unblock macros in an Excel file from email?
Save the attachment to your computer, right-click the file, choose Properties, tick Unblock on the General tab, click OK and open the file again. Your organisation’s policies can still block macros after this.
Why does my macro say the code must be updated for 64-bit systems?
Older macros that call Windows functions with Declare statements need the PtrSafe keyword in 64-bit Office, and pointer types need LongPtr. Update the Declare lines, ideally with #If VBA7 so the file still works in older Excel.
Can I run macros in Excel online or on the web?
No. Microsoft says you can’t create, run or edit VBA macros in Excel for the web. You can still open and edit the workbook there, but run the macros in the Excel desktop app.
My macro button does nothing but the code runs from the editor. Why?
The button may be assigned to a macro that was renamed or moved, or events were left switched off after an earlier crash. Right-click the button, choose Assign Macro and pick it again, and run a small reset macro to switch events back on.
Related guides
Sources
- Microsoft Learn: Macros from the internet are blocked by default in Office
- Microsoft Learn: 64-bit Visual Basic for Applications overview
- Microsoft Support: Work with VBA macros in Excel for the web
Tahir Abbas
Founder of Softotech Solutions. Tahir has fixed and rebuilt Excel VBA macros and spreadsheet automations for small businesses for more than ten years. Security steps here follow Microsoft’s documentation; only unblock files you trust.