Search code examples
excelfloating-pointdoublerounding-errorvba

Excel VBA rounding with doubles

I am trying to do a very accurate calculation using doubles that relate to cells in my worksheet. As I test I wrote the following code:

Sub MacroTest()

Dim opt As Worksheet
Set opt = Sheets("Optimisation")

Dim test As Double

test = CDec(opt.Cells(2, 10))

End Sub

In the cell J2 is the decimal value -£3,298.8599993... however when debugging the code it is clear that the test double is only picking up -3298.86 as the value. I don't understand why this would be as I thought doubles were more accurate than this. Most of the posts I have read seem to think that using CDec should sort this problem out, however as shown this does not seem to work for me.

Note - The value in J2 has been calculated using a formula, not sure if this makes any difference. Any help is greatly appreciated - thanks.

Solution

  • You should use opt.Cells(2, 10).Value2 in this case. Range.Value2 property:

    The only difference between this property and the Value property is that the Value2 property doesn’t use the Currency and Date data types. You can return values formatted with these data types as floating-point numbers by using the Double data type.

    So the default .Value will return Currency if the cell is formatted as currency while .Value2 will return a Double nevertheless.