VBA: optimize If conditional expression?

[Joe User]

I want to write:

If Left(s,2) = "Mr" Or Left(s,2) = "Ms" Then
...statements...
End If

But that will evaluate both Or operands even if the first one is true.

Is there a more efficient way to implement this?

Note: That example is a simplification. In actual practice, the
conditional expressions are more complicated. So don't try to optimize the
particular example. Optimize the paradigm.

My best:

doit = (Left(s,2) = "Mr")
If Not doit Then doit = (Left(s,2) = "Ms")
If doit Then
...statements...
End If

But that gets a little tedious. Alternatively:

Select Case True
Case Left(s,2) = "Mr", Left(s,2) = "Ms"
...statements....
End Select

But that seems a bit convoluted.

Am I overlooking the obvious?

 

[JLGWhiz]

Well, the options appear to be using If...ElseIf...Else...End If
or Select Case statements.

As a side note, I believe in the Or statement that if the first part of the
statement is true, it does not check the second part. It does check both
conditions in an And statement because they both have to be true for the
statement to be true.

But, without seeing your complex problem, that's about it.

 

[Joe User]

Well, the options appear to be using If...ElseIf...Else...End If

The "problem" is: it would have to be of the form:

If condition1 Then
Goto doit
Else If condition 2 Then
Goto doit
Else If condition3 Then
doit:
...statements...
End If

Not really a problem. Just undesirable. Also, "...statements..." cannot be
procedurized easily.

or Select Case statements.

I'm getting used to that form, as I have been using it in the interim. Very
malleable: easy to add additional conditions.

I believe in the Or statement that if the first part of
the statement is true, it does not check the second part

Since I asserted "that will evaluate both Or operands even if the first one
is true", you might think that I tested this, or at the very least, it might
motivate you to test it yourself.

Try the following....

Sub doit1()
If tryit(1, True) Or tryit(2, True) Then
tryit 0, False
End If
End Sub

Sub doit2()
Select Case True
Case tryit(1, True), tryit(2, True)
tryit 0, False
End Select
End Sub

Private Function tryit(n As Integer, t As Boolean) As Boolean
MsgBox "tryit " & n & " " & t
tryit = t
End Function


Remember that I am using Excel and VBA.


----- original message -----

 

[Dana DeLouis]

Select Case True

Case Left(s,2) = "Mr", Left(s,2) = "Ms"
...statements....
End Select

Hi. As a side note, the string function "Left() is called each time.
The idea behind Case is to do the "hard" calculation just once and store
it internally. Here's just an idea...

s = "Ms Smith"
Select Case Left$(s, 2)
Case "Mr", "Ms"
Debug.Print "Doing Something"
End Select


If I understand the question, maybe something along this line...

Sub TestIt()
Dim s As String
Dim k As String
s = "Mr John Smith"
k = Left$(s, 2)

If MemberQ(k, "Mr", "Ms", "Jr", "this", "that") Then
Debug.Print k; " is a member of list"
End If
End Sub

Function MemberQ(k, ParamArray v()) As Boolean
Dim j As Long
For j = LBound(v) To UBound(v)
MemberQ = StrComp(k, v(j), vbTextCompare) = 0
If MemberQ Then Exit Function
Next j
End Function


Be careful of the logic error of Left$(x,2) as a name could be "Mrs
Smith", and it would be captured as Mr.

= = = = = = = = = = =
HTH
Dana DeLouis

 

[Joe User]

As a side note, the string function "Left() is called each time.

"As a side note", I distinctly remember writing: "don't try to optimize the
particular example. Optimize the paradigm."

My question was about VBA control statements.

I was hoping that someone would say, "Oh, in VB, instead of using the Or
operator, you should use the BOr operator, which does a shunted boolean
left-to-right evaluation, similar to the difference between | and || in C".

I'm getting the sense that no such alternative exists in VB.

Too bad. The point is: we often want to write statements of the __form__
(f'get the details):

If this = "that" Or (that = "this" And they = "them") Or he = "she" Then

It's not so bad when everything is a numeric comparison. But with string
comparisons, it is horribly inefficient because all of the string compares
will be performed, and it is even worse when expensive functions are called
in the subexpressions.

Some of us have gotten used to the shunted left-to-right evaluation of
boolean expressions that C provides, as well as many other programming
languages.

Be careful of the logic error of Left$(x,2) as a name could
be "Mrs Smith", and it would be captured as Mr.

....Which was exactly my intention. I did not need to or care to make the
distinction. But that's about the programming example, not the paradigm.


----- original message -----

 

[Adrian C]

The "problem" is: it would have to be of the form:

If condition1 Then
Goto doit
Else If condition 2 Then
Goto doit
Else If condition3 Then
doit:
...statements...
End If

Not really a problem. Just undesirable. Also, "...statements..." cannot
be procedurized easily.

How about ...

Function test()
Dim strName As String
strName = "Mr Tibbs"

If CheckGreeting(strName) Then
MsgBox "Hello " & strName
End If

End Function

Function CheckGreeting(strName As String) As Boolean
If Left(strName, 2) = "Mr" Then
CheckGreeting = True
Else If Left(strName, 2) = "Sr" Then
CheckGreeting = True
Else
CheckGreeting = (Left(strName, 2) = "Ms")
End If
End Function