Wrapping Formulas In An Error Test

Pages in this article

First version

Oftentimes I have a range filled with formulas that as such work correctly, but need an error checking mechanism. Take for example the following simple situation:

A cell which may yield an error
If cell A3 contains no data or a zero, the result in C3 will be #DIV/0!

If you don't want the errors to show, the solution is simple: test for zero:

=IF(A3=0,"",B3/A3)

But I tend to have much more complicated formulas, in which it is not always just one cell that might cause an error of some sort, so I need to wrap the entire formula in an error check. I thought it might be useful to have a generic little tool that lets you specify how to wrap the current function in a cell with an error test.

Here is the first try at a subroutine:

Sub ChangeFormulas()
    Dim oCell As Range
    Dim sFormula As String
    Dim sInput As String
    Static sFormulaTemplate As String
    If sFormulaTemplate = "" Then
        sFormulaTemplate = "=IF(ISERROR(_form_),"""",_form_)"
    End If
    sInput = InputBox("Enter base formula", , sFormulaTemplate)
    If sInput = "" Then Exit Sub
    sFormulaTemplate = sInput
    For Each oCell In Selection
        sFormula = Replace(sFormulaTemplate, "_form_", Right(oCell.Formula, Len(oCell.Formula) - 1))
        oCell.Formula = sFormula
    Next
End Sub

So how does this work?

You select the cells with the formulas that need worked over.
Then run the sub. An Input box is shown with a default template to change the formula:

=IF(ISERROR(_form_),"",_form_)

What this means is that the current formula in the cell is going to be placed at each location where it says "_form_". Edit the formula template to match your need and click OK.

Because sFormulaTemplate is declared as Static, the utility remembers the template for a next time you use it.