2

I have a top cell which contains a formula referring to other cells. Those cells contain formulas referring to other cells.

I would like to evaluate the top cell while replacing in the whole evaluation tree every occurrence of a cell, say A4, by another cell, say A5.

I know you can navigate the tree of dependants, but can you evaluate the formula after performing a replacement ?

nicolas
  • 9,549
  • 3
  • 39
  • 83
  • This sort of thing comes up every now and then. See the comments here: http://stackoverflow.com/questions/1897906/is-this-the-regex-for-matching-any-cell-reference-in-an-excel-formula . Basically, @Charles is right. Stuff like `INDIRECT` and VBA UDFs mean you have to parse and evaluate a formula with runtime inputs in order to walk dependencies in the general case. But that's what Excel does! – jtolle Apr 21 '12 at 14:17
  • @jtolle indeed, that is why I was expecting to be able to reuse part of it. I might find a library which lets me do it, in the meantime I just duplicated the cells and minimized the tree depth. that severely drags me for future evolutions of the sheet, but that's life – nicolas Apr 21 '12 at 14:39
  • nicolas, you haven't really said what your application is. If it's as simple as just wanting to recalc the "top cell" for multiple values in one of the cells it depends on, Excel already has a built-in feature called a "Data Table" that does this. It's part of the "what-if" analysis under the "Data" menu. Also see this: http://stackoverflow.com/a/4640866/58845 – jtolle Apr 21 '12 at 18:35
  • @jtolle it looks like that's in the spirit I am looking for. no duplication of the formula itself, just a different evaluation context. I need to check it out deeper. thks – nicolas Apr 22 '12 at 08:21

2 Answers2

1

Why not just set Calculation to Manual, make the changes to the cells and then call Application.Calculate.
Seems a lot simpler and safer to use Excel's built-in dependency tracking rather than try to re-invent it.

Charles Williams
  • 23,121
  • 5
  • 38
  • 38
  • i would like to be able keep the original formula. i like this formula but would just like to replace 1 evaluation by n evaluations where the parameters have been shocked. and obviously the level of nested calls are not amazing (lik 4 or 5) but enough to make a hand crafted solution (copying n times all the cells involved) painful. – nicolas Apr 20 '12 at 15:57
  • There must be a library out there for dependency walking in excel. excel is *all* about dependency graph, i'd be famazed and ffaberglasted if there wasn't any... – nicolas Apr 20 '12 at 16:02
  • 1
    I dont undersand the problem: Why would anyone want to do a difficult job of duplicateing what Excel already does? – Charles Williams Apr 21 '12 at 23:22
  • I dont want to do exactly what it wants, but I sure want to do 99%. all I want is to change the values of some terminal nodes it uses when it evaluates the expression tree of a cell. That's why I wish there was a way to leverage on it, and change just the part that interests me, but I dont see how to do it. – nicolas Apr 22 '12 at 08:13
  • You can change the data points, use Range.Calculate or Sheet.Calculate or Application.calculate, read the results of the calculation and store them somewhere. Thats going to be easier, faster and more reliable than trying to walk, change and evaluate the dependency trees – Charles Williams Apr 22 '12 at 18:02
0

Yes You can use the Application.Evaluate to evaluate a formula. See this example

Sub Sample()
    Dim frmla As String

    frmla = Range("E1").Formula

    frmla = Replace(frmla, "A2", "A1")

    MsgBox Application.Evaluate(frmla)
End Sub
Siddharth Rout
  • 147,039
  • 17
  • 206
  • 250
  • ok but does it compose ? that's the key. say A2, from you example depends on A4, and I want to evaluate E1 replacing A4 with A5 – nicolas Apr 20 '12 at 11:15
  • actually if I have the value I can probably replace it with the result.... but I am very afraid this might break with array formula etc.. – nicolas Apr 20 '12 at 11:18
  • by compose I mean can i chain those calls ? – nicolas Apr 20 '12 at 11:18
  • From what I understand, it will not break it. `.Evaluate` can be used to evaluate array formulas as well. Give it a try and if it doesn't then show us the exact code that you are trying and we will take it form there? – Siddharth Rout Apr 20 '12 at 11:27
  • I don't think it will work. as I remember from my excel days, there are different api call for 'array formula' and 'normal formula'. Also, you might have noticed that formula string length is limited in size. – nicolas Apr 20 '12 at 11:31
  • (so pasting the result in the string would break the formula) – nicolas Apr 20 '12 at 11:33
  • are there any dependency tree to walk on, to first replace, and then evaluate is my question. I know how to walk the tree to *print*, but not to *build* or *evaluate* – nicolas Apr 20 '12 at 11:35
  • You don't need API to evaluate an array formula :) You can use `Evaluate` For Example This is an Array formula `=SUM(($A$2:$A$100=""Siddharth"")*($B$2:$B$100=""Male""))` You can use this in VBA to evaluate `MsgBox Evaluate("SUM(($A$2:$A$100=""Siddharth"")*($B$2:$B$100=""Male""))")` Notice, in this case we don't use the "=" sign. – Siddharth Rout Apr 20 '12 at 13:38
  • @Siddard my question relates to changing a *dependency*, which can happen at any level. obviously if it is 1 level deep, a simple string replace eval would do, but the crux of the problem is to go deeper, that is, to *compose* in the recursion. so i need in a way or another, api or wahtever you call it, to walk on the tree. imagine in your formula that A3 contains a formula, which makes reference to B3, which makes reference to D9, etc... – nicolas Apr 20 '12 at 15:07
  • Evaluate only evaluates the formula string it is given: it does not do recalculation of the other formulas the formula string depends on. – Charles Williams Apr 20 '12 at 15:29
  • @CharlesWilliams You are right Charles on this. No two things on that. But using the dependents you can actually find the formula the dependents have and then evaluate them using `Evaluate` and that is the point that I was trying to make :) – Siddharth Rout Apr 20 '12 at 15:36
  • 2
    @Siddarth, yes but its difficult to walk the dependency tree for anything except very simple cases: think about INDIRECT, OFFSET, VLOOKUP, Named Ranges, Named Formulas, Volatile UDFs etc etc – Charles Williams Apr 20 '12 at 15:42
  • 1
    +1 @CharlesWilliams: I agree with you again :) Difficult but not impossible ;) – Siddharth Rout Apr 20 '12 at 15:43
  • @SiddharthRout sure. we could even do it in assembly, introspecting the underlying xml document. to be viable solution though, your production of coding need to be higher than your boss's ability to change his mind. – nicolas May 01 '12 at 08:54