Evaluate if a variable is in a set of values

ronald_valdivia
ronald_valdivia used Ask the Experts™
on
Hi, I'm new in Visual Basic. I need to know how to do something like this in VB:

If CustomerName in ("Mike", "John", "Elizabeth") Then
......
......
......
End If
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
Two methods come to mind:

If CustomerName = "Mike" Or CustomerName = "John" or CustomerName = "Elizabeth" then
  do something
End If

2nd method

If Instr("MikeJohnElizabeth",CustomerName) > 0 then
  do something
End If

Author

Commented:
Isn't there something like defining a set and evaluate if the value of variable is in the set, because I wouldn't like to code a lot of "or" since I have several possible values for that variable

Commented:
'You can also use the case statement.

Select Case CustomerName
    Case "Mike", "John", "Elizabeth"
        'Do something
    Case "Jefferson", "Chrissie"
        'Do something
    Case Else
        'Do something else
End Select


'The Select Case will evaluate the CustomerName and then check each Case to determine if the condition is meet.  If the condition is meet, then it will execute the preceding code and then exit the Select Case.  You can add as many Cases as you need and use the Case Else if you want something to happen if none of the other conditions are meet.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Commented:
This might be a little complex, but it's just an idea:

Create a class called cVariable:

Private m_var As String

Public Function IsWithIn(s1 As String) As Boolean

If InStr(m_var, s1) > 0 Then
    IsWithIn = True
Else
    IsWithIn = False
End If

End Function


Public Property Get Value() As String

Value = m_var

End Property

Public Property Let Value(ByVal vNewValue As String)

m_var = vNewValue

End Property

and use it this way:

Dim m_var as cVariable
Set m_var = New cVariable
m_var.Value = "MikeJohnElizabeth"

If m_var.IsWithIn("Mike") Then
    MsgBox "Yes"
Else
    MsgBox "No"
End If


warning,
don't use code like:
If Instr("MikeJohnElizabeth",CustomerName) > 0 then
 do something
End If
There are several problems with this.

1) If the customer name is Neli, or Beth, or liz, you will get an erroneous hit

2) The string could include such things like Michael and the customer name is Mic

You could alternatively set the strings up in a collection and test that way.  The code is as follows:

Dim colNames As VBA.Collection

Private Sub Command1_Click()
    Dim sTestName As String
    sTestName = InputBox("Enter a name to check", "Check Name", "mike")
   
    If IsNameThere(sTestName) = True Then
        MsgBox sTestName & " appears in the collection"
    Else
        MsgBox sTestName & " DOES NOT appear in the collection"
    End If
   
End Sub

Private Sub Form_Load()
   
    Set colNames = New VBA.Collection
    colNames.Add "Mike", "Mike"
    colNames.Add "John", "John"
    colNames.Add "Elizabeth", "Elizabeth"
   
End Sub

Private Function IsNameThere(ByVal v_sName As String) As Boolean

    On Error Resume Next
    Dim sName As String
    sName = colNames(v_sName)
    IsNameThere = (Err.Number = 0)
    Err.Clear
End Function

Commented:
twalgrave, you are right, so we could modify it as follows:

If Instr("Mike,John,Elizabeth",CustomerName) > 0 then
 do something
End If

Commented:
sorry, I jumped the gun here. My answer is still flawed.  twalgrave's answer should work well here.

Commented:
Great solution by twalgrave.

It's one tiny weakness is that it does a case-insensitive search to find the item.  This also means you can't put "John" and "john" in the same collection.  Usually that's not important though.

Stronger solutions would take more code, which would be inappropriate for a beginner.

Commented:
willisp:

Your solution has merit, too, in some situations, but it requires a couple tweaks.
(1) Need delimiters before and after the strings.  It doesn't matter if the before/after delimiters are the same or different.
(2) Can't allow those delimiters to be in the strings.
(3) Must compare to the string surrounded by the delimiters.
(4) Optionally, someone could take this further and wrap up the needed routines in a Class and add additional methods.  Once you got that far, it would even be easy to hide the fact that the data's all stored in a string.

Private Sub Command3_Click()
    Dim sGroup As String
    sGroup = AddItemToGroup("Mike", sGroup)
    sGroup = AddItemToGroup("John", sGroup)
    sGroup = AddItemToGroup("Elizabeth", sGroup)

    Debug.Print IsItemInGroup("Mike", sGroup)       ' => True
    Debug.Print IsItemInGroup("Mik", sGroup)        ' => False
End Sub

Public Function AddItemToGroup(ByVal sItem As String, ByVal sGroup As String) As String
    If InStr(sItem, WrapItem("")) > 0 Then
        'Raise an error here.
    Else
        AddItemToGroup = sGroup & WrapItem(sItem)
    End If
End Function

Public Function WrapItem(ByVal sItem As String) As String
    WrapItem = "[" & sItem & "]"
End Function

Public Function IsItemInGroup(ByVal sItem As String, ByVal sGroup As String) As Boolean
    IsItemInGroup = (InStr(sGroup, WrapItem(sItem)) > 0)
End Function

Commented:
I occurs to me that the error trap should be here in WrapItem, and could then be left out of AddItemToGroup.

Public Function WrapItem(ByVal sItem As String) As String
    If InStr(sItem, WrapItem("")) > 0 Then
        'Raise an error here.
    Else
        WrapItem = "[" & sItem & "]"
    End If
End Function
farsight:  Nice solution also.  Yes, I knew the case-insensitivity of the testing and I count it in this case as an asset.
Function IsMember(ByVal Item, ParamArray List()) As Boolean
    Dim I               As Long
   
    For I = LBound(List) To UBound(List)
        If Item = List(I) Then
            IsMember = True
            Exit Function
        End If
    Next
    IsMember = False
End Function
'Call
If IsMember(CustomerName,"Mike", "John", "Elizabeth") Then
...
PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in Community Support that this question is:
- award the points to TigerZhao
Please leave any comments here within the
next seven days.
Per recommendation, force-accepted.

Netminder
EE Admin

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial