Single vs Double

J

J Laroche

Excel seems to keep numerical cell data as 8-byte values. This introduces
some conversion errors when calculations are made in VBA using 4-byte
variables (type Single). Using 8-byte variables (type Double) is preferable
to get accurate results. See the following procedure for a proof.

Sub FahrenheitToCelsius()
Dim a As Single
Dim b As Double

v = Selection.Value
a = Excel.WorksheetFunction.Round((v - 32) / 9 * 5, 1)
b = Excel.WorksheetFunction.Round((v - 32) / 9 * 5, 1)

Selection.Offset(0, 1) = a
Selection.Offset(0, 2) = b

End Sub

The results are:
Fahrenheit Celsius (4B) Celsius (8B)
35,6 2 2
78,6 25,8999996 25,9
45,1 7,30000019 7,3

From the 6th decimal there is a difference, even when rounding is not used.
Be aware of that if your calculations need to be extra accurate. The same
also occurs in WinXL 2000.

JL
Mac OS X 10.3.8, Office v.X 10.1.6
 
J

JE McGimpsey

J Laroche said:
Excel seems to keep numerical cell data as 8-byte values. This introduces
some conversion errors when calculations are made in VBA using 4-byte
variables (type Single). Using 8-byte variables (type Double) is preferable
to get accurate results.

This is correct. XL's numeric values are stored as Doubles (8-bytes).

I would never use Singles or Integers in a macro any longer. I see no
(or no significant) change in RAM requirements, and I believe that
Singles and Integers are internally converted into Doubles and Longs
during operation.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top