[AccessD] Array questions

[Bill Benson]

Oops, 3 things. I had to remove the on error goto 0 statement; didn’t need
the extra vEowMatched variable; and left out a test for no matches, which
 the previous code would not have returned -1 for, but this replacement
does.

Rationale retained below the code.

Sub TestIt()
Dim V() As String
Dim iMatchRow As Long
Dim NotAnArray As String

ReDim V(1 To 4)
V(1) = ""
V(2) = "Benson"
V(3) = "x"
V(4) = 5

iMatchRow = GetRow("Benson", V) 'Tests with base 1 array
Debug.Print iMatchRow


Erase V
iMatchRow = GetRow("Benson", V) 'Tests with empty array
Debug.Print iMatchRow

ReDim V(0 To 3) 'Tests with base 0 array
V(0) = "Benson"
V(1) = "cat"
V(2) = "x"
V(3) = 5
iMatchRow = GetRow("Benson", V)
Debug.Print iMatchRow

NotAnArray = "Who me?"
iMatchRow = GetRow("Benson", NotAnArray) 'Tests with non-array
Debug.Print iMatchRow


ReDim V(0 To 3) 'Tests with base 0 array and there is no match
V(0) = "xyz"
V(1) = "cat"
V(2) = "x"
V(3) = 5
iMatchRow = GetRow("Benson", V)
Debug.Print iMatchRow


Call GetRow 'Quit Exel

End Sub
Function GetRow(Optional strValue As String = "", Optional vArray As
Variant) As Long
Static objExcel As Object
Dim iLB As Long

GetRow = -1
If strValue = "" Then
    If Not objExcel Is Nothing Then
        objExcel.Quit
        Set objExcel = Nothing
    End If
    GoTo ExitFunction
Else
    On Error Resume Next
    iLB = -1
    iLB = LBound(vArray)
    If Err <> 0 Then
        GoTo ExitFunction
    Else
        If objExcel Is Nothing Then
            Set objExcel = CreateObject("Excel.Application")
        End If
        With objExcel.Application
            GetRow = .match(strValue, vArray) - IIf(iLB = 0, 1, 0)
        End With
    End If
End If
ExitFunction:

End Function


On Sun, Jan 22, 2023 at 3:18 AM Bill Benson <bensonforums at gmail.com> wrote:

> My trick probably earn me some groans or ahs depending on your bend.
> However since I use matching in arrays so frequently in every application,
> I use an instance of Excel to do my matching.
>
> It is only slow for the first (ever) use or until an empty string is
> passed which makes the Excel object variable get reset. The rest are (I
> believe) as fast as any VBA array match that could ever run, though I have
> not times different approaches.
>