Single vs double and rounding

[sark666]

Even though I've been using vba for a while, I've been bad in not declaring my variables which I've been trying to change.

So this code has me puzzled:

Sub test()
Dim res_rate As Single
Dim unique_issues As Integer
Dim headcount As Integer
unique_issues = 6
headcount = 1754

res_rate = Round(unique_issues * 100 / headcount, 1)
Range("A1") = res_rate
Debug.Print res_rate
End Sub

I thought I would declare res_rate as a single as double takes more memory and I don't need that precision.

Once I round res_rate I would think regardless if it was a double or single it's value would be permanently changed to one decimal place.</SPAN>

However the above code gives A1 a value of 0.300000011920929 but the debug print prints what I would expect: 0.3

Range("A1") is formatted as general.


When I change the above code and declare res_rate as double then I get what I would expect 0.3 in the debugger and cell A1.


So what am I not seeing? How come after rounding somehow res_rate is retaining the complete decimal value??</SPAN>

 

[shg]

All numbers on a worksheet are Doubles. The Double representation of 0.3 is hex 3FD3333333333333

The Single representation of 0.3 is 3E99999A, which, converted to a Double, is 3FD3333340000000, which is obviously greater than 0.3

 

[shg]

More simply,

Debug.Print CDbl(0.3!)

... illustrates the issue.

 

[sark666]

More simply,

Debug.Print CDbl(0.3!)

... illustrates the issue.

Thanks for the response. I wasn't aware of this. I'm not sure I fully understand but I kind of get it is because of hexidecimal conversion. Hmm, this makes me weary though of ever using a single declaration.

Would always using a double dodge this issue? I guess I could also format the cell to 1 decimal place but again, I thought rounding would permanently change the value to 1 decimal place whereas formatting doesn't actually change a value, but rather how it's displayed.

 

[shg]

The reason is because the IEEE floating point format uses base 2 decimals, and many base 10 decimals (e.g., 0.1) are repeating decimals in base 2. It's just something you need to be aware of and program around.