Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA: optimize If conditional expression?
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
optimize If conditional expression?
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" <joeu2004 wrote in message ... 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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
optimize If conditional expression?
"JLGWhiz" wrote:
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 ----- "JLGWhiz" wrote in message ... 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" <joeu2004 wrote in message ... 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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
optimize If conditional expression?
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 On 1/19/2010 10:45 PM, Joe User wrote: "JLGWhiz" wrote: 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 ----- "JLGWhiz" wrote in message ... 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" <joeu2004 wrote in message ... 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? -- = = = = = = = HTH :) Dana DeLouis |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
optimize If conditional expression?
"Dana DeLouis" wrote:
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 ----- "Dana DeLouis" wrote in message ... 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 On 1/19/2010 10:45 PM, Joe User wrote: "JLGWhiz" wrote: 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 ----- "JLGWhiz" wrote in message ... 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" <joeu2004 wrote in message ... 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? -- = = = = = = = HTH :) Dana DeLouis |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
optimize If conditional expression?
On 20/01/2010 03:45, Joe User wrote:
"JLGWhiz" wrote: 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. 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 -- Adrian C |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I optimize this code? | Excel Programming | |||
Conditional Expression in cell | Excel Worksheet Functions | |||
Conditional Expression Question | Excel Worksheet Functions | |||
Conditional Formatting OR Expression?? | Excel Discussion (Misc queries) | |||
Optimize SumProduct | Excel Discussion (Misc queries) |