473,241 Members | 1,967 Online

# Problem rounding to the nearest 0.5

Hi everyone,

I have a code that I can use in Excel to round to the nearest 0.5, but I cannot get it to work in Access. When I try to execute it I get the following error message: "Compile error: user defined type not defined." Any idea what I need to do to change my code to work in Access?

Here's the code:
Expand|Select|Wrap|Line Numbers
1. 'Round to nearest 0.5
2. Public Function vbaRoundTO(dblValue As Double, dblRoundTo As Double, _
3.     Optional RoundingOption As rOpt = rNearest) As Double
4.     Dim dblRoundedMutliple As Double
5.     Dim dblValueDiv As Double
6.     Dim dblValueNew As Double
7.
8.      'Set default retrun value if dblRoundTo = 0
9.     If dblRoundTo = 0 Then
10.         vbaRoundTO = 0 'OR vbaRoundTO = dblValue
11.         Exit Function
12.     End If
13.
14.      'Find multiple of RoundToSmallest
15.     dblValueDiv = dblValue / dblRoundTo
16.
17.      'Option to RoundUP or RoundDOWN
18.     Select Case RoundingOption
19.     Case rNearest
20.          'Round multiple to nearest
21.          'DO NOT USE VBA Round() function.
22.          'VBA : Round(2.5,0) = 2, i.e. rounds >=0.5 to 0 not 1
23.         dblRoundedMutliple = vbaRound(dblValueDiv, 0)
24.     Case rUp
25.          'Round multiple UP
26.         dblRoundedMutliple = vbaRound(dblValueDiv, 0, 1)
27.     Case rDown
28.          'Round multiple DOWN
29.         dblRoundedMutliple = vbaRound(dblValueDiv, 0, 2)
30.     Case Else
31.     End Select
32.
33.      'Calculate new "rounded-to" value
34.     dblValueNew = dblRoundedMutliple * dblRoundTo
35.
36.      'Return value
37.     vbaRoundTO = dblValueNew
38.
39. End Function
40.
Thank you for helping!

Linda
Aug 23 '07 #1
1 5305
I figured out the problem. I had failed to copy over all the necessary code. In case anyone needs it. Here it is.

Code:
Expand|Select|Wrap|Line Numbers
1. 'Set variables
2. Public Enum rOpt
3.     rNearest
4.     rUp
5.     rDown
6. End Enum
7. 'Rounding function
8. Public Function vbaRound(dblValue As Double, intDecimals As Integer, _
9.     Optional RoundingOption As rOpt = rNearest) As Double
10.     Dim dblPlacesFactor As Double
11.     Dim dlbRoundFactor As Double
12.
13.     If intDecimals < 0 Then
14.         vbaRound = 0
15.         Exit Function
16.     End If
17.
18.     dblPlacesFactor = 10 ^ intDecimals
19.
20.     Select Case RoundingOption
21.     Case rNearest 'Round to Nearest
22.         dlbRoundFactor = 0.5
23.     Case rUp 'Round UP
24.         dlbRoundFactor = 1
25.     Case rDown 'Round DOWN
26.         dlbRoundFactor = 0
27.     End Select
28.
29.     vbaRound = Int(dblValue * dblPlacesFactor + dlbRoundFactor) / dblPlacesFactor
30. End Function
31. 'Round to nearest 0.5
32. Public Function vbaRoundTO(dblValue As Double, dblRoundTo As Double, _
33.     Optional RoundingOption As rOpt = rNearest) As Double
34.     Dim dblRoundedMutliple As Double
35.     Dim dblValueDiv As Double
36.     Dim dblValueNew As Double
37.
38.      'Set default retrun value if dblRoundTo = 0
39.     If dblRoundTo = 0 Then
40.         vbaRoundTO = 0 'OR vbaRoundTO = dblValue
41.         Exit Function
42.     End If
43.
44.      'Find multiple of RoundToSmallest
45.     dblValueDiv = dblValue / dblRoundTo
46.
47.      'Option to RoundUP or RoundDOWN
48.     Select Case RoundingOption
49.     Case rNearest
50.          'Round multiple to nearest
51.          'DO NOT USE VBA Round() function.
52.          'VBA : Round(2.5,0) = 2, i.e. rounds >=0.5 to 0 not 1
53.         dblRoundedMutliple = vbaRound(dblValueDiv, 0)
54.     Case rUp
55.          'Round multiple UP
56.         dblRoundedMutliple = vbaRound(dblValueDiv, 0, 1)
57.     Case rDown
58.          'Round multiple DOWN
59.         dblRoundedMutliple = vbaRound(dblValueDiv, 0, 2)
60.     Case Else
61.     End Select
62.
63.      'Calculate new "rounded-to" value
64.     dblValueNew = dblRoundedMutliple * dblRoundTo
65.
66.      'Return value
67.     vbaRoundTO = dblValueNew
68.
69. End Function
Aug 23 '07 #2