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 Starbucks_33,

If your intention with

AsymArith(8010.092,2)

is to round to the nearest 100th, you should call it this way:

AsymArith(8010.092,100) (yields 8010.92)

Using Factor=2 rounds to the nearest 1/2. If you want to round to the nearest whole number use Factor= 1.

Damon

 

[Starbucks_33]

Damon,

Thanks for the reply. Using factor = 100 works except I get some wierd results in some cases for example.

1.) AsymArith(8018.095,100) = 8018.1 this works.

But when I try to pass a variable, it does not work.

2.) CTP = AsymArith(MinValue(ctp_cap * BaseFace0 / 1000, ctp_fac * BaseFace0 / 1000), 100)

where MinValue(ctp_cap * BaseFace0 / 1000, ctp_fac * BaseFace0 / 1000) = 8018.095

this results in = 8018.09

3.) or just

CTP = AsymArith(MinValue(ctp_cap * BaseFace0 / 1000, ctp_fac * BaseFace0 / 1000), 100)
CTP = AsymArith(CTP,100)
... doesnt work either. CTP is a double

4.) but when I put

Sub TestArith()
Dim xx As Double
xx = 8018.095
xx = AsymArith(xx, 100)
End Sub

this works --> xx = 8018.1

It is really confusing!
Any idea what the issue is?

Thanks!

 

[RoryA]

As a matter of interest, what makes you think that Application.Round will be slower than your own VBA version?

 

[Starbucks_33]

I never use "Application." because I have found it slows my code down quite a bit. I have also seen other posts saying the same thing.

I wrote a function for min and max rather then use application.max , min

 

[RoryA]

It will depend on what you are doing really. If you are working with worksheet ranges, I would almost guarantee that Application.WorksheetFunction.whatever will be faster than anything you write in VBA (and Application.whatever slightly slower than the WorksheetFunction version, but faster than VBA). If you are working with arrays/variables your VBA may well be faster but to be honest if performance is that much of an issue you shouldn't really be using VBA anyway if at all possible. At the end of the day though, whatever works best for you is the best answer - just be aware there may not be one solution that's optimal for all occasions.

 

[Starbucks_33]

All the coding is using VBA arrays and variables. I tried coding some projects in C++, but VBA is a lot easier to maintain and build. (The C++ was about 50x faster then the vba version that I wrote)

Here are the main issues with C++
1. it is hard to store and manipulate data
2. Creating a gui enviornment in C++ is a total nightmare
also I am not a programmer.
3. There is a msdn developer website that you can ask questions, but it is no where near as good as MrExcel!!!

In excel you can easily design a form with combo boxes and store data throughout the worksheet that you can access.

C++ you need to code every gui item, and it is very difficult (using visual express 2008). A friend with a masters in C++ computer science took her about 3 hours to figure out how to get it working.

Where do you store the data in C++, you have to use text files, this is very cumbersome to read and store.

But this is the clear advantage of excel, you can easily store and manipulate data in a worksheet. It is slightly slower then C++.

And if there was a problem with a funciton in C++ it woud be much harder to figure out.I cant figure out what the issue is.

 

[theozz]

How About ?

Sub test()
Dim xx As Double
Dim yy As Double
Dim zz As Double
Dim ww As Double
Dim MyCutt As Double
MyCutt = 12
xx = 8018.095
yy = AsymArith(xx, MyCutt)
zz = MyRound(xx, MyCutt)
ww = Sugg(xx, MyCutt)

Range("a1:a30").Clear
Range("a1") = "//   " & yy
Range("a2") = "//   " & zz
Range("a3") = "//   " & ww
Range("a4") = "//   " & Application.WorksheetFunction.Round(xx, MyCutt)
Beep
End Sub

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

Function MyRound(ByVal num As Double, Optional ByVal places As Double = 1) As Double
     If Int(num) Mod places = 0 Then
          MyRound = Round(num + 1 / (10 ^ places), places) - 1 / (10 ^ places)
     Else
          MyRound = Round(num, places)
     End If
End Function

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

 

[Starbucks_33]

Thanks for the extra functions Ozz,

But I still have the same problem. The functions work, but

when I pass a variable through like

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

I get 1080.09. instead of 1080.1.

Here MinValue(ctp_cap * BaseFace0 / 1000, ctp_fac * BaseFace0 / 1000) = 1080.095

 

[Starbucks_33]

I noticed something strange.

ppp = MinValue(ctp_cap * BaseFace0 / 1000, ctp_fac * BaseFace0 / 1000)
TotalFace = MinValue(ctp_cap * BaseFace0 / 1000, ctp_fac * BaseFace0 / 1000)

CTP = AsymArith(TotalFace, 100)
CTP = AsymArith(ppp, 100)

If TotalFace = ppp Then MsgBox "They match!" 'This is true
If TotalFace = 1080.095 Then MsgBox "They match!" 'This is not true!

Sto the second condition is not true?