***NEW CUSTOMER OFFER***
Get 15 minutes of free Live Help!

Determine if a value is in a VBA array

0 comments

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

Code:

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

Exit_Proc:
    On Error Resume Next
    Exit Function

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

End Function

Download Code:

basInArray.zip

Leave a Reply

Your email address will not be published. Required fields are marked *

Live Chat Software
%d bloggers like this: