Strange behaviour with IIf (Inline If)

[sijpie]

Step through this bit of code, and watch the value of the Variant vV

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> Strange()<br>****<SPAN style="color:#00007F">Dim</SPAN> vV<br>****<SPAN style="color:#00007F">Dim</SPAN> dD <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Double</SPAN><br>****<br>****<SPAN style="color:#007F00">' 1 =========</SPAN><br>****<SPAN style="color:#007F00">' Range("B4") is empty cell</SPAN><br>****vV = Range("B4")****<SPAN style="color:#007F00">' vV = Empty</SPAN><br>****dD = IIf(vV, Format(vV, "#,##0"), 0#)<br>****<SPAN style="color:#007F00">' now dD =0 and vV=Null !!</SPAN><br>****<br>****<SPAN style="color:#007F00">' 2 =========</SPAN><br>****vV = Range("B4")****<SPAN style="color:#007F00">' vV = Empty</SPAN><br>****<SPAN style="color:#00007F">If</SPAN> vV <SPAN style="color:#00007F">Then</SPAN><br>********dD = Format(vV, "#,##0")<br>****<SPAN style="color:#00007F">Else</SPAN><br>********dD = 0#<br>****<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>****<SPAN style="color:#007F00">' correct behaviour: dD=0, vV=Empty</SPAN><br>****<br>****<SPAN style="color:#007F00">' 3 =========</SPAN><br>****dD = 23.5<br>****vV = IIf(dD, Format(dD, "#,##0"), 0#)<br>****<SPAN style="color:#007F00">' correct behaviour</SPAN><br>****<br>****<SPAN style="color:#007F00">'4 =========</SPAN><br>****dD = 0<br>****vV = IIf(dD, Format(dD, "#,##0"), 0#)<br>****<SPAN style="color:#007F00">' correct behaviour</SPAN><br>****vV = IIf(0, Format(dD, "#,##0"), 0#)<br>****<br>****<SPAN style="color:#007F00">'5 =========</SPAN><br>****vV = <SPAN style="color:#00007F">Empty</SPAN><br>****dD = IIf(vV, Format(vV, "#,##0"), 0#)<br>****<SPAN style="color:#007F00">' now dD =0 and vV=Null !!</SPAN><br>****<br>****<SPAN style="color:#007F00">'6 =========</SPAN><br>****vV = 234.56<br>****dD = IIf(vV, Format(vV, "#,##0"), 0#)<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

2 questions:
1: Why does the inline If differ in behaviour from an normal If/Else/End If ?
2: Why does the Variant vV get changed by the Format (if vV is Empty)? I would assume the format function acts on a copy of the parameter as shown in example 6.

One for the true Experts & Geeks...

 

[sijpie]

code without stars
<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> Strange()<br>    <SPAN style="color:#00007F">Dim</SPAN> vV<br>    <SPAN style="color:#00007F">Dim</SPAN> dD <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Double</SPAN><br>    <br>    <SPAN style="color:#007F00">' 1 =========</SPAN><br>    <SPAN style="color:#007F00">' Range("B4") is empty cell</SPAN><br>    vV = Range("B4")    <SPAN style="color:#007F00">' vV = Empty</SPAN><br>    dD = IIf(vV, Format(vV, "#,##0"), 0#)<br>    <SPAN style="color:#007F00">' now dD =0 and vV=Null !!</SPAN><br>    <br>    <SPAN style="color:#007F00">' 2 =========</SPAN><br>    vV = Range("B4")    <SPAN style="color:#007F00">' vV = Empty</SPAN><br>    <SPAN style="color:#00007F">If</SPAN> vV <SPAN style="color:#00007F">Then</SPAN><br>        dD = Format(vV, "#,##0")<br>    <SPAN style="color:#00007F">Else</SPAN><br>        dD = 0#<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#007F00">' correct behaviour: dD=0, vV=Empty</SPAN><br>    <br>    <SPAN style="color:#007F00">' 3 =========</SPAN><br>    dD = 23.5<br>    vV = IIf(dD, Format(dD, "#,##0"), 0#)<br>    <SPAN style="color:#007F00">' correct behaviour</SPAN><br>    <br>    <SPAN style="color:#007F00">'4 =========</SPAN><br>    dD = 0<br>    vV = IIf(dD, Format(dD, "#,##0"), 0#)<br>    <SPAN style="color:#007F00">' correct behaviour</SPAN><br>    vV = IIf(0, Format(dD, "#,##0"), 0#)<br>    <br>    <SPAN style="color:#007F00">'5 =========</SPAN><br>    vV = <SPAN style="color:#00007F">Empty</SPAN><br>    dD = IIf(vV, Format(vV, "#,##0"), 0#)<br>    <SPAN style="color:#007F00">' now dD =0 and vV=Null !!</SPAN><br>    <br>    <SPAN style="color:#007F00">'6 =========</SPAN><br>    vV = 234.56<br>    dD = IIf(vV, Format(vV, "#,##0"), 0#)<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[RoryA]

IIf always evaluates both the true and false expressions - one of the reasons I almost never use it.

 

[sijpie]

Ah, good to know. But why would the variant passed to Format then change, only if its value is empty?

 

[RoryA]

That I confess I don't understand. I cannot see anything in the language specification that explains it.