VBA IIf statement error since update to Office 2016

gers1978 · Oct 10th 2016

My company just updated to Office 2016.

I have a spreadsheet with loads of code, created in Excel 2010, that has worked fine for months.

Since updating to 2016, the following line gives a type mismatch error (Error 2007):

Code

strStaff = IIf(IsError(DataRange(intRow, staffCol)), "(no staff)", Trim(WorksheetFunction.Proper(DataRange(intRow, staffCol))))

However if I change it to:

Code

If (IsError(DataRange(intRow, staffCol))) Then
     strStaff = "(no staff)"
Else
     strStaff = Trim(WorksheetFunction.Proper(DataRange(intRow, staffCol)))
End If

it works fine. Has IIf been removed from Office 2016?

Also posted here: http://www.mrexcel.com/forum/e…l-2016-a.html#post4652969

**S O** · Oct 10th 2016

Re: VBA IIf statement error since update to Office 2016

The IIf() syntax will evaluate both the true and false sections of the method in every scenario. If you've got an Error in the testing condition, then the "false" part of your method will more than likely also error because it's trying to evaluate some code that is using erroneous data.

e.g.

Code

n = 0


z = 5 / IIf(n = 0, 2, n)

This will still produce an error because the false part is still evaluated and 5 / 0 will produce an error.

When you use an If block, only the code after the (true) criteria is evaluated and so any errors wouldn't be picked up in the same way:

Code

If n = 0 Then
    x = 5 / 2
Else
    x = 5 / n '// Isn't evaluated, so doesn't produce an error
End If

I'd guess that your error was actually an accident waiting to happen, but perhaps Excel 2016 evaluates cell contents slightly differently which is why it's only appearing now?

In short - unless there's a particular advantage in your code to using IIf() - I'd just go with standard If/Else/End If syntax.

gers1978 · Oct 10th 2016

Re: VBA IIf statement error since update to Office 2016

Quote

I'd guess that your error was actually an accident waiting to happen.

You've hit the nail on the head. I think what's happened is since I changed a lot of my If Then Else statements to IIf (in an effort to streamline the amount of code, plus give me some practice using IIf as I'd never used it) I've never encountered a situation where there was an error in one of the cells. Obviously I'd tested the code when it used If Then Else statements and it worked fine, then when I switched to IIf, I've never retested, and it's came up by accident in a real world situation now.

Thanks!

**norie** · Oct 12th 2016

Re: VBA IIf statement error since update to Office 2016

S O

The error here isn't because the 2 parts of the Iff statement are evaluated.:)

Code

n = 0 
 
z = 5 / IIf(n = 0, 2, n)

It's simply because the Iif returns 0 and we get a division by zero.

**S O** · Oct 12th 2016

Re: VBA IIf statement error since update to Office 2016

Hi norie,

that IIf will return 2, not zero. Regardless of what gets returned though it will error because it will still evaluate the false part which will cause a division by zero. The point I'm making is that the error wouldn't occur with an "If/Else" syntax

**norie** · Oct 12th 2016

Re: VBA IIf statement error since update to Office 2016

Oops, got the 0 part wrong - it was kind of late.:)

However when I run this code there's no error and the message box displays 2.5.

Code

Dim n As Long
Dim z As Double


    n = 0


    z = 5 / IIf(n = 0, 2, n)
    
    MsgBox z

If you run this code the divide by zero error will pop up.

Code

Dim n As Long
Dim z As Double


    n = 0
    
    z = IIf(n = 0, 5 / 2, 5 / n)
    
    MsgBox z

**S O** · Oct 12th 2016

Re: VBA IIf statement error since update to Office 2016

Norie, apologies - I realise I made a mistake with my example (serves me right for not testing it!) and that it doesn't actually show the point that I'm trying to make.

This is what I was trying to explain:

Code

Sub test()


n = 0


x = IIf(n = 0, 5 / 2, 5 / 0)


End Sub

which will produce divide by zero error even though n = 0.

Hopefully that makes more sense.

skywriter · Oct 12th 2016

Re: VBA IIf statement error since update to Office 2016

Thanks for sharing that, good to know. :cheers:

**S O** · Oct 12th 2016

Re: VBA IIf statement error since update to Office 2016

You keep giving those beers out Bruce, you're going to become my new best friend

VBA IIf statement error since update to Office 2016

Participate now!

Share