Syntax error using IIf

Question

This is a simple question I hope. I learned about IIf today and want to implement it in some cases to save a few lines.

IIF(isnumeric(inputs), resume next, call notnum)

This is in red meaning there is a syntax error, how fix this? I have scanned MSDN article on this so I am not being lazy here.

What do you mean by "isnumeric(inputs)" - is this a function that returns a boolean? Is it an array of booleans? — Branden Keck, Dec 10 '15 at 20:48
IIF is used to return a result for example `msgbox "Good " & IIF(hour(now())<12,"Morning","Afternoon")` You can't use it to call routines or resume next. — Dan Donoghue, Dec 10 '15 at 21:19
just as it looks, why not simply use `If isnumeric(inputs) Then resume next Else call notnum` — Dirk Reichel, Dec 10 '15 at 21:33

Mathieu Guindon · Accepted Answer · 2015-12-11T04:43:56.733

That's not how the IIf function works.

It's a function, not a statement: you use its return value like you would with any other function - using it for control flow is a bad idea.

At a glance, IIf works a little bit like the ternary operator does in other languages:

string result = (foo == 0 ? "Zero" : "NonZero");

Condition, value if true, value if false, and both possible values are of the same type.

It's for turning this:

If foo = 0
    Debug.Print "Zero"
Else
    Debug.Print "NonZero"
End If

Into this:

Debug.Print IIf(foo = 0, "Zero", "NonZero")

Be careful though, IIf evaluates both the true and the false parts, so you will not want to have side-effects there. For example, calling the below Foo procedure:

Public Sub Foo()
    Debug.Print IIf(IsNumeric(42), A, B)
End Sub

Private Function A() As String
    Debug.Print "in A"
    A = "A"
End Function

Private Function B() As String
    Debug.Print "in B"
    B = "B"
End Function

Results in this output:

in A
in B
A

That's why using IIf for control flow isn't ideal. But when the true result and false result arguments are constant expressions, if used judiciously, it can help improve your code's readability by turning If...Else...End If blocks into a simple function call.

Like all good things, best not abuse it.

@ThomasShera as part of the *condition*, yes; you can build your Boolean expression off the value returned by `IsNumeric`. What will bite your in the rear at one point or another, is if either the *true part* or the *false part* has *side-effects*. In other words, the last two parameters of `IIf` should always be *constant expressions*, — Mathieu Guindon, Dec 10 '15 at 21:16

score 2 · Answer 2 · answered Dec 10 '15 at 20:49

2

You cannot use iif like this.

Here is a possible way to solve this:

if isnumeric(input) then
    do something ...
else
    call notnum
end if

answered Dec 10 '15 at 20:49

SQL Police

4,127
1
25
54

So I can't call functions with IIf or run IsNumeric? – Dec 10 '15 at 20:52
@ThomasShera Yes, you cannot call any other function within `iif`. If you want to call a function, you must use the classic `if` – SQL Police Dec 10 '15 at 22:50

score -1 · Answer 3 · answered Dec 10 '15 at 21:14

-1

IIf returns a value. As Mat's Mug stated, you can use it, to hand data into another method or function, but you can't call a procedure or function, which has no return value. IsNumeric() for the conditional is okay, though.

answered Dec 10 '15 at 21:14

Reen de Winter

79
6

Syntax error using IIf

3 Answers3

Linked