505.369.1650 [email protected]

This function will return a True if a particular value is found in an array.  It works with one-dimension arrays only.

For example, if astrCity is an array, you could check for the value Chicago like this:

If InArray(astrCity, “Chicago”) Then
        ' Run logic here.
End If


Public Function InArray(ArrayToTest, ValueToFind) As Boolean
' This procedure passes back True if it finds ValueToFind in ArrayToTest.
' ArrayToTest is a one-dimension array.
' InArray() Version 1.0.0
' Copyright © 2014 Extra Mile Data, www.extramiledata.com.
' For questions or issues, please contact [email protected].
' Use (at your own risk) and modify freely as long as proper credit is given.
On Error GoTo Err_Handler

    Dim lngElem As Long
    ' Loop through all the elements in ArrayToTest.  Look for
    ' the ValueToFind.  If it is found, exit with a true.

    For lngElem = 0 To UBound(ArrayToTest)
        If ArrayToTest(lngElem) = ValueToFind Then
            InArray = True
            GoTo Exit_Proc
        End If
    Next lngElem

    ' Didn't find the value, so pass back a False.
    InArray = False

    On Error Resume Next
    Exit Function

    MsgBox Err.Number & " " & Err.Description, vbCritical, "InArray()"
    Resume Exit_Proc

End Function

Download Code: