Worksheet Formula in VBA Part II

If you read Part I, you know that I’m a cantankerous old coot whose set in his ways. Well, it’s not as bad as all that. One reason that I never converted to writing worksheet formulas in VBA using R1C1 is that I don’t really write them.

I created this utility so that I could write the formula in a cell, then convert it to a string and paste it into my code. It has the added benefit of allowing me to see if the formula works first. Of course, that doesn’t insure that the utility works, but it hasn’t failed me yet. If you try it and it doesn’t work, let me know.

Note that the code uses the Precedent object to see which cells are in the formula. Because Precendents doesn’t work properly for references to other sheets, this may not work as expected if you have formulas that reference another sheet.

The output of the code is to print the string to the Immediate Window. Then I copy and paste it to the appropriate place in my code. It assumes that my formula will be relative to a range variable named “cell.” If you use a different variable name, do a Find and Replace after you paste.

Sub FormulaToVBA()
 
    Dim sForm As String
    Dim rPrec As Range
    Dim lRwDiff As Long, lColDiff As Long
    Dim sRefAdds() As String
    Dim sRefTemp As String
    Dim lRefCnt As Long
    Dim i As Long, j As Long
   
    If ActiveCell.HasFormula Then
        'Store the formula, replace double quotes with chr$(34) and
       'add quotes to each end
       sForm = ActiveCell.Formula
        sForm = Chr$(34) & Replace(sForm, Chr$(34), """ & chr$(34) & """) & Chr$(34)
       
        'Redim array to hold every combination of cell address
       ReDim sRefAdds(1 To (ActiveCell.Precedents.Count * 4))
       
        'Initialize the array counter
       lRefCnt = 1
       
        'Loop through the precedents to store their addresses in the array
       For Each rPrec In ActiveCell.Precedents
            sRefAdds(lRefCnt) = rPrec.Address(1, 1)
            sRefAdds(lRefCnt + 1) = rPrec.Address(0, 0)
            sRefAdds(lRefCnt + 2) = rPrec.Address(1, 0)
            sRefAdds(lRefCnt + 3) = rPrec.Address(0, 1)
           
            lRefCnt = lRefCnt + 4
        Next rPrec
       
        'sort array by length of reference - if we don’t replace
       'the longer references first, the shorter ones could replace
       'part of a longer one
       For i = LBound(sRefAdds) To UBound(sRefAdds) - 1
            For j = i + 1 To UBound(sRefAdds)
                If Len(sRefAdds(j)) > Len(sRefAdds(i)) Then
                    sRefTemp = sRefAdds(i)
                    sRefAdds(i) = sRefAdds(j)
                    sRefAdds(j) = sRefTemp
                End If
            Next j
        Next i
       
        'Replace each cell reference with an Offset assuming a variable
       'called cell to which the formula will be relative
       For i = LBound(sRefAdds) To UBound(sRefAdds)
            'Calculate the differences in rows and columns for offset
           lRwDiff = Range(sRefAdds(i)).Row - ActiveCell.Row
            lColDiff = Range(sRefAdds(i)).Column - ActiveCell.Column
       
            sForm = Replace(sForm, sRefAdds(i), _
                Chr$(34) & " & cell.Offset(" & lRwDiff & "," & lColDiff & _
                ").Address(#,#) & " & Chr$(34), 1)
               
            'Determine the reference type (A1, $A$1, $A1, A$1)
           Select Case (Len(sRefAdds(i)) - Len(Replace(sRefAdds(i), "$", "")) _
                + Abs(CLng(Left(sRefAdds(i), 1) = "$")))
               
                Case 1
                    sForm = Replace(sForm, "(#,#)", "(1,0)")
                Case 2
                    sForm = Replace(sForm, "(#,#)", "(0,1)")
                Case 3
                    sForm = Replace(sForm, "(#,#)", "(1,1)")
                Case 0
                    sForm = Replace(sForm, "(#,#)", "(0,0)")
            End Select
        Next i
       
        'Print the new formula
       Debug.Print sForm
    End If
End Sub

17 Comments

  1. Wendy! says:

    I’m still enjoying your blog…….
    but one comment/question… when I click on a calendar date I seem to get to the comments section of the first blog entry of that day…
    Or am I doing something wrong?

  2. Dick says:

    Glad to hear it Wendy. I don’t think you’re doing anything wrong, that’s just how it works. It takes you to the first Permalink for that day and you can use the links that the top to navigate. I don’t like it either.

    I might try archiving by week instead of by day. What do you think of that?

  3. Dick says:

    Wendy: I changed the archiving to weekly and added the weekly archive links to the side bar. I like it better, but it’s still not perfect. Your thoughts are appreciated. Thanks.

  4. Wendy! says:

    I like the weekly archive…!
    You’re very responsive!

  5. Toad says:

    OK, you’ve done it. After reading your blog for the past few weeks, I’m going to learn VBA. I figure that, between your blog and JWalk’s book, even a tyro like me should be able to do some useful things in VBA.

    This snippet of code looks particularly useful. I’m even more impressed with it than I am with spelling the word “Aegilops” using the column headers. (No offense, JWalk).

  6. Anantha says:

    Hi,

    I am working in finding the Precedents of a given cell.I am able to find the Precedents in the Active Worksheet. Is it possible to find the precedents of a given cell in an Inactive Worksheet?

    Plz advise.

    Thanks,

    Cheers,
    anantha

  7. Dick,

    Thank you for this code, i really like your code.

    I didn’t manage to copy paste it to my own project, because “”" gave an error:

    sForm = Chr$(34) & Replace(sForm, Chr$(34), “”" & chr$(34) & “”") & Chr$(34)

    It seems to work after several changes. It might be a good idea to post an Excel document with the code inside to avoid this problem.

    Best Regards,
    Vincent

  8. Thanks for the heads-up Vincent. I’ve converted it to my new method of posting code, so it should copy OK now.

  9. Alex J says:

    Dick,
    I don’t know if I’ve missed the point, but what I do is write the formula in a cell (and test it), then I turn on the macro recorder, F2 (edit) and ENTER to make the recorder think it recorded me writing the formula, then turn off the recorder. The formula is written in R1C1 notation in my new macro. I then copy to my code. Since I only do this twice in a blue moon, no utility required.

    Facile? Bien Oui! Thoughts?

  10. Alex: Where were you when I wrote this utility? Nice work.

  11. Alex J says:

    Thanks Dick. (Or does “Nice Work” mean “Thanks – just in the nick of too late”?)
    April 2004 I was probably in beautiful Sarnia, Ontario, admiring the majestic refineries there.

  12. No. There was no sarcasm there. I still need a way to automate this more. Starting the recorder, stopping the recorder, copying, pasting, and deleting a module is just too much work. How do I get into the macro recorder code directly?

  13. Alex J says:

    Well, Dick, it turns out that I WAS missing something. My past experience was is getting a string which represented a cell formula, and re-using it – as in:
    sFormula = “=SUM(RC[-1]:R[1]C[-1])”
    rng.formulaR1C1 = sFormula

    Obviously (at least it is obvious to me now), you want to acquire the cell formula and turn it into a VBA equation which computes in code.

    So – sorry I misunderstood, and, if this could be fully automated as you intend, then this would be a great way to have business rules written outside of VBA. I would love that! Right now I use a big class module with lots of properties for one of my major calculation engines. At least its centralized.

    I have no experience with automating macro recorder.

  14. Don says:

    Hey D, Thanks, this helped me out in a jam! IOU

  15. Matt J says:

    I think I’m missing something here. I’m not getting any output from this code. It runs through and processes the formula, but there’s nothing at the end for me to paste into my code except the original formuala.

  16. Matt: The output goes to the immediate window (Ctl+G in the VBE).

  17. On another note, I don’t really use this method any more. I should probably blog about it. I’ve converted (kicking and screaming, mind you) to using R1C1 in code. I write the formula in the cell and select it. Then I go to the immediate window and type:

    ?activecell.formular1c1

    Take that result and put it in my code like:

    rCell.FormulaR1C1 = “=IF(RC[-4]< =RC[-1],RC[-4],99000+RC[-1])”

    Works a peach, except for interpreting the formula in code.

Posting code or formulas in your comment? Use <code> tags!

  • <code lang="vb">Block of code goes here</code>
  • <code lang="vb" inline="true">Inline code goes here</code>
  • <code>Formula goes here</code>

Leave a Reply

Here's how to update your reports of company and nearly any web data: