Rounding numbers in Excel VBA

Keywords: 

The VBA Round() function rounds numbers. For example…

pi = 3.14159
Cells(3, 3) = Round(pi)

… will show 3 in the cell.

You can specify the number of decimal places. For example…

pi = 3.14159
Cells(3, 3) = Round(pi, 2)

… will show 3.14 in the cell.

Well, that’s how it should work. But it doesn’t always. When VBA puts a rounded number in a cell, sometimes it gets… er, unrounded.

How to fix this? One way is to convert a rounded number into a string before you put it into the cell. The Str() function does this. For example…

pi = 3.14159
Cells(3, 3) = Str(Round(pi, 2))

Use this trick if you’re expecting rounded numbers, but don’t always get them.