As evident by the numerous threads on this topic in the different
newsgroups, a lot of people have come across rounding errors in VBA when
working with floatingpoint numbers. The primary reason behind such
errors is that floatingpoint calculations with Single or Double data type
often can't be represented in binary (due to large range of numbers these data
types can operate on), leading to rounding inaccuracies.
The workaround is to work with scaled (Fixed point) numbers, that is,
Currency and Decimal data types. Here's a function that always works
(using "banker's rounding") by converting the value passed to
a Decimal data type.
Public Function Round( _
ByVal Number As Variant, NumDigits As Long, _
Optional UseBankersRounding As Boolean = False) As Double
Dim dblPower As Double
Dim varTemp As Variant
Dim intSgn As Integer
If Not IsNumeric(Number) Then
Err.Raise 5
End If
dblPower = 10 ^ NumDigits
intSgn = Sgn(Number)
Number = Abs(Number)
varTemp = CDec(Number) * dblPower + 0.5
If UseBankersRounding Then
If Int(varTemp) = varTemp Then
If varTemp Mod 2 = 1 Then
varTemp = varTemp  1
End If
End If
End If
Round = intSgn * Int(varTemp) / dblPower
End Function
