如何使 VBA 代码兼容 office 2010 - 64 位版本和旧的 office 版本

how to make VBA code compatible for office 2010 - 64 bit version and older office versions

提问by NewAutoUser

I have observed an issue with below function call when we migrated to office 2010-64 bit version.

当我们迁移到 Office 2010-64 位版本时，我发现以下函数调用存在问题。

Private Declare Sub CopyMem Lib "kernel32" Alias "RtlMoveMemory" (Destination As Any, Source As Any, ByVal Length As Long)

According to information available on http://msdn.microsoft.com/en-us/library/ee691831.aspxlink. I have changed above call as below and it has been working fine on office 2010 64 bit version.

根据http://msdn.microsoft.com/en-us/library/ee691831.aspx链接上提供的信息。我已将上述调用更改为如下所示，并且在 Office 2010 64 位版本上运行良好。

Private Declare PtrSafe Sub CopyMem Lib "kernel32" Alias "RtlMoveMemory" (Destination As Any, Source As Any, ByVal Length As Long)

The problem is, I need to make same call to work on older office versions as well and it throws compile error on older versions.

问题是，我也需要在旧的办公版本上进行相同的调用，它会在旧版本上引发编译错误。

Has anyone any idea how to make this call working for office 2010 and older office versions.

有没有人知道如何使此呼叫适用于 Office 2010 和较旧的 Office 版本。

回答by Charles Williams

As the MSDN article says, use conditional compilation: it works well for me in Excel 97 through Excel 2010 32-bit & 64-bit.

正如 MSDN 文章所说，使用条件编译：它在 Excel 97 到 Excel 2010 32 位和 64 位中对我来说效果很好。

#If VBA7 Then
Private Declare PtrSafe Sub CopyMem Lib "kernel32" Alias "RtlMoveMemory" (Destination As Any, Source As Any, ByVal Length As Long)
#Else
Private Declare Sub CopyMem Lib "kernel32" Alias "RtlMoveMemory" (Destination As Any, Source As Any, ByVal Length As Long)
#End if

回答by Jamie Garroch

I find the use of this VBA7compiler constant all over the Internet in relation to Office 64-bit compatibility but contrary to what is often said, this compiler constant detects Office installs using VBA7 e.g. Office 2010 onwards, not 64-bit Office. Confusingly, if you need to determine if you're using the 64-bit version of Office application, you need to use the Win64constant!

我发现整个Internet 上都使用此VBA7编译器常量与 Office 64 位兼容性有关，但与常说的相反，此编译器常量检测使用 VBA7（例如 Office 2010 及更高版本，而不是 64 位 Office）的 Office 安装。令人困惑的是，如果您需要确定是否使用的是 64 位版本的 Office 应用程序，则需要使用Win64常量！

Try this out of 32 and 64 bit versions of Office and you can see what I mean:

在 32 位和 64 位版本的 Office 中尝试此操作，您会明白我的意思：

Sub DemoCompilerConstants()

    #If VBA7 Then
        MsgBox "VBA7"
    #Else
        MsgBox "Not VBA7"
    #End If

    #If Win64 Then
        MsgBox "Win64"
    #Else
        MsgBox "NOT Win64"
    #End If

End Sub

Thanks to Steve Rindsbergfor putting me straight on this one! Steve also added:

感谢史蒂夫林兹伯格让我直截了当！史蒂夫还补充道：

Win64 actually tells you whether or not your version of your Office app is 64-bit. If you have to support older versions of Office, you may want to combine that with a VBA7 check to special-case versions of your Office app that won't understand the newer compiler directives. But if your code's failing on a 32-bit version of the Office app, it may well be because it's hitting your compiler directive, finding that yes, it's got VBA7 and then trying to exec the 64-bit API call, which won't fly because it's 32-bit Office.

Win64 实际上会告诉您您的 Office 应用程序版本是否为 64 位。如果您必须支持旧版本的 Office，您可能希望将其与 VBA7 检查结合到您的 Office 应用程序的特殊版本中，这些版本无法理解较新的编译器指令。但是，如果您的代码在 32 位版本的 Office 应用程序上失败，很可能是因为它遇到了您的编译器指令，发现是的，它有 VBA7，然后尝试执行 64 位 API 调用，这不会飞，因为它是 32 位 Office。

So this should be the correct approach for combining VBA7 and 64-bit Office compatibility:

所以这应该是结合 VBA7 和 64 位 Office 兼容性的正确方法：

#If VBA7 Then
  #If Win64 Then ' Declare using PtrSafe for Office 64 bit
    Declare PtrSafe Function ....
  #Else ' Declare for Office 32 bit
    Declare Function ....
  #End If
#Else ' Declare for Office 32 bit
  Declare Function ....
#End If

回答by Daniel Scott

Refining the excellent info/answer from Jamie Garroch(which explains how the VBA7 compiler constant doesn't tell you for sure if your code is running in a 64-bit Office application), the VBA compiler's #ifdirective can handle Andoperators.

完善来自Jamie Garroch的优秀信息/答案（它解释了 VBA7 编译器常量如何不能确定您的代码是否在 64 位 Office 应用程序中运行），VBA 编译器的#if指令可以处理And运算符。

That means you don't need to repeat your 32-bit function declarations. You can simply do this:

这意味着您无需重复 32 位函数声明。你可以简单地这样做：

    #If VBA7 And Win64 Then ...
        '64 Bit Declarations: for example ...
        Private Declare PtrSafe Sub API_CopyMemory Lib "kernel32" Alias "RtlMoveMemory" (dest As Any, source As Any, ByVal bytes As Long)
    #Else
        '32 Bit Declarations: for example ...
        Public Declare Sub API_CopyMemory Lib "kernel32" Alias "RtlMoveMemory" (dest As Any, source As Any, ByVal bytes As Long)
    #End If