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