Excel evaluated functions

Yay, finally, something else obscure to blog about - evaluated functions in excel


What you say? use Real english? - well if you insist. I'm talking about the ability to treat a text string in an excel cell as if it were a formula, and to evaluate it (work out what the answer would be - happy?)


Suppose, for example, you had a spreadsheet set out as follows:

The boxes in yellow are those you have highlighted for user input. The aim of the spreadsheet is to allow the user to select a range of dates to add the values together for.

The value in E4 is the result of a formula which reads

=CONCATENATE("=sum(",E2,":",G2,")")


This creates a nice formula as seen in cell E4, but it's no use to us at present, because excel sees it as text and doesn't treat it as a formula. (Typically, you might put an intermediate result like this into a hidden column or row somewhere so as not to confuse the user)


The typical (but problematic) answer given to this conunderum on the net is as follows:

1) right-click on e4 and select "Name a range"
2) modify the "Refers to" field as shown below and press ok




3) in your target (answer) cell enter =Name (in our case the cell will read =Mysum.) You end up with a spreadsheet that looks something like this....


Which is exactly what you wanted, right? - errm it might look that way, but watch what happens if you change one of the source values - let's say we change a6 to 999



Oops - the data value has changed - but the calculated total has not. Why not? Well that's because when Excel decides whether a cell needs recalculating, it works out whether any of the cells that the formula depends upon have changed.

In this case E5 depends upon E4, E4 depends upon E2 and G2. However E2 and G2 are plain text, they don't depend on anything - they're not cell references (at least as far as excel knows). Because none of the prerequisites have changed, there's no need to recalculate.

There are 2 easy work-rounds for this. You can change either of the values in E2 or G2, or force a complete recalculation using ctrl-shift-F9.

But is there a more elegant solution that doesn't require user input? - use your noggin - i wouldn't have asked the question if there weren't :)

unfortunately, it requires the use of a (very small) macro, which means you need to use a macro-enabled workbook, but you can't have everything.

So the new way to implement is like this:

1) press ALT-F11 to bring up the VBA interface

2) Add a module to your spreadsheet as follows:






In the module we're going to create a very small UDF (user defined function)

Presented here for easy cut and paste:

Public Function MyEval(s)
Application.Volatile True ' forced recalc every time
MyEval = Evaluate(s)
End Function

3) save and close the VBA interface.

4) the results cell (E5 in our example) should now read =Myeval(CONCATENATE("=",E4)).

5) We also need to modify cell E4 to remove the "=" in front of the sum

=CONCATENATE("sum(",E2,":",G2,")")



Once you've set this up, you will find that the result updates automatically every time a data value changes.



Job done



So how does this little piece of magic work?



The key is in the line


Application.Volatile True



This tells excel that the function value cannot be depended upon, and that the value should be recalculated on every worksheet change. Without this line, the macro would function in exactly the same way as the earlier method using cell names. It's the same method Excel uses internally to ensure that functions like now() get update regularly.

Enjoy