Jason writes:
I had a colleague ask me an interesting question the other day. He has repeating ranges of data that are structured similarly, and he wants to perform calculations on each block. The formulas can get kind of long and error prone. When he catches an error, he has to go back and Copy – Paste Special – Formulas again for each range. … He asks is there any quick formula to use another cell’s formula. For example if A1 has =SUM(B2:D3), typing =FOO(A1) in D2 would return the same as =SUM(E3:G4).
Oh, I’m familiar with writing buggy formulas and feeling like I’m forever fixing and copying. I think any solution is going to create so much overhead that it will be better to stick with copying. However, as Jamie Collins would say, it’s the Everest complex – do it because it’s there.
My first thought, of course, would be to parse the formula by looping through the Precedents property of the range with the formula. It would limit it’s effectiveness to formulas that only referred to ranges on the same sheet, but I knew parsing with string functions would be a losing proposition. Fortunately, I came to my senses and remembered the ever-handy ConvertFormula method of the Application object. Here’s my first hack at it:
Dim sForm As String
Application.Volatile
sForm = Application.ConvertFormula(rtarget.Formula, xlA1, xlR1C1, , rtarget)
sForm = Application.ConvertFormula(sForm, xlR1C1, xlA1)
InheritFormula = Application.Evaluate(sForm)
End Function
The inner workings of ConvertFormula have always been a mystery to me. Maybe if I new why it was invented, I would have a better of understanding of why it works the way it does. Like why the FromReferenceStyle is required but the ToReferenceStyle is optional. Anyway, the first ConvertFormula converts the formula (well, the name works) from A1 to R1C1 relative to the target cell. So =SUM(A3:A5) gets converted to =SUM(R[2]C:R[4]C). The second ConvertFormula converts it back to A1, presumably relative to the calling cell.
I’m sure this will break with many, many formulas. Array formulas come to mind and something that probably wouldn’t work too well with the Evaluate method. Come to think of it, maybe an array formula is what Jason’s “colleague” needs. (We know it’s really you Jason.) He could highlight the range, edit the formula, and control+shift+enter to fill it in the range. I suppose that’s not much better than just copying and pasting.
Below are the formulas on which I tested it. You’ll notice that I very cowardly didn’t include any complex or difficult formulas. I really don’t want to know where it doesn’t work.
i am so NOT going anywhere near this! lol
i tend to use error checking in the form of self evaluating formulas in an audit column adjacent to the selection and trust that will find errors in formulas.
I would do it as follows…
Click cell A1
Create a named range called FOO, where the ‘Refers to’ box is:
=SUM(B2:D3)
– note the use of relative vs absolute formula
In A1 type =FOO
In D2 type =FOO
Cheers
Ummm…why not use a UDF? A single point of “failure.” Correct it and all cells using the formula are corrected.
http://contrarianview.blogspot.com/2006/02/excel-formulas-and-vba-user-defined.html
Because UDF’s are excruciatingly slow on larger workbooks and cause trouble if not properly implemented. I try to stay away from UDF’s as far as I possibly can.
what about using names to hold forumuals? I think this is in PED and on chips site?
I have to say that i like the UFD method, but some times the speed is slow. Maybe for these big work books complied function might be the way to go.
Has anyone written any functions using c++.net?
Someone asked me for just this sort of thing earlier this week – they had a workbook with a protected page (wouldn’t show formulas), but he wanted to know what the formulas were. I said to try a function that would just show the formula of the cell, and that actually worked fine (a little to my surprise).
Something like this:
Function checkform(where)
checkform = where.Formula
End Function
Thanks all for the various feedback. For myself, I like the named range formula. I usually don’t use a UDF unless it is really complicated or in the case of application.convertformula, something I can’t really do with Excel formulas.
The colleague (who really exists) is going to be much more comfortable with Dick’s inheritformula UDF. The biggest advantage here is that it is easy and familiar. Lots of people don’t really understand named ranges beyond absolute references.
A motivation against a UDF is that if I write it, I then inherit all future maintenance tasks. :)
–Jason
The InheritFormula is a nice idea that works very well within extremely narrow criteria, which is that the worksheet containing the InheritFormula function calls is the activesheet. The reason for this limitation is the use of the Evaluate method.
The Evaluate method is very powerful and can be very appealing to many people. I use it (or used to use it) heavily and have learnt the hard way of one very important limitation. That is that it is evaluated in the context of the *active* worksheet!
To see where InheritFormula breaks, do the following. Of course, this illustrates a limitation of the Evaluate method, not a problem with the InheritFormula code.
In Sheet1, in D1 enter 1. In D2, enter =D1. In D3, enter =D2+Sheet2!D2
In Sheet2, in D1 enter 2. In D2 enter =InheritFormula(Sheet1!D2).
Now, the value of Sheet2!D2 will depend on which sheet was the active sheet at the time of the last recalculation.
If it was Sheet1, Sheet2!D2 will contain the value of Sheet1!D1 and Sheet1!D3 will contain a 2.
If it was Sheet2, Sheet2!D2 will contain the value of Sheet2!D1 and Sheet1!D3 will contain a 3.
That limitation is more of a code structure problem in my opinion. Using Application.Evaluate() is usually safe when, as Tushar says, one is working with the Active Sheet, or when doing stuff like
Evaluate(“ROW(A1:A10)”)
which is sheet independant. But when the sheet is important then one can just call
Sheets(“TheSheet”).Evaluate(“A1+A2?)
and get the correct result even if “TheSheet” is not the ActiveSheet. In the above example, changing the line of code that uses Application.Evaluate() to
InheritFormula = Application.Caller.Worksheet.Evaluate(sForm)
gives consisent results (In Tushar’s example, Sheet1!D3 always returns a 3)