We value your feedback.

Take our survey and automatically be enter to win anyone of the following:

Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

When that double precision number is moved into a VBA single precision variable then only the 7 most significant digits are captured and the remaining 8 are dropped. If a cell value of 1.23456789012345 is moved to a single precision variable then that value becomes approximately 1.234568.

When the value is moved from a single precision number back to a double precision number then superfluous digits are introduced. The reason is illusive without an understanding of how the numbers are actually stored. Numbers are stored in a binary form using either four bytes for a single precision number or eight bytes for a double precision number. In the single precision number approximately one byte is used for the exponent (power of 10) and in the double precision number approximately two bytes are used. So effectively there are about 3 bytes of precision in the single and 6.5 bytes of precision in the double. The superfluous digits are introduced because Excel and VB erroneously pad the internal hexadecimal representation of the value with zeros instead of translating it to decimal, padding with zeros, and reconverting back to hexadecimal. Excel and VB operate this way for two reasons: 1) it is much faster to just truncate and pad the hexadecimal form of the number and 2) most users of Excel don't care about the introduction of such insignificant errors.

If the 6.5 bytes of a double precision number are stored in binary as 7048861DDF791 then, when converted to a single precision number the stored value becomes 704886 (about 3 bytes). When the single precision number is converted back to a double the stored value becomes 7048860000000. Translated into decimal values these three hexadecimal values are equivalent to:

7048861DDF791 -> 1.23456789012345

704886 -> 1.234568

7048860000000 -> 1.23456788063049

(Note that the above hexadecimal values are not true representations of the decimal numbers and are created purely for demonstration purposes.)

Are the three values the same? That depends on how many significant digits are required. If plotting a trajectory to a distant star then 10 or 15 digits might be required and such a conversion would cause problems. If recording units sold then only need 5 or 6 significant digits are required and such a conversion is insignificant. In other words, whether a company sold 10 or 11 units is interesting, but whether the company sold 6,243,123 or 6,243,124 units is not. So for the purposes of analyzing units sold in a retail environment, the above three values are the same.

This behavior can result in what appears to be digits appearing from nowhere. For example, a value entered as 20.62 in a cell will display as 20.6200008392333 when converted to a single and back to a double.

The superfluous digits can easily be avoided by only using double variables when moving numbers to and from Excel cells and when performing calculations. A correct conversion from a single to a double can be done by converting the value to a string and appending zeros:

Dim SingleVariable As Single

Dim DoubleVariable As Double

SingleVariable = [A1]

DoubleVariable = CStr(SingleVariable) & "00000000"

[A2] = DoubleVariable

But this method is crude at best, especially when considering that the double variable and a double conversion function are readily available.

Excel and VB use IEEE 32 and 64 bit floating point number formats. For more information on how double precision numbers are stored see http://www.ee.unb.ca/tervo/ee6373/IEEE64.htm. To create actual hexadecimal representations of 32 and 64 bit floating point numbers see http://babbage.cs.qc.edu/courses/cs341/IEEE-754.html.

Kevin