.Value = .Value 是否类似于 VBA 中的 Evaluate() 函数？

Does .Value = .Value act similar to Evaluate() function in VBA?

提问by Charles Williams

Consider the following snippet. It writes the same formula to two cells A1and A2

考虑以下片段。它将相同的公式写入两个单元格，A1然后A2

Sub Main()
    With Range("A1")
        .Formula = "=1+1"
    End With

    With Range("A2")
        .Formula = "=1+1"
        .Value = .Value
    End With
End Sub

The second withblock uses .Value = .Valuewhich calculates/executes the formula therefore the formula disappears from the formula bar. Refer to hiding formulas from formula barfor a supportive reference.

第二个with块使用.Value = .Value哪个计算/执行公式，因此公式从公式栏中消失。请参阅从公式栏中隐藏公式以获得支持性参考。

Now, add another with block

现在，添加另一个块

With Range("A3")
     .Formula = "=1+1"
End With
Range("A4") = Evaluate(Range("A3").Formula)

You add a formula to the cell A3then that new cell's formula is being Evaluated()into another cell A4. The results as shown

您向单元格添加一个公式，A3然后该新单元格的公式将被添加Evaluated()到另一个单元格中A4。结果如图

I think the above kind of shows that .Value = .Valueand Evaluate()do the same thing.

我认为上述类型表明.Value = .Value并Evaluate()做同样的事情。

However, The below code pulls value out of a closed workbook using the two mentioned approaches. I have created a workbook book9.xlsmfor this example with a helloput in cell A1. book9.xlsmis the one I will be pulling the A1's value from. Consider the code

但是，下面的代码使用提到的两种方法从封闭的工作簿中提取值。我book9.xlsm为此示例创建了一个工作簿，hello并在单元格 A1 中放置了一个。book9.xlsm是我将从中提取A1's 值的那个。考虑代码

Sub PullValue()
    With Range("A1")
        .Formula = "='C:\Users\admin\Desktop\[book9.xlsm]Sheet1'!A1"
    End With

    With Range("A2")
        .Formula = "='C:\Users\admin\Desktop\[book9.xlsm]Sheet1'!A1"
        .Value = .Value
    End With

    Range("A3").Formula = "='C:\Users\admin\Desktop\[book9.xlsm]Sheet1'!A1"
    Range("A4") = Evaluate(Range("A3").Formula)
End Sub

The first withblock puts a formula in cell's A1value from a book9.xlsm. It gets executed therefore the pulled value is hellobut the formula bar shows the actual .Formulawhich is C:\....

第一个with块将公式放入单元格的A1值中book9.xlsm。它被执行，因此拉取的值是，hello但公式栏显示的是实际.Formula值C:\...。

The second withblock uses the .Value = .Valueas demonstrated above for the evaluation of the formula and hiding the formula by replacing it with the result.

第二个with块使用.Value = .Value上面演示的 来计算公式并通过用结果替换它来隐藏公式。

Range("A3") is the same as the first withblock.

Range("A3") 与第一个with块相同。

And now (A4) I am following the same principle as the first example(first snippet in this question) to Evaluate()the formula however It does not work this time.

现在 ( A4) 我遵循与Evaluate()公式的第一个示例（此问题中的第一个片段）相同的原则，但是这次它不起作用。

Please see all activated cells values and formula bar for each one

请查看每个激活的单元格值和公式栏

So now, I cannot say that .Value = .Valueis equal to Evaluate().

所以现在，我不能说这.Value = .Value等于Evaluate()。

The remarks of Evalutate()say that it can be used with formulas. But in the example I have shown it doesn't work.

Evalutate()的注释说它可以与公式一起使用。但在我展示的例子中，它不起作用。

Are the formulas used as parameters in Evaluate()restricted? I've always thought that Evaluate is very powerful but it kind of turns out that .Value = .Valueactually is even more powerful. Even though they are very similar they are somehow different ( but I am considering it might be my fault as the formula I chose for this example may be restricted or limited ). I think I have shown what makes them two similar and different at the same time. It still is like 50%/50% and I cannot tell exactly if they are the same or not. It would be great if somebody was able to explain what's missing here.

用作参数的公式是否Evaluate()受限？我一直认为 Evaluate 非常强大，但事实证明它.Value = .Value实际上更强大。尽管它们非常相似，但它们还是有所不同（但我认为这可能是我的错，因为我为此示例选择的公式可能受到限制或受限）。我想我已经展示了是什么让他们同时相似又不同。它仍然是 50%/50%，我无法准确判断它们是否相同。如果有人能够解释这里缺少的东西，那就太好了。

回答by Charles Williams

.value and Evaluate are not the same.
Excel maintains both a value and a formula string for each used cell, and you can get both of these independently using Range.Value and Range.Formula.
When you use Application.Evaluate to evaluate a string the string is evaluated as a formula on the active worksheet (so actually its better to use Worksheet.Evaluate rather than Application.Evaluate, and its faster too).
Using Rng1.Value=Rng2.Value copies the value from Rng2 into Rng1 and overwrites the formula of Rng1.
Using Rng1.Value=Evaluate(rng2.Formula) asks Excel to retrieve the formula string from rng2, evaluate it and return the result to Rng1.

The Evaluate method does not work exactly the same way as a formula in a cell: it has many "quirks" that you need to be aware of (including the fact that it does not work with formulas referring to external closed workbooks): see my blog post for details
Also its generally better to use .Value2 rather than .Value: see Value vs Value2 for details

.value 和 Evaluate 不一样。
Excel 为每个使用的单元格维护一个值和一个公式字符串，您可以使用 Range.Value 和 Range.Formula 独立获取这两者。
当您使用 Application.Evaluate 评估字符串时，该字符串将作为活动工作表上的公式进行评估（因此实际上使用 Worksheet.Evaluate 而不是 Application.Evaluate 更好，而且速度也更快）。
使用 Rng1.Value=Rng2.Value 将值从 Rng2 复制到 Rng1 并覆盖 Rng1 的公式。
使用 Rng1.Value=Evaluate(rng2.Formula) 要求 Excel 从 rng2 检索公式字符串，对其进行评估并将结果返回给 Rng1。

Evaluate 方法与单元格中的公式的工作方式并不完全相同：它有许多您需要注意的“怪癖”（包括它不适用于引用外部封闭工作簿的公式）：请参阅我的博客文章的详细信息
通常使用 .Value2 而不是 .Value 更好：有关详细信息，请参阅Value vs Value2

回答by stenci

.Value = .Valueand Evaluate()are different.

.Value = .Value并且Evaluate()是不同的。

I think you have some confusion with objects and their default properties.

我认为您对对象及其默认属性有些困惑。

Always keep in mind these two concepts:

永远记住这两个概念：

1. VBA allows you to use properties and methods without specifying the object. VBA will use their default object. For example when you use Evaluate()you actually use Sheet1.Evaluate().

2. VBA allows you to use objects without specifying the property. VBA will use their default property. For example when you use Range("A1") = 1you actually use Range("A1").Formula = 1. (You actually use Sheet1.Range("A1").Formula = 1!)

1. VBA 允许您在不指定对象的情况下使用属性和方法。VBA 将使用它们的默认对象。例如，当您使用时，Evaluate()您实际使用Sheet1.Evaluate().

2. VBA 允许您在不指定属性的情况下使用对象。VBA 将使用它们的默认属性。例如，当您使用时，Range("A1") = 1您实际使用Range("A1").Formula = 1. （你实际上使用Sheet1.Range("A1").Formula = 1！）

Going back to your example, when you do .Value = .Valueyou actually do Range("A2").Value = Range("A2").Value. The Value property of a cell can be a number, a string, an error, etc. And when it is a number, it could be the wrongnumber, that is not the correct result of the formula in that cell (for example because you have automatic calculation disabled). So .Value = .Valueis equivalent to .Formula = "<xxx>", where <xxx>is the last value calculated on the cell.

回到你的例子，当你这样做时，.Value = .Value你实际上是这样做的Range("A2").Value = Range("A2").Value。单元格的 Value 属性可以是数字、字符串、错误等。当它是数字时，它可能是错误的数字，这不是该单元格中公式的正确结果（例如，因为您禁用自动计算）。So.Value = .Value等价于.Formula = "<xxx>"，其中<xxx>是在单元格上计算的最后一个值。

When you do Range("A4") = Evaluate(Range("A3").Formula)you ask Excel to evaluate the formula and assign the result to the Formulaproperty of the range A4 (because the formula property is the default property of a range object.)

当您Range("A4") = Evaluate(Range("A3").Formula)要求 Excel 计算公式并将结果分配给Formula区域 A4的属性时（因为公式属性是区域对象的默认属性。）

回答by Monty Wild

.Value = .Value(within a cell's Withblock) simply sets the value of the cell to its current value, overwriting and removing its formula if it has one. The .Valueproperty simply represents the current value of a cell. If the cell contains a formula that has not yet been calculated, e.g. if calculation has been turned off, it may not return the formula result, instead returning the previous value of the cell.

.Value = .Value（在单元格的With块内）简单地将单元格的值设置为其当前值，如果有公式，则覆盖并删除其公式。该.Value属性仅表示单元格的当前值。如果单元格包含尚未计算的公式，例如，如果计算已关闭，则它可能不会返回公式结果，而是返回单元格的先前值。

Excel.Application.Evaluatetakes a string value, evaluating the string's contents as if it were a formula or the name of a cell, and returning the value of that cell or formula. If you pass it a string that can't be mapped to an Excel cell name or isn't a valid formula, you'll get an error. The purpose of Evaluateis to allow dynamically created formulas to be evaluated without having to write the formula out to a cell. If passed a formula, it should also return a result even if workbook calculation is turned off, though if passed a name, I would expect it to return the current value of a cell, and if workbook calculation is off, that value may not reflect the expected value of the referenced cell.

Excel.Application.Evaluate接受一个字符串值，将字符串的内容作为公式或单元格名称进行评估，并返回该单元格或公式的值。如果传递给它的字符串无法映射到 Excel 单元格名称或不是有效公式，则会出现错误。的目的Evaluate是允许评估动态创建的公式，而不必将公式写出到单元格中。如果传递了一个公式，即使关闭了工作簿计算，它也应该返回一个结果，但如果传递了一个名称，我希望它返回一个单元格的当前值，如果工作簿计算关闭，该值可能不会反映引用单元格的期望值。

Presumably since the .Valueof a cell and Evaluate()can return different results, the worksheet formula evaluation engine and the Application.Evaluate()engine are different, or at least have some different elements.

想必由于.Value一个单元格的和Evaluate()可以返回不同的结果，工作表公式计算引擎和Application.Evaluate()引擎是不同的，或者至少有一些不同的元素。

回答by Kamal G

Evaluate is a function And something which use followed by a dot (.) after a function,that is called as property. So ".Value",".Formula",".Text" are properties of Range which you are using here.

Evaluate 是一个函数，在函数后跟一个点 (.) 的东西被称为属性。所以 ".Value",".Formula",".Text" 是你在这里使用的 Range 的属性。

Don't mix up these 2 things.

不要混淆这两件事。

A function takes input, perform actions using its input variables and return results. And it works on the data type for which it is configured.

函数接受输入，使用其输入变量执行操作并返回结果。它适用于为其配置的数据类型。

.value is a generic property which is not dependent on data type. It can be string, numeric, float or whatever.

.value 是一个不依赖于数据类型的通用属性。它可以是字符串、数字、浮点数等。

so there is possibility where you get error from 1 and other work absolutely fine.

所以有可能你从 1 和其他工作中得到错误绝对没问题。