VBA - Check if a value exists in an array

September 2016


VBA - Check if a value exists in an array


Introduction

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 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


Related :

This document entitled « VBA - Check if a value exists in an array » from CCM (ccm.net) is made available under the Creative Commons license. You can copy, modify copies of this page, under the conditions stipulated by the license, as this note appears clearly.