Does VBA have an "OrElse" operator?

[jal]

Suppose you have two boolean functions.

If func1 = true or func2 = true Then MsgBox("Success")

Well, if func1 is true, the whole thing is true, so there is no need to evaluate func2. To evaluate func2, in this case, wastes unecesary time. In VB.Net you can use OrElse instead of OR:

If func1 = true OrElse func2 = true Then MsgBox("Success")

OrElse is efficient. It doesn't evaluate func2 unless necessary.

Does VBA have something similar?

 

[jal]

Well, this article says that VB6 doesn't have it, so VBA probably lacks it too.

 

[Banana]

A workaround:

If func1 Then
    TruePart:
    ....
Else
  If func2 Then
     Goto TruePart
  End If
End If

Is a Goto (yuck!) and weird construction worth it? Probably not. Just a thought.

 

[jal]

A workaround:

If func1 Then
    TruePart:
    ....
Else
  If func2 Then
     Goto TruePart
  End If
End If

Is a Goto (yuck!) and weird construction worth it? Probably not. Just a thought.

Yes, a bit yucky, but very ingenious.

 

[Steve R.]

What about IIF? The example below requires an extra Boolean variable, but it does avoid a goto statement and keeps program flow. The Boolean variable OKtoProceed could be eliminated if you are using something simple like a message box.

OKtoProceed=false
iif(FUNC1True, OKtoProceed=True,iif(FUNC2True,OKtoProceed=True,"Both Tests False"))
if OKtoProceed then
 ........
end if

 

[Banana]

IINM, IIF() evaluates both true and false parts, so it would be just as slow, if not slower.


Hmmm.... That's an idea, actually. If my memory serves, Jet's Iif() is actually different from VBA's Iif() in that Jet doesn't evaluate both. Therefore, we could do this:

SELECT Iif(func1, 1, Iif(func2, 1, 0));

and use the result to go through the codes.

 

[Steve R.]

IINM, IIF() If my memory serves, Jet's Iif() is actually different from VBA's Iif() in that Jet doesn't evaluate both.

That is my understanding too. Code still seems a bit long and convoluted.

 

[Banana]

Well, yet another option is to write a small .DLL in C that provides the functionality of OrElse which VBA can call using COM... something like this:

If OrElse(func1, func2) Then

It all depends on how really greased you want your elbows.

 

[Steve R.]

SELECT Iif(func1, 1, Iif(func2, 1, 0));

Very good. Thanks. This discussion is going to have "collateral damage" when I undertake future revisions to my project. In my permit tracking database, when the user goes to close the form, the program goes though a data validation exercise. If data validation fails, the form purposely does not close so the error can be fixed. My logic, however, has become quite twisted as I have added "tests". Your approach will help simplify the programming. I hope that JAL will be able to use your code.

 

[Banana]

Glad to know it helped gave you some ideas.

While cooking the dinner, I thought of one more thing.

In C# (I do not know if C and/or C++ share this as well), the switch syntax is much more flexible compared to VB's Select Case flow. Here's a sample:

switch(TestCondition)
{
    case 1:
       TestCondition++; //increment by one
       break;
    case 2:
       TestCondition++; //increment by one
       goto case 1; //move the control to the first case, having the effect of increasing by two
    case else:
       break; //Do nothing 
}

And I was thinking how nice it would be to have this and I realized it can be done with a bit extra typing:

Select Case TestCondition
      Case 1 
1        TestCondition=TestCondition+1
     Case 2
         TestCondition=TestCondition+1
         Goto 1
     Case Else
End Select

Note the '1' used as a line number to indicate the jump point for the subsequent case numbers. Still using that ugly goto, but at least we can blame C#/C++ for that.

 

[jal]

I'm only capable of "programming for dummies" so readability of code is top priority.

For some of you, IIF seems perfectly readable because you've been using it for a long time. I still can't quite get used to it so I only use it when necessary.

Here's an example of what is, for me, readable code (very similar to the IIF example above)

OKtoProceed=false
if func1 Then
OkToProceed = true
Elseif func2 Then
OkToProceed = true
end if
if not okToProceed then exit sub

 

[gemma-the-husky]

surely just this sort of thing must be more efficient.

Sub doit()
If func1 Then
GoTo truebit
End If

If func2 Then
GoTo truebit
End If

falsebit:
MsgBox ("False")
exit sub


truebit:
MsgBox ("True")


End Sub

 

[jal]

And by te way, in case no one mentioned this as yet, C# has bot the OR operator (single bar) and the OrElse operator (double bar).

if (func1 || func2) MessageBox("Sucess")

same as vb.net

if func1 orElse func2 Then MessageBox("Success")

 

[jal]

surely just this sort of thing must be more efficient.

Sub doit()
If func1 Then
GoTo truebit
End If

If func2 Then
GoTo truebit
End If

falsebit:
MsgBox ("False")
exit sub


truebit:
MsgBox ("True")


End Sub

Not too shabby. That works for me.

 

[Brianwarnock]

How about if you test for False

If Func1=False Then
If Func2=False Then
Exit Sub
End If
End If
Truepart:

End Sub

Brian

 

[jal]

How about if you test for False

If Func1=False Then
If Func2=False Then
Exit Sub
End If
End If
Truepart:

End Sub

Brian

Very readable.

 

[gemma-the-husky]

thats pretty concise, brian

 

[Banana]

Excellent additions.

You're right that C# does have OrElse; it was just interesting to illustrate the differences.

As for gemma-the-husky's suggestion, I would think it is efficient as we don't have to use a variable or something and directly jump to the statement. To be honest, though, it makes me cringes because we're now using two GoTo, with an Exit Sub which kinds of goes against the principle of one exit point for every sub, and repeating it enough is sure to get you a plateful of spaghetti. Not that I would think jal would go and write spaghetti code; just my little purist voice screaming blasphemy.

But experience has shown that sometime you gotta put it aside for that one situation, and whenever I do, I make a big header in the sub:

'Yes, you really, really want to do this way. 
'Yes, we did try different ways. 
'Yes, this is actually faster. 
'Yes, it actually works. 
'If you want to change this, please contact X first before doing anything!

At least this reassures my purist voice.

 

[Brianwarnock]

Having cut my programming teeth on machine code I tend to forget,ignore?, the modern constraints on the use of goto , even if during debugging we used to long for a "came from" . I think what matters is good information comments, but I would not champion the use of messy code, we just didn't have Do loops of any kind.

I did indent my code forgetting that the forum removes the formatting if you don't use code tags.

Brian

 

[gemma-the-husky]

a personal view

i used to try my hardest to avoid "goto" statements anywhere

i don't bother so much now. It's just whatever works. sometimes its easier to goto, to break a control loop, rather than artificially set a flag to enable the loop to stop on a true/false condition.
eg, in the foregoing

if funca or funcb then

you might also ask, why not find a way to embed funcb within funca, or even find a funcc that does both tests

since the underlying machine code must execute unconditional jumps, why not include them in code - as long as its documented and readable

i would much rather follow this sort of syntax than several nested if then else statements

all the compiler is doing is translating instructions into machnie code

i shouldnt imagine

if funca orelse funcb then
call funcc
else
call funcd
end if

resolves into greatly different machine code, compared with

if funca then goto do_c
if funca then goto do_c
call func_d
exit sub

do_c:
call func_c

although clearly its a bit easier to write

---------
in the same way that you cant

a) declare several typed variables together
dim a,b,c as long

b) declare and initiallise a variable in the same line
dim a as long = 123

c) have a char datatype
dim ch as char (ie a one character string)

d)or even define an array with a defined range, or non-numeric range
dim myarray1(1 to 8) as long
dim myarray("A" to "H") as long

e) or have a poniter data type
and thats one you cant get round (at least I haven't been able to!)

but you can achieve these things with minor effort, and possibly the resultant machine code isn't much different.