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
Download link
You can download the sample workbook here.
Image: ©123RFom