Type mismatch error when using match on an array in Excel VBA

Question

This code works fine

Dim MyArray

MyArray = Array("fee", "fi", "fo", "fum")

If Application.Match("fee", MyArray, 0) Then
   Debug.Print "Pass"
End If

However, this code throws a type mismatch error on the if statement

Dim MyArray

MyArray = Array("fee", "fi", "fo", "fum")

If Application.Match("foo", MyArray, 0) Then
   Debug.Print "Pass"
End If

What am I missing?

Answer 1

Application.Match

• The following two usually work the same:

  If IsNumeric(Application.Match("foo", MyArray, 0)) Then
  If Not IsError(Application.Match("foo", MyArray, 0)) Then
  

• If you pass correct parameters, the result can only be an integer (whole number) or a #N/A error.

• You shouldn't be paranoid about a different error than the #N/A error (Error 2042 in VBA).

• If you can't help it, here's something to play with.

Sub Test()
    
    ' Uncomment or out-comment any of the following lines before 'Get the index.'
    ' and observe the behavior.
    
    On Error GoTo ClearError
    
    Dim MyArray As Variant
    'Dim MyArray() As Variant
    'Dim MyArray() As String
    'Dim MyArray As String
    
    MyArray = Array("fee", "fi", "fo", "fum")
    
    ' Get the index.
    Dim Index As Variant: Index = Application.Match("fee", MyArray, 0)
    
    ' Display the result.
    If IsNumeric(Index) Then
        Debug.Print "Pass: Index = " & Index
    Else
        If Index = CVErr(xlErrNA) Then ' xlErrNA = 2042
            Debug.Print "No match"
        Else
            Debug.Print "Something unexpected happened (" & CStr(Index) & ")!"
        End If
    End If

ProcExit:
    Exit Sub
ClearError:
    MsgBox "Run-time error " & "[" & Err.Number & "]:" & vbLf & vbLf _
        & Err.Description, vbCritical
    Resume ProcExit
End Sub