Worksheet rounding vs VBA rounding

[Simon Cleal]

Excel 2K

The VBA Round function uses 'Bankers rounding' (half the time the .5 is
rounded up, half the time down).

The worksheet ROUND() function uses 'normal rounding' (.5 is always rounded
up)

Is there an easy way to make the VBA rounding the same as the worksheet
rounding

Thanks in advance
Simon

 

[Xcelion]

Hi Simon

You could use the same worksheet round function invoked using
WorksheetFunction.Round(arg1,arg2) in your VBA code

Thanks
Xcelion

 

[Bob Phillips]

Or you could cut your own

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


--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Simon Cleal]

Xcelion & Bob - Thanks for the alternative solutions

 

[okaizawa]

Hi,

Format function also does the arithmetic rounding like the worksheet
ROUND function. (except in a particular environment)

MsgBox CDbl(Format(2.5, "0")) 'shows 3

The Format() Function Gives Different Results in Windows XP Than in
Windows 2000
http://support.microsoft.com/kb/321047/en-us/


the accuracy of round function for decimal number is not so good.

MsgBox WorksheetFunction.Round(9000.92585, 4) 'shows 9000.9258
MsgBox Round(0.00015, 4) 'shows 0.0001

if you want such accuracy, you should think about a method that fits the
range of numerical data. (as we won't test functions with all number.)
for instance, if the minimum change is 0.00001,
Int((9000.92585 + 0.00001 / 10) * 10000 + 0.5) / 10000 is sure to return
9000.9259 even though a number has a small rounding error.