为什么Excel的“评估"方法是通用表达式评估器? [英] Why is Excel's 'Evaluate' method a general expression evaluator?

问题描述

最近出现了一些问题，涉及可以从Excel VBA调用的 Application.Evaluate 方法.旧的XLM宏语言还公开了 EVALUATE()函数.两者都可能非常有用.有人知道为什么公开的评估程序可以处理通用表达式吗?

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?

我自己的直觉是Excel需要给人们一种从字符串地址获取范围并获取命名公式的值的方法，而最简单的方法就是打开表达式评估器的门户.(VBA版本的帮助确实说明了它的目的是将Microsoft Excel名称转换为对象或值".)但是，当然，您不需要为执行此操作而评估任意表达式的能力.(也就是说，Excel可以提供 Name.Evaluate 方法或其他方法.)

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 似乎还没有完成.它的完整行为没有得到很好的记录，并且存在很多怪癖和局限性(如Charles Williams在此处所述:

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.

推荐答案

好吧，我认为需要启用VBA才能从命名公式"(或包含公式的字符串)中获取结果，(好的，还有丑陋的方法将公式插入备用单元格，然后读回结果的示例，但是例如在UDF内部将无法正常运行.

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).

在VBA中，确定定义的名称是否包含范围引用或公式的复杂性.在两种情况下，都可以使用评估".

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

有时，将Excel公式构建为字符串并对其进行求值也非常高效且简单，而不必将Excel中的所有数据导入VBA，然后在VBA中进行计算.(将数据从Excel导入VBA的成本很高，而在当前的.NET实现中则更糟).

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).

这篇关于为什么Excel的“评估"方法是通用表达式评估器?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！