VBA Excel Access roundup rounddown Function

This Excel Access tutorial explains how to use VBA to write an Access roundup (round up) and Access rounddown (round down) Function to simulate that in Excel. These roundup and rounddown Functions can also be used in Access and Excel VBA.

Excel Access roundup rounddown Function

In Excel Worksheet Function, there are round, roundup, rounddown, mround Functions.

In Excel VBA, there is only round Function but no roundup, rounddown, mround Functions. The round Function is not the same as that in Excel Worksheet Function, the round logic that is being used is called Round-To-Even logic. However, you can still access Worksheet Functions in Excel VBA with WorksheetFunction Method as below.

VBA Code – Excel Access roundup Function

Public Function wRoundUp(pValue, digit) As Double
    ExpandedValue = Abs(pValue) * (10 ^ digit) 'Retrieve integer part of the number
    wRoundUp = Sgn(pValue) * Int(ExpandedValue + 0.99999999) / 10 ^ digit
End Function

Explanation

This Excel Access roundup Function performs two major actions. First, the target number is expanded to the decimal places which you want to round up, and then add 0.9999999 to the number, so that it goes up to the next integer. Finally use INT Function to extract the integer part. The sign of the number is ignored during round up and down to simulate the behavior in Excel, then it is added back after rounding.

For example, we have a number 14.56 and we want to round up to 1 decimal places. 14.56 is first expanded to 145.6, and then add 0.9999999, so that it becomes something like 146.6. Finally extract the integer part (146), divided by 100 to become 14.6.

VBA Code – Excel Access rounddown Function

Public Function wRoundDown(pValue, digit) As Double
    ExpandedValue = Abs(pValue) * (10 ^ digit) 'Retrieve integer part of the number
    wRoundDown = Sgn(pValue) * Int(ExpandedValue) / 10 ^ digit
End Function

Explanation

This Excel Access rounddown Function performs two major actions. First, the target number is expanded to the decimal places which you want to round up, then we use INT Function to extract the integer part. The sign of the number is ignored during round up and down to simulate the behavior in Excel, then it is added back after rounding.

For example, we have a number 14.56 and we want to round down to 1 decimal places. 14.56 is first expanded to 145.6, and then we extract only 145. Finally divided by 10 to become 14.5.

Example – roundup and rounddown