ProfessionalExcel.com In the office, Excel is a way of life… not just a tool

23Apr/136

Replace IF(ISERROR()) with value_if_false argument

This tip relates to the technique of trapping and handling errors in a formula. Prior to Excel 2007 (e.g. Excel 2003, 2002, 2000), this was accomplished using a combination of the IF and ISERROR functions to test for an error in a formula, e.g. =IF(ISERROR(MyFormula),ReturnSomethingElse,MyFormula).

A reader of my blog was after some code to do something a little different to a previous post which looked at replacing the Excel 2007 IFERROR function with IF and ISERROR.

The slight twist on this was when you wanted to get rid of the error trapping (IF and ISERROR) and just have the original formula, warts (errors) and all!

In the VBA code below, I have developed a handy procedure (ReplaceIFISERRORCellFormulas) that will convert a range of selected cells that contain IF(ISERROR()) formulas to instead display the formula used in the 'value_if_false' argument of the original IF(ISERROR()) function.

Hope you all find it useful!

'*******************************************************************
'Developer:     Chris Read
'Objective:     Replaces all formulas in a selected range that contain
'               IF(ISERROR()) formulas with the 'value_if_false' argument of the IF(ISERROR()).
'               formulas.
'Parameters:
'Returns:
'Change log:
'               Developer:     Date:       Description:
'               CR             23/04/13    Created
'*******************************************************************
Public Sub ReplaceIFISERRORCellFormulas()
 
Dim rngSelection As Range
Dim rngCell As Range
Dim strFormula As String
 
On Error GoTo ErrHandler
 
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
 
    Set rngSelection = Application.Selection
 
    For Each rngCell In rngSelection
        strFormula = rngCell.Formula
        Do Until InStr(1, strFormula, "=IF(ISERROR") = 0
            strFormula = RemoveIFISERROR(strFormula)
        Loop
        rngCell.Formula = strFormula
    Next
 
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    Application.CalculateFullRebuild
 
ErrHandler:
    If Err.Number <> 0 Then
        MsgBox "Error in ReplaceIFISERRORCellFormulas:" & vbCrLf & vbCrLf & Err.Number & ": " & Err.Description
    End If
 
End Sub
'*******************************************************************
'Developer:     Chris Read
'Objective:     Gets the end position of the IF(ISERROR()) formula within
'               the formula string.
'Parameters:    strFormula
'Returns:       GetIFISERROREnd
'Change log:
'               Developer:     Date:       Description:
'               CR             23/04/13    Created
'*******************************************************************
Public Function GetIFISERROREnd(ByVal strFormula) As Long
 
Dim lngBracketsCount As Long
Dim lngPosition As Long
 
On Error GoTo ErrHandler
 
    lngPosition = 1
    lngBracketsCount = 0
 
    Do Until lngPosition = Len(strFormula) + 1
        If Mid(strFormula, lngPosition, 1) = "(" Then lngBracketsCount = lngBracketsCount + 1
        If Mid(strFormula, lngPosition, 1) = ")" Then
            lngBracketsCount = lngBracketsCount - 1
            If lngBracketsCount = 0 Then Exit Do
        End If
        lngPosition = lngPosition + 1
    Loop
 
    GetIFISERROREnd = lngPosition
 
ErrHandler:
    If Err.Number <> 0 Then
        MsgBox "Error in GetIFISERROREnd:" & vbCrLf & vbCrLf & Err.Number & ": " & Err.Description
    End If
 
End Function
'*******************************************************************
'Developer:     Chris Read
'Objective:     Gets the start position of the 'value_if_false' argument
'               of the IF(ISERROR()).
'Parameters:    strFormula
'Returns:       GetIFISERRORFalseArgumentStart
'Change log:
'               Developer:     Date:       Description:
'               CR             23/04/13    Created
'*******************************************************************
Public Function GetIFISERRORFalseArgumentStart(ByVal strFormula) As Long
 
Dim lngBracketsCount As Long
Dim lngPosition As Long
 
On Error GoTo ErrHandler
 
    lngPosition = Len(strFormula)
    lngBracketsCount = 0
 
    Do Until lngPosition = 0
        If Mid(strFormula, lngPosition, 1) = ")" Then lngBracketsCount = lngBracketsCount + 1
        If Mid(strFormula, lngPosition, 1) = "(" Then lngBracketsCount = lngBracketsCount - 1
        If Mid(strFormula, lngPosition, 1) = "," And lngBracketsCount = 0 Then Exit Do
        lngPosition = lngPosition - 1
    Loop
 
    GetIFISERRORFalseArgumentStart = lngPosition + 1
 
ErrHandler:
    If Err.Number <> 0 Then
        MsgBox "Error in GetIFISERRORFalseArgumentStart:" & vbCrLf & vbCrLf & Err.Number & ": " & Err.Description
    End If
 
End Function
'*******************************************************************
'Developer:     Chris Read
'Objective:     Replaces an IFERROR formula with a combination of
'               IF and ISERROR formulas.
'Parameters:    strFormula
'Returns:       RemoveIFISERROR
'Change log:
'               Developer:     Date:       Description:
'               CR             23/04/13    Created
'*******************************************************************
Public Function RemoveIFISERROR(strFormula) As String
 
Dim strIFISERRORFunctionResult As String
Dim lngIFISERRORFunctionResultStart As Long
Dim lngIFISERRORFunctionResultEnd As Long
Dim strReplacementFunction As String
Dim lngStart As Long
Dim lngEnd As Long
 
On Error GoTo ErrHandler
 
    lngStart = InStr(1, strFormula, "IF(ISERROR")
    lngEnd = GetIFISERROREnd(Mid(strFormula, lngStart, Len(strFormula))) + lngStart - 1
 
    lngIFISERRORFunctionResultEnd = lngEnd - 1
    lngIFISERRORFunctionResultStart = GetIFISERRORFalseArgumentStart(Mid(strFormula, lngStart, lngEnd - lngStart)) + lngStart - 1
    strIFISERRORFunctionResult = Mid(strFormula, lngIFISERRORFunctionResultStart, lngIFISERRORFunctionResultEnd - lngIFISERRORFunctionResultStart + 1)
 
    strReplacementFunction = strIFISERRORFunctionResult
 
    RemoveIFISERROR = Mid(strFormula, 1, lngStart - 1) & strReplacementFunction & Mid(strFormula, lngEnd + 1, Len(strFormula))
 
ErrHandler:
    If Err.Number <> 0 Then
        MsgBox "Error in RemoveIFISERROR:" & vbCrLf & vbCrLf & Err.Number & ": " & Err.Description
    End If
 
End Function
modReplaceIFISERROR
modReplaceIFISERROR
modReplaceIFISERROR.bas
Version: 1.0
5.5 KiB
731 Downloads
Details...
Comments (6) Trackbacks (0)
  1. Right, had a crack at the 2nd routine, and so here’s both of them, less error handlers. That 2nd one took way way longer than I thought.

    Now I’ll work on a Value_If_True routine, so I can use that to go from IF(ISERROR to IFERROR(…

    Public Function GetIFISERROREnd_Jeff(ByVal strFormula) As Long

    Dim lngBracketsCount As Long
    Dim lngPosition As Long

    lngPosition = 1
    lngBracketsCount = 0

    Do Until lngPosition = Len(strFormula) + 1
    If InStr(lngPosition, strFormula, “(“) LessThan InStr(lngPosition, strFormula, “)”) And InStr(lngPosition, strFormula, “(“) GreaterThan 0 Then
    lngPosition = InStr(lngPosition, strFormula, “(“) + 1
    lngBracketsCount = lngBracketsCount + 1
    Else:
    lngPosition = InStr(lngPosition, strFormula, “)”) + 1
    lngBracketsCount = lngBracketsCount – 1
    If lngBracketsCount = 0 Then Exit Do
    End If
    Loop
    GetIFISERROREnd_Jeff = lngPosition – 1
    End Function

    Public Function GetIFISERRORFalseArgumentStart_Jeff(ByVal strFormula) As Long

    Dim lngBracketsCount As Long
    Dim lngPosition As Long
    Dim strRev As String
    Dim lngOpenBracket As Long
    Dim lngClosedBracket As Long
    Dim lngComma As Long
    Dim lngMin As Long

    On Error GoTo ErrHandler

    lngPosition = 1
    lngBracketsCount = 0

    strRev = StrReverse(strFormula)
    Do Until lngPosition = Len(strFormula) + 1
    lngOpenBracket = InStr(lngPosition, strRev, “(“)
    lngClosedBracket = InStr(lngPosition, strRev, “)”)
    lngComma = InStr(lngPosition, strRev, “,”)

    If lngOpenBracket = 0 Then lngOpenBracket = 100000
    If lngClosedBracket = 0 Then lngClosedBracket = 100000
    If lngComma = 0 Then lngComma = 100000

    lngMin = Application.Min(lngOpenBracket, lngClosedBracket, lngComma) + 1
    If lngMin = 100001 Then Exit Do
    lngPosition = lngMin
    Select Case Mid(strRev, lngPosition – 1, 1)
    Case “(“: lngBracketsCount = lngBracketsCount – 1
    Case “)”: lngBracketsCount = lngBracketsCount + 1
    Case “,”: If lngBracketsCount = 0 Then Exit Do
    Case Else: GoTo ErrHandler
    End Select
    Loop

    GetIFISERRORFalseArgumentStart_Jeff = Len(strFormula) – lngPosition + 3

    End Function

  2. Hi Chris. Me again. I love this code! Hat’s off to you for a well thought out routine.

    I found a way to optimise it slightly that I thought I’d share.

    In the GetIFISERROREnd function, I see you iterate through strFormula one character at a time, and test if it is an open bracket or a closed bracket.

    I tweaked your code, so that instead of that one-character-at-a-time iteration, it instead finds the next occurrence of an open bracket or a closed bracket – whichever occurs first – then changes the lngBracketsCount accordingly before doing the next search for the next open or closed bracket starting at one position after the last open or closed bracket.

    Pretty minimal time saving, but I’m looking at running similar code on very large models with possibly tens of thousands of replacements needed, so this will help in that case.

    Here’s my tweak (Hopefully the comment parser won’t eat the relevant bits). I’ve yet to do the same with the GetIFISERRORFalseArgumentStart function. Will tweak that tomorrow (time for bed here in New Zealand).

    Do Until lngPosition = Len(strFormula)
    If InStr(lngPosition, strFormula, "(") 0 Then
    lngPosition = InStr(lngPosition, strFormula, "(") + 1
    lngBracketsCount = lngBracketsCount + 1
    Else:
    lngPosition = InStr(lngPosition, strFormula, ")") + 1
    lngBracketsCount = lngBracketsCount - 1
    If lngBracketsCount = 0 Then Exit Do
    End If

    Loop

    • Whoops, it did eat my code. here it is again, with the less than and greater than bits in English.

      Do Until lngPosition = Len(strFormula)
      If InStr(lngPosition, strFormula, “(“) Less Than InStr(lngPosition, strFormula, “)”) And InStr(lngPosition, strFormula, “(“) Greater Than 0 Then
      lngPosition = InStr(lngPosition, strFormula, “(“) + 1
      lngBracketsCount = lngBracketsCount + 1
      Else:
      lngPosition = InStr(lngPosition, strFormula, “)”) + 1
      lngBracketsCount = lngBracketsCount – 1
      If lngBracketsCount = 0 Then Exit Do
      End If

      Loop

      • Cheers Jeff, always up for a bit of optimisation. I’ll pop that in my version when I have the chance.

        Good to see someone’s finding this stuff useful!

        • Very useful. Keep the articles coming mate. I expect another one by the time I wake up (just got up in the middle of the night to have a nice cup of cocoa. Woke up in a code sweat…)

  3. Hi Chris. Came across this site while looking for approach to programatically convert IF(ISERROR( to IFERROR(. I see you have this code – which will be a good base for me – as well as some code to do the opposite of what I’m after.

    Before I start tinkering, I don’t suppose you have some code that does what I’m after lying about?

    Regards

    Jeff


Leave a comment

No trackbacks yet.