http://support.microsoft.com/default...;en-us;Q194983
PRB: Round Function different in VBA 6 and Excel Spreadsheet
http://support.microsoft.com/default...;en-us;Q196652
HOWTO: Implement Custom Rounding Procedures
http://support.microsoft.com/default...;en-us;Q225330
OFF2000: New Round Function in Visual Basic for Applications 6.0
http://support.microsoft.com/default...;en-us;Q209996
ACC2000: How to Round a Number Up or Down by a Desired Increment
http://support.microsoft.com/default...;en-us;Q279755
INFO: Visual Basic and Arithmetic Precision
--
Regards,
Tom Ogilvy
"Andrew" wrote in message
...
Ture Magnusson wrote:
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.
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).