.Value = .Value与VBA中的Evaluate（）函数类似吗？ [英] Does .Value = .Value act similar to Evaluate() function in VBA?

问题描述

请考虑以下代码段。它将相同的公式写入两个单元格 A1 和 A2

  Sub Main（）
 With Range（A1）
 .Formula == 1 + 1
结束于
 b $ b使用范围（A2）
 .Formula == 1 + 1
 .Value = .Value 
结束于
 End Sub 
   
 
 
第二个与块使用 .Value = .Value ，它计算/执行公式，因此公式从公式栏中消失。请参阅说它可以与公式一起使用。但在示例中，我已经显示它不工作。 
 
 
 
  
 
 
 
公式是否用作 Evaluate（）中的参数？我一直认为Evaluate是非常强大的，但事实证明， .Value = .Value 实际上是更强大。即使他们非常相似，他们有点不同（但我认为这可能是我的错，因为我选择的公式，这个例子可能会受到限制或限制）。我想我已经展示了什么使他们两个相似和不同的同时。它仍然是50％/ 50％，我不能确切地告诉他们是否相同。 
解决方案
 .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了解详情 
 
Consider the following snippet. It writes the same formula to two cells A1 and 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 with block uses .Value = .Value which calculates/executes the formula therefore the formula disappears from the formula bar. Refer to hiding formulas from formula bar for a supportive reference.



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 A3 then that new cell's formula is being Evaluated() into another cell A4. The results as shown

 

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

However, The below code pulls value out of a closed workbook using the two mentioned approaches. I have created a workbook book9.xlsm for this example with a hello put in cell A1. book9.xlsm is the one I will be pulling the A1's value from. Consider the code
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 with block puts a formula in cell's A1 value from a book9.xlsm. It gets executed therefore the pulled value is hello but the formula bar shows the actual .Formula which is C:\....

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

Range("A3") is the same as the first with block. 

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. 

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



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

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



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 = .Value actually 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.
解决方案
.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 = .Value与VBA中的Evaluate（）函数类似吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！