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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 |
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 |
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?
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?
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.
I like the weekly archive…!
You’re very responsive!
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).
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
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
Thanks for the heads-up Vincent. I’ve converted it to my new method of posting code, so it should copy OK now.
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?
Alex: Where were you when I wrote this utility? Nice work.
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.
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?
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.
Hey D, Thanks, this helped me out in a jam! IOU
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.
Matt: The output goes to the immediate window (Ctl+G in the VBE).
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:
Take that result and put it in my code like:
Works a peach, except for interpreting the formula in code.