' Round value with this many digits.
Private Function RoundOff(ByVal value As Double, ByVal _
    digits As Integer) As Double
Dim shift As Double

    shift = 10 ^ digits
    RoundOff = CInt(value * shift) / shift
End Function

See also Tutorial: Using Floating Point Numbers.

Dangerous. Revise and explain that CInt coerces a value to 16bit signed. CLng is the better choice in most cases anyway, but you still have to ensure the input values stay within 2^31...

I recently did some research on rounding in VB, and it is a surprisingly complex subject! Your RoundOff routine will round by using the language's underlying rounding functions. So, for most versions of VB/VBA (but not all), it will use so-called "banker's rounding". This is an acceptable form of rounding in some financial scenarios, but it does not always provide the answer that you would expect.

The same problem applies to VB's "Round" function (added in VB6, I believe). In fact, these functions are dependent on the underlying OS'es rounding scheme, as implemented in oleaut32.dll. But, this can sometimes be a problem (e.g., in pre-SP1 versions of XP), as shown in the article The Format() Function Gives Different Results in Windows XP Than in Windows 2000

Microsoft has a good KB article documenting the different types of rounding in KB article HOWTO: Implement Custom Rounding Procedures.

To avoid reliance on the OS'es rounding scheme, and implement so-called Asymmetric Arithmetic Rounding, I created the following routine:

Public Function RoundIt(ByVal aNumberToRound As Double, _
  Optional ByVal aDecimalPlaces As Double = 0) As Double

On Error GoTo ErrHandler

Dim nFactor As Double
Dim nTemp As Double

    nFactor = 10 ^ aDecimalPlaces
    nTemp = (aNumberToRound * nFactor) + 0.5
    RoundIt = Int(CDec(nTemp)) / nFactor

'-----------EXIT POINT------------------
ExitPoint:

Exit Function

'-----------ERROR HANDLER---------------
ErrHandler:

    Select Case Err.Number
        Case Else
            ' Your error handling here
            RoundIt = 0
            Resume ExitPoint
    End Select
End Function

Here's how to implement "symmetric arithemtic" rounding:

Function SymArith(ByVal x As Double, _
  Optional ByVal DecimalPlaces As Double = 1) As Double

    SymArith = Fix(x * (10 ^ DecimalPlaces) + 0.5 * Sgn(x)) _
        / (10 ^ DecimalPlaces)
End Function

Which rounding method to use depends on your needs. I usually use "asymmetric" because it's how most folks round numbers in their heads. But, one of my apps use "symmetric" because it is emulating another software package that uses symmetric rounding.

To ensure that rounding is happening the way you expect it to (and won't change due to an OS service pack), I recommend always using your own rounding routines.

? Round(134.425,2)
 134.43
? Round (1.425,2)
 1.42

The first rounds 134.425 incorrectly towards an odd digit while the second correctly rounds 1.425 to an even digit.

Ben's requirement was to reverse-engineer an application that showed this behavior so this inconsistency was quite a problem. Eventually he found a Microsoft Knowledge Base article (How To Implement Custom Rounding Procedures) that seems to imply that VBA's Round function uses code similar to this:

Function BRound(ByVal X As Double, _
         Optional ByVal Factor As Double = 1) As Double
'  For smaller numbers:
'  BRound = CLng(X * Factor) / Factor
Dim Temp As Double, FixTemp As Double
  Temp = X * Factor
  FixTemp = Fix(Temp + 0.5 * Sgn(X))
  ' Handle rounding of .5 in a special manner
  If Temp - Int(Temp) = 0.5 Then
    If FixTemp / 2 <> Int(FixTemp / 2) Then ' Is Temp odd
      ' Reduce Magnitude by 1 to make even
      FixTemp = FixTemp - Sgn(X)
    End If
  End If
  BRound = FixTemp / Factor
End Function

It seems that the problem is caused by rounding errors. You cannot store all decimal numbers with a binary representation without some errors. You could use an infinite-precision rational representation (where you track numerators and demoninators) but that would be much less efficient.

It seems that this example is bumping up against binary representation errors. I suspect you could fiddle with the algorithm a bit to fix this particular problem but you'll probably have others for certain values.

You can get better results if you keep an eye on your precision. For example, instead of asking whether the final digit is exactly 5, ask whether it is very close to 5 (i.e. 0.05000000001 is close enough to 0.05).