Equivalent of "in" for If Then in VB Excel

Question

I am very new to using VBA macros in Excel. I want to check for a string value in a set of other string values. For example: is value of cc variable in ("Q6", "_Q6", "1_Q", "2_Q", "3_Q", "4_Q") where cc could be Q6.

My pseudo-code is:

If cc in ("Q6", "_Q6", "1_Q", "2_Q", "3_Q", "4_Q") Then  

I don't know what operator should be in place of in. So, what should be instead of in because currently it giving me syntax error. Thanks a lot for your help.

Answer 1

The Select Case statement can do this quite nicely (and efficiently). See http://www.exceltrick.com/formulas_macros/vba-select-case-statement/

Select Case cc
    Case "Q6", "_Q6", "1_Q", "2_Q", "3_Q", "4_Q"
        ' If ... Then
        ' Stuff you want to do if any of the above
    Case Else
        ' Else
        ' Whatever you if none of the above
End Select

Answer 2

You can use the Filter function which will return a string array if the match is found. For your example:

UBound(Filter(Array("Q6", "_Q6", "1_Q", "2_Q", "3_Q", "4_Q"), "Q6", True, vbBinaryCompare))

That returns 1 but note that if the 3rd argument is False then it will return the items of the array that don't match the input, which would be 5.

A more complete example:

Option Explicit

Sub Test()

    Dim varData As Variant
    Dim strCC As String
    Dim varFilter As Variant
    Dim intCounter As Integer

    varData = Array("Q6", "_Q6", "1_Q", "2_Q", "3_Q", "4_Q")
    strCC = "foo"
    varFilter = Filter(varData, strCC, True, vbBinaryCompare)
    For intCounter = LBound(varFilter) To UBound(varFilter)
        Debug.Print varFilter(intCounter)
    Next intCounter

End Sub