VBA Macros Stopped Working? Complete Troubleshooting Guide

Your VBA macros ran perfectly. Then a Windows or Office update happened, and now they don't. The symptoms range from a popup saying macros are blocked, to a compile error in the VBA editor, to buttons that simply do nothing when clicked. The causes are distinct and the fixes are different for each one.

This guide covers every common reason VBA macros stop working after an update, with exact steps to diagnose and fix each problem.

1. Macros Blocked — Yellow Bar or No Prompt At All

Cause A: Macro settings are set to disable all macros

Check your Trust Center settings:

1. Open Excel, go to File → Options → Trust Center → Trust Center Settings
2. Click Macro Settings
3. Make sure the selected option is "Disable all macros with notification" (not the one without notification)
4. Click OK, close Excel, reopen your file

If it's set to "Disable all macros without notification," Excel will silently block macros and show no yellow bar. This is the most common case where macros appear to simply "not work" — no error, no prompt, just nothing happens.

Cause B: Mark of the Web (MOTW) blocking

Files downloaded from the internet, received as email attachments, or opened from a network share carry a hidden Windows flag (MOTW). Windows 11 24H2 expanded the scope of this flag — more files are blocked than before, including files from network drives that weren't blocked in previous versions.

When MOTW is present, Excel blocks macros and in some configurations doesn't even show the yellow bar. To remove the flag from a single file:

1. Close the file in Excel
2. Right-click the file in File Explorer → Properties
3. On the General tab, find the "Security" message at the bottom
4. Check Unblock and click OK
5. Reopen the file

For multiple files, use PowerShell to unblock an entire folder:

Get-ChildItem -Path "C:\YourFiles" -Filter "*.xls*" -Recurse | Unblock-File

Cause C: File not in a Trusted Location

Adding the folder where your files live to Excel's Trusted Locations bypasses both macro settings and MOTW checks:

1. File → Options → Trust Center → Trust Center Settings → Trusted Locations
2. Click Add new location
3. Browse to your files folder, optionally check "Subfolders"
4. Click OK

2. Compile Error: PtrSafe Required

This error means your VBA project contains Win32 API declarations written for 32-bit Office. 64-bit Office (the default since Microsoft 365 in 2019) requires an updated declaration syntax.

Finding All Affected Declarations

In the VBA editor (Alt + F11), use Find & Replace (Ctrl + H) to search across all modules:

• Search for: Declare Function
• Also search for: Declare Sub

Every match is a declaration that needs updating.

The Fix

Add PtrSafe between Declare and Function or Sub. For any parameter or return type that holds a memory address or pointer (window handles, process handles, memory pointers), change the type from Long to LongPtr.

Before (32-bit)	After (64-bit compatible)
Declare Function FindWindow...	Declare PtrSafe Function FindWindow...
...hWnd As Long	...hWnd As LongPtr
...As Long (pointer return)	...As LongPtr

Regular integer parameters that aren't pointers (ByVal bShow As Long for a boolean flag, for example) don't need to change. Only values that hold memory addresses.

Supporting Both 32-bit and 64-bit

If your workbook needs to run on both 32-bit and 64-bit Office, use conditional compilation:

#If VBA7 Then
    Declare PtrSafe Function GetTickCount Lib "kernel32" () As LongPtr
#Else
    Declare Function GetTickCount Lib "kernel32" () As Long
#End If

VBA7 is defined in Office 2010 and later (the first version to support 64-bit). This block compiles the right declaration for the Office version being used.

3. Compile Error: Missing or Broken References

VBA projects can reference external type libraries — Microsoft ActiveX Data Objects, Microsoft Scripting Runtime, third-party COM components, and others. When the referenced library isn't present at the expected path or version, the reference breaks.

Finding Broken References

1. Open the VBA editor (Alt + F11)
2. Go to Tools → References
3. Look for any entry with "MISSING:" at the beginning of the name

Any reference with "MISSING:" is broken. The library it points to isn't installed or isn't at the expected path.

The Fix

For each broken reference:

1. Uncheck the broken "MISSING:" reference
2. Scroll through the available references list and find the current version of the same library
3. Check it and click OK

Common broken references after Office or Windows updates:

Missing Reference	Replacement
Microsoft ActiveX Data Objects 2.x Library	Microsoft ActiveX Data Objects 6.1 Library
Microsoft DAO 3.6 Object Library	Microsoft Office 16.0 Access Database Engine Object Library
Microsoft Excel X.0 Object Library (wrong version)	Microsoft Excel 16.0 Object Library
Any third-party COM library	Reinstall the third-party software

4. Buttons and Controls Don't Respond

Excel sheets use two types of controls: Form Controls (the older type, accessed from the Forms toolbar) and ActiveX Controls (the newer type, accessed from the Developer tab). The two types behave differently and break differently.

ActiveX Controls: The Culprit

ActiveX controls in Excel workbooks are registered COM objects. On Windows 11 24H2 and with updated Office security policies, ActiveX controls in files that aren't in a Trusted Location may be disabled. When disabled, they appear but don't respond.

The fix:

1. Add the file's folder to Excel's Trusted Locations (as described above)
2. Or: Go to File → Options → Trust Center → Trust Center Settings → ActiveX Settings and select "Enable all controls without restrictions" (only in a controlled environment)

If controls are completely missing or render as empty boxes, the issue may be that the ActiveX control's DLL is no longer registered. This requires re-registering the control using regsvr32 or reinstalling the software that provided it.

Form Controls: Usually Not the Problem

Form Controls (inserted from Developer → Insert → Form Controls) are simpler and don't depend on ActiveX registration. If a Form Control button does nothing, check that the assigned macro still exists: right-click the button → Assign Macro → verify the macro name points to an existing procedure.

5. Macros Run But Produce Runtime Errors

Error 429: ActiveX Component Can't Create Object

This error means VBA code is trying to instantiate a COM object that isn't registered. Common causes:

• The Office component being created (e.g., CreateObject("Access.Application")) requires Access to be installed, and it isn't
• A third-party COM component was uninstalled during an Office update
• The component needs to be run as 32-bit but Office is 64-bit (or vice versa)

Check whether the application the VBA is trying to instantiate is installed. If it's an Office component, confirm it's included in your Office installation via the Office installer.

Error 3706: Provider Cannot Be Found

This error in connection strings usually means the data provider referenced doesn't exist on this machine. The most common cause: code using Provider=Microsoft.Jet.OLEDB.4.0 on a machine where the Jet 4.0 provider was removed (Windows 11 24H2).

Fix: Update the connection string to use the ACE provider:

' Old — broken on Windows 11 24H2:
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\data.mdb"

' Fixed:
conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\data.mdb"

You may also need to install the Microsoft Access Database Engine 2016 Redistributable.

Error 1004: Application-Defined or Object-Defined Error

This is a catch-all that can mean many things. Common causes in updated environments:

• The code references a named range that no longer exists or was renamed
• A worksheet name the code expects has changed
• The code writes to a cell or range that's protected
• An Excel feature the code uses behaves differently in a newer version

Step through the macro with F8 in the VBA editor to identify the exact line that fails, then inspect what it's trying to do at that point.

6. The File Format Is the Underlying Problem

Sometimes the individual fixes above address symptoms but not the root cause. If your workbook is in the old .xls binary format, you're fighting a format that modern Office increasingly treats as hostile. The security restrictions that cause macro blocking, the PtrSafe issues that cause compile errors, the ActiveX problems — all of these hit .xls files harder than .xlsm files, because .xls is a legacy format that Windows and Office security policies treat with suspicion.

The durable fix is migrating to .xlsm, which gives you:

• Modern Open XML format, treated as a first-class citizen by Office
• VBA code preserved in a format with better long-term support
• Fewer macro blocking incidents from security policies
• Better compatibility with future Windows and Office updates

See our guide: How to Convert .xls to .xlsx Without Losing Macros.

← Back to all posts