use "SET" statement with "iif "

[smallxyz]

Hi All,

Is it illegitimate for VBA to use "SET" statement with "iif" ?

I got an error for the below coding︰

    Dim Rng As Range, URng As Range   

    Set URng = Nothing
    Set Rng = Range("A1")
    Set URng = IIf(URng Is Nothing, Rng, Union(URng, Rng))

    MsgBox URng.Address(1, 1)

Is that the only correct writing is as follows？

    Dim Rng As Range, URng As Range   
    Set URng = Nothing
    Set Rng = Range("A1")

    If (URng Is Nothing) Then
        Set URng = Rng
    Else
        Set URng = Union(URng, Rng)
    End If

    MsgBox URng.Address(1, 1)

My reason of combining "SET" with "IIF" mainly is to simplify the code length for better readability.
If that is disallowed in VBA, I will stick to the "If Then Else" statement.

Thanks all.

 

[Fluff13]

The IIF function evaluates everything, so if Rng is nothing you will get an error with the Union as there is nothing to join.

 

[BobBridges]

Yeah, IIF can be a real help if both outcomes are already calculated, but if one of them would be invalid then IIF always bombs even if it wouldn't have chosen that one.

 

[BobBridges]

By the way, I don't use IIF very much for that very reason. (Also I worry unreasonably about making my programs run as fast as possible; because IIF evaluates both outcomes, even though it's going to use only one, it sometimes will run slower. Of course, you'd notice the difference only if IIF is inside a loop executed thousands of times.) But if you want to use it, you could easily enough write your own IIF function that works properly.