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