JKP Application Development Services.

                    Microsoft Office Application Development

Wrapping Formulas In An Error Test

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

Feedback

Since you have managed to reach the end of this article, maybe you would care to write me a small message, expressing your opinion on this article?

Click here to write an email message to me (Maybe I'll add a feedback page with the feedback I get).

    Subscribe in a reader

Use the contact page to issue questions or comments about this website.
Copyright © 2003-2008 JKP Application Development Services