VBA is often used for calculations – when you create these calculations, you can end up with a number that has multitude of decimal places.  The Round function in VBA lets you round the number to a set amount of decimal points, or to remove the decimal points entirely.

The round function is made up of 2 parts – the number and the amount of decimal places you want after the number. The syntax for the round function has 2 arguments and is as follows:  Round(number, [number of decmals]) 

The number of decimals is optional – you do not need to include this argument.  If you leave out this argument, zero will be assumed and the Round function will round to the nearest integer.

If you divide 100 by 3 – you would get 33.33 recurring (33.3333333….etc).   The Round function would round DOWN the number to 33.33 if you set the amount of decimal places to 2, or would round down to 33 if you left out the amount of decimal places.

Function TestRound() As Double
 'create variables
	Dim dblOne As Double
	Dim dblTwo As Double
'populate the variables
	dblOne = 100
	dblTwo = 3
 'calculate the result with 2 decimal places
	TestRound = Round(dblOne/dblTwo,2)
End Function

With the round function, the answer is 33.33, whereas if you were to leave out the round function, the answer would be 33.3333333333333 (NOTE: Excel stops at 13 decimal places).

If you did not include the decimal places argument, then the answer would be 33.

Function TestRound() As Double
 'create variables
	Dim dblOne As Double
	Dim dblTwo As Double
'populate the variables
	dblOne = 100
	dblTwo = 3
 'calculate the result with no decimal places
	TestRound = Round(dblOne/dblTwo)
End Function

Using the Excel RoundUp and RoundDown Functions in VBA

The Round function in VBA limited in that it will always either round up or round down – depending on the number – so 19.58786 will round UP to 19.59 whereas 19.58246 will round down to 19.58 – the trick is to have a look at the number in 3^rd place after the decimal – if that number is 5 or greater, it will always round up, otherwise it will always round down.

Sometimes, you wish to force the code to round up or down – regardless of the number that is in the 3^rd decimal place.  If you wish to do this in VBA code, you actually have to use a built in Excel function to round up or down.   The Excel functions are found in the function wizard in Excel and are usually used within an Excel workbook – not in the actual VBA code.  However, they are all available to be used in VBA code if required:

As you can see in the graphic above, there are three rounding functions available to use in Excel, but only one of them is a function that is also available in VBA.  The other two functions have to be used from these Excel functions.

For example:

Function TestRoundUp() As Double
 'create variables
	Dim dblOne As Double
	Dim dblTwo As Double
'populate the variables
	dblOne = 100
	dblTwo = 3
 'calculate the result with 2 decimal places
	TestRoundUp = Application.WorksheetFunction.RoundUp (dblOne/dblTwo,2)
End Function

Note we have to reference the Worksheet function to use it.  The answer to the TestRoundUp function would be 33.34.  If we were to use the Application.WorksheetFunction.RoundDown function, then the answer would be 33.33.

Function TestRoundDown() As Double
 'create variables
	Dim dblOne As Double
	Dim dblTwo As Double
'populate the variables
	dblOne = 100
	dblTwo = 3
 'calculate the result with 2 decimal places
	TestRoundDown = Application.WorksheetFunction.RoundDown (dblOne/dblTwo,2)
End Function

There are a few other ways of using the RoundUp and RoundDown function in Excel or VBA – you can use negative numbers in the [number of decimals] section of the function.  The negative number that you use in the number of decimals section, indicates the amount of rounding you wish to do IN MULTIPLES OF 10.

For example:

Function TestRoundUpNeg() As Double
 'create variables
	Dim dblOne As Double
	Dim dblTwo As Double
'populate the variables
	dblOne = 100
	dblTwo = 3
 'calculate the result with 2 decimal places
	TestRoundUpNeg = Application.WorksheetFunction.RoundUp(dblOne/dblTwo,-1)
End Function

The answer to this above would be 40 – it is rounding up 33.3333 to the nearest 10 – which is 40.

Should you use -2, the answer would be 100 – as it would round UP the 33.333 to the nearest 100.

Similarly, if you use -3 – the answer would be 1000; and -4 10000

You can also use the RoundDown function to do the same thing

Function TestRoundDownNeg() As Double
 'create variables
	Dim dblOne As Double
	Dim dblTwo As Double
'populate the variables
	dblOne = 100
	dblTwo = 3
 'calculate the result with 2 decimal places
	TestRoundDownNeg = Application.WorksheetFunction.RoundDown(dblOne/dblTwo,-1)
End Function

The answer in the above example would be 30 as it is rounding DOWN the number to the nearest 10. 

However, if you were to use -2, the function would not find a nearest hundred going downwards, as there is not one, so the answer would be zero.  Similarly, -3, -4 etc would also return as result of zero.

The Banker’s “Idiosyncrasy”

A small peculiarity with the round function and one to take note of is called ‘bankers’ rounding and only occurs when you are rounding to 1 decimal place.

If you had the following function:

Function TestRound() As Double
'calculate the result with 1 decimal place
TestRound = Round(33.55, 1)
MsgBox TestRound
End Function  

The answer logically is 33.6 and that is the answer you would receive.

However, if you had this function below:

Function TestRound() As Double
'calculate the result with 1 decimal place
	TestRound = Round(33.65, 1)
	MsgBox TestRound
End Function  

You would expect the answer to be 33.7 as we would expect the round function to round up seeing as the second decimal place is a 5.   However, when we run this function, we receive the following answer

Which is the same answer we received in the first function where the number to round was 33.55.

This is because for some reason, VBA will round to the nearest EVEN number – and as 6 is even and 7 is not, it returns the answer of 33.6.

If we were to round 33.75 were would therefore get 33.8, and if we were to round 33.85, we would also get 33.8.

NOTE: this only occurs when you are rounding to 1 decimal place.

In conclusion, the Round function in VBA is extremely useful when you want to do calculations and display them with a set number of decimal places, or perhaps no decimal places at all. 

It has a number of different ways of rounding that you can use, and in Excel, you can use the RoundUp and RoundDown functions that are built into Excel.  Unfortunately, these are not available in the other applications that use VBA – you are restricted to using the Round function itself.