title: Does .Value = .Value act similar to Evaluate() function in VBA?
body: <p>Consider the following snippet. It writes the same formula to two cells <code>A1</code> and <code>A2</code></p>
  
  <pre><code>Sub Main()
      With Range("A1")
          .Formula = "=1+1"
      End With
  
      With Range("A2")
          .Formula = "=1+1"
          .Value = .Value
      End With
  End Sub
  </code></pre>
  
  <p>The second <code>with</code> block uses <code>.Value = .Value</code> which calculates/executes the formula therefore the formula disappears from the formula bar. Refer to <a href="https://stackoverflow.com/questions/16684297/hiding-formulas-in-formula-bar/16686868#16686868">hiding formulas from formula bar</a> for a supportive reference.</p>
  
  <p><img src="https://i.stack.imgur.com/LH9LF.png" alt=".Value = .Value hides the formula from bar"></p>
  
  <p>Now, add another with block </p>
  
  <pre><code>With Range("A3")
       .Formula = "=1+1"
  End With
  Range("A4") = Evaluate(Range("A3").Formula)
  </code></pre>
  
  <p>You add a formula to the cell <code>A3</code> then that new cell's formula is being <code>Evaluated()</code> into another cell <code>A4</code>. The results as shown</p>
  
  <p><img src="https://i.stack.imgur.com/bhUlF.png" alt="Evaluate cells formula"> </p>
  
  <p>I think the above kind of shows that <code>.Value = .Value</code> and <code>Evaluate()</code> do the same thing. </p>
  
  <p>However, The below code pulls value out of a closed workbook using the two mentioned approaches. I have created a workbook <code>book9.xlsm</code> for this example with a <code>hello</code> put in cell A1. <code>book9.xlsm</code> is the one I will be pulling the <code>A1</code>'s value from. Consider the code</p>
  
  <pre><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
  </code></pre>
  
  <p>The first <code>with</code> block puts a formula in cell's <code>A1</code> value from a <code>book9.xlsm</code>. It gets executed therefore the pulled value is <code>hello</code> but the formula bar shows the actual <code>.Formula</code> which is <code>C:\...</code>.</p>
  
  <p>The second <code>with</code> block uses the <code>.Value = .Value</code> as demonstrated above for the evaluation of the formula and hiding the formula by replacing it with the result. </p>
  
  <p>Range("A3") is the same as the first <code>with</code> block. </p>
  
  <p>And now (<code>A4</code>) I am following the same principle as the first example(first snippet in this question) to <code>Evaluate()</code> the formula however It does not work this time. </p>
  
  <p>Please see all activated cells values and formula bar for each one</p>
  
  <p><img src="https://i.stack.imgur.com/EDpMC.png" alt="results"></p>
  
  <p>So now, I cannot say that <code>.Value = .Value</code> is equal to <code>Evaluate()</code>. </p>
  
  <p>The remarks of <a href="http://msdn.microsoft.com/en-us/library/office/ff193019.aspx" rel="nofollow noreferrer">Evalutate()</a> say that it can be used with formulas. But in the example I have shown it doesn't work. </p>
  
  <p><img src="https://i.stack.imgur.com/lGMeT.png" alt="Evaluate() msdn"></p>
  
  <p>Are the formulas used as parameters in <code>Evaluate()</code> restricted? I've always thought that Evaluate is very powerful but it kind of turns out that <code>.Value = .Value</code> 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.</p>