How to Round in MS Access, VBA

Question

Whats the best way to round in VBA Access?

My current method utilizes the Excel method

Excel.WorksheetFunction.Round(...

But I am looking for a means that does not rely on Excel.

Answer 1

Int and Fix are both useful rounding functions, which give you the integer part of a number.

Int always rounds down - Int(3.5) = 3, Int(-3.5) = -4

Fix always rounds towards zero - Fix(3.5) = 3, Fix(-3.5) = -3

There's also the coercion functions, in particular CInt and CLng, which try to coerce a number to an integer type or a long type (integers are between -32,768 and 32,767, longs are between-2,147,483,648 and 2,147,483,647). These will both round towards the nearest whole number, rounding away from zero from .5 - CInt(3.5) = 4, Cint(3.49) = 3, CInt(-3.5) = -4, etc.

Answer 2

VBA.Round(1.23342, 2) // will return 1.23

Answer 3

Be careful, the VBA Round function uses Banker's rounding, where it rounds .5 to an even number, like so:

Round (12.55, 1) would return 12.6 (rounds up) 
Round (12.65, 1) would return 12.6 (rounds down) 
Round (12.75, 1) would return 12.8 (rounds up)   

Whereas the Excel Worksheet Function Round, always rounds .5 up.

I've done some tests and it looks like .5 up rounding (symmetric rounding) is also used by cell formatting, and also for Column Width rounding (when using the General Number format). The 'Precision as displayed' flag doesn't appear to do any rounding itself, it just uses the rounded result of the cell format.

I tried to implement the SymArith function from Microsoft in VBA for my rounding, but found that Fix has an error when you try to give it a number like 58.55; the function giving a result of 58.5 instead of 58.6. I then finally discovered that you can use the Excel Worksheet Round function, like so:

Application.Round(58.55, 1)

This will allow you to do normal rounding in VBA, though it may not be as quick as some custom function. I realize that this has come full circle from the question, but wanted to include it for completeness.

Answer 4

If you're talking about rounding to an integer value (and not rounding to n decimal places), there's always the old school way:

return int(var + 0.5)

(You can make this work for n decimal places too, but it starts to get a bit messy)

Answer 5

1 place = INT(number x 10 + .5)/10
3 places = INT(number x 1000 + .5)/1000

and so on.You'll often find that apparently kludgy solutions like this are much faster than using Excel functions, because VBA seems to operate in a different memory space.

eg If A > B Then MaxAB = A Else MaxAB = B is about 40 x faster than using ExcelWorksheetFunction.Max

Answer 6

In Switzerland and in particulat in the insurance industry, we have to use several rounding rules, depending if it chash out, a benefit etc.

I currently use the function

Function roundit(value As Double, precision As Double) As Double
    roundit = Int(value / precision + 0.5) * precision
End Function

which seems to work fine

Answer 7

To expand a little on the accepted answer:

"The Round function performs round to even, which is different from round to larger."
--Microsoft

Format always rounds up.

  Debug.Print Round(19.955, 2)
  'Answer: 19.95

  Debug.Print Format(19.955, "#.00")
  'Answer: 19.96

ACC2000: Rounding Errors When You Use Floating-Point Numbers: http://support.microsoft.com/kb/210423

ACC2000: How to Round a Number Up or Down by a Desired Increment: http://support.microsoft.com/kb/209996

Round Function: http://msdn2.microsoft.com/en-us/library/se6f2zfx.aspx

How To Implement Custom Rounding Procedures: http://support.microsoft.com/kb/196652

Answer 8

Lance already mentioned the inherit rounding bug in VBA's implementation. So I need a real rounding function in a VB6 app. Here is one that I'm using. It is based on one I found on the web as is indicated in the comments.

' -----------------------------------------------------------------------------
' RoundPenny
'
' Description:
'    rounds currency amount to nearest penny
'
' Arguments:
'    strCurrency        - string representation of currency value
'
' Dependencies:
'
' Notes:
' based on RoundNear found here:
' http://advisor.com/doc/08884
'
' History:
' 04/14/2005 - WSR : created
'
Function RoundPenny(ByVal strCurrency As String) As Currency

         Dim mnyDollars    As Variant
         Dim decCents      As Variant
         Dim decRight      As Variant
         Dim lngDecPos     As Long

1        On Error GoTo RoundPenny_Error

         ' find decimal point
2        lngDecPos = InStr(1, strCurrency, ".")

         ' if there is a decimal point
3        If lngDecPos > 0 Then

            ' take everything before decimal as dollars
4           mnyDollars = CCur(Mid(strCurrency, 1, lngDecPos - 1))

            ' get amount after decimal point and multiply by 100 so cents is before decimal point
5           decRight = CDec(CDec(Mid(strCurrency, lngDecPos)) / 0.01)

            ' get cents by getting integer portion
6           decCents = Int(decRight)

            ' get leftover
7           decRight = CDec(decRight - decCents)

            ' if leftover is equal to or above round threshold
8           If decRight >= 0.5 Then

9              RoundPenny = mnyDollars + ((decCents + 1) * 0.01)

            ' if leftover is less than round threshold
10          Else

11             RoundPenny = mnyDollars + (decCents * 0.01)

12          End If

         ' if there is no decimal point
13       Else

            ' return it
14          RoundPenny = CCur(strCurrency)

15       End If

16       Exit Function

RoundPenny_Error:

17       Select Case Err.Number

            Case 6

18             Err.Raise vbObjectError + 334, c_strComponent & ".RoundPenny", "Number '" & strCurrency & "' is too big to represent as a currency value."

19          Case Else

20             DisplayError c_strComponent, "RoundPenny"

21       End Select

End Function
' -----------------------------------------------------------------------------

Answer 9

Here is easy way to always round up to next whole number in Access 2003:

BillWt: IIf([Weight]-Int([Weight])=0,[Weight],Int([Weight])+1)

For example: wt = 5.33,(Integer = 5), so 5.33 - 5 =.33 (<>0), so answer is int = 5 = 1 =6, etc. If wt = 6.000, int = 6 , so 6-6=0 --> answer =wt = 6.