Does X exist in Array

[Jonmo1]

I'm still learning to use arrays, but they are very useful. To this point i have used a line like this to determine if a value exists within the array.

x = array("Test","Test2","Test3"))
Y = worksheetfunction.match("Test2", x)
IF Y <> "" Then ....etc.

Is there an easier way to check if a value exists within an array?


Thanks

 

[Scott Huish]

You could loop through it if you want, but I'm not sure that it gains you much, and would probably take longer if you had a larger array. But personally, I would use a variable type for the type of value that Match returns, namely a number, and then check for a non-zero return.

Sub test()
Dim x, y As Long
On Error Resume Next
x = Array("Test", "Test2", "Test3")
y = Application.WorksheetFunction.Match("Test2", x)
If y Then
    MsgBox "Found it"
End If
End Sub

 

[Jonmo1]

So I had the basic idea right? using match?

There's no built in VBA function that would return true/false like

ISIN("Test2", X)


suppose I could make a UDF...

 

[Scott Huish]

So I had the basic idea right? using match?

Yes

There's no built in VBA function that would return true/false like

ISIN("Test2", X)

No