Wrapping Formulas In An Error Test

Pages in this article

1. Formula Wrapper
2. Array Formulas

Enable For Array formulas

Of course the first attempt was far from complete. If any cell in the range contains an array formula, the subroutine errors out.

Accounting for array formulas is a bit more complicated.

First of all, we need to ensure a range containing a single array formula is treated as a single entity. Secondly, we need to keep track of the ranges with a single array formula so we don't try to convert their formula more than once. The code below does all that:

 Sub ChangeFormulas()
    Dim oCell As Range
    Dim sFormula As String
    Dim sInput As String
    Dim oDone As Range
    Dim bFirst As Boolean
    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) - IIf(Left(oCell.Formula, 1) = "=", 1, 0)))
        If bFirst = False Then
            bFirst = True
            Set oDone = oCell
            If oCell.HasArray Then
                oCell.CurrentArray.FormulaArray = sFormula
                Set oDone = Union(oDone, oCell.CurrentArray)
            Else
                oCell.Formula = sFormula
                Set oDone = Union(oDone, oCell)
            End If
        ElseIf Intersect(oDone, oCell) Is Nothing Then
            If oCell.HasArray Then
                oCell.CurrentArray.FormulaArray = sFormula
                Set oDone = Union(oDone, oCell.CurrentArray)
            Else
                oCell.Formula = sFormula
                Set oDone = Union(oDone, oCell)
            End If
        End If
    Next
End Sub

 

Frequently asked Questions