Return Index of an Element in an Array - Word VBA

There is an array dimmed as Variant Dim valuesAll As Variant, created using ADO connection to Excel file and loading record set into the array.

This array is loaded from Excel file into Word VBA and the macro that searches the Element index in this array is lunched from Word VBA Editor, that's why I can't use:

Dim pos, arr, val

arr=Array(1,2,4,5)
val = 4

pos=Application.Match(val, arr, False)

if not iserror(pos) then
   Msgbox val & " is at position " & pos
else
   Msgbox val & " not found!"
end if

I get Compile error: Method or data member not found.

I need a solution that'll work under Word VBA.

The array is two-dimensional and the value's (strPESELfromWord) index I'm searching is in array's second row (second column in source data). I don't want to open Excel file at all - that's why I use ADO connection to Excel file, so there's no need to open it.

In my macro this value (strPESELfromWord), whose index I'm looking for is in the variable called strPESELfromWord.

This is the code I got so far:

Sub GetRows_returns_a_variant_array()

Dim connection As ADODB.connection
Dim recSetAll As ADODB.Recordset
Dim recSetHalf As ADODB.Recordset
Dim exclApp As Excel.Application     'This code is written and lunched from Word VBA Editor
Dim exclWorkbk As Excel.Workbook
Dim mySheet As Excel.Worksheet

Dim wordDoc As Word.Document
Dim strPESELfromWord As String
Dim strQuery0 As String
Dim strQuery1 As String
Dim strQuery2 As String
Dim strQuery3 As String
Dim strSexDigit As String
Dim valuesAll As Variant                'Should there be valuesAll() ??
Dim valuesHalf As Variant
Dim txt As String
Dim lngPos As Long
Dim intRemainder As Integer
Dim r As Integer
Dim c As Integer

    Set wordDoc = Word.ActiveDocument
    Debug.Print wordDoc.Name
    Set connection = New ADODB.connection
    Debug.Print "ADODB.connection.Version = " & connection.Version
    strPESELfromWord = Trim(Selection.Text)
    Debug.Print "Wybrano PESEL " & strPESELfromWord & "."

    strSexDigit = Mid(strPESELfromWord, 10, 1)      'Extract 10th digit from PESEL number
    Debug.Print strSexDigit
    intRemainder = strSexDigit Mod 2                'If 1 is remaider it means it's a male, and if there's no remainder, than it's a female.
    Debug.Print "Remainder equals " & intRemainder & "."

    'Open the database connection.
    connection.Open "Provider = Microsoft.ACE.OLEDB.12.0;Data Source = X:\Roesler\Excel\FW 1\custdb.xlsm;" & _
                "Extended Properties=""Excel 12.0 Macro;HDR=YES;IMEX=1;"";"                 'now it works

    'Select the data.
    strQuery0 = "SELECT * FROM Books ORDER BY Title, Year"
                        'Example, reading an unnamed range of cells:  "SELECT * FROM [Sheet1$A1:B10]"
    strQuery1 = "SELECT * FROM [data$]"      '[data$]   is the table name; in this case it's the sheet name;
                        'Once connected to an Excel workbook, a worksheet or range is the equivalent of a table or view.
                        'The table name of a worksheet is the name of the worksheet with a dollar sign ("$") appended to it, and surrounded with square brackets ("[" and "]").
    strQuery2 = "SELECT * FROM [data$] WHERE pesel <> ''"      'col B = pesel; col C = data_urzodzenia; col D = imie_nazwisko
    strQuery3 = "SELECT index, pesel, data_urodzenia, imie_nazwisko FROM [data$]"

    'Get the records.
    Set recSetAll = connection.Execute(strQuery2, , adCmdText)    'Set recordset = connection.Execute (CommandText, RecordsAffected, Options)
    Set recSetHalf = connection.Execute(strQuery3, , adCmdText)   'RecordsAffected = A Long variable to which the provider returns the number of records that the operation affected.
                                                                  'Evaluates CommandText as a textual definition of a command or stored procedure call.

    'Load the values into a variant array.
    valuesAll = recSetAll.getRows            'GetRows returns a variant array holding the Recordset's valuesAll.
    valuesHalf = recSetHalf.getRows

   ' Close the recordset and connection.
    recSetAll.Close
    connection.Close

   'lngPos = Application.Match(strPESELfromWord, valuesAll, 0)
    lngPos = Application.Match(strPESELfromWord, Application.Index(valuesAll, 2, 0), 0)
      

How about this function? Will it do the work? I've found it here.

Function FindLoop(arr, val) As Boolean
    Dim r As Long, c As Long
    For r = 1 To UBound(arr, 1)
    For c = 1 To UBound(arr, 2)
        If arr(r, c) = val Then
            FindLoop = True
            Exit Function
        End If
    Next c
    Next r
End Function

How to call this function from the main procedure and exactly in which place should I call it?

This is how the source data sheet looks like: