Check if a value exists in an array VBA

Check if a value exists in an array VBA

To check if a value exists in an array, we can loop through its elements. However there is another solution! You can use the INDEX () function, native to Excel and in the case of two-dimensional arrays use a combination of the INDEX/MATCH function. However, if the value is not found, Application.Match returns an error. You will need to take into account any possible errors, as well as the number of dimensions of the array variable. This small function will help you out.

Using the Match function

Function EstDans(mot As String, Tabl) As Boolean
Dim Dimension As Byte, j As Integer
On Error Resume Next
If IsError(UBound(Tabl, 2)) Then Dimension = 1 Else Dimension = 2
On Error GoTo 0
Select Case Dimension
    Case 1
        On Error Resume Next
        EstDans = Application.Match(mot, Tabl, 0)
        On Error GoTo 0
    Case 2
        For j = 1 To UBound(Tabl, 2)
            On Error Resume Next
            EstDans = Application.Match(mot, Application.Index(Tabl, , j), 0)
            On Error GoTo 0
            If EstDans = True Then Exit For
        Next
End Select
End Function

Invoking the function

Sub test()
Dim Tb(), i As Integer
'tb 2 dimensions :
Tb = Range("A2:C16").Value
Debug.Print EstDans(MaValeur, Tb)
Erase Tb
'tb 1 dimension :
ReDim Preserve Tb(15)
For i = 0 To 14
    Tb(i) = Cells(i + 2, 1)
Next
Debug.Print EstDans(MaValeur, Tb)
End Sub

Using a loop

The structure of this function is similar to the one using Match.

Function BoucleSurTabl(mot As String, Tb)
Dim Dimension As Byte, i As Long, j As Long
On Error Resume Next
If IsError(UBound(Tb, 2)) Then Dimension = 1 Else Dimension = 2
On Error GoTo 0
Select Case Dimension
    Case 1
        For j = LBound(Tb) To UBound(Tb)
            If Tb(j) = mot Then BoucleSurTabl = True: Exit Function
        Next
    Case 2
        For i = LBound(Tb, 1) To UBound(Tb, 1)
            For j = LBound(Tb, 2) To UBound(Tb, 2)
                If Tb(i, j) = mot Then BoucleSurTabl = True: Exit Function
            Next j
        Next i
End Select
End Function

Observations

Against all odds, you will get a better result with the loop function on large arrays than using Application.Match. Testing a 2-dimensional array with a the following Range ("A1: Y20002").

  • Using the Match function: 8.300781 seconds.
  • Using the loop: 0.4375 seconds.

Testing a one-dimensional array:

  • Using the Match function: instant
  • Using the loop: 0.015625 seconds
  • You can download the sample workbook here.
More Excel questions? Check out our forum!
Subject Replies
How to calculate positions(1st,2nd,3rd...) in Excel 4
Unable to change date format in Excel 39
How to convert number into text in Excel 9
Convert number to spelling 28
Date formatting in dd/mm/yyyy 11
Removing " false" value in "IF" 10
Automatically transfer data from one sheet to another 22
Comparing two columns of names in Excel 8
Excel - To generate unique ID 4
converting number into words (Excel) 6

Excel