VBA array functions: find value in array

Array function to find a particular value in either a one-dimensional or multi-dimensional array. Returns false if the value is not found and true if it is.

Table of Contents

Function
Parameters
Usage example

Function

VBA
Function InArray(ByVal searchValue As Variant, arr As Variant, Optional caseInsensitive As Boolean) As Boolean

    Dim element As Variant
    
    On Error Resume Next
    If Not IsArray(arr) Then Exit Function
    
    If caseInsensitive Then searchValue = LCase(searchValue)
    For Each element In arr
        If caseInsensitive Then element = LCase(element)
        If element = searchValue Then
            InArray = True
            Exit For
        End If
    Next element

End Function

Parameters

Name	Optional/ Required	Data type	Description
searchValue	Required	Variant	The value to be found inside the input array.
arr	Required	Variant	The input array in which the search value is to be found.
caseInsensitive	Optional	Boolean	Default value is false (i.e. case-sensitive search). Specifies whether the search should be case-insensitive (“a” = “A”), or not (“a” ≠ “A”).

Usage example

VBA
Sub TestInArray()

    Debug.Print InArray("abc", Array(1, 2, "ABC", 4), True) ' true
    Debug.Print InArray("abc", Array(1, 2, "ABC", 4), False) ' false

End Sub