Excel VBA: get value of cell, independent of cell's formatting

Question

In Excel VBA, Range("A1").Value 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.

How do I get the actual underlying value of the cell?

Worksheet

Make a new document, enter the following values in cells:

• A1: 0.00001
• A2: =A1
• A3: =A1=A2

As you'd expect, A3 results in TRUE. Now change the formatting of A2 to Accounting, using 2 decimal places. A2 now reads $ 0.00, but the underlying value is still 0.00001, so A3 is still TRUE.

VBA

Make a new module and add in the following function:

Function f(addr As String)
    f = Range(addr).Value
End Function

As you can see, this just gets the value of a range using the Value method of the Range object.

Worksheet

Back to the worksheet. Enter the following values:

• B1: =f("A1")
• B2: =f("A2")
• B3: =B1=B2

A1 and A2 have the same underlying value, but B1 and B2 don't, even though they're both calculated using the Value method of A1 and A2.

The expression in A3 (=A1=A2) is accessing the actual underlying value of A1 and A2. How do I access these values in VBA?

Answer 1

It initally came up TRUE for me as well because I added the formatting after I entered the formulas.

To repro - re-edit B2.

To get the underlying value you need to use the VALUE2 property which seems to ignore the formatting:

Function f(addr As String)
    f = Range(addr).Value2
End Function