Why is Excel's 'Evaluate' method a general expression evaluator?

A few questions have come up recently involving the Application.Evaluate method callable from Excel VBA. The old XLM macro language also exposes an EVALUATE() function. Both can be quite useful. Does anyone know why the evaluator that is exposed can handle general expressions, though?

My own hunch is that Excel needed to give people a way to get ranges from string addresses, and to get the value of named formulas, and just opening a portal to the expression evaluator was the easiest way. (The help for the VBA version does say its purpose it to "convert a Microsoft Excel name to an object or a value".) But of course you don't need the ability to evaluate arbitrary expressions just to do that. (That is, Excel could provide a Name.Evaluate method or something instead.)

Application.Evaluate seems kind of...unfinished. It's full behavior isn't very well documented, and there are quite a few quirks and limitations (as described by Charles Williams here: http://www.decisionmodels.com/calcsecretsh.htm) with what is exposed.

I suppose the answer could be simply "why not expose it?", but I'd be interested to know what design decisions led to this feature taking the form that it does. Failing that, I'd be interested to hear other hunches.

Well I think its required to enable VBA to get the result from a Named Formula (or a string containing a formula), (OK there is also the ugly method of inserting the formula into a spare cell and then reading back the result, but for example that won't work from inside a UDF).

In VBA its complex to determine if a Defined Name contains a range reference or a formula. Using Evaluate works for both cases.

Its also sometimes very efficient and simpler to build Excel formulae as strings and evaluate them rather than having to bring all the data from Excel into VBA and then do the calculations in VBA. (Its expensive to get data from Excel into VBA and even worse with current .NET implementations).

All good points, but I was wondering why let people evaluate arbitrary expressions. I'm going to update my question a little.

jtolle 2010-04-15 19:38:43

The last part of your answer does point me in the right direction. It is useful to evaluate simple expressions, but you wouldn't write a separate "simple expression evaluator" and expose that; you'd just expose the whole thing. Still, I wonder why the rough edges?

jtolle 2010-04-15 19:53:44

Just so it's clear, this answer was to the question before I edited it, which was "Why does Excel expose an 'Evaluate' method at all?". Those are all good reasons.

jtolle 2010-04-15 20:02:07

Not sure what you are getting at: a Defined Name can contain any Excel formula, so Evaluate needs to be able to evaluate any Excel formula, not just simple ones.I guess that the reason Evaluate has a few quirks is that they are reasonably easy to bypass and never bubble up to the top of the list of things to fix.

Charles Williams 2010-04-16 12:06:00

I guess I was just hung up on the difference between calling 'Evaluate("my_name")', where 'my_name' is already defined, and 'Evaluate(<arbitrary string>)'. Perhaps my question should have been why Excel provides 'Application.Evaluate' instead of, say, 'Name.EvaluatesTo'. Of course, what a name evaluates to depends on where it is used from if there are relative references. And it *is* useful to be able to build a string and just evaluate it without stuffing it in a name or a cell formula. So I think I'm satisfied...

jtolle 2010-04-16 22:24:34

My guess is that Evaluate functionality is required for Excel's internal calculations. And, once created, they realized that Evaluate is extremely flexible and therefore could be a valuable addition to the public object model, so they exposed it. This is just my guess, but it feels about right.

Mike Rosenblum 2010-04-20 12:17:30

ansaurus

tags:

views:

answers:

Why is Excel's 'Evaluate' method a general expression evaluator?

related questions