在 vba 中分解评估
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18625971/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Breaking down evaluate in vba
提问by Matthew Paulin
I've been searching online trying to figure out what the use of evaluate is. What I get from msdn is: " An expression that returns an object in the Applies To list." I have no clue what this means.
我一直在网上搜索试图弄清楚评估的用途是什么。我从 msdn 得到的是：“一个表达式，它返回适用于列表中的一个对象。” 我不知道这是什么意思。
The reason I ask is because I've been given a piece of code and I'm trying to make some logical sense out of it. Why is it written this way? What is the advantage of using evaluate instead of a more traditional approach? What is the correct syntax of a long line of nested functions?
我问的原因是因为我得到了一段代码，我试图从中找出一些逻辑意义。为什么这样写？使用评估而不是更传统的方法有什么优势？一长串嵌套函数的正确语法是什么？
Here is my code example:
这是我的代码示例：
With Range("B1", Cells(Rows.Count, "B").End(xlUp))
.Value = Evaluate("Index(If(Left(Trim(" & .Address & "),1)=""."",Replace(Trim(" & .Address & "),1,1,""""),Trim(" & .Address & ")),)")
End With
Can someone help me break this down and make some sense out of it? It is supposed to remove leading periods and get rid of excess spaces in all cells in column b. My problem is that it only works if I run it twice. If I could make some sense out of this then I may be able to manipulate it to make it function correctly.
有人可以帮我分解一下并从中理解吗？它应该删除前导句点并去除 b 列中所有单元格中的多余空格。我的问题是它只有在我运行两次时才有效。如果我能从中得到一些道理，那么我也许可以操纵它以使其正常运行。
For extra credit, How would I build a statement like this if I wanted to go through the same range and remove all dashes ("-")?
额外的功劳，如果我想通过相同的范围并删除所有破折号（“-”），我将如何构建这样的语句？
I really want to learn. Any help appreciated.
我真的很想学习。任何帮助表示赞赏。
采纳答案by David Zemens
OK here goes:
好的，这里是：
Evaluatetells the application to evaluate a function in this context.
Evaluate告诉应用程序在此上下文中评估函数。
The function is a string concatenation of "Index(If(Left(...which includes some dynamic components (.Address), because it's being applied to the entire range:
该函数是一个字符串连接，"Index(If(Left(...其中包含一些动态组件 ( .Address)，因为它被应用于整个范围：
Range("B1", Cells(Rows.Count, "B").End(xlUp))
Range("B1", Cells(Rows.Count, "B").End(xlUp))
What this does, effectively, is to evaluate the formula for each cell in that range, but only writes the formula's evaluated value to each cell.
这实际上是为该范围内的每个单元格计算公式，但只将公式的计算值写入每个单元格。
Equivalent would be to fill the range with the formula, and then do a copy + paste-special (values only) to the range. This is obviously more expensive in terms of memory and time consuming processes, especially for a larger range object.
等效的方法是用公式填充范围，然后对范围进行复制 + 特殊粘贴（仅限值）。这在内存和耗时过程方面显然更昂贵，尤其是对于更大范围的对象。
I personally don't favor this approach, but that's a matter of my personal preference primarily.
我个人不赞成这种方法，但这主要是我个人喜好的问题。
Advantages in this case is that it's filling in an entire range of cells -- which could be 10 cells or 10,000 cells or 1,048,576 cells (in Excel 2007+) in one statement.
在这种情况下，优点是它填充了整个单元格范围——在一个语句中可以是 10 个单元格或 10,000 个单元格或 1,048,576 个单元格（在 Excel 2007+ 中）。
Alternative methods would be to do a For/Nextloop (which is expensive in terms of memory and therefore slow on large ranges), even if you're doing a loop over an array in memory and writing the resulting array to the worksheet, I think there is a certain elegance to using a singlestatement like this code.
替代方法是执行For/Next循环（这在内存方面很昂贵，因此在大范围内很慢），即使您正在对内存中的数组进行循环并将结果数组写入工作表，我认为有使用像此代码这样的单个语句有一定的优雅。
