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.

Solution 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.

Solution 2

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

Solution 3

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?

Solution 4

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

Solution 5

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

View more solutions

Comments

[Gary McGill]

thanks. The problem with "64-bit" was reported by a colleague, who then left the building, so the details were sketchy and I obviously filled in the blanks wrongly.

[Admin]

@David Heffernan Thanks. I was confusing it with VB.NET/VSTO.

[Gary McGill]

so, to clarify... My existing Declare statement will work in Office 2007 on both 32 and 64-bit Windows? And will work in Office 2010 on 32-bit Windows? But for Office 2010 on 64-bit Windows I need to use the new VB7 features - and this means I need a 2010-specific version of my application? Is there no way to have something backwards-compatible that supports all versions (as used to always be the case with previous versions of Office)?

[David Heffernan]

you should be able to have a single version of your code but you really want to test it out for yourself.

[Gary McGill]

Thanks for the advice on how to test and support applications. I think what I really deserved was advice on how to post questions (or rather, when not to!).

[David Heffernan]

Well, you can update and change the question but you'll just make this answer seem silly. You'd be better off with a new question in my view. I think this one has been answered.

[shen]

If the problem is 64 bit office, then VBA7 is not the answer. You need the win64 compilation constant. VBA7 will still work on Office 2010 32bit.

[Alain]

@AnonymousType These instructions come precisely from the MSDN documentation on VB7 and PtrSafe declarations: msdn.microsoft.com/en-us/library/ee691831.aspx The need to use PtrSafe comes from the use of VB7, not the use of Office on a 64 bit system. It is merely coincidental that 64 systems support only VB7, so this is killing two birds with one stone.

[Alain]

#if Win64 is only needed in odd situations where you actually need to load two different dlls or use functions with different signatures on 32 bit and 64 bit systems. For simply making legacy spreadsheets work on 64-bit machines which force VB7, it's not necessary. 32-bit Office 2010 is intentionally compatible with all legacy VB6 spreadsheets without any modifications (as per the documentation).

[shen]

oops my bad. thanks for th explanation @Alain, I understand the diff fully now.

[HaveAGuess]

thats the system arch not Excel btw

[bananafish]

@Alain Thank you Mr. Lifesaver!

[eisb]

@Alain I know this is a few years after the fact, but inside the VBA7 statement the Long should be LongLong, or ideally LongPtr for compatibility between 32- and 64-bit versions. (from msdn.microsoft.com/en-us/library/office/…)

[Gary McGill]

Thanks. Nice resource.

[wooobie]

@HaveAGuess According to the Microsoft documentation it is indeed the Office architecture and not the system architecture. To quote the article: "The Win64 conditional compilation constant is used to determine which version (32-bit or 64-bit) of Office is running."

[Tom the Toolman]

The hwnd parameter is not correct. Handles (e.g. hWnd) and pointers are 64-bit on 64-bit Windows. For these, you must use LongPtr, which represents the appropriate size for the platform you're using (32 or 64 bits). Find a copy of WinAPI for VBA.