question

JamesGarrison-3189 avatar image
0 Votes"
JamesGarrison-3189 asked Viorel-1 edited

VBA IIF(condition,true,false) always evaluates both branches... except when it doesn't?

In all documentation and everywhere I've looked on the web, it is clearly stated that:

IIf always evaluates both truepart and falsepart, even though it returns only one of them. Because of this, you should watch for undesirable side effects. For example, if evaluating falsepart results in a division by zero error, an error occurs even if expr is True.

I seem to have found one situation where this is either not true, or is treated as a special case that is undocumented.

     Dim rs as RecordSet ' Assume this is populated
     Dim done as boolean
     dim key as long
     Do while not done
         key = iif(rs.EOF,MAXLONG,rs!id)
         ...
     Loop

(For reasons that don't matter here I can't just write Do while not rs.eof... in the real code it's actually a 2-way merge)

Normally, when a Recordset is at EOF, attempting to refer to a field (i.e. rs!id) results in Error 3012: No current record. In the specific case above, when at EOF, no error is raised, which violates my expectation based on the documentation.

Mind you, I'm not complaining, as the observed behavior is what I want to happen.

What bothers me is that I'd hate to write code that depends on undocumented behavior that could change at any time. Is there any evidence or documentation anywhere that the observed behavior is intended and not a corner case that slipped through the cracks?

office-vba-dev
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Viorel-1 avatar image
0 Votes"
Viorel-1 answered Viorel-1 edited

I think that rs!id is equivalent to rs.Fields(“id”), which returns a Field. This works even if there is no current record. The statement ‘Dim f As Field: Set f = rs!id’ works regardless the value of rs.EOF. Therefore, when IIf evaluates rs!id, it gets a field object and this does not cause an issue, because the object is not further used. The problem occurs when you try to get the field’s value, such as ‘Dim key: key = rs!id’ (which is equivalent to rs.Fields(“id”).Value because Value is the default property). Here you will see the error.

To force IIf to raise the error, try this:

 key = iif( rs.EOF, MAXLONG, (rs!id) )



5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

TvanStiphout avatar image
0 Votes"
TvanStiphout answered

I see the same thing. When we reverse the two arguments, we get the expected behavior:
key = IIf(rs.EOF, rs!ID, MAXLONG)
I will run this by MSFT; I don't think this is by design.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.