VBA equivalent for SQL 'IN' function

[Guest]

SQL, SAS and other 'languages' have a set function that allows the developer
to determine whether a variable's value exists within a set identified in the
'IN' statement; e.g. If myVar in ("A","B","C") then .....
Is there a VBA equivalent?

 

[Tim Williams]

msgbox
Application.WorksheetFunction.match("help",Array("this","might","help"),0)

Tim

 

[tony h]

I am not sure of a direct equivalent but three options:
1. You could use a delimited list and then

if instr(1, "!A!B!C!" , "!" & myvar & "!") <> 0 then etc

2. use

Select Case myvar
Case "A", "B", "C"
'do my stuff
End Select


3 create your own function to do it such as

Function myIN(strSearch, ParamArray strIN()) As Boolean
Dim v As Variant

myIN = False

For Each v In strIN()
If strSearch = v Then
myIN = True
Exit For
End If
Next
End Function

if myin(myvar,"B","C","A") then

Regard

 

[Guest]

Thank you Tim. I'll see if I can make it work.

msgbox
Application.WorksheetFunction.match("help",Array("this","might","help"),0)

Tim

 

[Guest]

Thanks Tony. I think that the 3rd method will work.