如何使VBA代码与64位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:

我有一个在excel2007中开发的VBA应用程序，它包含以下代码，允许从Shell32.dll访问ShellExecute函数:

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.

显然，应用程序不会在64位版本的Windows上编译(仍然使用32位的Office 2007)。我假设这是因为声明需要更新。

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.

我读过Office 2010引入了一个新的VBA运行时(VB7)，它有一些新的关键字，可以在Declare语句中使用，以便在64位Windows上正常工作。VB7还具有新的预定义编译器常量，以支持条件编译，根据应用程序是在32位Windows上运行还是在64位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).

然而，由于我被Office 2007困住了，我需要一个替代的解决方案。我的选择是什么?(如果可能的话，我真的不希望发布我的应用程序的两个独立版本)。

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.

然而，根据下面大卫的回答，我对我的声明不能起作用的情况是错误的。它唯一不能工作的环境是Windows 64位的Office 2010 64位。所以，Office 2007不是问题。

7 个解决方案

#1

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

我已经在使用我的内部工具在新的64位机器上使用我的内部工具在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.

请注意，在旧的VB6中，PtrSafe甚至不是一个有效的命令，因此它将以红色显示，就像您有一个编译错误一样，但它实际上不会产生错误，因为编译器将跳过if块的第一部分。

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

使用上述代码的应用程序在2003年、2007年、2010年和2010年都能完美地运行。

#2

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.

Office 2007只有32位，所以没有问题。您的问题只出现在Office 64位上，它有32位和64位版本。

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

当您只有Office 2007时，您不能指望支持64位Office 2010用户。解决方案是升级。

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?

如果你唯一声明的是ShellExecute，那么一旦你获得64位的Office，你就没有太多的事情要做了，但是当你不能运行你发布的程序时，支持用户并不是真的可行的!想想当他们报告错误时你会怎么做?

#3

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

我发现了这段代码(注意有些长时间被改成了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

来源:http://www.cadsharp.com/docs/Win32API_PtrSafe.txt

#4

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

实际上，检查32位或64位平台的正确方法是使用在所有VBA版本(16位、32位和64位版本)中定义的Win64常量。

#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

源代码:VBA帮助编译器常量

#5

Use PtrSafe and see how that works on Excel 2010.

使用PtrSafe，看看它在excel2010上是如何工作的。

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

修正了“微软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

val(application.version)>12.0不能工作，因为Office 2010有32位版本和64位版本

#6

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.

这个答案很可能是错误的。我以为VBA现在在CLR上运行，但它不是。无论如何，这个回复可能对某人有用。与否。

---

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.

如果运行Office 2010 32位模式，则与Office 2007相同。(“问题”是在64位模式下运行的Office)。在这里，重要的是执行上下文(VBA/CLR)的位，而加载的VBA/CLR的位取决于主机进程的位。

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

在32/64位调用之间，最值得注意的错误是对“指针类型”使用长或int (CLR中的常量大小)而不是IntPtr(基于位的动态大小)。

The ShellExecute function has a signature of:

ShellExecute函数的签名为:

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

在这种情况下，重要的是HWND是IntPtr(这是因为HWND是一个“句柄”，它是void*/“void指针”)，而且不长。以pinvoke.net ShellExecute为例。(虽然有些“解决方案”在pinvoke.net上不太可靠，但这是一个很好的开始)。

Happy coding.

快乐的编码。

---

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

至于任何“新语法”，我都不知道。

#7

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

要为所有版本的Office编写，请使用更新的VBA7和Win64条件编译器常量的组合。

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

VBA7确定代码是否在VB编辑器的版本7中运行(VBA版本在Office 2010+中发布)。

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

Win64确定正在运行的Office版本(32位或64位)。

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

有关更多细节，请参阅Microsoft Support文章。