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

 

[Damon Ostrander]

Hi xld and Starbucks_33,

I stand corrected! This shows that one's long-held assumptions can be drastically wrong. Thank you for your test results.

Theoretically, the Application's Round should be faster because setting up the call should be roughly the same whether accessing it in memory from the Application.WorksheetFunction object or setting up the call to the VBA version (Round, which incidentally is not written in VBA, but being a built-in VBA function is probably written in C). I'm thinking it may be that it is not in real memory but in virtual memory, and gets loaded each iteration of the loop, and Windows is not smart enough to see that it should be kept in real memory. I have found in the past that calling external library functions via DLLs can be very fast, much faster than writing the same code in VBA, but this is only true if the function has to do a lot of operations. This experience may have served to set up my incorrect assumptions about the WorksheetFunction object.

Another interesting find. I wrote a pure-VBA version (i.e., no use of the VBA Round function) and found it to be even faster than Starbuck's myRound function:

Function doRound(X As Double, Optional Places As Integer = 0) As Double
   Dim Power10    As Double
   If Places = 0 Then
      doRound = Int(X + 0.5)
   Else
      Power10 = 10 ^ Places
      doRound = Int(X * Power10 + 0.5) / Power10
   End If
End Function

This suggests that setting up the parameters for a function call--even if the function is part of the VBA math library--is an expensive operation. I did not at all expect this to be the case, but decided to test it since I was so wrong before.

I should also point out that the myRound function as written will take longer if the value has an integer part that is even than if it is odd. So myRound(3.09475392, 4) is faster than myRound(4.09475392, 4). This is because of the extra logic introduced to do "Bankers' Rounding".

One other thing:

Using the VBA Timer function gives better timing and is easier to use than the Now() function. Here is the code I used to do the timing:

Sub MainTestSpeed()
   Dim number_of_loops As Long
   Dim time1   As Single
   Dim time2   As Single
   Dim time3   As Single
   
   Dim i       As Long
   number_of_loops = 1000000
   time1 = Timer
   Call TestSpeedofRound1(number_of_loops)
   time2 = Timer
   Call TestSpeedFunction(number_of_loops)
   time3 = Timer
   MsgBox "Application.Round took " & time2 - time1 & " sec"
   MsgBox "myRound took " & time3 - time2 & " sec"
End Sub

You all keep Excelling.

Damon

 

[xld]

Theoretically, the Application's Round should be faster because setting up the call should be roughly the same whether accessing it in memory from the Application.WorksheetFunction object or setting up the call to the VBA version (Round, which incidentally is not written in VBA, but being a built-in VBA function is probably written in C).

Absolutely not, theoretically or practically.

Do not assume that two actions will take the same effort because they look the same, achieve the same.

A call to VBA round is a very straightforward, low-level call to some function buried within the O/S. There will be layers to go through, but these are very clearly defined, slick and very quick.

A call to a worksheetfunction means calling back to Excel, which means navigating the COM layer, and a couple more, which is extremely slow. ANd then it has to send the result back the same way. The COM layer is slow, that is why XLLs are fast, UDFs are not. Although, as was stated, it is unlikely that we can write VBA that was as fast as the equivalent Excel function, even if we could it would still be much slower because of the COM layer. And it gets worse with .Net because this uses the COM layer and piles the interoperability layer on top.

 

[Starbucks_33]

From the little I have heard - XLL's seem like code written in some other language that excel can interact with easily. I have written code in C++ and used excel's GUI to essentially create the text files that the C++ program uses and then "start" the C++ with the "Shell" function (because the C++ was much faster). So basically are you saying you can write a function like "round" in C++ and have excel access this executable, which would be faster then writing a UDF?

 

[xld]

Yes, XLLs are written in C++ so you have a head start.

If you want to try these, take a look here http://smurfonspreadsheets.wordpress.com/, scroll down to the item 'Creaing XLLs', Simon has given you some good starters. The chapter on 'XLLS and the C API' in PED is also a good reference.

 

[RoryA]

And just in case you weren't aware, PED = Professional Excel Development.

 

[Damon Ostrander]

Hi again xld,

I don't know about XLLs, but the routines in DLLs (Dynamic Linked Libraries) can be compiled in almost any language--I have even written them in FORTRAN. And yes, they can be very fast, much faster than a VBA UDF, and do some serious number crunching. You use the VBA Declare statement to define the interface to the DLL routine.

Damon

 

[xld]

I don't see the relevance of that to what I said. I was saying that a call to a worksheetfunction will not be faster, theoretically or not, than VBA native code, and I explained why.

If the DLL is invoked, indirectly, from a worksheet via a UDF, you still have the COM layer to navigate, so it isn't going to be fast. In my experience, DLLs only gain an advantage when there is real low-level stuff required, or there is serious number crunching; otherwise the overhead in linking loses the time saving of the compiled code. Thus, it is great for stuff like M-C simulations, but would be an absolute waste of time in a simple rounding UDF as is being discussed here.

Of course, DLLs do give you the advantage of protecting your code IPR, but then you have a deployment issue.

The other thing is that you cannot call a DLL method from the worksheet.