VBA ROUND Function

What is Excel VBA Round Function?

The Excel VBA Round function is a built-in function that allows you to round a numeric value to a specified number of decimal places. It is commonly used in VBA programming within Excel to manipulate and format numeric data. The VBA Round function rounds a given number to a specified number of decimal places, as indicated by the user. This function rounds numbers to various levels of precision, such as two-digit decimals, three-digit decimals, or even zero decimals.

Let us look at an example. Here, we demonstrate how to use the VBA Round function to round a negative value to one decimal place. We initialize the “NegativeValue” variable with the value -7.9876. The VBA Round function is applied to round it to the nearest tenth (1 decimal place).

Once you run the above code, the result is displayed in a message box, showing that the rounded value of -7.9876 is -8.0.

Key Takeaways
1. The Excel VBA Round function is used to round numeric values in VBA code.
2. The VBA Round function allows you to dynamically specify the number of decimal places to round to, giving you greater control over the rounding process compared to Excel formulas.
3. Excel also has a ROUND function for use in worksheet cells, while VBA’s Round function is used in programming.
4. The VBA Round function can be seamlessly integrated into larger Excel macros, enabling you to perform a wide range of data manipulation and analysis tasks in combination with rounding operations.

Syntax

The syntax for the Round function in VBA is as follows:

Round(Expression, [NumDigitsAfterDecimal])

• Expression: The numeric value you want to round.
• [NumDigitsAfterDecimal] (optional): The number of decimal places to which you want to round the expression. If omitted, it rounds to the nearest whole number (integer).

How to Use Excel VBA Round Function?

Using the Round function in Excel VBA is straightforward. Follow these steps:

Step 1: In the Excel workbook, press ALT + F11 to open the Visual Basic for Applications (VBA) editor.

Step 2: Inside the VBA editor, go to Insert > Module to insert a new module where you can write your VBA code.

Step 3: Inside the newly created module, declare the variables you intend to use. Use the Dim statement to declare these variables. Typically, you’ll need a variable to store the numeric value you want to round and another variable to store the rounded result. For instance, it can be as shown below.

Step 4: Assign a numeric value to the variable that you want to round. This value will be the input to the VBA Round function.

Step 5: Use the VBA Round function within your VBA code. Write a line of code that applies the VBA Round function to the variable holding the numeric value you want to round. You can specify the number of decimal places to round to, as needed.

Step 6: Display the rounded result to view the outcome of the rounding operation. You can use a message box to incorporate the rounded value into further calculations or store it in another variable.

Step 7: Save your VBA project by clicking the disk icon in the VBA editor or pressing CTRL + S. Then, either press F5 or use the “Run” command in the VBA editor to execute your VBA code.

Examples

Example #1 – Rounding to 2 Decimal Places

In this example, we will see how to use the VBA Round function to round up the numeric value 15.6789 to two decimal places and display the result in a message box.

Step 1: In the new module, we start by creating a new subroutine (sub) named “RoundToTwoDecimalPlaces.”

Step 2: Here, we declare a variable named “ValueToRound” and specify its data type as “Double.” Double is a data type for floating-point numbers.

Step 3: Like the previous line, we declare another variable named “RoundedValue” with the data type “Double.” This variable will store the rounded result.

Step 4: In this line, we assign the value 15.6789 to the “ValueToRound” variable. It is the numeric value that we want to round to two decimal places.

Step 5: Here, we use the Round function to round the “ValueToRound” variable to two decimal places. The result of this rounding operation is then assigned to the “RoundedValue” variable.

Step 6: This line displays a message box (a pop-up dialog) with the text “Rounded Value: ” followed by the value stored in the “RoundedValue” variable. This allows you to see the rounded result.

Step 7: Save the macro and execute the code by clicking on the green triangle(Run). Once you execute the code, you will see a message box with the required result.

Here is the complete code:

Sub RoundToTwoDecimalPlaces()

Dim ValueToRound As Double

Dim RoundedValue As Double

ValueToRound = 15.6789

RoundedValue = Round(ValueToRound, 2) ‘ Rounds to 2 decimal places

MsgBox “Rounded Value: ” & RoundedValue

End Sub

Example #2 – Rounding to Nearest Integer

In this example, we will understand how a VBA Round function code takes the numeric value 19.456 and rounds it to the nearest integer. The rounded result is then displayed in a message box.

Step 1: In the new module, we first create a new subroutine named “RoundToNearestInteger.”

Step 2: Again, we declare a variable named “ValueToRound” with the data type “Double.”

Step 3: Similarly, we declare a “RoundedValue” variable with the data type “Double.”

Step 4: Now, we assign the value 19.456 to the “ValueToRound” variable. This is the numeric value we want to round to the nearest integer.

Step 5: Here, we use the Round function without specifying the number of decimal places, so it rounds “ValueToRound” to the nearest whole number (integer).

Step 6: Here, we use Msgbox VBA function that displays a message box with the text “Rounded Value: ” followed by the rounded result stored in the “RoundedValue” variable.

Step 7: Save the macro and click on Run. Once you execute the code, you will see a message box with the required result.

Here is the complete code:

Sub RoundToNearestInteger()

Dim ValueToRound As Double

Dim RoundedValue As Double

ValueToRound = 19.456

RoundedValue = Round(ValueToRound) ‘ Rounds to the nearest integer

MsgBox “Rounded Value: ” & RoundedValue

End Sub

Difference Between Excel and VBA ROUND Function

The VBA Round function serves the same purpose of rounding numeric values, but there is a key difference in their usage. Let us understand the differences:

• Excel ROUND Function: In Excel, you can use the “Round” function in worksheet cells to round values directly within your spreadsheets. It takes the same arguments as the VBA Round function but is used within cell formulas.
• VBA Round Function: The VBA Round function is used in Visual Basic for Applications, which is a programming environment within Excel. It allows you to perform more complex operations, manipulate data, and automate tasks using VBA code.

Important Things To Note

1. The VBA Round function works with numeric data types (e.g., Double, Integer). Ensure that the data type of your variable is compatible with rounding.
2. Rounding follows standard mathematical rules. Values with a decimal portion of 0.5 or greater round up, while values less than 0.5 round down.
3. Be mindful of precision when rounding. Rounding to many decimal places may result in inaccuracies due to floating-point representation.
4. Consider implementing error handling in your VBA code to handle potential errors if non-numeric values are encountered.
5. Rounding negative values may produce unexpected results.

1. How do you round to 2 decimal places in VBA?

To round a value to 2 decimal places in VBA, you can use the Round function with the desired number of decimal places specified as the second argument.

For example, RoundedValue = Round(ValueToRound, 2) will round the “ValueToRound” variable to 2 decimal places.

2. Why is the Round function in Excel VBA not working?

The Round function in Excel VBA may not work correctly due to several reasons:

1 – Ensure that the data types of the values you’re trying to round are compatible with the function.

2 – Check for any syntax errors in your VBA code, such as missing parentheses or incorrect arguments.

3 – Ensure that variables used in the VBA Round function are properly initialized with valid numeric values.

4 – Implement error handling in your VBA code to handle cases where non-numeric values are encountered during rounding.

3. How do you round a range of cells in VBA?

To round a range of cells in VBA, you can loop through the cells within the range and apply the VBA Round function to each cell’s value individually. This allows you to round each cell in the range according to your specified criteria.

4. How do you round up to the nearest 0.5 in Excel VBA?

To round up to the nearest 0.5 in Excel VBA, you can use the WorksheetFunction.Ceiling function.

This function rounds a number up to the nearest specified multiple. For example, RoundedValue = WorksheetFunction.Ceiling(ValueToRound, 0.5) will round “ValueToRound” to the nearest 0.5.

This has been a guide to VBA Round Function. Here we explain how to use Excel VBA Round function and syntax along with examples & downloadable excel template. You can learn more from the following articles –