How to Fix Excel Macros Not Working After Switching to 64-Bit Office
Question details
Users are unable to run Excel VBA macros after migrating from older computers with 32-bit Office to new computers running 64-bit Office.
- Product
- Microsoft Excel
- Device & OS
- Windows
- Scenario
- Opening and running legacy macro-enabled workbooks (.xlsm) on newly installed 64-bit Excel environments.
- Observed behavior
- Macros fail to execute, trigger compile errors related to Windows API declarations, or do not appear in the macro list. A dramatic file size increase may also occur.
First, verify whether the macros are completely missing from the workbook or if they are visible but fail to run upon execution. Make sure to back up your original 32-bit workbook before making any modifications to the VBA code.
Update VBA API Declarations for 64-Bit Compatibility
Modify existing 32-bit Windows API Declare statements to be compatible with 64-bit Office by using the PtrSafe keyword and LongPtr data types.
When moving from 32-bit to 64-bit Excel, any VBA code making calls to the Windows API must be updated. 64-bit systems use 64-bit pointers and handles, which require specific data types. Without these updates, the code will fail to compile and cause the macro to stop working.
Open your macro-enabled workbook in Excel and press Alt + F11 to launch the Visual Basic for Applications (VBA) Editor.
Press Ctrl + F to open the Find dialog. Search for the word 'Declare' within the 'Current Project' to locate all Windows API calls.
For every API declaration found, insert the keyword 'PtrSafe' immediately after 'Declare' (e.g., change 'Declare Function' to 'Declare PtrSafe Function').
Within those declarations, change any data types that represent pointers or handles from 'Long' to 'LongPtr'.
In the top menu, click 'Debug' and then 'Compile VBAProject'. This will check your code for any remaining errors. Save the workbook once it compiles successfully.
Check Macro Security Settings and Add-ins
Ensure that macros are enabled in the Trust Center of the new Excel installation and verify that incompatible add-ins are not causing errors.
Experience Seamless Compatibility with WPS Office
If Microsoft Office architecture updates are breaking your workflow, consider switching to WPS Office. It is a lightweight, high-performing alternative that perfectly supports Microsoft Excel formats with a familiar interface, ensuring your daily work continues without interruption.
- 1. Install WPS Office: Download and install the free version of WPS Office from the official website to your computer.
- 2. Open Your Spreadsheet: Launch WPS Office and drag your Excel workbook directly into the application workspace.
- 3. Continue Your Work: Use the familiar tools and ribbon layout to manage your data, formulas, and charts seamlessly.
Frequently Asked Questions
Why do my Excel macros work on older computers but not new ones?
Newer computers generally have the 64-bit version of Microsoft Office installed by default, whereas older machines typically used the 32-bit version. Macros containing 32-bit Windows API calls will crash or fail to compile on 64-bit Office unless the code is manually updated.
What does the PtrSafe keyword do in Excel VBA?
The PtrSafe keyword asserts that a Declare statement is safe to run in 64-bit versions of Office. It is strictly required for all Windows API declarations in 64-bit VBA to prevent memory addressing errors.
Why did my macro-enabled workbook file size suddenly increase?
A sudden, dramatic increase in file size when switching Office versions can indicate workbook corruption, bloated formatting, or incompatible hidden add-ins. Resaving the file in the binary format (.xlsb) or clearing unused cell ranges can help resolve the bloat.
How can I write VBA code that works on both 32-bit and 64-bit Excel?
You can use conditional compilation statements like '#If Win64 Then' or '#If VBA7 Then' to write two different sets of API declarations within your module. This allows the same workbook to execute the correct block of code safely on both system architectures.
