title: Excel VBA: get value of cell, independent of cell's formatting
body: <p>In Excel VBA, <code>Range("A1").Value</code> should return the underlying value of the range A1 on the worksheet.  But I'm getting a different value if the cell is formatted as Accounting.</p>
  
  <p>How do I get the actual underlying value of the cell?</p>
  
  <p><strong>Worksheet</strong></p>
  
  <p>Make a new document, enter the following values in cells:</p>
  
  <ul>
  <li>A1: 0.00001</li>
  <li>A2: =A1</li>
  <li>A3: =A1=A2</li>
  </ul>
  
  <p>As you'd expect, A3 results in <code>TRUE</code>.  Now change the formatting of A2 to Accounting, using 2 decimal places.  A2 now reads <code>$ 0.00</code>, but the underlying value is still <code>0.00001</code>, so A3 is still <code>TRUE</code>.</p>
  
  <p><strong>VBA</strong></p>
  
  <p>Make a new module and add in the following function:</p>
  
  <pre><code>Function f(addr As String)
      f = Range(addr).Value
  End Function
  </code></pre>
  
  <p>As you can see, this just gets the value of a range using the <code>Value</code> method of the <code>Range</code> object.</p>
  
  <p><strong>Worksheet</strong></p>
  
  <p>Back to the worksheet.  Enter the following values:</p>
  
  <ul>
  <li>B1: =f("A1")</li>
  <li>B2: =f("A2")</li>
  <li>B3: =B1=B2</li>
  </ul>
  
  <p><code>A1</code> and <code>A2</code> have the same underlying value, but <code>B1</code> and <code>B2</code> don't, even though they're both calculated using the <code>Value</code> method of <code>A1</code> and <code>A2</code>.</p>
  
  <p>The expression in <code>A3</code> (<code>=A1=A2</code>) is accessing the actual underlying value of <code>A1</code> and <code>A2</code>.  How do I access these values in VBA?</p>