VBA - Using the Like operator with Select Case

December 2016


VBA - Using the Like operator with Select Case


Introduction

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,...)

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.

Related :

This document entitled « VBA - Using the Like operator with Select Case » from CCM (ccm.net) is made available under the Creative Commons license. You can copy, modify copies of this page, under the conditions stipulated by the license, as this note appears clearly.