VBA select case like: operator, string, statement

Both Select Case and Like operators are powerful and extremely useful commands in VBA language , but under normal circumstances, they don't work together. In this article you will learn how to overcome this limitation (If, ElseIf,...)

Why use Select Case and Like together?

Select Case is an excellent and efficient tool for setting specific instructions when you have a multiple different conditions (like saying "Congratulations" if a student gets a high grade, or "Fail" if they get a low one), and Like is a very efficient way of sorting through lots of data and pulling out commonalities (like all email addresses that end with @gmail.com). But generally, they cannot be used together, as demonstrated in this code: 

Sub Select_Case_Like()
word = "KAKAO"
Select Case word
    Case mot Like "*K*K*"
        MsgBox "Good"
    Case Else
        MsgBox "Not Good"
End Select
End Sub

Whatever the content of the word variable, it will always return "not good" ...

But help is at hand! In order to run both functions at the same time, you can either use True expression, or Boolean values. 

How to use the True expression?

To use the Like operator in a Select Case,  add the True expression. So instead of comparing a single value against multiple cases, you can directly use a condition as the expression. For example:

Dim fruit As String
fruit = "Apple"

Select Case True
    Case fruit Like "*apple*"
        ' Code to be executed if the fruit name contains "apple"
    Case fruit Like "*orange*"
        ' Code to be executed if the fruit name contains "orange"
    Case Else
        ' Code to be executed if none of the conditions are met
End Select

What is a useful Boolean function?

Another way to combine Select Case and Like together is to directly compare Boolean values. 

The function

Dim isFound As Boolean
isFound = True

Select Case isFound
    Case True
        ' Code to be executed if isFound is True
    Case False
        ' Code to be executed if isFound is False
End Select

Here, the expression being evaluated is the Boolean variable isFound. Depending on its value (True or False), the corresponding case will be executed.