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

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.

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

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


wrote in message
...
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.

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

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

-----Original Message-----
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.
.

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

Try

v = Application.Round(2.138, 2

--
Message posted from http://www.ExcelForum.com

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

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

wrote in message
...
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.

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

Grüezi jamie

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.

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]

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

You guys are the best. Thanks a heap for the solutions

On Fri, 21 May 2004 05:21:59 GMT,
) wrote:

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.

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

On Fri, 21 May 2004 05:21:59 GMT, ) wrote:

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.

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