Updating VBA code from Microsoift Office 32 bit to Microsoft Office 64 bit

user_19737577 0
Viewed 40

What changes need to be made to this coding (VBA in Office/EXCEL 32 bit) to work in Office/EXCEL 64 bit? I have been researching stackoverflow for answers.

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
vba excel
2 Answers

Index of the First Matching Value in an Array

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
user_98140690

Nothing needs to be changed in this code to work in 64bit Excel.

The official Microsoft documentation "Compatibility between the 32-bit and 64-bit versions of Office" can easily be discovered with a quick web search.

VBA 7 replaces the VBA code base in Office 2007 and earlier versions. VBA 7 is available in both the 32-bit and 64-bit versions of Office. It provides two conditional compilation constants:

VBA7 - Helps ensure the backward compatibility of your code by testing whether your application is using VBA 7 or the previous version of VBA.

Win64 Tests whether code is running as 32-bit or 64-bit.

With certain exceptions, the macros in a document that work in the 32-bit version of the application also work in the 64-bit version.

user_4479700
Related