Hi Everyone!
Dick has kindly invited me to post for Daily Dose of Excel.
Being my first post I want to share something useful, but not too technical.
One bit of VBA I like is the Immediate If function – IIF.
IIF is basically If-Then-Else logic bundled into a single function.
Here is an example.
Let’s say you have 5 fruit in cells A1 to A5.
Apple Orange Pear Banana Mango
We could test for the existence of Mango as follows:
Dim bln As Boolean, rng As Range
bln = False
For Each rng In Range(“A1:A5”)
If rng.Value = “Mango” Then bln = True
Next
If bln = True Then
MsgBox “Mango was found”
Else
MsgBox “Mango was not found”
End If
End Sub
But by using IIF, we can reduce the If-Then-Else block into a single line.
Dim bln As Boolean, rng As Range
bln = False
For Each rng In Range(“A1:A5”)
If rng.Value = “Mango” Then bln = True
Next
MsgBox IIf(bln, “Mango was found”, “Mango was not found”)
End Sub
I also find using IIF handy when making a comma separated list of values:
Dim str As String, rng As Range
str = “”
For Each rng In Range(“A1:A5”)
str = str & IIf(str = “”, “”, “, “) & rng.Value
Next
MsgBox str
End Sub
hi Rob,
I don’t like the IIF so much.
so there.
no only kidding,
The thing with IIF is that it looks at both true and false expressions, so it can casue errors some times, say it one exression is out of range or something
I tend to aviod it,
But that’s just me ;)
Hi Rob,
I think your use of IIF is a good example of when it may be useful. But is performance is important, If-Then-Else structures are the way to go.
Here’s an exmaple (code in the Immediate Window) that does NOT get around the division by zero error which the If..Else..End If equivalent would:
x = 0 : ? IIf(x = 0, 0, 1 / x)
I don’t use IIf myself, essentially a matter of style really. I find a If..Else..End easier to debug (e.g. placing breakpoints and Debug.Asserts) and maintain (read: remember what it does two weeks later) but I can see how a lightweight IIf can read well in code.
The exception is Jet (MS Access) SQL code, in which the VBA5 functions (as distinct from methods) may be used. Because Jet lacks the standard SQL Case syntax, IIf is pretty much essential.
Jamie.
I’m with Jamie that I don’t use if for style reasons. But there is one time that I do use it, and that’s in concatenating strings.
sString = “The stock went ” & IIf( x < y,”down”,”up”)
the alternative seems to be
dim sDownUp As String
If x < y Then
sDownUp = “down”
Else
sDownUp = “up”
End If
sString = “The stock went ” & sdownup
That’s just too much work.
I just thought of another exception to the rule. I’m always using IIf in the Immediate Window e.g.
For Each ws in ThisWorkbook.Worksheets : _
? IIf(ws.Visible, vbNullString, vbCrLf & ws.Name), : _
Next
Jamie.