Rounding in VBA - Any ideas?

[Michiel via OfficeKB.com]

Hi all,

This rounding issue in VBA drive me crazy!
Many posts report that VBA uses the bankers rounding. But I need the standard
rounding.

So I tried to make my own Mathematical Rounding function. For some
mysterieous reasons it does not always work as expected.

Anyone who likes solving strange behaviour of VBA is kindly invited to see
why.

MathRound(101.68000000000001,5) works OK --> 101.68000
MathRound(102.63000000000001,5) does not work ---> 102.63001 (102.63000
expected)

The function:
Function MathRound(dblInput As Double, intDigits As Integer) As Double
'Rounds the input value (DblInput) to the number of digits specified in
intDigits
'The container values dblContainer and dblContner seem to be necessary
because
'without them the calculations are wrongly passed through.
Dim dblContainer As Double
Dim lngExpon As Long
Dim lngLong As Long
Dim dblContner As Double
dblContainer = dblInput + 5 * (10 ^ -(intDigits + 1)) 'Add a bit to make
sure truncation is done right.
lngExpon = 10 ^ intDigits 'calculate the power of ten to be multiplied
based on the number of digits
dblContner = Round(dblContainer * lngExpon, 0) 'PUT value in second
container
lngLong = Fix(dblContner) 'truncate value
MathRound = lngLong / lngExpon 'Devide back to the correct number of
digits
End Function

Thanks!

 

[Niek Otten]

Excel's precision is only 15 decimal digits. You're using 17.
Some information here:

http://www.cpearson.com/Excel/rounding.htm

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| Hi all,
|
| This rounding issue in VBA drive me crazy!
| Many posts report that VBA uses the bankers rounding. But I need the standard
| rounding.
|
| So I tried to make my own Mathematical Rounding function. For some
| mysterieous reasons it does not always work as expected.
|
| Anyone who likes solving strange behaviour of VBA is kindly invited to see
| why.
|
| MathRound(101.68000000000001,5) works OK --> 101.68000
| MathRound(102.63000000000001,5) does not work ---> 102.63001 (102.63000
| expected)
|
| The function:
| Function MathRound(dblInput As Double, intDigits As Integer) As Double
| 'Rounds the input value (DblInput) to the number of digits specified in
| intDigits
| 'The container values dblContainer and dblContner seem to be necessary
| because
| 'without them the calculations are wrongly passed through.
| Dim dblContainer As Double
| Dim lngExpon As Long
| Dim lngLong As Long
| Dim dblContner As Double
| dblContainer = dblInput + 5 * (10 ^ -(intDigits + 1)) 'Add a bit to make
| sure truncation is done right.
| lngExpon = 10 ^ intDigits 'calculate the power of ten to be multiplied
| based on the number of digits
| dblContner = Round(dblContainer * lngExpon, 0) 'PUT value in second
| container
| lngLong = Fix(dblContner) 'truncate value
| MathRound = lngLong / lngExpon 'Devide back to the correct number of
| digits
| End Function
|
| Thanks!
|
| --
| Message posted via OfficeKB.com
| http://www.officekb.com/Uwe/Forums.aspx/excel-functions/200809/1
|

 

[Ron Rosenfeld]

Hi all,

This rounding issue in VBA drive me crazy!
Many posts report that VBA uses the bankers rounding. But I need the standard
rounding.

So I tried to make my own Mathematical Rounding function. For some
mysterieous reasons it does not always work as expected.

Anyone who likes solving strange behaviour of VBA is kindly invited to see
why.

MathRound(101.68000000000001,5) works OK --> 101.68000
MathRound(102.63000000000001,5) does not work ---> 102.63001 (102.63000
expected)

To do rounding the same way that Excel does on the worksheet, use

Application.worksheetfunction.Round(arg1, arg2)


Note that the values you are using are specified with more precision than is
available to Excel. If you want to use high precision numbers in your routine,
you could input the values as a string, and then use the Decimal data type,
which allows about 28 digit precision; or you could download and install the
Xnumbers.xla add-in which allows up to 250 sigificant digits, and has an xround
function which will operate on long numbers.
--ron

 

[Michiel via OfficeKB.com]

Thank you both!

I do not wantt o use the worksheet function since I want the functio to be
universal, so I can use it un VBA word and in Access as well.

The idea of trunc-ing it via a string is an interesting one however! I'll
start playing around with that one.

THANKS!

 

[Jerry W. Lewis]

MS chose not to display more than 15 digits because digits beyond the 15th
may not be what you expect (because numbers are stored in binary, not
decimal). Consider dblContainer*lngExpon which you round to produce
dblContner. To 17 figures, the actual values of dblContainer*lngExpon for
your two cases are
10168000.500000000
10163000.500000002
the 1st number ends in exactly 0.5, so it rounds to the nearest even integer
(down). The 2nd number exceeds 0.5, so it rounds up.

VBA did what you told it to do, but that is different than what you wanted
it to do. If I were you, I would look at CDbl(CStr(dblInput*lngExpon)). The
CStr step removes figures beyond the 15th, so that fractional parts should
round up or down depending on whether they are > or < 0.5. Fractional values
of exactly 0.5 can round as you choose. You said that you wanted 5's up
rounding, but this approach will also give you more expected results in
"banker's rounding" than the VBA Round function, which does not buffer
against the vagaries of binary representations.

Jerry

 

[Michiel via OfficeKB.com]

Thanks again all for your help.

I used the approach with the string and created this function.
I think it works perfactly. IF someone thinks it does not then please tell me!


Function MathRound(dblInput As Double, Optional intDigits As Integer = 0) As
Double
'This function performs a mathematical rounding. Of the dblInput value.
'It will not round like the round function of VB.
'IF specified it will round towards the intDigits digits.
'EXample: MathRound(1.5) -> 2 And MathRound(2.5) -> 3
Dim strTemp As String
Dim dblTemp As Double
Dim lngExpon As Long
Dim lngPos As Long

Dim n As Long

lngExpon = 10 ^ intDigits 'The factor to multiply with in case more than 0
digits
dblTemp = (dblInput * lngExpon) + 0.5 'Correction factor to truncate the
number correctly
strTemp = CStr(dblTemp) 'Hold the value in string to truncate it

lngPos = InStr(1, strTemp, ".") 'find digit in string
If lngPos > 0 Then strTemp = Left(strTemp, lngPos - 1) 'Truncate
dblTemp = CDbl(strTemp) 'Return number back to double

MathRound = dblTemp / lngExpon 'Calculate back to required number of digits.

End Function