You upgraded from Office 2016 (or 2013, or 2019) to Microsoft 365. Spreadsheets that ran VBA macros flawlessly for years now throw compile errors, silently refuse to run, or produce wrong results. This is not a rare edge case — it is the single most common complaint we hear from organizations migrating to Microsoft 365. The good news is that every one of these issues has a specific fix. This guide covers them all, from the simplest Trust Center toggle to the deep code changes required for 64-bit compatibility.
There is no single cause. VBA macro breakage after an Office 365 upgrade usually involves one or more of these factors hitting simultaneously:
PtrSafe keyword.The rest of this guide addresses each cause individually with specific fixes.
This is the first thing to check because it is the most common cause of macros "not working" — they are not broken, they are being blocked by policy.
A better approach than lowering macro security globally is to designate specific folders as trusted. Macros in files stored in trusted locations run without prompts or blocks.
Starting with Office updates in late 2022, Microsoft blocks VBA macros in files that have the "Mark of the Web" (MOTW) — a hidden NTFS alternate data stream that Windows applies to any file downloaded from the internet, including email attachments opened from Outlook.
When MOTW blocks macros, you see a red bar at the top of the workbook saying "SECURITY RISK: Microsoft has blocked macros from running because the source of this file is untrusted." There is no "Enable Content" button — it is a hard block.
If you have many files that need unblocking, use PowerShell:
Get-ChildItem -Path "C:\MacroFiles" -Recurse -Include *.xlsm,*.xls | ForEach-Object {
Unblock-File -Path $_.FullName
Write-Host "Unblocked: $($_.Name)"
}Alternatively, moving the files to a Trusted Location bypasses the MOTW check entirely.
LegacyLeaps scans your workbooks, identifies every VBA compatibility issue, and tells you exactly what needs to change — before you touch a line of code.
Run a Free ScanThis is the most technically involved fix, and it affects any VBA code that uses Windows API calls through Declare statements. If you upgraded from 32-bit Office to 64-bit Office 365, every API declaration in your code must be updated.
When you open the workbook, you get:
Compile error: The code in this project must be updated for use on 64-bit systems.
Please review and update Declare statements and then mark them with the PtrSafe attribute.This error prevents all macros in the workbook from running — not just the ones with API calls.
Step 1: Find all Declare statements in your VBA project. Open the VBA editor (Alt+F11), use Edit > Find (Ctrl+F), search for "Declare" across the entire project.
Step 2: Add the PtrSafe keyword after Declare:
' Before (32-bit only):
Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" _
(ByVal lpClassName As String, ByVal lpWindowName As String) As Long
' After (64-bit compatible):
Private Declare PtrSafe Function FindWindow Lib "user32" Alias "FindWindowA" _
(ByVal lpClassName As String, ByVal lpWindowName As String) As LongPtrStep 3: Change parameter and return types that represent pointers or handles from Long to LongPtr. This is the tricky part — not every Long should become LongPtr. Only parameters that represent handles (HWND, HDC, HINSTANCE), pointers, or return values from functions that return handles need changing. Parameters that represent counts, flags, or pixel values stay as Long.
Here is a reference table for the most commonly used API functions:
| API Function | Parameters to Change to LongPtr | Return Type |
|---|---|---|
| FindWindow | None (string params) | LongPtr (returns HWND) |
| SendMessage | hWnd, wParam, lParam | LongPtr |
| GetActiveWindow | None | LongPtr (returns HWND) |
| ShellExecute | hwnd | LongPtr |
| GetTickCount | None | Long (not a handle) |
| Sleep | dwMilliseconds stays Long | None (Sub) |
| SetTimer | hWnd, nIDEvent, lpTimerFunc | LongPtr |
Step 4: If you need the code to work on both 32-bit and 64-bit, use conditional compilation:
#If VBA7 Then
Private Declare PtrSafe Function FindWindow Lib "user32" Alias "FindWindowA" _
(ByVal lpClassName As String, ByVal lpWindowName As String) As LongPtr
#Else
Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" _
(ByVal lpClassName As String, ByVal lpWindowName As String) As Long
#End IfThe VBA7 constant is true in Office 2010 and later (both 32-bit and 64-bit). For code that needs to distinguish between 32-bit and 64-bit specifically, use #If Win64 Then.
VBA projects reference external type libraries for things like file dialogs, common controls, and ActiveX components. When you upgrade Office, the specific versions of these libraries change, and references can break.
| Missing Reference | Cause | Fix |
|---|---|---|
| Microsoft Office 14.0 / 15.0 / 16.0 Object Library | Version-specific reference | Uncheck the missing one, scroll down and check the current version |
| Microsoft Forms 2.0 Object Library (FM20.DLL) | UserForms library not registered | Re-register: regsvr32 "C:\Windows\System32\FM20.DLL" |
| MSCOMCTL.OCX (Common Controls) | Not included with Office 365 | Download and register manually, or replace controls |
| Microsoft DAO 3.6 Object Library | Jet engine reference | Replace with Microsoft Office 16.0 Access Database Engine Object Library |
| COMDLG32.OCX (Common Dialog) | Legacy control not shipped | Replace with Application.FileDialog or Application.GetOpenFilename |
After fixing references, go to Debug > Compile VBAProject to check for remaining errors before running any macros.
Microsoft has removed or changed several VBA methods and properties across Office versions. If your code uses any of these, it will fail at runtime with "Method or data member not found" or "Object doesn't support this property or method" (Error 438).
| Removed / Changed | Version Removed | Replacement |
|---|---|---|
Application.FileSearch | Office 2007 | Dir() function or FileSystemObject |
CommandBar (many methods) | Office 2010+ (Ribbon) | Ribbon XML customization or CommandBars with limited functionality |
Application.FileDialog (msoFileDialogSaveAs) | Still works but behavior changed | Check return value handling |
UserForm.Show vbModeless in .xls format | Behavior differs in .xlsx/.xlsm | Test modeless forms after format conversion |
SmartTags collection | Office 2010 | No direct replacement; use cell validation or comments |
Assistant object (Clippy) | Office 2007 | Remove code or replace with MsgBox |
The most impactful one is Application.FileSearch, which was heavily used in VBA code written before 2007. The replacement using Dir() requires rewriting the search logic:
' Old code (broken):
With Application.FileSearch
.NewSearch
.LookIn = "C:\Reports"
.SearchSubFolders = True
.FileType = msoFileTypeExcelWorkbooks
.Execute
For i = 1 To .FoundFiles.Count
Debug.Print .FoundFiles(i)
Next
End With
' Replacement using Dir():
Dim filePath As String
filePath = Dir("C:\Reports\*.xls*")
Do While filePath <> ""
Debug.Print "C:\Reports\" & filePath
filePath = Dir()
LoopActiveX controls embedded in worksheets (not UserForms) are a persistent source of problems after Office upgrades. Common symptoms include:
Office caches ActiveX control metadata in .exd files. After an upgrade, these cached files become invalid and cause controls to malfunction. Deleting them forces Office to rebuild the cache.
%APPDATA%\Microsoft\Forms\..exd files in this folder.%TEMP%\Excel8.0\ and %TEMP%\VBE\ for additional .exd files.If the problem persists, the ActiveX control itself may need to be re-registered or replaced with a Form Control equivalent, which does not depend on ActiveX and is more reliable across Office versions.
| Error | Message | Most Likely Cause After Upgrade |
|---|---|---|
| Error 48 | "Error in loading DLL" | A DLL referenced by a Declare statement is not found or is the wrong bitness |
| Error 429 | "ActiveX component can't create object" | A COM object (like Scripting.FileSystemObject or ADODB.Connection) is not registered |
| Error 438 | "Object doesn't support this property or method" | Calling a removed API or using a method that changed signature |
| Error 453 | "Can't find DLL entry point" | API function name is wrong (often an Alias issue) or DLL version changed |
| Error 2147319779 | "Automation error" | Type library mismatch; re-register the library or update the reference |
| Compile error | "User-defined type not defined" | A missing reference to a type library that defines a data type used in the code |
| Compile error | "Sub or Function not defined" | A module that contained the Sub/Function was lost during format conversion, or a missing add-in |
If you have more than a handful of macro workbooks, fixing them one at a time as users report problems is reactive and painful. A systematic audit before or immediately after the upgrade saves time.
Declare Function, Application.FileSearch, or specific library references.Declare statements without PtrSafe. Look for variables typed as Long that store API return values.ThisWorkbook.VBProject.References and flag any with .IsBroken = True.For organizations with dozens or hundreds of macro workbooks, this audit is a project in itself. LegacyLeaps automates steps 1-4 — it scans your entire file collection, extracts VBA metadata, flags 64-bit issues, identifies missing references, and generates a prioritized fix list. The scan runs locally on your machine, so no code or data leaves your network.
LegacyLeaps scans every workbook in your environment, flags 64-bit API issues, missing references, and deprecated methods — then gives you a prioritized fix list. No files leave your machine.
Download LegacyLeaps FreeThe most common causes are: (1) Office 365's default macro security blocks macros from untrusted locations, (2) the upgrade switched you from 32-bit to 64-bit Office which breaks API Declare statements that lack PtrSafe, (3) your VBA code references a library or ActiveX control that is not installed with Office 365, or (4) the file was opened from a network location or email attachment and blocked by Mark of the Web.
This error means your VBA code uses Windows API Declare statements that are not 64-bit compatible. You need to add the PtrSafe keyword to each Declare statement and change Long to LongPtr for handle and pointer parameters. For example, Private Declare Function FindWindow Lib "user32"... becomes Private Declare PtrSafe Function FindWindow Lib "user32"... with handle parameters changed from Long to LongPtr.
Go to File > Options > Trust Center > Trust Center Settings > Macro Settings. Select "Enable VBA macros" or "Disable VBA macros with notification" (which shows a yellow bar letting you enable per-file). For permanent access, add your file's folder to the Trusted Locations list under Trust Center > Trusted Locations. Note that Group Policy may override these settings in enterprise environments.
Yes. Office 365 still offers a 32-bit installation option. During installation, choose the 32-bit version from the advanced options. This avoids all PtrSafe and LongPtr issues. However, 32-bit Office is limited to about 2 GB of memory, which can be a problem for large workbooks. Microsoft recommends 64-bit for new installations, so this is a viable short-term fix but not a long-term strategy.
Practical fixes for legacy Excel and Access problems. No spam.