VBA equivalent for SQL 'IN' function

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?

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

Tim

--
Tim Williams
Palo Alto, CA


"Mitch" <Mitch@discussions.microsoft.com> wrote in message
news:6530B3E4-5FBC-454F-B910-663C13DCAE15@microsoft.com...
> 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?

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

Regards

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

"Tim Williams" wrote:

> msgbox
> Application.WorksheetFunction.match("help",Array("this","might","help"),0)
>
> Tim
>
> --
> Tim Williams
> Palo Alto, CA
>
>
> "Mitch" <Mitch@discussions.microsoft.com> wrote in message
> news:6530B3E4-5FBC-454F-B910-663C13DCAE15@microsoft.com...
> > 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?
>
>
>

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

"tony h" wrote:

>
> 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
>
> Regards
>
>
> --
> tony h
> ------------------------------------------------------------------------
> tony h's Profile: http://www.excelforum.com/member.php...o&userid=21074
> View this thread: http://www.excelforum.com/showthread...hreadid=507480
>
>