Rounding Functions to fix Vba round bankers method

Starbucks_33

Active Member
Joined
Jun 16, 2008
Messages
345
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">
HTML:
  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:
HTML:
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
 
Last edited:

Damon Ostrander

MrExcel MVP
Joined
Feb 17, 2002
Messages
4,239
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

Active Member
Joined
Jun 16, 2008
Messages
345
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!
 
Last edited:

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,317
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
As a matter of interest, what makes you think that Application.Round will be slower than your own VBA version?
 

Starbucks_33

Active Member
Joined
Jun 16, 2008
Messages
345
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,317
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
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

Active Member
Joined
Jun 16, 2008
Messages
345
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

Active Member
Joined
Jun 11, 2007
Messages
328
How About ?

Code:
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
Code:
Function AsymArith(ByVal x As Double, Optional ByVal Factor As Double = 1) As Double
     AsymArith = Int(x * Factor + 0.5) / Factor
End Function

Code:
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
Code:
Function Sugg(x As Double, Optional PP As Double = 0)
     Sugg = Int(x * 10 ^ PP + 0.5) / 10 ^ PP
End Function
 

Starbucks_33

Active Member
Joined
Jun 16, 2008
Messages
345
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

Active Member
Joined
Jun 16, 2008
Messages
345
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?
 

Forum statistics

Threads
1,081,515
Messages
5,359,229
Members
400,523
Latest member
ExcelNewbie98

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top