VB Round( ) function

  • Thread starter Thread starter Andrew
  • Start date Start date
A

Andrew

If in a cell on a spreadsheet, I enter the following equation:
=ROUND(50.5, 0)
I obtain the result 51, which I would expect, since 0.5 should be rounded
up.

If I write the following VB funcion:

Function RoundFunc(Mark As Integer) As Integer
RoundFunc = Round(Mark, 0)
End Function

And then enter the following equation into a cell:
=RoundFunc(50.5)

I obtain the result 50. i.e. 0.5 is rounded down

Why the difference? Is there a function that I can use in my VB code that
will round 0.5 up rather than down?
 
Andrew said:
If in a cell on a spreadsheet, I enter the following equation:
=ROUND(50.5, 0)
I obtain the result 51, which I would expect, since 0.5 should be rounded
up.

If I write the following VB funcion:

Function RoundFunc(Mark As Integer) As Integer
RoundFunc = Round(Mark, 0)
End Function

And then enter the following equation into a cell:
=RoundFunc(50.5)

I obtain the result 50. i.e. 0.5 is rounded down

Why the difference? Is there a function that I can use in my VB code that
will round 0.5 up rather than down?
Click to expand...

When u use an integer as parameter for your function, you will loose all
numbers after the decimal point! The solution is to use a double for the
parameter.



Glenn
 
Andrew,
There is no VBA 'Round' function (not w/ Excel 97 anyway).
You could use cLng instead:
When the fractional part is exactly 0.5, CInt and CLng
Click to expand...
always round it to the nearest even number. For example,
0.5 rounds to 0, and 1.5 rounds to 2. CInt and CLng differ
from the Fix and Int functions, which truncate, rather
than round, the fractional part of a number. Also, Fix and
Int always return a value of the same type as is passed in.
This is still a bit querky on the 0.5 fraction. To always
round up from 0.5 try:
MyRound = int(MyConst + 0.5)
David
 
The problem is not with VBA, you need to read the number
in as a decimal number not an integer:

Function RoundFunc(Mark As Double) As Integer
RoundFunc = Round(Mark, 0)
End Function
 
Andrew,

Perhaps the standard for rounding is different in
different parts of the world? Back in school (here
in Sweden) I was tought to round to the mearest
EVEN number when the value is just in-between.

49,5 should be rounded to 50
50,5 should be rounded to 50
51,5 should be rounded to 52
52,5 should be rounded to 52
53,5 should be rounded to 54
....and so on.

VBA's ROUND function behaves this way, just the
way I want it to. VBA also works like this when
automatically converting from floting-point to integer
data types. See this example:

Sub Test()
Dim d As Double
Dim i As Integer

MsgBox Round(3.5,0) '4

MsgBox Round(234.5,0) '234

d = 49.5
i = d
MsgBox i '50

d = 52.5 '52
i = d
MsgBox i
End Sub


Excel's ROUND worksheet function always rounds
UP (or actually away from zero). You can use Excel's
rounding function in VBA, like this:

RoundFunc = Application.Round(Mark, 0)

Also, as Glenn told you, you should change the datatype
of the Mark parameter to Double.
 
When u use an integer as parameter for your function, you will loose
all numbers after the decimal point! The solution is to use a double
for the parameter.



Glenn
Click to expand...

A typo. I tried numerous different versions of the function, and posted the
wrong one! The problem still exists with:

Function RoundFuncDouble(Mark As Double) As Double
RoundFuncDouble = Round(Mark, 0)
End Function

A cell containing
=RoundFuncDouble(50.5)
still returns 50
 
James said:
The problem is not with VBA, you need to read the number
in as a decimal number not an integer:

Function RoundFunc(Mark As Double) As Integer
RoundFunc = Round(Mark, 0)
End Function
Click to expand...

Sorry, a typo. I tried numerous different versions of the function, and
posted the
wrong one! The problem still exists with the above

A cell containing
=RoundFunc(50.5)
still returns 50
 
Ture said:
Andrew,

Perhaps the standard for rounding is different in
different parts of the world? Back in school (here
in Sweden) I was tought to round to the mearest
EVEN number when the value is just in-between.

49,5 should be rounded to 50
50,5 should be rounded to 50
51,5 should be rounded to 52
52,5 should be rounded to 52
53,5 should be rounded to 54
...and so on.

VBA's ROUND function behaves this way, just the
way I want it to. VBA also works like this when
automatically converting from floting-point to integer
data types. See this example:

Sub Test()
Dim d As Double
Dim i As Integer

MsgBox Round(3.5,0) '4

MsgBox Round(234.5,0) '234

d = 49.5
i = d
MsgBox i '50

d = 52.5 '52
i = d
MsgBox i
End Sub


Excel's ROUND worksheet function always rounds
UP (or actually away from zero). You can use Excel's
rounding function in VBA, like this:

RoundFunc = Application.Round(Mark, 0)

Also, as Glenn told you, you should change the datatype
of the Mark parameter to Double.
Click to expand...
Ah!! This explains the problem. Many thanks.

I am aware of the alternative method of rounding (to the nearest even
number), although I'm not clear as to the reason for this. Nevertheless, my
application specifically requires the same functionality as the ROUND
worksheet function, so your above suggestion seems to be my solution.

What I find suprising is that the two functions behave differently, and that
Microsoft appear to make no mention of the difference in their help files.
Rather inconsistent and very confusing.

(P.S. My use of Integer parameter in the original posting was a typo. I
tried many solutions and copied the wrong one).
 
Rounding away 5 to produce an even number is the ASTM standard for
rounding. MS calls it "Bankers' rounding", though I have yet to see any
evidence that it is used in banking. I have also seen it called
"unbiased rounding" since it tends to equalize the number times you
round up vs. round down. It also corresponds to the IEEE standard for
rounding. Excel 2000 introduced a Round function, and it rounds in this
fashion.

Rounding away 5 to produce the next larger number is a simplified
version of the above rule, that most of us in the US were taught in
elementary school. It is a poor way to treat data, since it tends to
introduce rounding biases, but is specified by the USP (United States
Pharmacopoeia), the IRS (U.S. Internal Revenue Service), and by European
banking web sites for Euro conversions. This is the way that Excel's
worksheet ROUND function has always rounded.

Jerry
 
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q194983
PRB: Round Function different in VBA 6 and Excel Spreadsheet

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q196652
HOWTO: Implement Custom Rounding Procedures

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q225330
OFF2000: New Round Function in Visual Basic for Applications 6.0


http://support.microsoft.com/default.aspx?scid=kb;en-us;Q209996
ACC2000: How to Round a Number Up or Down by a Desired Increment


http://support.microsoft.com/default.aspx?scid=kb;en-us;Q279755
INFO: Visual Basic and Arithmetic Precision
 
If in a cell on a spreadsheet, I enter the following equation:
=ROUND(50.5, 0)
I obtain the result 51, which I would expect, since 0.5 should be rounded
up.

If I write the following VB funcion:

Function RoundFunc(Mark As Integer) As Integer
RoundFunc = Round(Mark, 0)
End Function

And then enter the following equation into a cell:
=RoundFunc(50.5)

I obtain the result 50. i.e. 0.5 is rounded down

Why the difference? Is there a function that I can use in my VB code that
will round 0.5 up rather than down?
Click to expand...

The difference is because the VBA Round function uses a different algorithms,
and 0.5 will be rounded up or down depending on whether the preceding number is
odd or even.

The equivalent VBA function, to round like the worksheet:

Application.Worksheetfunction.Round(n,digits)


--ron
 
Back
Top