Excel is forcing numbers on me!

[Mister_joe]

Hi folks,
I type the numbers shown below into cells in a worksheet but Microsoft Excel modifies them.


Data typed into a cell
Data displayed by Excel


999,999,999,999,999.99
999,999,999,999,999.00


99,999,999,999,999,999
99,999,999,999,999,900.00


99,999,999,999,999,999,999
99,999,999,999,999,900,000.00


999,999,999,999,999,999,999
999,999,999,999,999,000,000.00


999,999,999.99
1000000000



Is there a way to stop this from happening? I am writing a vba code that uses the numbers typed into certain cells and I do not want Excel to alter them because that may lead to the wrong results.


Thanks.

[JKwan]

may be typing an apostrophe in front of your numbers is what you need?

'999,999

[ranman256]

Expand the cell length.
Also, the value you see IN the cell may be different in the function bar (the Fx box,atop the cells C & D).
The function bar IS THE EXACT NUMBER, but the cell box may round or reformat for viewing. (dumb)

[Bob Phillips]

It's all down to the floating point engine in Excel. Although it can display 30 decimal points, the precision for a specified number is confined to 15 significant figures.

[Paul_Hossler]

It's ugly, but if you enter long numbers as text (with a leading single quote: '99999999999999999) internally you can get more precision using Decimal-typed variables




Sub numbers()
Dim v As Variant

'Decimal variables 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.

'Note
'At this time the Decimal data type can only be used within a Variant, 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.

v = CDec(Replace(ActiveSheet.Range("a1").Text, ",", vbNullString))
MsgBox v
End Sub




If you start doing math, I suspect that you'll get inaccuracies.

I remember seeing a BCD package for excel a long time ago that allow almost unlimited precision by treating string 'numbers' on a character by character basis