Has IIF changed???

Running Excel 2013
VBA

If I type

Please Login or Register  to view this content.

I get an error of "= expected"

but if I type

Please Login or Register  to view this content.

it is accepted.

Am I missing something totally obvious here?

As I recall, and the resources I have looked up, it was simply IIF(Condition to evaluate, True part, False part) as in:
IIF (Value > 10, Perform this action if Value is <= 10, Perform this action is Value is > 10) (which I copied from another resource but I don't know if I am properly supposed to cite them or not...).

Just rather use some one line statements rather than a bunch of five lined
If then / End If

or worse yet
If then : statement : Else : statement : End If

... or am I losing my mind in my old(er) age?

It has the same behavior going back at least to Excel 2003.

What does the first statement accomplish, why would you use it without an assignment?

OK, here's the long answer to why your first construct doesn't work -- probably way more than you wanted to know.

It has to do with the parens.

When you invoke a Sub with Call, you must use parens around the arguments:

Please Login or Register  to view this content.

When you assign the result of a function to a variable, you must use parens around the arguments:

Please Login or Register  to view this content.

If you want to pass arguments ByVal (by value, a copy of the variable) or ByRef (by reference, a pointer to the variable), according to the procedure's signature, you don't include parens:

MySub arg1, arg2
MyFunc arg1, arg2

If you want to pass arguments ByVal, irrespective of the procedure’s signature (which won't work for a procedure expecting an array (because arrays are always passed by reference) or an object), you enclose the argument in parens, or an extra set of parens. In the examples below, arg1 is passed by value, and arg2 according to the procedure’s signature:

Please Login or Register  to view this content.

That’s because when you add parens around an argument that doesn't require them, the argument is evaluated, and the result of the evaluation is passed to the procedure. It means the argument is passed by value regardless of how the procedure requested it.

That also explains why you can't use parens around two or more arguments when they are not required; the evaluation of (arg1) may be meaningful, but the evaluation of (arg1, arg2) is not. That's why this generates an error:

Please Login or Register  to view this content.

(That example is exactly analogous to your IIf)

That also explains why if you call a procedure expecting a range with parens where none are required, you get a type mismatch error; because the evaluation of a range is a Variant, or Variant/Array.

You can't pass a Variant ByRef as a static type (Long, String, ...).

You can pass a statically-typed variable ByRef as a Variant, and the called procedure can modify its value with data of the appropriate (or coercible) type.

So: Parens means completely different things according to context. VBA might have used some other bookends (curly braces, square brackets, whatever) to indicate enclosing a list of arguments versus a call for evaluation, but it didn't.

Since objects are always passed ByRef, you can never put unnecessary parens around an object variable. Chip Pearson (http://www.cpearson.com/excel/byrefbyval.aspx) explains the meaning of ByRef and ByVal for an object variable:
The ByRef and ByVal modifiers indicate how the reference is passed to the called procedure. When you pass an object type variable to a procedure, the reference or address to the object is passed -- you never really pass the object itself. When you pass an object ByRef, the reference is passed by reference and the called procedure can change the object to which that reference refers to. When an object is passed ByVal, a copy of the reference (address) of the object is passed.

You're welcome.

@ brucemc777
Hi brucemc777,
I too found shg’s post an unusually full and informative reply, and had already mentioned that to shg via Rep comment. I too find it great to know why, rather than just getting it done. Both as you say, - then one can reason better in future, - and of course contribute later better oneself to the Forum.
I just recently started using IIF. I found a good way of remembering what it is about is to make the comparison with the Excel Spreadsheet Function IF

Spreadsheet
Value of this cell in which the IF Formula is in = IF (Logic_Test, Value_if_True, Value_if_False)

VBA
Value of this variable = IIF (Logic_Test, Value_if_True, Value_if_False)

It is not a perfect comparison. For example in a spreadsheet you can use two arguments like these:

=IF(1=0,1)

=IF(1=1,1)

And it Gives you this:

FALSE

1

_ . I do not think you can get Boolean stuff out of the VBA IIF, or at least you need all three arguments or it errors with “arguments not optional” if you try less than 3 arguments
_ . But the comparison with either a cell or a variable on the LHS of the equation as it were , helps me to keep on track with what the single line VBA IIF is about. VBA IF is a bit different, which i think belongs to the multi line code world of IF Else Nested IFs, Looping etc. etc.But you can ( if you feel so strangely inclined ) usually reduce a set of complicated looping, Nested IFs etc with a single line of Nested IIFs – see here a crazy example:
This code
http://www.excelforum.com/developmen...ml#post4213978
is converted to a single line of IIFs in the first code here
http://www.excelforum.com/developmen...ml#post4213980

Hope that contributes as an alternative “Layman” explanation
Alan
P.s. I think you have the second and third arguments wrong here
IIF (Value > 10, Perform this action if Value is <= 10, Perform this action is Value is > 10)
P. P.s. Maybe if you have any comments on my point/ question 2) below it would be helpful..

_ ...........................................

@shg
Hi shg
You gave a great explanation of the use of parens and a nice explanation of the ByVal (copy of variable) and ByRef (referring specifically to the only copy as it were). That really helped me get that straight. And the extra “nested” parens to “convert” as it were to ByVal ( by virtue of the extra parens and evaluate thing ) was new and i had not read that anywhere.
A couple of quick follow up Comments/questions if i may,

_1) If i understand correctly this is what would be referred to as the “Procedure’s signature“

Please Login or Register  to view this content.

So in this case defining here arg1 as ByVal and arg2 as ByRef for the Sub Procedure sigshg
That being the case, then going back to your examples, in all the following 4 cases:

Please Login or Register  to view this content.

I am passing arguments according to the procedure's signature. Is that correct? I ask as your initial explanation seemed to be saying that this was the case for the last 2 only

Please Login or Register  to view this content.

_2) Although I learned a great deal from your explanation, the relevance to the OP’s question is lost on me. Was his use of the word “Evaluate” in post #3 that which set you off explaining the extra parens and evaluate thing. And hence (1 = 1, 0, 1) is meaningless. That appears only vaguely parallel to why this does not work

Please Login or Register  to view this content.

These are the correct syntax ( they “work” )

Please Login or Register  to view this content.

In addition Either of these two seems to work

Please Login or Register  to view this content.

Or something like this works

Please Login or Register  to view this content.

So are you saying the parens will try to evaluate if it can. It will recognise a string or something like vbExclamation or something like a Property which when applied to an Object returns a string. It will not get us, for example, to the message box title. ( or give us the Exclamation in the message box, but just its “Evaluated number” 16, (WETF that means!!)
I am following your general idea that VBA uses its Paren pair for different things differently. Which confuses the issue. For example, One might of reasonably expected this to be syntaxly correct,

Please Login or Register  to view this content.

This form is typically correct when arguments are not named ( and must consequently be given in correct defined order ). But for the Message box it don’t work. - VBA just to confuse us does not use the usual syntax here and omits the paren pair
This code

Please Login or Register  to view this content.

Can be explained i think along your “extra parens is evaluate” argument. Maybe

Please Login or Register  to view this content.

Can be thought of as the Explicit version of the Implicit

Please Login or Register  to view this content.

_ .. just as 2 / 1 + 1 is the Implicit way ( based on the Maths rules of order of evaluating) of writing the explicit ( ( 2 / 1 ) + 1) )
_......
Coming back to the OP.... The VBA IIF is more like the spreadsheet IF, as i tried to explain at the start of this post. Your arguments maybe more relevant to the VBA IF Then , then.
Any comments ? And if not, thanks again for some very informative info, even if possibly not so relevant to the OP question, directly..

Thanks again
Alan

@ brucemc777
Hi brucemc777
Thanks for popping back to the Thread.
I Must admit as a beginner i am still a bit lost to the relevance from all of what shg wrote to exactly what you are doing.
My (probably naive thought) was that you just had the syntax and general idea about VBA IIFs not quite right. Hence my ( again maybe naive ) explanation that it worked similarly to the Spreadsheet IF and was a bit different to the VBA IF.

For example, that commented out code line of yours is written “syntaxly” along the lines of the VBA IF.
And as you probably know ( and said i think ) it errors.
However, Following my explanation of VBA IIF , the code line would look like this

Please Login or Register  to view this content.

which if you try is syntaxly OK and does not error. - It is the direct equivalent of this

Please Login or Register  to view this content.

But I learned a lot from the Thread anyway. It helped consolidate my knowledge of the ByVal and ByRef stuff in Procedures, and the extra (___) “parens pair” to do an evaluation was something interesting and new

Alan
_ ……………………
P.s. BTW

Originally Posted by brucemc777

...... There probably still is a better way to do it without cheating with the ":" bunch-lines-together-on-the-same-line way, but that's the story!

One way i have seen of doing that is to make an Array of the nine different things, then do a For Each Loop or similar to do the IF code bit for each. But the exact code syntax for that is a stretching me beginners brain a bit. But maybe that gives you one last thought direction
Alan

@bruce: You should know that ...

1. IIf is slow - much slower than

Please Login or Register  to view this content.

2. IIf always evaluates both TruePart and FalsePart; if one of them errors, you'll get a run-time error, i.e.,

Please Login or Register  to view this content.

@DE: Those constructs are emphatically not equivalent.

Hi shg

Originally Posted by shg

...
@DE: Those constructs are emphatically not equivalent.

emphatically? - Do they not do roughly at least the same in most cases, for example

Please Login or Register  to view this content.

Alan.
p.s. I did not know you could put Else on the same line as Then, so learnt something again, thanks

Words have meaning, DE; they are not equivalent, and differ significantly in functionality and performance. Other than that, I guess you could say they're the same.

Performance-wise, this is particularly egregious:

Please Login or Register  to view this content.

It has to access the worksheet twice, and for no reason at all:

Please Login or Register  to view this content.

... gives the same result.

Hi,

Originally Posted by shg

Words have meaning, DE; they are not equivalent, and differ significantly in functionality and performance. Other than that, I guess you could say they're the same.

Performance-wise, this is particularly egregious:.....
... gives the same result.

OK, I was just demonstrating the correct syntax for the OP, that is to say the equivalent of IF Else EndIf to the IIF. - I confess had not particularly looked at the exact terms in the code As the OP said , his example was just "for structural example, not functional"

Thanks for coming back
Alan