我应该如何使我的 VBA 代码与 64 位 Windows 兼容？

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

提问by Gary McGill

I have a VBA application developed in Excel 2007, and it contains the following code to allow access to the ShellExecutefunction from Shell32.dll:

我有一个在 Excel 2007 中开发的 VBA 应用程序，它包含以下代码以允许ShellExecute从以下位置访问该函数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 Declaredeclaration 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 Declarestatement 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).

显然，该应用程序无法在 64 位版本的 Windows（仍在使用 32 位 Office 2007）上编译。我认为这是因为 Declare声明需要更新。

我读到 Office 2010 引入了一个新的 VBA 运行时 (VB7)，并且它有一些新的关键字可以在Declare语句中使用，以使其能够在 64 位 Windows 上正常工作。VB7 还具有新的预定义编译器常量以支持条件编译，其中将使用旧声明或新声明，具体取决于应用程序是在 32 位还是 64 位 Windows 上运行。

但是，由于我一直在使用 Office 2007，因此我需要一个替代解决方案。我有哪些选择？（如果可能的话，我真的不想发布我的应用程序的 2 个单独版本）。

However, per David's answer below, I was mistaken about the circumstances in which my Declarestatement 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.

但是，根据下面大卫的回答，我误会了我的Declare陈述不起作用的情况。它不起作用的唯一情况是 Windows 64 位上的 Office 2010 64 位。因此，Office 2007 不是问题。

回答by Alain

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

我已经在使用 Office 2010 的新 64 位计算机上使用我的内部工具的人遇到了这个问题。

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.

使用上述代码的应用程序可以在 Office 2003、2007 和 2010 32 位和 64 位上完美编译和运行。

回答by David Heffernan

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 位，所以没有问题。您的问题仅在具有 32 位和 64 位版本的 Office 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 Declarethat you have is that ShellExecutethen 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?

如果Declare您唯一拥有的是ShellExecute64 位 Office，那么您将无事可做，但是当您无法运行您发布的程序时，支持用户实际上并不可行！想想当他们报告错误时你会怎么做？

回答by Patrick Lepelletier

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

我找到了这个代码（请注意，有些Long已更改为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

回答by Jon49

Use PtrSafe and see how that works on Excel 2010.

使用 PtrSafe 并查看它在 Excel 2010 上的工作原理。

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

更正了“Microsoft Excel 2010 Power Programming with VBA”一书中的错字。

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

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

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

回答by GollyJer

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

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

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

VBA7确定代码是否在 VB 编辑器的第 7 版（Office 2010+ 中提供的 VBA 版本）中运行。

Win64determines 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 Articlefor more details.

有关更多详细信息，请参阅Microsoft 支持文章。

回答by Dee Bee

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 帮助

回答by Dee Bee

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 相同。（“问题”是 Office 在 64 位模式下运行）。执行上下文 (VBA/CLR) 的位数在这里很重要，加载的 VBA/CLR 的位数取决于宿主进程的位数。

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

在 32/64 位调用之间，最显着的错误是使用long或int（CLR 中的常量大小）而不是IntPtr（基于位的动态大小）用于“指针类型”。

The ShellExecutefunction 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 HWNDis a "HANDLE" which is void*/"void pointer") and not long. See pinvoke.net ShellExecuteas an example. (While some "solutions" are shady on pinvoke.net, it's a good place to look initially).

在这种情况下，重要的是 HWND 是IntPtr（这是因为HWND是一个“HANDLE”，它是void*/“void 指针”）而不是long。以pinvoke.net ShellExecute为例。（虽然 pinvoke.net 上的一些“解决方案”是阴暗的，但它是最初查看的好地方）。

Happy coding.

快乐编码。

---

---

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

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

回答by Evando Charles De Loiola

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.

感谢 Jon49 的洞察力。