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 |
Download “modReplaceIFISERROR.bas” modReplaceIFISERROR.bas – Downloaded 1305 times – 5 KB
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
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…)
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