A VBA Macro Stopped Working After an Office Update: Diagnosing It Without Removing Protections

In most cases the file is not damaged. Three separate mechanisms explain nearly every occurrence: macro blocking on files that came from outside the machine (Mark of the Web), a reference that broke in Tools > References, and a Declare statement that a 64-bit Office build refuses to compile. Each has a different fix.

What actually changed - on the machine, not in the file

An Office update almost never changes the code stored inside the workbook. It changes three things around it: the versions of the object libraries registered on the machine, the compilation architecture (32-bit or 64-bit), and the default behaviour towards files that arrive from the internet or from a network share.

So the first diagnostic question is not "what is in the code" but "where does this file live and where did it come from". A file that ran for years out of a local folder and was suddenly emailed to a colleague will behave differently without a single line changing.

Which message did you get, exactly?

The message is the diagnosis. These four cover most cases:

That last row wastes the most time. When a reference is missing, VBA can no longer resolve the names of built-in functions, so it flags an error on a line that looks entirely ordinary. Anyone who starts editing that line is fixing something that was never broken.

Why does removing Mark of the Web work when lowering macro security does not?

Because they are two different mechanisms. Mark of the Web is a marker the operating system attaches to a file that was downloaded through a browser, received as an attachment, or opened from a location outside the trusted zone - and it travels with the file. Removing it deals with one file and leaves the protection active for everything else.

Lowering the setting in the Trust Center, by contrast, disables the check for every file that machine opens from then on - including the next one that arrives by email and that you did not write. It also does not reliably work: a file sitting on a network share that is not in the Local intranet zone or in the trusted sites list is blocked regardless.

The three correct routes, in order of scope:

• A single file: right-click > Properties > tick Unblock on the General tab. If that checkbox is absent, the PowerShell Unblock-File cmdlet removes the same marker.
• A folder you control: add it as a Trusted Location via File > Options > Trust Center > Trust Center Settings.
• A recurring source, such as an internal server: add the location to Windows trusted sites so files from it never acquire the marker in the first place.

The reference that went MISSING

In Alt+F11 > Tools > References, an unresolved entry is prefixed MISSING:. That happens when the DLL is registered on the machine but the file itself was removed, moved, or replaced by another version - for example after a jump from one Outlook version to a newer one.

One behaviour matters before you touch anything: a workbook saved on a machine with the newer version pins the reference to that version, and then breaks on the machine still running the older one. That is why the fault appears for some users and not others, and why it returns after being "fixed".

• Untick the MISSING: entry and click OK.
• Run Debug > Compile to see what is still unresolved.
• If the code genuinely needs the library, tick the version present on the machine and compile again.
• For code that runs across users on different versions, switching to late binding - declaring as Object and using CreateObject - takes the reference out of the equation entirely.

32-bit to 64-bit: the statements that stop compiling

Office 2010 through 2016 installed as 32-bit by default. From Office 2019 and Microsoft 365, the default is 64-bit. The difference only affects code that calls the Windows API directly through a Declare statement.

In older code, pointers and handles were declared as Long, a 4-byte type. In a 64-bit environment those are 8-byte values, and they get truncated. Microsoft added three tools: the PtrSafe keyword, which asserts a statement targets 64-bit; the LongPtr alias, which resolves to Long on 32-bit and LongLong on 64-bit; and the LongLong type itself.

A legacy declaration that reads:

• Declare Function GetActiveWindow Lib "user32" () As Long

becomes:

• Declare PtrSafe Function GetActiveWindow Lib "user32" () As LongPtr

Note the trap that catches half-finished fixes: adding PtrSafe alone satisfies the compiler but does not repair a return value still typed Long. The code runs and returns a truncated value. If the file must work in both environments, wrap the declarations in #If VBA7 Then and #If Win64 Then.

A diagnosis order that does not destroy evidence

1. Copy the file aside. Everything from here happens on the copy.
2. Record the exact message wording, the Office version, and the architecture (File > Account > About).
3. Establish the file's origin: download, attachment, network share, or local folder.
4. If the message is the macro block, deal with the marker, not with the security level.
5. If the macro does run but fails, open Alt+F11 and run Debug > Compile before reading any code.
6. Open the References dialog and look for MISSING:.
7. Only if no reference is missing, search every module for Declare and check for PtrSafe.

What to verify before calling it fixed

• The file was tested by a second user, not only by whoever fixed it.
• It was tested from the location it is actually opened from day to day, not from the desktop.
• Any security level changed temporarily has been restored.
• References were checked on the machine running the oldest Office version still in use.

A macro file that has become business infrastructure and breaks itself on every update is a familiar signal. The same question arises when one spreadsheet carries an entire process - see when a spreadsheet has outgrown itself, handing over a single-owner spreadsheet, and a method for consolidating forty Excel files.

Sources

• Macros from the internet are blocked by default in Office - Microsoft Learn
• 64-bit Visual Basic for Applications overview - Microsoft Learn
• Can't find project or library - Microsoft Learn