正在将VBA代码从Microsoift Office 32位更新为Microsoft Office 64位

Question

要在Office/EXCEL 64位中工作，需要对该编码(Office/EXCEL32位中的VBA(进行哪些更改？我一直在研究stackoverflow以寻找答案。

Public Function FindInArray(a As Variant, tofind As Variant) As Integer
'Returns -1 if not found or position in array if found
Dim i As Integer
FindInArray = -1
If Not IsArrayEmpty(a) Then
For i = LBound(a) To UBound(a)
If a(i) = tofind Then
FindInArray = i
Exit For
End If
Next i
End If
End Function

Answer 1

数组中第一个匹配值的索引

Option Explicit
Sub NumMatchTEST()

Dim Arr() As Variant: Arr = VBA.Array(CVErr(xlErrNA), Date, 1, 2, "Yes")
Debug.Print NumMatch(Arr, 2) ' 3 since 'Arr' is zero-based
Debug.Print NumMatch(Arr, "Not") ' -1 since not found

ReDim Arr(1 To 4): Arr(4) = "New"
Debug.Print NumMatch(Arr, "New") ' 4 since 'Arr' is one-based
Debug.Print NumMatch(Arr, Nothing) ' -1 since an error occurred
End Sub
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Purpose:      Returns the index of the first matching value ('FindValue')
'               in an array ('Arr').
' Remarks:      It will work correctly for a 1D array
'               and for a single-column 2D array, with a lower limit >= 0.
'               It returns '-1' if no match or if an error occurs.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Function NumMatch(Arr() As Variant, ByVal FindValue As Variant) As Long
NumMatch = -1
On Error GoTo ClearError

Dim ArrIndex As Variant: ArrIndex = Application.Match(FindValue, Arr, 0)
If IsNumeric(ArrIndex) Then NumMatch = ArrIndex + LBound(Arr) - 1
ProcExit: Exit Function
ClearError: Resume ProcExit
End Function

Answer 2

此代码中无需更改任何内容即可在64位Excel中工作。

微软官方文档"；Office"32位和64位版本之间的兼容性"；可以通过快速网络搜索轻松发现。

VBA 7取代了Office 2007及早期版本中的VBA代码库。VBA 7在32位和64位版本的Office中都可用。它提供了两个条件编译常量：

VBA7-通过测试帮助确保代码的向后兼容性无论您的应用程序使用的是VBA 7还是早期版本的VBA。

Win64测试代码是以32位还是64位运行。

除了某些例外，文档中在32位版本的应用程序也适用于64位版本。