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
How to calculate positions(1st,2nd,3rd...) in Excel 4
Unable to change date format in Excel 39
How to convert number into text in Excel 9
Convert number to spelling 28
Date formatting in dd/mm/yyyy 11
Removing " false" value in "IF" 10
Automatically transfer data from one sheet to another 22
Comparing two columns of names in Excel 8
Excel - To generate unique ID 4
converting number into words (Excel) 6

Excel