Excel is forcing numbers on me!

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.

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

'999,999

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)

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.

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

Code:

---

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