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.

score 72 · Accepted Answer · answered Apr 01 '11 at 14:26

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.

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. — Anonymous Type, Jul 18 '12 at 23:44
@AnonymousType These instructions come precisely from the MSDN documentation on VB7 and PtrSafe declarations: http://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, Jul 19 '12 at 00:46
`#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). — Alain, Jul 19 '12 at 01:05
@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 https://msdn.microsoft.com/en-us/library/office/gg264421%28v=office.14%29.aspx) — eisb, May 12 '15 at 19:01

score 6 · Answer 2 · answered Nov 02 '15 at 15:56

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

score 5 · Answer 3 · answered Mar 31 '11 at 22:10

5

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?

answered Mar 31 '11 at 22:10

David Heffernan

601,492
42
1,072
1,490

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. – Gary McGill Mar 31 '11 at 22:21
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)? – Gary McGill Mar 31 '11 at 22:25
you should be able to have a single version of your code but you really want to test it out for yourself. – David Heffernan Mar 31 '11 at 22:32
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!). – Gary McGill Mar 31 '11 at 22:43
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. – David Heffernan Mar 31 '11 at 23:04

score 4 · Answer 4 · edited Jul 04 '12 at 09:00

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

edited Jul 04 '12 at 09:00

Jon Peltier

5,895
1
27
27

answered Mar 31 '11 at 22:58

Jon49

4,444
4
36
73

score 3 · Answer 5 · answered Jun 25 '12 at 04:29

3

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

answered Jun 25 '12 at 04:29

Dee Bee

39
1

thats the system arch not Excel btw – HaveAGuess Oct 14 '13 at 17:25
3

@HaveAGuess According to the Microsoft [documentation](https://msdn.microsoft.com/en-us/library/office/gg264421.aspx#Anchor_1) 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." – wooobie Mar 23 '17 at 15:50

score 3 · Answer 6 · answered Apr 17 '18 at 00:34

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.

score 0 · Answer 7 · 2011-03-31T22:23:32.587

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.

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

7 Answers7

Linked