Is there an "IN" statement in VBA like the one in SQL Server?

[Steven Kogan]

I was trying Google an answer for this question and couldn't find a good substitute for SQL's IN feature in VBA from the postings that came up.

In VBA the closest I could get to a construct of:

   If myVar in("abc", "def", "hgi") then
      'insert code here
   End If 

is:

Select Case myVar
    Case "abc", "def", "hgi"
        'insert code here
End Select 

That's okay, but does someone have a function to simulate SQL's 'in' statement in VBA? The idea is to replace the expression:
myVar = "abc" or myVar = "def" or myVar = "hgi" etc... with something like:

fnIn(myVar, "abc", "def", "hgi"... (string array)...)

[gershwyn]

I know of no function native to VBA that will duplicate SQL's IN statement. I assume what you dislike about the Select statement in this case is that it uses hardcoded values, and you'd like to work with something a little more dynamic.

If you have your heart set on the behavior you described, one option is to roll your own. This function accepts a string value and an array, and returns true if the former is found within the latter:

Public Function fnIn(sCandidate As String, aList() As String)
  fnIn = False
  For Each Value In aList
    If sCandidate = Value Then
      fnIn = True
      Exit Function
    End If
  Next
End Function

[NeoPa]

Good thinking Steve, but as Gershwyn says, there's nothing built-in.

You already have my best idea included (Select Case) so the option is to roll your own, or not.

Welcome to Bytes!

[Steven Kogan]

Thanks Gershwyn and NeoPa. That answers my question.

[NeoPa]

As an alternative, that allows for multiple parameters rather than an array, this may help :

Public Function In(strField As String, _
                   ParamArray avarArgs() As Variant) As Boolean
    Dim strVal As String
 
    'In starts automatically as False
    For Each strVal In avarArgs
        If strField = strVal Then
            In = True
            Exit Function
        End If
    Next strVal
End Function

This would be invoked in the following way :

If In(strLetter, "A", "B", "C", "D") Then _
    Call MsgBox("Found in A - D.")