VBA replaces long number with # sign (Excel 2010)

[Everlong]

Hi all,
I have an issue with Windows 7 Excel 2010. I'm retrieving some data from an API inside Excel using VBA and one of the values I get is 1098087649200.0001 (Variant/Decimal). However, when I post this value into an Excel cell, it shows as 1098087649200 (Variant/Double). I tried to format the cell before pasting the value in there using .NumberFormat = '0.00000', but I still get the same value set in Excel cell. I tried to do some debugging and when I try to create a Variant variable with this value, VBA replaces it with 1098087649200#
i.e. I put
Dim varTest As Variant
varTest = 1098087649200.0001

and when I move my focus from this row to a different one the values is
varTest = 1098087649200#

However if I remove the first 3 numbers, it shows all decimals
varTest = 8087649200.0001 (removed 109 from the front of the number)


Could anyone please advise why is this happening and how to avoid it? I'm using this number in a data comparison, and this is causing a fail due to 0.0001 difference.

[SamT]

Format the cell as a number with 4 decimal places.

[Paul_Hossler]

Worksheets can only handle Double-level precision, so when a too large number is put there it gets truncated or maybe converted to scientific format

That's why I think Sam's idea won't work -- the precision is lost, but the display format is correct of a Double with 4 decimal places



https://msdn.microsoft.com/en-us/library/office/gg251687.aspx


Decimal variables (https://msdn.microsoft.com/en-us/library/office/gg264568.aspx) are stored as 96-bit (12-byte) signed integers scaled by a variable power of 10. The power of 10 scaling factor specifies the number of digits to the right of the decimal point, and ranges from 0 to 28. With a scale of 0 (no decimal places), the largest possible value is +/-79,228,162,514,264,337,593,543,950,335. With a 28 decimal places, the largest value is +/-7.9228162514264337593543950335 and the smallest, non-zero value is +/-0.0000000000000000000000000001.

At this time the Decimal data type can only be used within a Variant (https://msdn.microsoft.com/en-us/library/office/gg264568.aspx), that is, you cannot declare a variable to be of type Decimal. You can, however, create a Variant whose subtype is Decimal using the CDec function.




The best you can do is use VBA internally, and/or store it on a worksheet as a string and use CDec if you need to pass it to VBA (




Option Explicit
Sub FunnyNumbers()
Dim D As Variant

'convert to Variant/Decimal
D = CDec("1098087649200.0001")

'all there
MsgBox D

'only shows Double precision on worksheet
Range("A1").Value = D
MsgBox Range("A1").Value

'internally you can do math with them
MsgBox D * 100
'can store on worksheet as a string
Range("A2").Value = "'" & CStr(D)

End Sub




Could anyone please advise why is this happening and how to avoid it? I'm using this number in a data comparison, and this is causing a fail due to 0.0001 difference.

On the WS, maybe keep both as strings, bring them into VBA to compare

I'd need test data to offer anymore ideas

[SamT]

The number the OP used as an example only has 17 significant digits. Excel can handle 29 digits.

[Paul_Hossler]

I think it's more a matter of precision that a floating point number can have, not necessarily the size of the number


https://support.microsoft.com/en-us/help/78113/floating-point-arithmetic-may-give-inaccurate-results-in-excel



The mantissa and the exponent are both stored as separate components. As a result, the amount of precision possible may vary depending on the size of the number (the mantissa) being manipulated. In the case of Excel, although Excel can store numbers from 1.79769313486232E308 to 2.2250738585072E-308, it can only do so within 15 digits of precision. This limitation is a direct result of strictly following the IEEE 754 specification and is not a limitation of Excel. This level of precision is found in other spreadsheet programs as well.


18394

A2 truncates the 'big' number to the limits of precision, and A3 doesn't go past those limits

[SamT]

I see.

If MS was a contractor, I would say that they build to code and paint it. That's an insult, case you dint know.

[Paul_Hossler]

For people who want the ultimate accuracy and precision for balancing their checkbooks, there is a add-in called xNumbers that stores really big numbers as strings and has functions to perform 'math' on them



The most part of the functions of this package have an optional parameter - Digit_Max - setting the max of significant digits for floating point computation. Since version 6.0.5.2 the maximum precision level can be set up to 32,670 digits.

http://www.thetropicalevents.com/Xnumbers60

[SamT]

My fingers handle my checkbook just fine.

32 thousand digits! Wow! I know a carpenter who would like that.