How to resolve VBA problems with 64-bit Office 2010

I installed the 64-bit Office 2010 on my Win7-64 system a few months ago. Since then I have encountered only one issue with it, and that has to do with writing VBA code that needs to run on both 64 and 32-bit systems. In my case the code is in an Access application, but the same issue will apply to any Office-64 application that uses VBA.

The issue occurs when you use VBA to call a Windows DLL that uses pointers. In essence, 64-bit pointers are handled differently from 32-bit pointers. So MS included a new keyword to accommodate this: PtrSafe. This keyword is only used in 64-bit VBA code; it causes a syntax error in 32-bit code.

So how to have one set of VBA that works for both 64 & 32-bit systems? The answer is: Conditional Compiling. What has to happen is the VBA compiler needs to see the 64-bit code if it is running on a 64-bit system, but if running on a 32-bit system it needs to see the 32-bit code. The easiest way to show how this works is by example:


Option Compare Database
Option Explicit

#If Win64 Then

Private Declare PtrSafe Function GetOpenFileName Lib "comdlg32.dll" Alias _
"GetOpenFileNameA" (pOpenfilename As OPENFILENAME) As Long

#Else

Private Declare PtrSafe Function GetOpenFileName Lib "comdlg32.dll" Alias _
"GetOpenFileNameA" (pOpenfilename As OPENFILENAME) As Long

#End If

Private Type OPENFILENAME
lStructSize As Long
hwndOwner As Long
hInstance As Long
lpstrFilter As String
lpstrCustomFilter As String
nMaxCustFilter As Long
nFilterIndex As Long
lpstrFile As String
nMaxFile As Long
lpstrFileTitle As String
nMaxFileTitle As Long
lpstrInitialDir As String
lpstrTitle As String
flags As Long
nFileOffset As Integer
nFileExtension As Integer
lpstrDefExt As String
lCustData As Long
lpfnHook As Long
lpTemplateName As String
End Type


This routine uses the Windows Common Dialog Open function to return a user selected file name. The Common Dialog uses pointers, so its invoking code has to be customized for both 32 and 64 bit systems. That's what the # character does in front if the initial If test - it detects the type of system and sends the appropriate VBA code to the compiler.

Same code in both branches?

OOps - sorry, the correct code is:

#If Win64 Then
Private Declare PtrSafe Function GetOpenFileName Lib "comdlg32.dll" Alias _
"GetOpenFileNameA" (pOpenfilename As OPENFILENAME) As LongPtr
#Else
Private Declare PtrSafe Function GetOpenFileName Lib "comdlg32.dll" Alias _
"GetOpenFileNameA" (pOpenfilename As OPENFILENAME) As Long
#End If