check if value exists in a vba array

[peioz]

I want to check if a value exists in an array. And if so, take action.
I do that with the following code. But I don't think this is an elegant way of doing so :

For Each k In projects()
If k = "var" then
'marker for value in array
l = 1
Else
End If
Next k

If l = 1 Then
**** do something *****
End If

anyone ?

 

[Andrew Poulsom]

You can use the Match WorksheetFunction like this:

Sub Test()
    Const var As String = "var"
    Dim projects As Variant
    Dim x As Long
    projects = Array("x", "var", "y")
    On Error Resume Next
    x = WorksheetFunction.Match(var, projects, False)
    If Err = 0 Then
'       **** do something ****
        MsgBox "Found"
    Else
'       **** clear error ****
        Err.Clear
        MsgBox "Not Found"
    End If
    On Error GoTo 0
End Sub