Another If statement question

[ChrisO]

G’day Willi

I think, without testing, True, False, True, True.

But now I will go and see if I made a fool of myself.

Regards,
Chris.

 

[Pat Hartman]

It's almost enough to suggest... don't do divisions in IIf functions in VBA.

I would go further than that and suggest not using IIf() in VBA at all! There is no reason to when IF..Then..Else and Select Case are so much more readable.

 

[ChrisO]

Again Pat and I seem to disagree but that is not correct.

I agree with Pat on everything she has posted so far in this thread but not on this: -

“not using IIf() in VBA at all”

But I will try to explain my reasoning the way I think… (reductio ad adsurdum)

Private Sub Form_Current()

    [color=green]'   We could use 1...[/color]
    
    Me.txtMyTextBox.ForeColor = IIf((SomeThingOrOther), vbRed, vbGreen)
    
    [color=green]'   or 2...[/color]
    
    If (SomeThingOrOther) Then
        Me.txtMyTextBox.ForeColor = vbRed
    Else
        Me.txtMyTextBox.ForeColor = vbGreen
    End If

End Sub

Both are very simple (reductio ad adsurdum) examples.

So what’s the difference? Well not very much at all.

Without testing I would say the second lot of code is faster than the first and it is easier to read than the first.

But I think the speed difference in the above example would be miniscule and in most cases not warranted.

So I think, as Pat has suggested, the difference comes down to how readable it is.

We, after awhile, come to understand the IIf Function like this: -

IIf((SomeThingOrOther), vbRed, vbGreen)
IIf(True, True part, False part)

So we become familiar with the simple IIf function.

So, under the circumstance of being familiar with the simple IIf function, what’s the difference?

Believe it or not readability.

The readability of a function or subroutine can depend on if it is all in view at a given time.

This would be an old axiom that Pat would surly know.
Keep the function or subroutine to a single page.

That axiom was created to minimize ‘page flipping’ when the procedure was printed.
It is still valid in screen based systems.
If we can, but not always done, then viewing a procedure that is totally contained in the viewing area is good.

So, with practice And with simple cases And when speed is not important, we come back to readability.
(Please note the three AND’ed conditions above because that is why this thread was started.)

Private Sub Form_Current()

    Me.txtMyTextBox.ForeColor = IIf((SomeThingOrOther), vbRed, vbGreen)
    
    [color=green]'   is better than...[/color]
    
    If (SomeThingOrOther) Then
        Me.txtMyTextBox.ForeColor = vbRed
    Else
        Me.txtMyTextBox.ForeColor = vbGreen
    End If

End Sub

Simply because the vertical screen consumption of one line is better than five.

We all have our own opinion but what I really dislike are absolutes.
(I hope that was read as a personal preference and not a criticism.)

Maybe all it should be is just something to think about. (And try.)

Regards,
Chris.

 

[WayneRyan]

Chris,

I agree (as usual) with your train of thought in general.
BUT, I hate nested IIf statements.

Computers get paid next to nothing, yet have no problem reading them.
People get paid a lot more, and have a lot more trouble reading them.

Ever picked apart an APL statement? Concise doesn't mean clear.

Give me a nested If - Then - Else anyday (with code tags)!

Wayne

 

[Agnostiker]

In the past I avoided the IIf-statement, but in case of simple 'display switch' for exampel like

lblXYZ.Caption = IIf(IsNull(strQ), "Kraut", "Kindergarten")

I learned to like AND use AND read it, saving space. (Thanks Chris recalling the origin )
So I think too it's dependend from how familiar with this 'mysterious' IIF-statement I'am.

But, what I wanted to ask:

I would be really interested to know, how this IIf-statement is translated in machine code by the compiler!
Somebody who knows?

Kind regards

Willi

 

[KenHigg]

lblXYZ.Caption = IIf(IsNull(strQ), "Kraut", "Kindergarten")

Equals:

if IsNull(strQ) then
lblXYZ.Caption = "Kraut"
else
lblXYZ.Caption = "Kindergarten"
end if

Is this what you are talking about?
kh

 

[Agnostiker]

Is this what you are talking about?

Sorry, no.
This is not I'am talking about.

As shown before (see the instructive Posting #10 from Pat Hartman) there must be a difference between an IIf statement and its 'equal' If-Else solution.
So my question is how the compiler is translating this IIf statement in machine code .

Regards

Willi

 

[WayneRyan]

Agnostiker,

It's been a while, but here's the general idea. Both of the
VBA constructs below would generate roughly the machine code.

I don't want to carry it a step further and assemble that
into machine code.

I think Chris used to work with the PDP-11 computers. This
is sort of MACRO-11.

IIf(A > B, A, B)

If A > B Then
   fnValue = A
   Exit Function
ELse
   fnValue = B
   Exit Function
End If

Start:	Mov A, R0
        Mov B, R1
        Cmp R0, R1
        BH  MoveA
MoveB:	Move R1, (SP)+
        RTS PC
MoveA:	Move R0, (SP)+
        RTS PC

Wayne

 

[Pat Hartman]

The generated code would be similar but not identical. The big difference is that the IIf() is actually LESS efficient so the fact that it is more concise is irrelevant. ALL segments of the IIf() are evaluated every time the IIf() is executed whereas only the "true" path of the nested If or Case statement is. So the IIf()'s code would not branch over the MoveB section.

Please take note that when an IIf() is evaluated in SQL it does NOT evaluate all paths each time as it does in VBA. It acts like the nested If in that it only evaluates along the "true" path.

Chris, a simple IIf() with only a single true and false is obviously simple enough to understand and would certainly be appropriate if your code actually needed the flexibility of a function. So, truth be told, I might at some time have actually used one in VBA. But in general I don't. The criteria I use for deciding how to do something when multiple choices are available are:
1. Readibility
2. Efficiency

Of course the priorities change if the code is in a loop that executes multiple times. The more frequently a piece of code is executed, the more important it is to make it efficient.

 

[Agnostiker]

Mille grazie, Wayne!

But to explain that 'mysterious' behavior of 'IIf' (see Posting #10) we need to have a construction like this I think:

IIf(A > B, C, D)

where 'C' or 'D' could be a 'division-by-zero-expression' f.e.

So I suppose, 'C' and 'D' will be loaded in registers also in the 'Start'?
Like:

Start:	Mov A, R0
        Mov B, R1
        Mov C, R2
        Mov D, R3
        Cmp R0, R1
        BH  MoveA
MoveB:	Move R3, (SP)+
        RTS PC
MoveA:	Move R2, (SP)+
        RTS PC

and this is the reason we'll get f.e. an 'devision-by-zero-error' in any case?
Or I'am wrong?

Willi

 

[Agnostiker]

So, truth be told, I might at some time have actually used one in VBA.

Thanks for your confession, Pat! So I'am relieved...

And I think, we all are in the same line: IN GENERAL DON'T!
That's right?

Regards

Willi

 

[ChrisO]

Pat, this is the sort of thing I like…

“Of course the priorities change if the code is in a loop that executes multiple times.
The more frequently a piece of code is executed, the more important it is to make it efficient.”

It implies that we should understand just when, where and under what circumstances we do or do not do things in a certain way.
And without that understanding we can not correctly choose between readability and efficiency.

Wayne

I also dislike them when they are complex enough to warrant a second look.
But when they are simple I think the saving of screen real estate can be the deciding factor.


Willi

I’m only on my second cup of coffee so I hope this makes sense: -

Sub Test()
    Dim X As Long
    Dim Y As Long
    
    X = 1
    Y = 1
    
    MsgBox IIf(False, X / 1, Y / 0)
    
    MsgBox MyTestFunction(X / 1, Y / 0)
    
End Sub


Function MyTestFunction(A As Long, B As Long) As Long

    MsgBox "Here in MyTestFunction"
    
End Function

If we run these two lines of code they both raise the same division by zero error.

We know that the IIf function will raise the error but strictly speaking it is not raised in the IIf function.
Just like the MyTestFunction function, the error is raised while evaluating the arguments before they are passed to the function.

In the above example MyTestFunction does not execute its MsgBox.

So it seems it’s not the “fault” of the IIf function at all but the argument evaluating mechanism.
This would seem to explain why the IIf function seems to always evaluate both True and False parts.
Not because it’s the fault of the nasty old IIf function but simply because it is a function and all arguments are evaluated before passing.

Hope that makes some sense…now for my third cup of coffee.

Regards,
Chris.

 

[Agnostiker]

Chris,

fortunately having my 12th cup I think I could follow your explanations. And as I followed it fell like scales just from my hairs: what a stupid, simpletonary and dumpfpfeifentrotteliger* Kraut I am!
Apart from having given today two or three wrong and absolutly foolish advices, I did not thought over that this IIf statement could be just a normal, ordinary and alldaytrivial FUNCTION!
I really feel ashamed of my last question.
Sorry!

But I want thank you and Pat, and Wayne and all the others for your friendly and helpful Postings. To me it has been a very instructive and stimulating Thread.
Thank you all.

And now I will try 'to hold the ball flat'**...

Willi



*sorry, can't translate.
**hope you understand in english

 

[ChrisO]

G’day Willi

“I really feel ashamed of my last question.”

Not at all, the only dumb question is the one that was never asked… but should have been asked.

More coming up, but please don’t wait for the reply, it might take awhile.
(Posted 2:39AM Berlin time.)

Regards,
Chris.

 

[ChrisO]

The poor old maligned IIf function.

With the conditions as stated above, even the If function behaves the same.

    If Y / 0 Then
        MsgBox "You'll never get here."
    Else
        MsgBox "You'll never get here either."
    End If

The moral of the story is, don’t do divisions in arguments that are passed to any function.
In fact, don’t do anything that might cause any error while passing any argument to any function or subroutine.
The argument will simply crash at evaluation, before it gets to the procedure.
The validity of calculated or assignment arguments needs to be evaluated before we attempt to pass them.

The question of which is faster, the IIf or the If function needs to be tested but also requires a little more thought.

If Y / 0 Then, evaluates one passed argument to the If function.
IIf(False, X / 1, Y / 0), has to evaluate three passed arguments to the IIf function.
Which should be faster… and why?

Technically, from a code point of view, the IIf function is no better or worse than any other procedure when evaluating arguments.

For example: -

Option Explicit
Option Compare Text

Public Declare Function timeGetTime Lib "Winmm.dll" () As Long
Public Declare Sub Sleep Lib "kernel32" (ByVal lngMilliseconds As Long)


Sub Test()
    Dim X        As Long
    Dim lngStart As Long
    
    lngStart = timeGetTime()
        X = MyTestFunctionA(DelayMilliseconds(2000))
    MsgBox timeGetTime() - lngStart

    lngStart = timeGetTime()
        X = MyTestFunctionB(DelayMilliseconds(2000), DelayMilliseconds(3000))
    MsgBox timeGetTime() - lngStart

End Sub


Function MyTestFunctionA(lngFirstMilliseconds As Long)

    ' Do nothing.
    
End Function


Function MyTestFunctionB(lngFirstMilliseconds As Long, lngSecondMilliseconds As Long)

    ' Do nothing.
    
End Function


Function DelayMilliseconds(Delay As Long) As Long

    Sleep Delay
    
End Function

MyTestFunctionA takes 2 seconds to evaluate its argument.
MyTestFunctionB takes 5 seconds to evaluate its arguments.

Therefore the question is, “Why is the IIf function so maligned?”

Well, a bit more code is required…

    lngStart = timeGetTime()
    If True Then
        X = MyTestFunctionA(DelayMilliseconds(2000)) ' < 2 seconds
    Else
        X = MyTestFunctionA(DelayMilliseconds(3000))
    End If
    MsgBox timeGetTime() - lngStart

    lngStart = timeGetTime()
    If False Then
        X = MyTestFunctionA(DelayMilliseconds(2000))
    Else
        X = MyTestFunctionA(DelayMilliseconds(3000)) ' < 3 seconds
    End If
    MsgBox timeGetTime() - lngStart

But we would never do that... would we?
Well the sad fact is that some people do and that makes some people sad facts.
(But the point is…2 seconds XOR 3 seconds is still better than 5 seconds.)

Now that we understand the technical code point of view AND we only use readable IIf functions then the IIf function has the advantage of screen real estate.

Since, under these circumstances, the IIf function is readable then using it helps to make our whole procedure more readable simply by trying to keep the whole procedure ‘on screen’.

I think I might have to come back and edit this later.

Regards,
Chris.

 

[Agnostiker]

Dear members,

having had a very instructive and enlithening journey on the marvelous land of the disregarded and frequently even calumniated IIF function (Thanks to all) I would like to wash out the ash from my hair and return to the origin starting point throwing my last dump question into the wideness of this very friendly and helpful council:

In a statement like

IF cond1 AND cond2 AND cond3 THEN ...

why does ACCESS will evaluate all this conditions, even if the first is false?

It is not because it is a function - as in the case of the IIf function - , or I'am wrong once again? Please show mercy....

Confused and anxious waiting for Yours judgment

Willi
the Agnostiker

 

[ChrisO]

G’day Willi.

Here is the way I see it…

Because that is how VBA, and all true BASIC’s, should handle it in order to comply with the definition of BASIC.

That behavior is BASIC, for better or for worse, and to be a true BASIC it must comply.

Regards,
Chris.

 

[Agnostiker]

Hi Chris!

...and that's the reason one must 'invent' two new 'AND' and 'OR' operators?

http://support.microsoft.com/?id=817250

Another instructive article I found here


http://www.mvps.org/vb/index2.html?tips/truth.htm

Ciao

Willi

 

[KenHigg]

why does ACCESS will evaluate all this conditions, even if the first is false?

I think it is merely because it is interpreted - the interpreter engine is set up to work the code in one larger chunk. (or maybe not!?)

Maybe if someone could compile some version of basic into a test executable?

kh

 

[ChrisO]

No…one does not need to invent anything. It has already been invented.

This is difficult to try and explain…

The logical states are defined in hardware…software simply tries to keep pace.

The computer screen you are looking at is running on hardware logic.
The data that is being processed is being processed at a hardware logic level.
The software is trying to translate software instructions to hardware instructions.

Hardware logic governs the system, not the software.

The OR, AND, XOR, NAND, and the like, must be processed within the logical definition of the hardware truth tables.

Those tables will be found in digital electronics books and not necessarily in software books.

Without those digital electronic definitions your computer would not exist.

It does not matter what computer you use, nor what language you may use on that computer, but it must comply with the hardware truth tables.

Some languages may have the intelligence to ‘bail out’ after a False is found in an AND statement. VBA and BASICS do not. But they give you the option to write it in such a way that you achieve the same outcome. You now have the control and the flexibility.

Regards,
Chris.