In Operator Equivalent

[Guest]

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!

 

[Dick Kusleika]

DrM

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

There really should be an easier way to do it.

 

[Tushar Mehta]

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

 

[Dick Kusleika]

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

 

[Tushar Mehta]

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