VBA select case like: operator, string, statement

VBA select case like: operator, string, statement

Under normal circumstances, the Select Case statement doesn't work with the Like operator. In this article you will learn how to overcome this limitation (If, ElseIf,...)

How to use Select Case & Like?

The following code demonstrates that Select Case doesn't work with the Like operator:

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" ...

How to use the True expression?

To use the Like operator in a Select Case, you will need to add the True expression.

Sub Select_Case_True_Like()
word = "KAO"
Select Case True
    Case word Like "*K*K*"
        MsgBox "Good"
    Case Else
        MsgBox "Not Good"
End Select
word = "KAKAO"
Select Case True
    Case word Like "*K*K*"
        MsgBox "Good"
    Case Else
        MsgBox "Not Good"
End Select
End Sub

What is a useful Boolean function?

The function

Function Case_True_Like(word As String) As Boolean
Select Case True
    Case word Like "*K*K*"
        Case_True_Like = True
    Case Else
        Case_True_Like = False
End Select
End Function

Invoking the function

Sub Test()
MsgBox Case_True_Like("KAKAO")
End Sub
Need more help with Excel? Check out our forum!
Subject Replies
Unable to change date format in Excel 39
How to calculate positions (1st,2nd,3rd...) in Excel 6
How to convert number into text in Excel 9
Copying data from one Excel sheet to another. 30
Convert number to spelling 28
Excel - IF function with multiple words 14
Date formatting in dd/mm/yyyy 11
Automatically transfer data from one sheet to another 22
If a cell contains any text then add a value 20
Comparing two columns of names in Excel 8
Around the same subject

Excel