How do you "Round" a value in Microsoft Excel VB?

J

jamie

Hi,

I have a number like 2.138 which I want to round to two decimal
places.

What is the command to do this?

Within a spreadsheet it's round(2.138,2) but this does not seem to
work in VBA.

I had read somewhere that all excel spreadsheet commands work within
VBA but this doesn't seem to be true. Can anyone elaborate?

Thanks guys.
 
T

Trevor Shuttleworth

Jamie

various options - in the Immediate Window:

?Application.WorksheetFunction.ROUND(2.138,2)
2.14
?WorksheetFunction.ROUND(2.138,2)
2.14
?Application.ROUND(2.138,2)
2.14
?ROUND(2.138,2)
2.14

Regards

Trevor
 
G

Greg Wilson

You need to qualify the worksheet function using either
the WorksheetFunction or Application properties:

Sub XXX()
Dim Num As Single
Num = 2.1234
MsgBox WorksheetFunction.Round(Num, 2)
MsgBox Application.Round(Num, 2)
End Sub
 
N

Nigel

Try putting the formula into the relevant cell with this VBA code

Range("A1").FormulaR1C1 = "=ROUND(2.138,2)"

If it is within the VBA code that you wish to convert the number by
assignment to a variable use

Dim myVar as Double
myVar = 2.138
myVar = Round(myVar, 2)

Cheers
Nigel
 
T

Thomas Ramel

Grüezi jamie

[email protected] schrieb am 21.05.2004
I have a number like 2.138 which I want to round to two decimal
places.

What is the command to do this?

Within a spreadsheet it's round(2.138,2) but this does not seem to
work in VBA.
Click to expand...

How about the 'mother' of all rounding-functions?

Int(2.138*100+0.5)/100

--
Regards

Thomas Ramel
- MVP for Microsoft-Excel -

[Win XP Pro SP-1 / xl2000 SP-3]
 
R

Ron Rosenfeld

Hi,

I have a number like 2.138 which I want to round to two decimal
places.

What is the command to do this?

Within a spreadsheet it's round(2.138,2) but this does not seem to
work in VBA.

I had read somewhere that all excel spreadsheet commands work within
VBA but this doesn't seem to be true. Can anyone elaborate?

Thanks guys.
Click to expand...

You've received a number of solutions. However, be aware that the newer
versions of VBA implement the ROUND function differently than the
worksheetfunction. The worksheet function ROUND does what I believe is called
algebraic rounding. It rounds up at 0.5 and rounds down below that. So
5.5-->6 and 12.5-->13

The VBA ROUND method does what has been called "banker's rounding". 0.5 will
round to the nearest even number. So 5.5-->6 but 12.5-->12


--ron
 
Top