In Operator Equivalent
 

Hello gang! I was wondering what is the VBA equivalent of the IN operator.
For instance, in SAS I might use:

IF x IN ("Me","You","Dog","Boo") THEN DO

instead of having to write

IF x="ME" or x="You" etc.

Thanks for your help!
--
Dr. M

In Operator Equivalent
 

DrM

If Not IsError(Application.Match(x, Array("Me", "You", "Dog", "Boo"),
False)) Then

There really should be an easier way to do it.

--
Dick Kusleika
Excel MVP
Daily Dose of Excel
www.dicks-blog.com

Dr. M wrote:
Hello gang! I was wondering what is the VBA equivalent of the IN
operator. For instance, in SAS I might use:

IF x IN ("Me","You","Dog","Boo") THEN DO

instead of having to write

IF x="ME" or x="You" etc.

Thanks for your help!

In Operator Equivalent
 

Not as elegant as a native function would be but at least keeps it
completely in VB ;-)

Function ISIN(x, StringSetElementsAsArray)
ISIN = InStr(1, Join(StringSetElementsAsArray, Chr(0)), _
x, vbTextCompare) 0
End Function
Sub testIt6()
Dim x As String
x = "dog"
MsgBox ISIN(x, Array("Me", "You", "Dog", "Boo"))
End Sub

The Join function is available in VB6, though it is not difficult
writing one for VB5 (XL97).

Of course, strictly speaking, the correct data structure for a set is a
collection. But, that is another story.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
DrM

If Not IsError(Application.Match(x, Array("Me", "You", "Dog", "Boo"),
False)) Then

There really should be an easier way to do it.

In Operator Equivalent
 

This will prevent ISIN("You", Array("Yours","Mine")) from being True

ISIN = InStr(1, Chr$(0) & Join(StringSetElementsAsArray, Chr$(0)) &
Chr$(0), _
Chr$(0) & x & Chr$(0), vbTextCompare) 0

--
Dick Kusleika
Excel MVP
Daily Dose of Excel
www.dicks-blog.com

Tushar Mehta wrote:
Not as elegant as a native function would be but at least keeps it
completely in VB ;-)

Function ISIN(x, StringSetElementsAsArray)
ISIN = InStr(1, Join(StringSetElementsAsArray, Chr(0)), _
x, vbTextCompare) 0
End Function
Sub testIt6()
Dim x As String
x = "dog"
MsgBox ISIN(x, Array("Me", "You", "Dog", "Boo"))
End Sub

The Join function is available in VB6, though it is not difficult
writing one for VB5 (XL97).

Of course, strictly speaking, the correct data structure for a set is
a collection. But, that is another story.


In article ,
says...
DrM

If Not IsError(Application.Match(x, Array("Me", "You", "Dog", "Boo"),
False)) Then

There really should be an easier way to do it.

In Operator Equivalent
 

DUH! What a basic error! Thanks for catching it.

Knew I should have stuck with a collection. {vbg}

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
This will prevent ISIN("You", Array("Yours","Mine")) from being True

ISIN = InStr(1, Chr$(0) & Join(StringSetElementsAsArray, Chr$(0)) &
Chr$(0), _
Chr$(0) & x & Chr$(0), vbTextCompare) 0