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?
B3
is definitely coming upFALSE
. - Joe