Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
about & operator | Excel Discussion (Misc queries) | |||
what does ~ operator mean? | Excel Discussion (Misc queries) | |||
Like Operator | Excel Programming | |||
Need help with the Like Operator | Excel Programming | |||
Can the AND operator be of use here? | Excel Programming |