|
|
|
|
|
|
Access
|
Rounding Functions (nearest decimal / nearest multiple)
|
|
Ease of Use
|
Easy
|
Version tested with
|
97, 2002, 2003
|
Submitted by:
|
Cosmos75
|
Description:
|
These two functions are for rounding numbers.
- vbaROUND() rounds a number to the nearest decimal spots you specify.
- vbaROUNDTO() rounds a number to the nearest multiple you specify.
Should be possible to use this function in other Office VBA code.
|
Discussion:
|
The ROUND() formula in Access VBA does not behave like the ROUND() you may be used to in an Excel worksheet. Try the following two in Access VBA; Round(2.5, 0) and Round(3.5, 0). You will find that the results are as follows; Round(2.5, 0) = 2 and Round(3.5, 0) = 4. The ROUND() function in VBA uses, Banker's rounding. That is, it rounds to the nearest even number. You can call the Round function from Excel using the following line in Access VBA -- [Application.WorksheetFunction.Round(Range(2.5), 0)]. But it is very simple to create and use a function in vba rather than to call Excel's ROUND function.
|
Code:
|
instructions for use
|
Option Compare Database
Option Explicit
Public Enum rOpt
rNearest
rUp
rDown
End Enum
Public Function vbaRound(dblValue As Double, intDecimals As Integer, _
Optional RoundingOption As rOpt = rNearest) As Double
Dim dblPlacesFactor As Double
Dim dlbRoundFactor As Double
If intDecimals < 0 Then
vbaRound = 0
Exit Function
End If
dblPlacesFactor = 10 ^ intDecimals
Select Case RoundingOption
Case rNearest
dlbRoundFactor = 0.5
Case rUp
dlbRoundFactor = 1
Case rDown
dlbRoundFactor = 0
End Select
vbaRound = Int(dblValue * dblPlacesFactor + dlbRoundFactor) / dblPlacesFactor
End Function
Public Function vbaRoundTO(dblValue As Double, dblRoundTo As Double, _
Optional RoundingOption As rOpt = rNearest) As Double
Dim dblRoundedMutliple As Double
Dim dblValueDiv As Double
Dim dblValueNew As Double
If dblRoundTo = 0 Then
vbaRoundTO = 0
Exit Function
End If
dblValueDiv = dblValue / dblRoundTo
Select Case RoundingOption
Case rNearest
dblRoundedMutliple = vbaRound(dblValueDiv, 0)
Case rUp
dblRoundedMutliple = vbaRound(dblValueDiv, 0, 1)
Case rDown
dblRoundedMutliple = vbaRound(dblValueDiv, 0, 2)
Case Else
End Select
dblValueNew = dblRoundedMutliple * dblRoundTo
vbaRoundTO = dblValueNew
End Function
|
How to use:
|
- Create a module
- Give the name the module (e.g. basVbaRound)
- Call the function in your VBA code
|
Test the code:
|
- vbaRound() has 3 arguments, the third of which is optional.
- The optional argument is for rounding UP or DOWN.
- vbaRound(value, decimals places to round to, optional argument)
-
- vbaRound(2.5, 0, 0) = 3 'Rounds to nearest
- vbaRound(2.5, 0, 1) = 3 'Rounds UP
- vbaRound(2.5, 0, 2) = 2 'Rounds DOWN
-
- You use it like you would the vbaROUND() function but instead of specifying the number of decimals to round to, you specify the number you want to round to.
-
- e.g.
- vbaRoundTo(2.45, 0.25, 0) = 2.5 'Round to nearest
- vbaRoundTo(2.45, 0.25, 1) = 2.5 'Round UP
- vbaRoundTo(2.45, 0.25, 2) = 2.25 'Round DOWN
|
Sample File:
|
vbaRounding97-2000.zip 42.16KB
|
Approved by mdmackillop
|
This entry has been viewed 179 times.
|
|