excel - How should I make my VBA code compatible with 64-bit Windows?

Question

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.

Answer 1

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.

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