Basic variable type question (and maybe more)...

[tizwoz123]

Just a quick question, I may have a couple more later but for now just one.

What's the difference between a single and a double variable? The definition I find is...

? single ≡ 32 bit (4 byte) floating point number between
-3.402823E38 to - 1.401298E-45 and
1.401298E-45 to 3.402823E38

? double ≡ 64 bit (8 byte) floating point number between
-1.79769313486231E308 to -4.94065645841247E-324
4.94065645841247E-324 to 1.79769313486232E308

Which brings about on question, What about zero and all the values close to it that seem to be outside the ranfe of both of these variable types?

For the function Z= X/(Y^2), if we stick x=70 and y=1.71 and treat the Z as double, we get the answer 23.93898964 whereas the correct answer is actually 23.93898977. How does this discrepancy come about? Which makes me question when we should use Double as opposed to Single? I just though double has more decimal places and would be more accurate with some calculations but clearly I'm wrong.

[mikerickson]

If you are working with numbers whose absolute value is < 10^(-325), Excel is probably not the right platform for your use.

This routine shows the advantage of Double over Single.

Sub test()
    Dim singVal As Single
    Dim doubleVal As Double
    singVal = CSng(70) / CSng(CSng(1.71) ^ 2)
    doubleVal = CDbl(70) / CDbl(CDbl(1.71) ^ 2)
    MsgBox singVal & vbCrLf & doubleVal
End Sub

[Bob Phillips]

For the function Z= X/(Y^2), if we stick x=70 and y=1.71 and treat the Z as double, we get the answer 23.93898964 whereas the correct answer is actually 23.93898977. How does this discrepancy come about? Which makes me question when we should use Double as opposed to Single? I just though double has more decimal places and would be more accurate with some calculations but clearly I'm wrong.

By my calculation, it comes out as 23.9389897746315.

[johnske]

Just a quick question, I may have a couple more later but for now just one.

What's the difference between a single and a double variable? The definition I find is...


Which brings about on question, What about zero and all the values close to it that seem to be outside the ranfe of both of these variable types?

For the function Z= X/(Y^2), if we stick x=70 and y=1.71 and treat the Z as double, we get the answer 23.93898964 whereas the correct answer is actually 23.93898977. How does this discrepancy come about? Which makes me question when we should use Double as opposed to Single? I just though double has more decimal places and would be more accurate with some calculations but clearly I'm wrong.

If you want your answer as a double then all other variables used in that calculation must also be declared as double, otherwise you'll get an incorrect answer e.g. this will give the correct answer
[vba]
Option Explicit
Sub a()
Dim X As Double, Y As Double, Z As Double
X = 70: Y = 1.71
Z = X / (Y ^ 2)
MsgBox Z 'gives correct answer (23.9389897746315)
End Sub
[/vba]
but because there are mixed data types this will give an incorrect answer
[vba]
Option Explicit

Sub b()
Dim X As Long, Y As Single, Z As Double
X = 70: Y = 1.71
Z = X / (Y ^ 2)
MsgBox Z 'gives wrong answer (23.9389887065614)
End Sub
[/vba]

[rlv]

Be wary of inadvertent type conversion. If I do the calculation and store the result as a single, then do a type conversion to double, I get the same erroneous value as you did (23.93898964)


[vba]Sub Test()
Dim S As Single
Dim D As Double, TC As Double
Dim V As Variant

S = 70 / 1.71 ^ 2
D = 70 / 1.71 ^ 2
V = 70 / 1.71 ^ 2

TC = S ' Implied type conversion

Debug.Print "S = " & S
Debug.Print "D = " & D
Debug.Print "V = " & V
Debug.Print "XL = " & ActiveSheet.Range("A1") 'calc in spreadsheet
Debug.Print "Type Conversion TC (rounded to 8 places) = " & Format(TC, "0.00000000")

End Sub[/vba]
Results:

S = 23.93899
D = 23.9389897746315
V = 23.9389897746315
XL = 23.9389897746315
Type Conversion TC (rounded to 8 places) = 23.93898964