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.

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.

Aspect	Excel ROUND Function	VBA Round Function
Application Context	Used in Excel worksheet cells	Used in VBA code within Excel
Syntax	=ROUND(number, num_digits)	ROUND(Expression, [NumDigitsAfterDecimal])
Flexibility	Limited to simple calculations	Allows complex data manipulation
Output	Typically used for display within cells	Used for intermediate or backend calculations
Integration	Integrated with Excel	Integrates with VBA scripts

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.

Frequently Asked Questions (FAQs)

Recommended Articles

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 –

• VBA Switch Function
• VBA With Statement
• VBA Index Match