Excel Rounding VBA vs XLsheet - weird problem

[GentleGeek]

Hello, New to the Forum. Looks great!!

I am pulling out my hair at the moment-
I have learned that the ROUND function functions differently in VBA vs. in a worksheet.
I have found an MS webpage that explains and also gives some alternate functions that "should" solve the problem.
http://support.microsoft.com/kb/196652

But, my problem remains. There may be a "bug" in the "Fix" Function.

The "bug" (which I have traced in excruciating detail in the VBA) is when I want to round "9.405" to 2 decimals for example, the SymArith function gets a correct intermediate value of "941", but FIX() then gives me "940", and my final result comes out to "9.40". What gives? This is not a "fix"!!

Here is MS's code:

Function SymArith(ByVal X As Double, _
         Optional ByVal Factor As Double = 1) As Double
   SymArith = Fix(X * Factor + 0.5 * Sgn(X)) / Factor
End Function

(One note about this function and the other replacement functions, MS should have used "10^Factor" instead of just "Factor", so that the functions would work the same as the regular ROUND function.

To debug, I broke the calculation into 2 steps, used Breakpoints to check intermediate values, and used "10^Factor" instead of just "Factor". I still get the same wrong result:

Function RoundSymArith(ByVal X As Double, _
        Optional ByVal Factor As Double = 1) As Double
    Dim XX As Double
    XX = X * 10 ^ Factor + 0.5 * Sgn(X)
    RoundSymArith = Fix(XX) / 10 ^ Factor
End Function

HELLLLLLLPPPP!!!!

Thanks in advance.

 

[roentgen]

Instead of trying to fix, why not use =WorksheetFunction.Round (value, dec places) in your vba instead? This gives the same result as the worksheet formula.

 

[GentleGeek]

Thanks for the reply.

I understand that using Worksheet.Round is about 7-10 times slower than using a custom round function in VBA. Someone on this board did thorough testing about 2-3 years ago. Apparently it's because of the process of going back and forth between VBA and Excel Worksheet.

Why doesn't FIX just work properly??

Do you think it could be a problem with not all values being DOUBLE datatype? Or maybe a problem of type conversion, or insufficient precision?

"I'm bound and determined to get a 'proper' solution to this problem!"

 

[ZVI]

Hi & welcome to the Board, GentleGeek!
Try my version of VBA arithmetic rounding function.
It's approx. in 6.7 times faster than WorksheetFunction.Round()

<font face=Courier New>
' VBA arithmetic rounding function
' Syntax is the same as for WorksheetFunction.Round(V,DecPlaces)
Function ZVI_Round(V, Optional DecPlaces = 0) As Double
  Dim b#
  b = Fix(DecPlaces)
  If DecPlaces < 0 Then
    b = 10 ^ -b
    ZVI_Round = Round(V / b + V * 2E-16, 0) * b
  Else
    ZVI_Round = Round(V + V * 2E-16, b)
  End If
  If Abs(ZVI_Round) = 0 Then ZVI_Round = 0  ' Exclude -0 return value
End Function</FONT>

Regards,
Vladimir

 

[tusharm]

From what I can tell you've run into the inherent limitation of how a number is stored in a computer. This is an inherent problem with all digital computers. For more, see
(Complete) Tutorial to Understand IEEE Floating-Point Errors
http://support.microsoft.com/kb/42980
or
Floating-point arithmetic may give inaccurate results in Excel
http://support.microsoft.com/kb/78113
or search google (w/o the quotes) 'floating point site:support.microsoft.com'

The page you referred to documents things as the author(s) meant them to be. As far as I can tell, there are no errors in the code sample used (SymArith) or in the Fix function. The webpage clearly states what Factor is supposed to be. It is *not* a power of 10 but instead, it is the number used to adjust to *any* value. As documented, to round to 0.05 use a factor of 20. In general, use Factor = 1/{value you want rounding to}. So, if you want rounding to 0.01, use a factor of 100.

To work around the floating point error, add a "fudge factor" as Vladimir did or use a data type with more bits as in the sample below. Interestingly enough, a data type with fewer bits (Single, for example) will also work for your example of 9.405.

One caveat about the code below. It has been a long time since I had to worry about floating point errors. Consequently, the simple approach below may not encompass all possibilities.

Option Explicit

Function SymArith(ByVal X As Double, _
         Optional ByVal Factor As Double = 1) As Double
    Dim Temp As Variant
    Temp = CDec(X) * Factor + 0.5 * Sgn(X)
    SymArith = Fix(Temp) / Factor
    End Function

Use the function as =SymArith(9.405,100)

Thanks for the reply.

I understand that using Worksheet.Round is about 7-10 times slower than using a custom round function in VBA. Someone on this board did thorough testing about 2-3 years ago. Apparently it's because of the process of going back and forth between VBA and Excel Worksheet.

Why doesn't FIX just work properly??

Do you think it could be a problem with not all values being DOUBLE datatype? Or maybe a problem of type conversion, or insufficient precision?

"I'm bound and determined to get a 'proper' solution to this problem!"

 

[GentleGeek]

Vladimir, thanks for your proposed alternate function. I see that it works by just adding a very tiny fudge factor to the value to be rounded. I had actually thought about doing something like that myself. But, I was trying to stay away from that if I could.. hoping that there would be some "legitimate" solution. Nevertheless, I am starting to think you're approach may be the way to go.. that way I can stop worrying about this.

Tusharm, yes I am aware of the inherent limitation of how numbers are stored in computers. I had hoped and expected that by using "Double" datatype I would avoid that problem. But Vladimir's fudge factor approach seems to avoid the problem, and your use of a Variant variable also seems to take care of the problem.

Also, I do appreciate your feedback about the use of "Factor" in the SymArith function. I can see a value in rounding to amounts other than powers of 10. Nevertheless, I strongly believe in consistency- these new functions should work the same way as the traditional ROUND function. Alternatively, they could have a 3rd parameter- a switch where 1 means "use the same way as the traditional ROUND function", and 2 means "round based on the actual value entered as the 2nd parameter." (default is "1") ... Consistency-- I can't remember all the intricacies of every function, and I hope that I do not have to look up in the manual every time I use one of those functions that I seldom use. The 3rd parameter lets me use the function the same way as I use ROUND, and yet also gives me that other more flexible rounding calculation. ... whatever!! LOL (I talk too much sometimes!)

Oh, one last thing (on my high horse for a moment.. LOL)-- I really think those new functions from MS should "work", and not have the problems that I have identified. I ought to be able to count on something from them that it should work properly.. Or am I foolish in that assumption?...

Thanks to you both! Have a great day!

GG

 

[ZVI]

After this night investigation I’ve found correct arithmetic rounding API function in library OLEAUT32.dll which is shipped with Windows OS

Here is the code:

<font face=Courier New>
Private Declare Sub VarRound Lib "oleaut32" (ByRef pvarIn As Variant, ByVal cDecimals As Long, ByRef pvarResult As Variant)

' The same as WorksheetFunction.Round()
Function ArithRound(V, Optional DecPlaces = 0)
  If DecPlaces < 0 Then
    Dim b#
    b = 10 ^ -Fix(DecPlaces)
    VarRound CDbl(V) / b, 0, ArithRound
    ArithRound = ArithRound * b
  Else
    VarRound V, DecPlaces, ArithRound
  End If
End Function
</FONT>

Regards,
Vladimir

 

[ZVI]

P.S. In my testing ArithRound() works in 12 ... 13 times faster than WorksheetFunction.Round()
Cheers,
Vladimir

 

[GentleGeek]

Vladimir.. thanks for your continued investigation. My project involves calculations with lots of dollar figures, so accurate rounding is imperative. I initially created my model in a worksheet file of 5 sheets. I am enhancing it now by programming it all in VBA, with the XLS sheets only for my input and output forms.

The solution you previously provided does the trick, and so I have gone ahead and am using that.

I did just play around with your new function, and unfortunately, I can't get it to work.

I get no message about OLEAUT32, so I assume the VBA finds it. I have used the Windows File Search utility and have verified that it is there.

When I put =ArithROUND(9.405,2) in a cell on a test sheet, it gives me 9.4 as a result and not 9.41 as it should. Similarly when I run it totally from within a VBA Sub procedure- same result.

Anyway, I'm not going to worry about it at this point. I'm using your other function. And working on other parts of my project.

Thanks again.

GG

 

[ZVI]

Your truth, GentleGeek!
For regret, API function of oleaut32.dll provides the same banking rounding as VBA Round() function. Sorry for wasting time on it.

But after minor optimization of mine version it becomes in 13 … 14 times faster than WorksheetFunction.Round() function:

<font face=Courier New>
' ZVI:2009-06-02 VBA arithmetic rounding function
' Arguments are the same as for WorksheetFunction.Round(V,DecPlaces)
Function ZVI_Round(V As Double, Optional DecPlaces As Integer = 0) As Double
  If DecPlaces < 0 Then
    ZVI_Round = Round(V / 10 ^ -DecPlaces + V * 2E-16, 0) * 10 ^ -DecPlaces
  Else
    ZVI_Round = Round(V + V * 2E-16, DecPlaces)
  End If
  If Abs(ZVI_Round) = 0 Then ZVI_Round = 0  ' Exclude -0 return value
End Function
</FONT>

Results of the speed testing applied to some rounding functions are as follows:

Excel Workbook
	A	B	C	D	E	F
1	Method	Operations	Seconds	Opers per sec	Speed Ratio %	Comment
2	WorksheetFunction.Round()	1 000 000	4.172	239 672	100%	VBA
3	MathRound()	1 000 000	1.500	666 667	278%	MathRound = CDbl(FormatNumber(V, DecPlaces))
4	SymArith()	1 000 000	1.266	789 819	330%	Tushar Mehta
5	ZVI_Round()	1 000 000	0.297	3 368 421	1405%	ZVI
Tests

Regards,
Vladimir