Search Tools Links Login

Symmetric Arithmetic Rounding (Excel style rounding)


The Visual Basic functions CByte(), CInt(), CLng(), CCur() and Round() user a Banker's Rounding algorithm. For example, VBA.Round(0.15,1) = 0.2 **AND** VBA.Round(0.25,1) = 0.2. The following code uses Symmetric Arithmetic Rounding (similar to the Excel Worksheet Round function) where Round(0.15,1) = 0.2 and Round(0.25,1) = 0.3. Also, precision is enhanced by passing the 'Number' parameter as variant and using CDec within the routine. This helps circumvent floating point limitations. To see an excellent resource on different rounding procedures (the basis for this code) see Microsoft Article ID: Q196652.

Original Author: Brian Pierron

Code

Public Function Round(Number As Variant, _
           Optional NumDigitsAfterDecimal As Long) As Variant
  If Not IsNumeric(Number) Then
    Round = Number
  Else
    Round = Fix(CDec(Number * (10 ^ NumDigitsAfterDecimal)) + 0.5 * Sgn(Number)) / _
        (10 ^ NumDigitsAfterDecimal)
  End If
End Function

About this post

Posted: 2002-06-01
By: ArchiveBot
Viewed: 112 times

Categories

Visual Basic 6

Attachments

No attachments for this post


Loading Comments ...

Comments

No comments have been added for this post.

You must be logged in to make a comment.