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
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