# Rounding Functions to fix Vba round bankers method

#### Starbucks_33

##### Active Member
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
Hi Starbucks_33,

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
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
As a matter of interest, what makes you think that Application.Round will be slower than your own VBA version?

#### Starbucks_33

##### Active Member
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
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
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

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
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
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?

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

### 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...