Excel VBA: Select Case if ActiveCell like "*string*"

Question

I'm working on a macro that takes the current value of the ActiveCell and changes that value based on a select case.

However, I am unable to determine if the ActiveCell contains a wild card string. I am not sure if my syntax is correct. How can I get my select case to compare?

Select Case ActiveCell.Value

    Case ActiveCell.Value Like "*string*"
        ActiveCell.Value = "contains string"

End Select

Answer 1

It is possible to use wildcards. Keep these two things in mind: First, string comparison expressions evaluate to a Boolean data type (True/False); Second, per the developer reference for the Select...Case statement, any Case expression(s) must be "implicitly convertible" to the same data type as that of the Select Case test expression. To demonstrate, let's use the code from the original post above.

Select Case ActiveCell.Value  '-->ActiveCell.Value is the test expression

    Case ActiveCell.Value Like "*string*"  '-->(ActiveCell.Value Like "*string*") is the Case expression.
        ActiveCell.Value = "contains string"

End Select

If we selected a cell containing a string value in any worksheet, then used the Immediate Window to test the data type of these two expressions using the TypeName() function, we would get the following:

?TypeName(ActiveCell.Value)
 String
?TypeName(ActiveCell.Value Like "*string*")
 Boolean

As you can see, Select...Case will not work here because the data types are not implicitly the same (a minor exception to this would be if the macro was run on any cells in a worksheet that contained the single-word values of "True" or "False", which Excel automatically converts to Boolean).

The solution is actually a very simple one. Just change the test expression to True.

Select Case True

    Case (ActiveCell.Value Like "*string*")
        ActiveCell.Value = "contains string"

End Select

This is essentially the same as writing:

If (ActiveCell.Value Like "*string*") = True Then ActiveCell.Value = "contains string"

It's mostly a matter of personal preference whether you use If...Then or Select...Case. I personally like the Select...Case construct due to the readability of the code, but also for other benefits (such as the ability to pass each Case a list of expressions separated by commas rather than using an OR operator, making the code more concise).

Answer 2

Is and like cannot be used as comparison operators in a select case statement in VBA.
If possible it would be better to substitute in an if-then statement:

If ActiveCell.Value Like "*string*" Then ActiveCell.Value = "string"

From the VBA glossary for comparison operators:

A character or symbol indicating a relationship between two or more values or expressions. These operators include less than (<), less than or equal to (<=), greater than (>), greater than or equal to (>=), not equal (<>), and equal (=). Additional comparison operators include Is and Like. Note that Is and Like can't be used as comparison operators in a Select Case statement.