Search code examples
excelvbafloating-accuracy

What is the accuracy of the Decimal data type?

I could not find anywhere which states VBA has a special variable for number e, so I figured a hefty number of decimal places would suffice. The manual states as follows:

You can use Decimal variables for money values. The advantage is the precision of the values. The Double data type is faster and requires less memory, but it is subject to rounding errors. The Decimal data type retains complete accuracy to 28 decimal places.

https://learn.microsoft.com/en-us/dotnet/visual-basic/programming-guide/language-features/data-types/numeric-data-types

I've tried doing Dim e@ ("@" being the decimal identifier from the manual), as well as this format

Dim MyDecimal As Variant
MyDecimal = CDec(3000000000000)

from https://bettersolutions.com/vba/data-types/decimal-data-type.htm

However, when I try initializing e with the value 2.7182818284590452353602874713, it gets rounded down to 2.71828182845905. What's going on here? Can 28 decimal places not actually be utilized?

Solution

  • What's going on here? Can 28 decimal places not actually be utilized?

    This is because, when you do like that, the value is read as a Double, thus truncated to what this data type can hold, before CDec converts it.

    ? CDec(2.7182818284590452353602874713)
 2.71828182845904 

? CDec(CDbl("2.7182818284590452353602874713"))
 2.71828182845904 

? CDec("2.7182818284590452353602874713")
 2.7182818284590452353602874713