Building Formulas

When writing complex formulas, it’s usually beneficial to build the formula by putting smaller pieces of it in several cells, then combine those cells into one long ugly formula. I’ve been thinking that I need to write a macro that does the combining for me, but like so many things, it gets on the back burner and never gets off.

Fellow MVP, Bernie Deitrick, shared a macro that he wrote and I finally decided to write mine. So this is at least partially stolen from Bernie (Thanks).

Sub BuildFormula()

    Dim rPrec As Range, rAllPrecs As Range
    Dim sForm As String
    Dim rCell As Range
    
    If TypeName(Selection) = “Range” Then
        For Each rCell In Selection.Cells
            On Error Resume Next
                Set rAllPrecs = rCell.Precedents
            On Error GoTo 0
            
            If Not rAllPrecs Is Nothing Then
                sForm = rCell.Formula
                For Each rPrec In rAllPrecs
                    If rPrec.HasFormula Then
                        sForm = Replace(sForm, rPrec.Address(0, 0), _
                            “(” & Replace(rPrec.Formula, “=”, “”, 1, 1) & “)”)
                    End If
                Next rPrec
                rCell.Formula = sForm
            End If
        Next rCell
    End If
    
End Sub

Here’s a simplistic example of how it works. Note that I’ve overdone it with parentheses, but better safe than sorry.

Build0

Build1

Build2

Update: Jonathon notes a bug in my macro. If both A1 and A10 are in the precedents, the A10 would be replaced by A1’s formula except for the zero. Now I remember why I never wrote this macro. Anyway, here’s another stab at it. I think converting the formula to R1C1 relative references should eliminate that problem. Please let me know if I’ve missed anything else or just made it a whole lot worse.

Sub BuildFormula()

    Dim rprec As Range, rAllPrecs As Range
    Dim sForm As String
    Dim sNewRef As String ‘new variable to convert address to R1C1
    Dim rCell As Range
    
    If TypeName(Selection) = “Range” Then
        For Each rCell In Selection.Cells
            On Error Resume Next
                Set rAllPrecs = rCell.Precedents
            On Error GoTo 0
            
            If Not rAllPrecs Is Nothing Then
                ‘Convert the formula to relative R1C1
                sForm = Application.ConvertFormula(rCell.Formula, xlA1, xlR1C1)
                
                For Each rprec In rAllPrecs
                    If rprec.HasFormula Then
                        ‘Create a formula with the precedents relative address
                        sNewRef = “=” & rprec.Address(0, 0)
                        ‘Convert the formula to R1C1 relative to rCell
                        sNewRef = Application.ConvertFormula(sNewRef, xlA1, xlR1C1, , rCell)
                        ‘Remove the = sign
                        sNewRef = Right(sNewRef, Len(sNewRef) – 1)
                        
                        ‘Replace the R1C1 reference in the formula
                        sForm = Replace(sForm, sNewRef, _
                            “(” & Replace(rprec.Formula, “=”, “”, 1, 1) & “)”)
                    End If
                Next rprec
                rCell.Formula = sForm
            End If
        Next rCell
    End If
    
End Sub

Posted in Uncategorized

4 thoughts on “Building Formulas

  1. That’s very cool!

    I’m not sure I’d have much use for it, but the concept is cool and I could see some people making use of it (rather than copying and pasting).

  2. This doesn’t work if your formula references both A1 and A10 (the A10 gets replaced by (contents of A1)0, or A1 and AA1 (the AA1 gets replaced by A(contents of A1), or if your formula both references A1 and has the text “A1? in a string.

    You need to make sure that the characters before and after the cell reference are not letters or numbers, and that you’re not inside a quoted string.

    The parentheses are essential. You didn’t overdo it.

  3. Hi Dick,

    This is an interesting concept and i’ve enjoyed playing around with it. However, it doesn’t work so well when refering to multi-cell ranges of any kind. For example

    ColA ColB ColC
    1 =A1 =INDEX(B1:B3,2)
    1 2
    1 =A3

    Currently the INDEX formula returns a value of 2. However if you apply your macro then the formula becomes:

    =INDEX((A1):(A3), 2)

    Which changes the returned value to 1. Not really ideal.

    Whilst I would trust myself recognise formulas where using this functionality would be dnagerous, I would hesitate before recommending it to everyone.

    Fantastic to see new innovation like this though.

    Kind Regards

    Charlie

Leave a Reply

Your email address will not be published. Required fields are marked *