VBA: Function to search in an array

[rajat_magic]

Is there a function in VBA that can establish whether a particular value is part of an array, or is it necessary to actually cycle through all of the values and check individually?

 

[billritz]

aMyArray = Array("W", "X", "Y", "Z")
sLookFor = "W"
MsgBox (aMyArray(Application.Match(sLookFor, aMyArray, 0) - 1))

 

[jimrward]

Is your array sorted or unsorted

 

[billritz]

not sorted but Match throws an error if the value is not in the array, so I wrapped it in IsError. I used something like
If IsError(Application.Match(sLookFor, aMyArray, 0)) Then
MsgBox ("NOT FOUND")
Else
MsgBox ("FOUND")
End If