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

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.

Actually, the correct way of checking for 32 bit or 64 bit platform is to use the Win64 constant which is defined in all versions of VBA (16 bit, 32 bit, and 64 bit versions).

#If Win64 Then 
' Win64=true, Win32=true, Win16= false 
#ElseIf Win32 Then 
' Win32=true, Win16=false 
#Else 
' Win16=true 
#End If

Source: VBA help on compiler constants

To write for all versions of Office use a combination of the newer VBA7 and Win64 conditional Compiler Constants.

VBA7 determines if code is running in version 7 of the VB editor (VBA version shipped in Office 2010+).

Win64 determines which version (32-bit or 64-bit) of Office is running.

#If VBA7 Then
'Code is running VBA7 (2010 or later).

     #If Win64 Then
     'Code is running in 64-bit version of Microsoft Office.
     #Else
     'Code is running in 32-bit version of Microsoft Office.
     #End If

#Else
'Code is running VBA6 (2007 or earlier).

#End If

See Microsoft Support Article for more details.

Office 2007 is 32 bit only so there is no issue there. Your problems arise only with Office 64 bit which has both 32 and 64 bit versions.

You cannot hope to support users with 64 bit Office 2010 when you only have Office 2007. The solution is to upgrade.

If the only Declare that you have is that ShellExecute then you won't have much to do once you get hold of 64 bit Office, but it's not really viable to support users when you can't run the program that you ship! Just think what you would do you do when they report a bug?

This work for me:

#If VBA7 And Win64 Then
    Private Declare PtrSafe Function ShellExecuteA Lib "Shell32.dll" _
        (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 ShellExecuteA Lib "Shell32.dll" _
        (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

Thanks Jon49 for insight.

This answer is likely wrong wrong the context. I thought VBA now run on the CLR these days, but it does not. In any case, this reply may be useful to someone. Or not.

If you run Office 2010 32-bit mode then it's the same as Office 2007. (The "issue" is Office running in 64-bit mode). It's the bitness of the execution context (VBA/CLR) which is important here and the bitness of the loaded VBA/CLR depends upon the bitness of the host process.

Between 32/64-bit calls, most notable things that go wrong are using long or int (constant-sized in CLR) instead of IntPtr (dynamic sized based on bitness) for "pointer types".

The ShellExecute function has a signature of:

HINSTANCE ShellExecute(
  __in_opt  HWND hwnd,
  __in_opt  LPCTSTR lpOperation,
  __in      LPCTSTR lpFile,
  __in_opt  LPCTSTR lpParameters,
  __in_opt  LPCTSTR lpDirectory,
  __in      INT nShowCmd
);

In this case, it is important HWND is IntPtr (this is because a HWND is a "HANDLE" which is void*/"void pointer") and not long. See pinvoke.net ShellExecute as an example. (While some "solutions" are shady on pinvoke.net, it's a good place to look initially).

Happy coding.

As far as any "new syntax", I have no idea.

Use PtrSafe and see how that works on Excel 2010.

Corrected typo from the book "Microsoft Excel 2010 Power Programming with VBA".

#If vba7 and win64 then
  declare ptrsafe function ....
#Else
  declare function ....
#End If

val(application.version)>12.0 won't work because Office 2010 has both 32 and 64 bit versions

i found this code (note that some Long are changed to LongPtr):

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

source: http://www.cadsharp.com/docs/Win32API_PtrSafe.txt