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.