VBA - Using the Like operator with Select Case

January 2017


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


Published by deri58. Latest update on August 6, 2014 at 10:43 AM by deri58.
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).