Hey, guys! I just made it back from an incredible Alaskan vacation, and I’m all refreshed and ready to give you more exciting VBA tutorials.
Endicott Arm, Alaska
The VBA Round function behaves differently than the Excel Worksheet ROUND function. This tutorial tells you everything you need to know about the differences between the two rounding functions.
It’s important to understand the differences BEFORE you begin a project so you won’t spend hours performing unnecessary troubleshooting.
VBA Round vs Excel ROUND
1st Difference: Required Arguments
The VBA Round function and the Excel ROUND function follow the same syntax, with a few subtle differences.
Excel: =ROUND(number, num_digits)
VBA: Round(Number, [NumDigitsAfterDecimal])
The Excel ROUND function requires two arguments. The first argument is the number you want to round and the second argument is a whole number greater than or equal to 0 representing how many digits after the decimal you want to round to. Both arguments are required.
The VBA Round function accepts the same two arguments, but the second argument is optional. If you omit the NumDigitsAfterDecimal argument, the Round function in VBA assumes you want to round to the nearest whole number. In other words, it defaults to 0 decimal digits.
2nd Difference: Rounding Methods
You’re probably familiar with the Excel ROUND function. It rounds up for decimals ending in a 5 or greater. For example,
Excel Rounding ROUND(1.5,0) equals 2 ROUND(1.4,0) equals 1 ROUND(2.5,0) equals 3 ROUND(2.4,0) equals 2 ROUND(2.25,1) equals 2.3 ROUND(2.24,1) equals 2.2
This is how most people were typically taught to round.
The VBA Round function is different. In VBA, the round function uses what is often referred to as Banker’s Rounding. Bankers rounding rounds decimals ending in a 5 to the nearest even number. Let’s take a look at some examples:
VBA Rounding Round(1.5) equals 2 Round(1.4) equals 1 Round(2.5) equals 2 Round(2.4) equals 2 Round(2.25,1) equals 2.2 Round(2.24,1) equals 2.2
Notice how each time the digit you wanted to round ended in a 5, the VBA round function rounded to the nearest even integer, regardless of whether that required rounding up or rounding down. Also notice how the second argument is optional when you want to round to the nearest whole number.
I want to show you a side by side example comparing the results of Excel rounding and VBA rounding to drive home the differences.
Here’s the macro I used to populate the VBA results in the image above:
Sub VBAroundDemo() For i = 2 To 5 Range("c" & i) = Round(Range("a" & i), 0) Next i For i = 6 To 9 Range("c" & i) = Round(Range("a" & i), 1) Next i End Sub
How to use Excel ROUND in VBA
The advantage of banker’s rounding is that it attempts to remove any bias in calculations performed on rounded numbers. Many people are unfamiliar with this way of rounding since it’s different from what they were taught and they assume that there must be an error in the VBA rounding algorithm. This isn’t true, but the perception can cause hours of frustration to user’s not expecting the behavior difference.
Don’t despair. You can still use the more traditional rounding algorithm in VBA by calling the Excel ROUND function with Application.WorksheetFunction. Here’s how you do it:
Sub ExcelROUNDdemo() Dim d As Double d = 2.5 d = Application.WorksheetFunction.Round(d, 0) 'Now returns a value of 3 End Sub
In the example above, the variable
d = Round(d, 0), it would return a value of 2.
I hope you learned something in this tutorial. Although there are more than one difference between the VBA Round function and the Excel ROUND function, the key takeaway is that the two functions use different rounding algorithms. Be aware of this to avoid surprises when you’re deep into your next project.
Reach out to me via my VBA Consulting page to get your own VBA questions answered.
Also, please stop by my Excel Add-ins page for some great products. This is my primary source of blog revenue and the money I receive from sales helps pay to keep this website online and accessible for all to learn. I haven’t been making many sales lately, so I’m relying on you to help me turn this around!
To share the skill you just learned with others, post this article on Google+, Twitter, and Facebook. Sharing on social media is how I’m able to reach and teach more people about the awesome power of VBA.
I want to thank all my readers who have already subscribed to my email list and I encourage you to go ahead and subscribe if you haven’t done so. You’ll love freebies and the fun VBA content I send your way!
About Ryan Wells
Ryan Wells is a Nuclear Engineer and professional VBA Developer. He is the lead developer of several VBA applications, including PDF VBA - a leading Excel Add-in for exporting Excel Objects, like charts and tables, to PDFs. Discover more of his popular Excel Add-ins, including Mouse To Macro and CF Shapes, at his dedicated Excel Add-ins page.Follow