Annoying rounding bug

[voodoo]

I am trying to rounddown in VBA but as VBA does not support rouddown I am trying with Fix and Int functions. I know Excel itself supports rounddown but due to the coding requiremnts I need to use VBA. But I have found the follwoing annoying bug.

dblSumGross = 710000
dblCommRate = 0.0006

Now dblSumGross * dblCommRate = 426 exactly. Even rounding in Excel to 30 decimal places it is excatly 426. But when I use the Fix or Int function in my VBA code I get 425!

e.g.

dblTotalComm = Fix(dblSumGross * dblCommRate)
dblTotalComm = Int(dblSumGross * dblCommRate)

both produce a value of 425

Fix and Int functions are supposed to round non interger values down to the nearest integer but here it seems to be taking one integer 426 at taking it down to the next integer 425

This is really annoying and I am struggling to find a solution.

Anyone with any ideas about this one?

Thanks

voodoo

 

[SIXTH SENSE]

hi voodoo!

I got the same result too.
But when I do the multipication in a variable that is
declared integer, I got the rigth answer.

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> xxx()
<SPAN style="color:#00007F">Dim</SPAN> temp <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>
dblSumGross = 710000
dblCommRate = 0.0006
temp = (dblSumGross * dblCommRate)
[j1] = (temp)
[j2] = Int(temp)
[j3] = Fix(temp)

<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[Mudface]

Try this. I think the problem is that a double data type isn't quite accurate enough. You can't declare a decimal data type, but it can be stored in a Variant by using the CDec function: -

Public Sub Test()
Const dblSumGross As Variant = 710000
Const dblCommRate As Variant = 0.0006
Dim dbltotalcomm As Variant

dbltotalcomm = dblSumGross * dblCommRate
MsgBox Fix(CDec(dbltotalcomm))
dbltotalcomm = dblSumGross * dblCommRate
MsgBox Int(CDec(dbltotalcomm))

End Sub

 

[voodoo]

Thanks guys, a couple of good suggestions. I think I can work with these and get the result I want.

 

[voodoo]

I just discovered that VBA does infact support Ronddown. But you need to predix it with Application.

I had tried just something like

dblComm = RoundDown(dblGross * dblCommRate, 0)

and you get a compeil error "Sub or Function not defined. But when I tried

dblComm = Application.RoundDown(dblGross * dblCommRate, 0)

Het presto it works! For most function you don't need the Application prefix so this is an unsual case.