VBA rounding problem

Question

I have this obscure rounding problem in VBA.

a = 61048.4599674847
b = 154553063.208822
c = a + b   
debug.print c
Result: 
154614111.66879

Here is the question, why did VBA rounded off variable c? I didn't issued any rounding off function. The value I was expecting was 154614111.6687894847. Even if I round off or format variable c to 15 decimal places I still don't get my expected result.

Any explanation would be appreciated.

Edit:

Got the expected results using cDec. I have read this in Jonathan Allen's reply in Why does CLng produce different results?

Here is the result to the test:

a = cDec(61048.4599674847)
b = cDec(154553063.208822)
c = a + b
?c
154614111.6687894847

Floating-point arithmetic may give inaccurate results in Excel — SeanC

chris neilsen chris neilsen · Accepted Answer · 2011-09-07T08:27:13

The reason is the limited precission that can be stored in a floating point variable.
For a complete explanation you shoud read the paper What Every Computer Scientist Should Know About Floating-Point Arithmetic, by David Goldberg, published in the March, 1991 issue of Computing Surveys.

Link to paper

In VBA the default floating point type is Double which is a IEEE 64-bit (8-byte) floating-point number.

There is another type available: Decimal which is a 96-bit (12-byte) signed integers scaled by a variable power of 10
Put simply, this provides floating point numbers to 28 digit precission.

To use in your example:

a = CDec(61048.4599674847)
b = CDec(154553063.208822)
c = a + b   
debug.print c
Result: 
154614111.6687894847

VBA rounding problem

2 Answers