# Thread: RoundUp function in Access (2003)

1. ## RoundUp function in Access (2003)

Is there a way to replicate the Excel RoundUp function?
I've tried referencing the Excel 11.00 Object Library in the VBA editor Recerences window and use the following code in the module for a report that uses the function

Private Function rates(varToTest As String, tape As Date)

Select Case varToTest
Case "*3"
rates = roundup(tape, 0) * 3
Case "*4"
rates = roundup(tape, 0) * 4
Case "*5"
rates = roundup(tape, 0) * 5
Case "*2"
rates = roundup(tape, 0) * 2 + 5
Case "

2. ## Re: RoundUp function in Access (2003)

In order to use Excel functions in Access, you'd have to create an Excel application object, so you'd be running an invisible instance of Excel just to perform some calculations. That's a lot of overhead.

You could copy this function into a module:

Public Function Roundup As Variant(Number As Variant, NumDigits As Integer) As Double
Roundup = -Sgn(Number) * Int(-Abs(Number) * 10 ^ NumDigits) * 10 ^ -NumDigits
End Function

If you only want to be able to round up to the nearest whole number, you can simplify it to

Function Roundup(Number As Variant) As Long
Roundup = -Sgn(Number) * Int(-Abs(Number))
End Function

Note: neither function will handle null values. If you need that, you can use (for example)

Function Roundup(Number As Variant) As Variant
On Error Resume Next
Roundup = -Sgn(Number) * Int(-Abs(Number))
End Function

3. ## Re: RoundUp function in Access (2003)

Great job Hans, thank you.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•