Synopsis
This document describes the steps to convert a typical VBA project from 32-bit to 64-bit. This document is a work in progress and is derived from experience in Excel VBA projects, but should be applicable to Access, etc.
Applicable Cases
For the most part, written VBA in 32-bit is identical to 64-bit. There are a few cases where this is not true, and I will describe how to remediate them here. But first, learn about the conditional compilation statements.
Conditional Compilation Statements & Variables
The #if directive allows you to optionally compile code depending on compilation variables. We’ll focus on these two compilation variables:
- VBA7: Office 2010 (32 & 64-bit) rolled out a new VBA codebase; this variable is true if you’re coding in Office 2010 or later
- Win64: True if you are running 64-bit Office, False otherwise
Here are a few usage examples, to change which code is compiled depending on which version / bitness of Office you are using:
#if Win64 then
Declare PtrSafe Function MyMathFunc Lib “User32” (ByVal N As LongLong) As LongLong
#else
Declare Function MyMathFunc Lib “User32” (ByVal N As Long) As Long
#end if
#if VBA7 then
Declare PtrSafe Sub MessageBeep Lib “User32” (ByVal N AS Long)
#else
Declare Sub MessageBeep Lib “User32” (ByVal N AS Long)
#end if
You can read here for more information:
https://msdn.microsoft.com/en-us/library/office/ee691831(v=office.14).aspx
Case 1: Function Declarations
Functions must be declared using the PtrSafe attribute. For example:
Declare Function MyMathFunc Lib “User32” (ByVal N As Long) As Long
Must be declared as:
Declare PtrSafe Function MyMathFunc Lib “User32” (ByVal N As LongLong) As LongLong
You should use the #if Win64 Then… conditional compilation statements for any function declarations to allow your code to run in 32-bit and 64-bit Office.
Case 2: LongLong Datatype / Different Library Return Types
Some of the functions you may have used return a different datatype! For example, the ADODB.RecordSet.RecordCount method returns LongLong (64-bit long).
Here’s an example of how to write the function for 32-64 bit compatibility:
#If Win64 Then
Private Function GetNumRecords(RS As ADODB.Recordset) As LongLong
#Else
Private Function GetNumRecords(RS As ADODB.Recordset) As Long
#End If
GetNumRecords = RS.RecordCount
End Function
Case 3: LongPtr (A TRUE Pointer Type)
Some functions will require changing their arguments
#If VBA7 Then
Private Declare PtrSafe Function CallNextHookEx Lib “user32” (ByVal hHook As LongPtr, _
ByVal ncode As Long, ByVal wParam As LongPtr, lParam As Any) As LongPtr
#else
Private Declare Function CallNextHookEx Lib “user32” (ByVal hHook As Long, _
ByVal ncode As Long, ByVal wParam As Long, lParam As Any) As Long
#end if
Conversion Methodology
From 64-bit Office: Debug -> Compile VBA Project, fix errors, repeat until everything works. However it may be easier to search for your Declare statements and fix all those at once. You still need to compile because that’s the fastest way to determine if a return or argument type has changed to LongLong or LongPtr.
Test that the automation still works in 32-bit Office, and do a final test of functionality to make sure everything works the same.e
Systems and Processes - Development 