Rounding Functions to fix Vba round bankers method

[Starbucks_33]

Hi,

I was using the "round" functionin VBA and realized that it does not work, because it uses the "Bankers" method of rounding, whereas I want the "Arithmatic" rounding, I looked through the web and went to:

http://support.microsoft.com/kb/196652

But the problem is the function is not working.

<A href="http://support.microsoft.com/kb/196652">

  Function AsymArith(ByVal X As Double, _
            Optional ByVal Factor As Double = 1) As Double
     AsymArith = Int(X * Factor + 0.5) / Factor
   End Function

I put in AsymArith(8010.095,2) and it gives me 8011.

I also tried this function:

Function myRound(num, Optional places As Long = 0)
    If Int(num) Mod 2 = 0 Then
        myRound = Round(num + 1 / (10 ^ places), places) - 1 / (10 ^ places)
    Else
        myRound = Round(num, places)
    End If
End Function

myRound(8010.095,2) gives 8010.09

I cant get rid of the underlines. A

Also I don't want to use Application.round because this will slow down my code too much

Thanks

 

[RoryA]

I was thinking more along the lines of a VB6 dll as it would be much easier to switch between VBA and VB classic than to try and work with C++ (for me, anyway!)

 

[Damon Ostrander]

Hi again Starbucks_33,

In your example where you got 1080.09 instead of 1080.1, this is because you told AsymArith to round to hundredths, not tenths. If you want 1080.1 (tenths) you need to set

CTP = AsymArith(MinValue(ctp_cap * BaseFace0 / 1000, ctp_fac * BaseFace0 / 1000), 10)

I also agree with rorya that Application.WorksheetFunction.xxxx is much more efficient than any function written in VBA. This is because the worksheet functions are written and compiled in either assembler or C, and written with efficiency very much in mind. The only time VBA is more efficient is when in writing the code you can take advantage of your knowledge of the particular nature of the problem you are solving to do things with considerably fewer steps compared with the built-in and more general worksheet functions. Sometimes using the built-in worksheet functions is like using a sledgehammer to pound in a tack.

Damon

 

[theozz]

MySuggest

Function Sugg(x As Double, Optional PP As Double = 0)
     Sugg = Int(x * 10 ^ PP + 0.5) / 10 ^ PP
End Function

this code... PP <=15
cause Excel can calculate 15 digit number...

 

[Starbucks_33]

Thank you for your help on my issue!

1.) I am surprised that you say that the "application.round" would be faster then the function because I have always been told otherwise. Probably I will just test the two in a loop and see which is more efficient. I should have done this in the first part.

2.) I think unfortunately one problem with the round I have been having is that the excel is using "binary" logic in vba but is doing something else in the worksheet. In my example the two expressions were not the same in vba because of the binary nature of the code.

The following code demonstrates the problem: ... but I am not sure why the "application.round" works but the "round" does not work. (this doesnt have to do with the bankers method)

Sub RoundBinaryProblem()
Dim xx As Double, yy As Double, zz As Double

xx = Round(1080.095, 2)
MsgBox "Value of correct round " & xx

yy = Round(8.4401 * 950000 / 1000, 2)
MsgBox "Value of incorrect round " & yy

zz = Application.Round(8.4401 * 950000 / 1000, 2)
MsgBox "Value of correct round using Application.Round " & zz

End Sub

 

[RoryA]

Round is a VBA function; Application.Round is short for Application.WorksheetFunction.Round which is the Excel worksheet function. They work differently (hence the original problem!)

 

[Starbucks_33]

I think the original problem had to do with the "bankers" method where the round function will round up if the second to last digit is even and down if odd, when the last digit is equal to 5.

But this problem has to do with the "binary" nature of vba, so I guess it is an additional problem./

 

[RoryA]

I was responding to your "but I am not sure why the "application.round" works but the "round" does not work" comment. They are not the same function, which is why they work differently. I suspect the Worksheetfunction version automatically compensates for floating point errors.

 

[Starbucks_33]

the application round is much slower --> see the code below for example

The function is about 2 seconds, application round is about 30 seconds, on my machine

Sub MainTestSpeed()
Dim number_of_loops As Long
Dim Time_Run As Double
Dim i As Long
Time_Run = Now
number_of_loops = 10000000

'Call TestSpeedofRound1(number_of_loops)
Call TestSpeedFunction(number_of_loops)
Time_Run = (60 * Minute(Now - Time_Run) + Second(Now - Time_Run))
MsgBox "The macro took " & Time_Run & " Seconds to run " & number_of_loops & " loops"
End Sub
Sub TestSpeedofRound1(loops)
Dim xx As Double
Dim i As Long
For i = 1 To loops
xx = Application.Round(3.09475392, 4)
Next i
End Sub
Sub TestSpeedFunction(loops)
Dim i As Long
Dim xx As Double
For i = 1 To loops
xx = myRound(3.09475392, 4)
Next i
End Sub
Function myRound(num, Optional places As Long = 0)
If Int(num) Mod 2 = 0 Then
myRound = Round(num + 1 / (10 ^ places), places) - 1 / (10 ^ places)
Else
myRound = Round(num, places)
End If
End Function

 

[xld]

I also agree with rorya that Application.WorksheetFunction.xxxx is much more efficient than any function written in VBA. This is because the worksheet functions are written and compiled in either assembler or C, and written with efficiency very much in mind.

How do you work that one out? If you are using Application.Round, it intrinsically means that you are in VBA, so you have to call out to Excel to invoke the function, and get the result back. That is one hell of an overhead.

Round in Excel might be faster than a UDF, but VBA Round is some 36 times quicker than APplication.Round in my tests, and the function that 'theozz' gave, which is pure VBA, was 8 times quicker.

 

[xld]

the application round is much slower --> see the code below for example

The function is about 2 seconds, application round is about 30 seconds, on my machine

Which is exactly what I would expect and is in-line with my tests, as explained in my previous post.