Greg Lovern wrote:

Hi Mike,

Excel's limitation of 15 significant digits is not caused by Excel or
Microsoft. All other spreadsheet programs, including Lotus 123 and Corel
Quattro Pro, have the same limitation. The limitation is enforced by the
microprocessor. All Intel, Intel-compatible, Macintosh, and UNIX
microprocessors have the same limitation.

The 15 significant digit limitation is part of an industry standard called
"IEEE 754", which was created to achieve faster processing by sacrificing
precision. IEEE 754 was ratified in 1985, by which time it had already
become a de facto standard.

Your comments are correct in principle, but the details are a bit more
complicated than you imply (as you doubtless understand from the work
involved in writing xlPrecision).

The IEEE standard, governs binary representation, not decimal. Since
1818181818181818 is exactly representable in IEEE double precision, it
may display fully in other spreadsheets (I would have to try it). It
takes 17 decimal digits to uniquely specify an IEEE double precision
number, but some 16 decimal digit numbers cannot be represented.
Microsoft imposed a 15 decimal digit limit on inputs and on displayed
output, because to avoid questions like "why when I enter
9007199254740993, does it get changed to 9007199254740992?" as would
happen with an IEEE double precision program that displays more than 15
decimal digits.

The real surprise is in how Excel chooses to convert 16+ digit input
into a 15 digit displayed value. If I were writing the interface with a
15 decimal digit display limit, I would have used the entire input to
store the closest IEEE double precision approximation to that input in
the cell, then I would display the cell contents to 15 decimal digits.
The result would be that 1818181818181818 would have been exactly
stored, and would display as 1818181818181820, just as the VBA editor
does. Instead Excel first truncates the input to 15 digits, then stores
the IEEE approximation to that 15 digit number. The result is
unnecessarily lost precision that is not even rounded properly.

You can verify that 1818181818181818 can be exactly represented by
calculating it as a result =1818181818181810+8. The value will display
as 1818181818181820, but is stored with full precision as you can verify
by subtracting 1818181818181810.

Regardless, the fact remains that there is rarely (if ever) any reason
to do math on credit card numbers or account numbers, and hence the
simplest and fastest solution is to enter them as text.

Jerry