How to Audit Excel VBA Code Before Migrating to .xlsx

The most reliable way to avoid a broken migration is to know exactly what's in your VBA code before you convert anything. This checklist covers every compatibility issue that surfaces when moving from legacy .xls to modern .xlsx or .xlsm on 64-bit Windows 11.

Step 1: Identify All VBA-Containing Files

In Windows Explorer, search for *.xls and *.xlsm. Not all .xls files have VBA — files without macros don't need the audit. To quickly check: open the file, press Alt+F11. If the Project Explorer on the left shows any modules beyond "ThisWorkbook," the file has VBA.

A faster approach for many files: use the LegacyLeaps scanner, which detects VBA presence across all files in a folder without opening each one manually.

Step 2: Check for Win32 API Declare Statements

This is the most common source of 64-bit compile errors. In the VBA editor, use Ctrl+F and search all modules for Declare (with a trailing space).

For each hit, check:

• Does the line contain PtrSafe? If not, it needs it added.
• Are any parameters typed as Long that represent handles, pointers, or memory addresses? Those need to change to LongPtr.

' Needs fixing:
Declare Function GetDC Lib "user32" (ByVal hwnd As Long) As Long

' Fixed for 64-bit:
Declare PtrSafe Function GetDC Lib "user32" (ByVal hwnd As LongPtr) As LongPtr

Step 3: Check for MISSING References

Tools > References in the VBA editor. Any library listed as MISSING will cause the project to fail to compile. Common culprits:

• Microsoft DAO 3.6 Object Library (may need updating to Office 16.0 Access engine)
• Microsoft Scripting Runtime (usually present but sometimes missing on clean installs)
• Any reference to a specific version of a COM library that was upgraded

Note every MISSING reference and its last known name — you'll need to re-add the correct current version after migration.

Step 4: Identify ActiveX Controls

Open each worksheet in Design Mode (Developer tab > Design Mode). Any ActiveX control on a sheet is a potential compatibility problem. Note the control type — especially:

• Microsoft Forms controls (generally fine in 64-bit)
• Third-party .ocx controls (may be 32-bit only)
• MSComctl.ocx controls (TreeView, ListView, ProgressBar) — have 64-bit versions but need re-registration
• Calendar controls — mscal.ocx is 32-bit only, needs replacement

Step 5: Search for Deprecated Functions

Some VBA functions and statements that worked in Excel 2003 are deprecated or behave differently in modern versions:

• Application.FileSearch — removed in Excel 2007. Replace with Dir() or FileSystemObject.
• Application.FindFile — removed in Excel 2007.
• CreateObject("Scripting.FileSystemObject") — works but may require the Scripting Runtime reference.
• SendKeys — still works but unreliable in 64-bit. Flag any usage for review.

Use Ctrl+F across all modules to search for each of these.

Step 6: Check External File Paths Hardcoded in VBA

Search for hardcoded drive letters or paths (C:\, D:\, \server\). These paths may not exist on the target machine or may need updating after the file is moved during migration.

Step 7: Document Everything Found

Before converting, create a simple table:

File	Declare Statements	MISSING Refs	ActiveX Controls	Deprecated Functions	Risk
client-model.xls	3 (need PtrSafe)	DAO 3.6	None	None	Medium
payroll-calc.xls	0	None	Calendar (mscal)	FileSearch	High

High-risk files need individual attention. Medium-risk files can be batch-converted with automated PtrSafe fixes. Low-risk files (no VBA issues found) are safe for straight batch conversion.

Related Resources

• VBA Macros Stopped Working? Complete Troubleshooting Guide
• VBA PtrSafe Error After Windows Update — How to Fix It
• The Complete Guide to Legacy Excel Migration

← Back to all posts