Arrays

[Colin Macleod]

Is there a function which can check whether a particular value occurs in an
array? For example, if the array contained the elements "red" "orange" and
"green" then a check against "red" would come up with "true" whereas "blue"
would come up with "false". I realise I could loop through each element in
turn - I wondered whether there was a quicker way.

Thanks

Colin Macleod

 

[Chip Pearson]

Colin,

You can use Application.Match to test for the existence of an
item in an array. For example,

Dim S(1 To 3) As String
Dim Res As Variant
S(1) = "red"
S(2) = "green"
S(3) = "blue"
Res = Application.Match("blue", S, 0)
If IsError(Res) Then
Debug.Print "Not in array"
Else
Debug.Print "Item: " & Res
End If

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com