Excel – How should I make the VBA code compatible with 64-bit Windows

64-bitexcelvba

I have a VBA application developed in Excel 2007, and it contains the following code to allow access to the ShellExecute function from Shell32.dll:

Private Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" (ByVal hwnd As Long, ByVal lpOperation As String, ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long

I originally said:

Apparently the application will not
compile on a 64-bit version of Windows
(still using 32-bit Office 2007). I
assume that this is because the
Declare declaration needs updated.

I've read that Office 2010 introduced
a new VBA runtime (VB7), and that this
has some new keywords that can be used
in the Declare statement to allow it
to work properly on 64-bit Windows.
VB7 also has new predefined compiler
constants to support conditional
compilation where either the old or
new declaration will be used,
depending on whether the application
is running on 32 or 64-bit Windows.

However, since I'm stuck with Office
2007 I need an alternative solution.
What are my options? (I'd really
prefer not to have to release 2
separate versions of my application if
at all possible).

However, per David's answer below, I was mistaken about the circumstances in which my Declare statement won't work. The only circumstances under which it won't work is Office 2010 64-bit on Windows 64-bit. So, Office 2007 is not an issue.

Best Answer

I've already encountered this problem on people using my in-house tools on new 64 bit machines with Office 2010.

all I had to do was change lines of code like this:

Private Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" _
    (ByVal hwnd As Long, ByVal lpOperation As String, ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long

To This:

#If VBA7 Then
    Private Declare PtrSafe Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" _
        (ByVal hwnd As Long, ByVal lpOperation As String, ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long
#Else
    Private Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" _
        (ByVal hwnd As Long, ByVal lpOperation As String, ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long
#End If

You will, of course want to make sure that the library you're using is available on both machines, but so far nothing I've used has been a problem.

Note that in the old VB6, PtrSafe isn't even a valid command, so it'll appear in red as though you have a compile error, but it won't actually ever give an error because the compiler will skip the first part of the if block.

code Appearance

Applications using the above code compile and run perfectly on Office 2003, 2007, and 2010 32 and 64 bit.

Related Topic