Geek Answers Handbook

Why is the Excel Evaluate Method Evaluator of Common Expressions?

Recently, there have been several questions related to the Application.Evaluate method called from Excel VBA. The old XLM macro language also provides the EVALUATE() function. Both can be quite helpful. Does anyone know why an evaluator who is exposed can handle common expressions, though?

My own guess is that Excel was supposed to give people a way to get ranges from string addresses and get the value of named formulas, and just opening the portal to the expression analyzer was the easiest way. (The help for the VBA version speaks about its purpose - "convert the Microsoft Excel name to an object or value.") But, of course, you don't need the ability to evaluate arbitrary expressions just for that. (That is, Excel may provide a Name.Evaluate method or something like that.)

Application.Evaluate seems to be ... unfinished. This complete behavior is not well documented, and there are quite a few quirks and restrictions (as described by Charles Williams here: http://www.decisionmodels.com/calcsecretsh.htm ) with what is exposed.

I believe that the answer may simply be “why not expose it?”, But I would be interested to know what design decisions have led to the fact that this function takes the form that it performs. Otherwise, I would be interested to hear other cramps.

+1
design eval vba excel
source share
1 answer

Well, I think this is necessary so that VBA can get the result from the Named Formula (or the line containing the formula) (OK there is also an ugly method to insert the formula into the backup cell and then read the result, but, for example, it will not work from UDF).

In VBA, it is complex to determine if a given name contains a range link or formula. Using Evaluate works for both cases.

It is also sometimes very efficient and easier to create Excel formulas as strings and evaluate them, rather than transferring all the data from Excel to VBA, and then doing the calculations in VBA. (Its expensive to retrieve data from Excel in VBA and even worse with current .NET implementations).

+2
source share

More articles:


All Articles