Using InStr instead of And in If statement?

Question

Instead of using AND in a long If statement, I'm using InStr to match a dynamic value to a known list. Like this:

If InStr("John, George, Harry", personName) Then...

Is this okay? Should I be using AND instead? Is there a better way?

Thanks.

Answer 1

You could store the names in a hash and then test if it is in the hash which would be faster than checking whether it is in a string or in a list.

Answer 2

What about substrings, like Anne-Marie and person name = Anne?

You could use a select case statement instead...

Lifted example from DATABISON

Sub My_Select_Case_3_Text()
Dim my_val As String

my_val = "Pineapple"
Select Case my_val
Case "Apple": MsgBox "The fruit is Apple"
Case "Orange": MsgBox "The fruit is Orange"
Case "Pineapple": MsgBox "The fruit is Pineapple"
End Select
End Sub

Answer 3

No, that's not okay. If you do it that way, you'd match someone named "n, Ge" and "Geor".

My VB is a bit rusty, but you can do:

Dim namearray() As String = {"John", "George", "Harry"}
Dim name As String = "John"
For i = LBound(namearray) To UBound(namearray)
    If namearray(i) = personName Then
        ... i is the array index ...
    End If
Next i

I don't know if VB6 has Array.indexOf, but if it does, then you can also use the approach described here.

If your list of names is very large, you may want to use a Dictionary

Answer 4

A couple of options

If Instr("[John][George][Harry]","[" & personname & "]") Then

will prevent partial matches. If you happen to be in Excel vba, you could use the built in MATCH worksheet function

Application.WorksheetFunction.Match("John",array("John","George","Harry"),false)

Answer 5

I don't think there is any single answer for all occasions.

If the list is very short and fixed use a series of tests with Or:

If personName = "John" Or personName = "George" Or personName = "Harry" Then

A mid-sized list could be represented as a String as suggested already, with a slight optimization:

If InStr("$John$George$Harry$", "$" & personName & "$") Then 

You might also use an array as your list, along with Filter():

If UBound(Filter(Array("$John$", "$George$", "$Harry$"), _
                 "$" & personName & "$")) >= 0 Then

Those options probably work better if you have a pre-built String or Array rather than inlining them within an expression.

For a longer list you might use a Scripting.Dictionary object to hold the test cases. This incorporates a collision-resolved hash, and it has an Exists() method. A VB6 Collection works too though you need to use exception trapping to implement Exists-like functionality.

Even better if you have multiple "fields" to test (name and eye color?) you can use a fabricated ADO Recordset and its Filter property. This makes it easy to determine whener you have the George with blue eyes or the Harry with brown eyes. For a longer list of candidates you can set the dynamic property Optimize to True on the Recordset's Fields you want hashed for better performance.