When an Excel macro that used to work suddenly stops, the cause is usually outside the code. Something around it changed: Office started blocking macros in the file, the file was saved as .xlsx and lost its code, a sheet or a file the macro refers to was renamed or moved, a library reference went missing, or Office moved to 64-bit. Each one leaves a recognizable symptom, so start from what you see when you run the macro.
Find your symptom
| What you see | Likely cause |
|---|---|
| A red bar: “Microsoft has blocked macros from running because the source of this file is untrusted” | 1. The file came from the internet or an email |
| A yellow bar with Enable Content, or the button does nothing at all | 2. Macros are disabled in the Trust Center |
| The macros are gone from the list | 3. The file was saved as .xlsx |
| Run-time error ‘9’: Subscript out of range | 4. A sheet or workbook was renamed |
| Run-time error ‘1004’, ’53’ or ’76’ when opening a file | 5. A file or folder moved |
| Compile error: Can’t find project or library | 6. A missing reference |
| Compile error: The code in this project must be updated for use on 64-bit systems | 7. Office is now 64-bit |
1. The file came from the internet or an email
Microsoft 365 blocks macros in files that Windows has marked as coming from the internet, which includes email attachments you save. Close the file, right-click it in File Explorer, choose Properties, tick Unblock on the General tab, click OK, and open it again. A file you use every day can live in a Trusted Location instead.
Only for files you trust
Only unblock a file you trust: see Is It Safe to Enable Macros in a Downloaded Excel File?
2. Macros are disabled in the Trust Center
The setting is at File > Options > Trust Center > Trust Center Settings > Macro Settings. “Disable VBA macros with notification” shows the yellow Enable Content bar; “Disable VBA macros without notification” makes a macro button do nothing, with no message. On a work computer the setting may be locked by your company’s policy, and only IT can change it. The full steps are in How to Enable Macros in Excel.
3. The file was saved as .xlsx
An .xlsx file cannot hold macros. Save a macro workbook as .xlsx and Excel warns that the VB project cannot be saved in a macro-free workbook; answer Yes and the code is dropped.
If the workbook is still open
Save it again right away as Excel Macro-Enabled Workbook (.xlsm): the code is still in memory until you close the file. Once it is closed, the code is only in an earlier .xlsm copy or a backup.
4. A sheet or workbook was renamed
Worksheets("Data") fails with run-time error 9, Subscript out of range, the moment someone renames the Data tab. Either update the name in the code, or refer to the sheet by its code name: the name in the VBA editor’s Properties window, such as Sheet1, which does not change when the tab is renamed.
Sheet1.Range("A1").Value = "Updated"
5. A file or folder moved
A path typed into the code, such as "C:\Reports\Input.xlsx", breaks when the folder moves or the file goes to another computer. Workbooks.Open stops with run-time error 1004 and “Sorry, we couldn’t find” the file; Open and Kill stop with error 53 (file not found) or 76 (path not found). Build paths from the workbook’s own folder, or let the user pick the file:
Dim inputFile As Variant
inputFile = Application.GetOpenFilename("Excel files (*.xlsx), *.xlsx")
If inputFile = False Then Exit Sub
Workbooks.Open inputFile
6. A missing reference
In the VBA editor, open Tools > References. An entry marked MISSING: is a library from another program, such as Outlook, Word or a database driver, that is missing on this PC or installed at a different version. The error often points at an innocent line like Left or Date, with “Can’t find project or library”. Untick the MISSING entry and tick the version that is installed. For code that has to run on many PCs, the lasting fix is late binding, CreateObject("Outlook.Application"), which does not depend on a version.
7. Office is now 64-bit
Code that calls Windows directly through Declare statements must be marked PtrSafe in 64-bit Office, and its handles and pointers declared as LongPtr. Otherwise the module will not compile, with the 64-bit message in the table above. Add PtrSafe after Declare and change the handle types; the rest of the code is usually fine.
Common questions
Why does my macro work on my computer but not on a coworker’s?
The same seven causes, seen from the other side. Their copy may carry the internet mark, their Trust Center may be stricter, their Office may be 64-bit, or a library your code uses may be missing on their PC. If they are on a Mac, code that reaches into Windows will not run there at all; see Does Excel VBA Work on Mac?
Can a Windows or Office update break a macro?
Updates change security defaults more often than they change VBA itself, and the block on macros in downloaded files is the usual one. Code that drives another program, such as Outlook, can also break when that program is updated, through a missing or changed reference.
How do I find the line that is failing?
Click Debug on the error message. The VBA editor opens with the failing line highlighted in yellow. Hover over a variable to see its value, or press F8 to run the macro one line at a time from there.
When the Fix Is Bigger Than One Line
Some macros were written for a version of the business that no longer exists:
- Hard-coded everything: file paths, sheet names and row counts that break with every change
- Copied from three places: code nobody can read, with no error handling
- One person’s PC: references and settings that only work where it was written
A rewrite keeps what the macro does and removes what makes it fragile.
Macro Still Broken?
Send us the error message word for word and the code, and we’ll fix it.
Small fixes start at $69.
Every project gets a fixed-price quote before any work starts: the price you approve is the price you pay.