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.

 

[GentleGeek]

Vladimir, Wow. Great results on the optimization!!

I have expanded the function to incorporate your optimized code plus my flexible functionality idea expressed in my comment of June 1. I have the following code, but haven't been able to figure out how to get my "branch" to take advantage of your optimized portion. Maybe you can figure out that "trick". Otherwise, I'm hoping I can get it with a little more time, but here it is for now:

' ZVI:2009-06-02 VBA arithmetic rounding function
' GG:2009-0604 expanded functionality
' 1st & 2nd Arguments are the same as for WorksheetFunction.Round
' 3rd Argument lets the user select the rounding "mode"- by 10's or other
Function ZVIGG_Round(V As Double, Optional Factor As Integer = 0, Optional Mode As Integer = 1) As Double
  Dim Temp As Variant
  If Mode =1 Then
     If Factor < 0 Then
        ZVIGG_Round = Round(V / 10 ^ -Factor + V * 2E-16, 0) * 10 ^ -Factor
     Else
        ZVIGG_Round = Round(V + V * 2E-16, Factor)
     End If
  Else
   Temp = CDec(V) * Factor + 0.5 * Sgn(V)
   ZVIGG_Round  = Fix(Temp) /   Factor
  End If
  If Abs(ZVIGG_Round) = 0 Then ZVIGG_Round = 0  ' Exclude -0 return value
End Function

All the best to you.

GG

 

[ZVI]

In this case the 2nd argument would be declared: Optional Factor As Double = 1
But CDec() conversion will stay the bottle neck because it is relatively slow function.
For speeding up the code you can try this variant:

<font face=Courier New>
  ' ...
  Else
   ZVIGG_Round = Round(V * Factor + V * 2E-16, 0) / Factor
  End If</FONT>

It seems for me that rounding with using of factor is not user friendly.
Instead I would suggest such VBA analogue of Excel MROUND() function:

<font face=Courier New>
' ZVI:2009-06-07 VBA rounding to required accuracy function
' Returns a number rounded to the desired multiple
' Like Excel function =MROUND(Value,Multiple) from Analysis ToolPack Add-In
Function ZVI_MRound(V As Double, Optional Multiple As Double = 1) As Double
  If Multiple = 0 Then Exit Function
  ZVI_MRound = Round(V / Abs(Multiple) + V * 2E-16, 0) * Abs(Multiple)
  If Abs(ZVI_MRound) = 0 Then ZVI_MRound = 0
End Function</FONT>

Regards,
Vladimir

 

[ZVI]

Please ignore the code parts in my previous post.

The fixed code:

<font face=Courier New>
  Else
   ZVIGG_Round = Round((V + V * 2E-16)* Factor, 0) / Factor
  End If</FONT>

<font face=Courier New>
' ZVI:2009-06-07 VBA rounding to the desired multiple
' Like Excel function =MROUND(Value, Multiple) from Analysis ToolPack Add-In
Function ZVI_MRound(V As Double, Optional Multiple As Double = 1) As Double
  If Multiple = 0 Then Exit Function
  ZVI_MRound = Round((V + V * 2E-16) / Abs(Multiple), 0) * Abs(Multiple)
  If Abs(ZVI_MRound) = 0 Then ZVI_MRound = 0
End Function</FONT>

 

[GentleGeek]

Sorry for my slow response this time...

Thanks for the additional revisions.. I like the capabilities and speed of the function you have created (with a little contribution from me).

Also, I think the MRound function is interesting, and it does seem more intuitive some of the time, though I'm not sure it is more intuitive when your multiple is a 7 or a 9 for example. Or when you use negative values for Multiple.

All the best to you in Crimea.
GG (in Memphis, TN)