VBA IIf statement error since update to Office 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

  • 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.

  • 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!

  • 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.

  • 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

  • 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
  • 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.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!