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

Question

Consider the following snippet. He writes the same formula in 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 evaluates / executes the formula, so the formula disappears from the formula line. See hiding formulas from the formula bar for a reference link.

Now add another one with block

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

You add the formula to cell A3 , then the new formula of cell Evaluated() to another cell A4 . The results shown in the figure.

I think the above shows show that .Value = .Value and Evaluate() do the same.

However, the code below extracts the value from a closed book using the two approaches mentioned. I created the book book9.xlsm book for this example using hello placed in cell A1. book9.xlsm is the one I pulled from the A1 value. 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 places the formula in cell A1 value from book9.xlsm . It starts, so the pulled out hello value, but the formula bar shows the actual .Formula , which is equal to C:\...

The second with block uses .Value = .Value , as shown above, to evaluate the formula and conjugate the formula, replacing it with the result.

The range ("A3") matches the first with block.

And now ( A4 ) I follow the same principle as the first example (the first fragment in this question) before the Evaluate() formula, but this time it does not work.

Please see all the values ​​of active cells and the formula bar for each of them.

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

Evalutate () comments say that it can be used with formulas. But in the example I showed, it does not work.

Are the formulas used as parameters in Evaluate() limited? I always thought that Evaluate is very powerful, but it looks like .Value = .Value is actually even more powerful. Although they are very similar, they are somewhat different (but I believe that this may be my mistake, because the formula that I chose for this example may be limited or limited). I think I showed what makes them two the same and different at the same time. It’s just like 50% / 50%, and I can’t say for sure whether they are the same or not. It would be great if someone could explain what was missing here.

Answer 1

.value and Evaluate are not the same.
Excel supports both a value and a formula line for each cell used, and you can get both of them independently using Range.Value and Range.Formula. you use Application.Evaluate to evaluate the row, which the row evaluates to as a formula on the active sheet (so in fact it is better to use Worksheet.Evaluate rather than Application.Evaluate and its speed too). Using Rng1.Value = Rng2.Value copies the value from Rng2 to Rng1 and overwrites the formula Rng1.
Using Rng1.Value = Evaluate (rng2.Formula) requests Excel to retrieve the formula string from rng2, evaluate it, and return the result to Rng1.

The Evaluate method does not work exactly like a formula in a cell: it has many “quirks” you need to know about (including the fact that it does not work with formulas related to external closed books): see my blog post for more details
Also it is generally better to use .Value2 rather than. Value: see Value vs Value2 for details

Answer 2

.Value = .Value and Evaluate() are different.

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

Always keep these two concepts in mind:

• VBA allows you to use properties and methods without specifying an object. VBA will use its default object. For example, when you use Evaluate() , you actually use Sheet1.Evaluate() .

• VBA allows you to use objects without specifying a property. VBA will use its default property. For example, when you use Range("A1") = 1 , you are actually using Range("A1").Formula = 1 . (In fact, you are using Sheet1.Range("A1").Formula = 1 !)

Returning to your example, when you do .Value = .Value , you really do Range("A2").Value = Range("A2").Value . The Value property of a cell can be a number, a string, an error, etc. And when this number, it may be the wrong number, this is not the correct result of the formula in this cell (for example, because you have the automatic calculation disabled). So .Value = .Value equivalent to .Formula = "<xxx>" , where <xxx> is the last value computed for the cell.

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

Answer 3

.Value = .Value (inside the With block) simply sets the cell value to its current value, overwriting and deleting its formula, if any. The .Value property simply represents the current value of the cell. If the cell contains a formula that has not yet been calculated, for example. if the calculation was disabled, it cannot return the result of the formula, instead returning the previous value of the cell.

Excel.Application.Evaluate takes a string value, evaluating the contents of the string as if it were a formula or cell name, and returned the value of that cell or formula. If you pass it a string that cannot be matched with the Excel cell name or is not a valid formula, you will receive an error message. The purpose of Evaluate is to enable dynamically created formulas to be evaluated without having to write the formula in a cell. If a formula is passed, it should also return a result, even if workbook calculation is turned off, although if I pass the name, I expect it to return the current value of the cell, and if workbook calculation is turned off, this value may not reflect the expected value of the reference cell.

Presumably, since .Value cells and Evaluate() may return different results, the worksheet formula evaluation engine and Application.Evaluate() engine are different or at least have several different elements.

Answer 4

Evaluation is a function. And something that is used after a period (.) After a function called a property. So, ".Value", "Formula", ". Text" are the Range properties that you use here.

Do not mix these two things.

The function takes input, performs actions using its input variables, and returns the results. And it works with the data type for which it is configured.

.value is a general property that does not depend on the data type. It can be string, numeric, floating or any other.

so there is a chance when you get an error from 1 and another job is absolutely normal.