Select Case and Like Comparison

[Greg]

Hello,

The VBA help file for the Select Case statement says the test
expression should be a numeric or string expression. I am trying to
use a Select Case statement to perform a Like comparison on a test
string. Test1 below illustrates one of many failed attempts to use
"myString" as the test expression and a Like statement in the Case
statement.

Sub Test1()
Dim myString As String
myString = "ABCD"
Select Case myString
Case Like "A??D" 'Compile error here
MsgBox "Match"
Case Else
MsgBox "No match found"
End Select
End Sub

I did discover however, that I could use the boolean value "True" as a
test expression and the Select Case routing works as expected.

Sub Test2()
Dim myString As String
myString = "ABCD"

Select Case True
Case myString Like "A??D"
MsgBox "Match"
Case Else
MsgBox "No match found"
End Select
End Sub

Questions: Is there a way to use a string expression as the test
expression and Like in the Case statements? Would using the boolean
value True as the test expression cause instability or introduce errors
in procedure?

Thanks

 

[Jay Freedman]

Hi Greg,

Look at the Help topic on the Like operator, and you find that it
always takes two arguments, one before and one after:

result = string Like pattern

(In the biz, this is known as a "binary operator" -- "binary" for two
arguments, as opposed to "unary" for one argument.) That's why your
first try got a compile error: the expression Like "A??D" is invalid
syntax.

The "result" variable can be True or False (or, technically, it can be
Null if one of the arguments is Null). So in a distorted sort of way
Select Case True will work.

But when you're distinguishing between only two possible values, True
and False, the Select statement is massive overkill. Use If ... Then
instead. Reserve the Select for the situation where you would have
multiple ElseIf clauses in a complicated If ... Then ... ElseIf
structure, or you're looking for a value within a range.

 

[Greg Maxey]

Hi Jay,

Yeah I suspected I was doing something odd ball. Actually I was working to
figure out the format of a date string shown below and I figured a Select
Case looked cleaner than an IF ElseIf Else construction. That was when I
learned that I couldn't get Select Case oDateRgn and Case Like to work.
Thanks for the information.

Select Case True
Case oDateRng.Text Like "##/##/####"
dFormat = "MM/dd/yyyy"
Case oDateRng.Text Like "##/##/##"
dFormat = "MM/dd/yy"
Case oDateRng.Text Like "## [A-z]??. ##"
dFormat = "dd MMM. yy"
Case oDateRng.Text Like "## [A-z]* ####"
dFormat = "dd MMMM yyyy"
Case oDateRng.Text Like "[A-z]* ##, ####"
dFormat = "MMMM dd, yyyy"
Case Else
dFormat = "MMMM dd, yyyy"
End Select