Problem rounding to the nearest 0.5

[lindabaldwin]

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:

'Round to nearest 0.5
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
 
     'Set default retrun value if dblRoundTo = 0
    If dblRoundTo = 0 Then
        vbaRoundTO = 0 'OR vbaRoundTO = dblValue
        Exit Function
    End If
 
     'Find multiple of RoundToSmallest
    dblValueDiv = dblValue / dblRoundTo
 
     'Option to RoundUP or RoundDOWN
    Select Case RoundingOption
    Case rNearest
         'Round multiple to nearest
         'DO NOT USE VBA Round() function.
         'VBA : Round(2.5,0) = 2, i.e. rounds >=0.5 to 0 not 1
        dblRoundedMutliple = vbaRound(dblValueDiv, 0)
    Case rUp
         'Round multiple UP
        dblRoundedMutliple = vbaRound(dblValueDiv, 0, 1)
    Case rDown
         'Round multiple DOWN
        dblRoundedMutliple = vbaRound(dblValueDiv, 0, 2)
    Case Else
    End Select
 
     'Calculate new "rounded-to" value
    dblValueNew = dblRoundedMutliple * dblRoundTo
 
     'Return value
    vbaRoundTO = dblValueNew
 
End Function
 

Thank you for helping!

Linda

[lindabaldwin]

I figured out the problem. I had failed to copy over all the necessary code. In case anyone needs it. Here it is.

Code:

'Set variables
Public Enum rOpt
    rNearest
    rUp
    rDown
End Enum
'Rounding function
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 'Round to Nearest
        dlbRoundFactor = 0.5
    Case rUp 'Round UP
        dlbRoundFactor = 1
    Case rDown 'Round DOWN
        dlbRoundFactor = 0
    End Select
 
    vbaRound = Int(dblValue * dblPlacesFactor + dlbRoundFactor) / dblPlacesFactor
End Function
'Round to nearest 0.5
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
 
     'Set default retrun value if dblRoundTo = 0
    If dblRoundTo = 0 Then
        vbaRoundTO = 0 'OR vbaRoundTO = dblValue
        Exit Function
    End If
 
     'Find multiple of RoundToSmallest
    dblValueDiv = dblValue / dblRoundTo
 
     'Option to RoundUP or RoundDOWN
    Select Case RoundingOption
    Case rNearest
         'Round multiple to nearest
         'DO NOT USE VBA Round() function.
         'VBA : Round(2.5,0) = 2, i.e. rounds >=0.5 to 0 not 1
        dblRoundedMutliple = vbaRound(dblValueDiv, 0)
    Case rUp
         'Round multiple UP
        dblRoundedMutliple = vbaRound(dblValueDiv, 0, 1)
    Case rDown
         'Round multiple DOWN
        dblRoundedMutliple = vbaRound(dblValueDiv, 0, 2)
    Case Else
    End Select
 
     'Calculate new "rounded-to" value
    dblValueNew = dblRoundedMutliple * dblRoundTo
 
     'Return value
    vbaRoundTO = dblValueNew
 
End Function