2
Thanks

A few words of thanks would be greatly appreciated.

VBA - Using the Like Operator With Select Case

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



Select Case & Like - Test

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

Using the True expression -Test

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

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

Conclusion

This article offers an alternative to the use of If ElseIf.
Photo: Unsplash
2
Thanks

A few words of thanks would be greatly appreciated.

Ask a question
CCM is a leading international tech website. Our content is written in collaboration with IT experts, under the direction of Jean-François Pillou, founder of CCM.net. CCM reaches more than 50 million unique visitors per month and is available in 11 languages.

Related

This document, titled « VBA - Using the Like Operator With Select Case », is available under the Creative Commons license. Any copy, reuse, or modification of the content should be sufficiently credited to CCM (ccm.net).