Making sure your VBA code is 64-bit compatible

Question

My Mircosoft Office 2010 is x86 but when I try running oe VBA, I get an error that it is not x64 compatible.

I have this code:

Private Declare Function SHFileOperation Lib "shell32.dll" Alias _
    "SHFileOperationA" (lpFileOp As SHFILEOPSTRUCT) As Long

Private Declare Function PathIsNetworkPath Lib "shlwapi.dll" _
    Alias "PathIsNetworkPathA" ( _
    ByVal pszPath As String) As Long

Private Declare Function GetSystemDirectory Lib "kernel32" _
    Alias "GetSystemDirectoryA" ( _
    ByVal lpBuffer As String, _
    ByVal nSize As Long) As Long

Private Declare Function SHEmptyRecycleBin _
    Lib "shell32" Alias "SHEmptyRecycleBinA" _
    (ByVal hwnd As Long, _
     ByVal pszRootPath As String, _
     ByVal dwFlags As Long) As Long

Private Const FO_DELETE = &H3
Private Const FOF_ALLOWUNDO = &H40
Private Const FOF_NOCONFIRMATION = &H10
Private Const MAX_PATH As Long = 260

Private Type SHFILEOPSTRUCT
    hwnd As Long
    wFunc As Long
    pFrom As String
    pTo As String
    fFlags As Integer
    fAnyOperationsAborted As Boolean
    hNameMappings As Long
    lpszProgressTitle As String
End Type

''''''''''''''''''''''''''''''''''''''
' Download API function.
''''''''''''''''''''''''''''''''''''''
Private Declare Function URLDownloadToFile Lib "urlmon" Alias _
                        "URLDownloadToFileA" ( _
                            ByVal pCaller As Long, _
                            ByVal szURL As String, _
                            ByVal szFileName As String, _
                            ByVal dwReserved As Long, _
                            ByVal lpfnCB As Long) As Long

I tried changing every Private Declare Function to Private Declare PtrSafe Function

But when I try loading the code, my Excel is getting unresponsive.

Does anyoe know about a solution?

Thank you

Solver: just needed to change Long to LongPtr too

Your script seems to rely on shell32.dll, some of the code in shell32.dll won't work on 64-bit systems. — serakfalcon
I don't think it's a dupe, but this question might be related. — RubberDuck

Alex K. Alex K. · Accepted Answer · 2014-05-24T14:47:05

Long is a 32 bit integer but under a 64 bit version of Office calling the Windows API handles and pointers are going to be 64 bits wide so the APIs above will fail.

LongPtr is provided in VBA7 to accommodate this, Microsoft's Compatibility Guide describes what you need to do to make you code run under x86/x64 versions of Office.

Making sure your VBA code is 64-bit compatible

1 Answers