Rounding in VBA

[srinivas]

Hi Team,

I am facing a problem with rounding a number using VBA in excel.

Let me explain with an example.

suppose take 2 numbers 23.2067 and 23.2234. Now I need to round these
2 numbers to 2 digits.

I am using application.round(number,2) to round up to 2 decimals. I am
getting 23.2 and 23.22.

But I want 2 digits after the decimal means for 23.2067 i want it to
be 23.20. Can it be possible in VBA?
when i am trying to do it by text(number,"0.00") its giving me an
error sub function not defined.

I have one alternative by using split function

temp = split(application.round(number,2),".")

and by calculating length of temp(1) I can know the number of digits
after decimal. If i have only one number after decimal I can
concatenate 0 with the number.

this is a long process and in my macro this case may come once in
10000 calculatons.

So can any body suggest an alternative way or easy way to handle this.


Regards,
Srinivas

 

[Vergel Adriano]

Srinvas,

try something like this.

Sub test()
With Range("A1")
.Value = 23.2067
.NumberFormat = "0.00"
End With
End Sub

 

[Mike H]

23.2067 rounded to 2 decimal places is 23.21 and not 23.20 as in your example
so your not really rounding the number if you really want 23.20 your
truncating it.

to round the numbers use x = Round(23.2067, 2)
to truncate them use x = Val(Left(23.2067, 5))

Mike

 

[Ron Rosenfeld]

Hi Team,

I am facing a problem with rounding a number using VBA in excel.

Let me explain with an example.

suppose take 2 numbers 23.2067 and 23.2234. Now I need to round these
2 numbers to 2 digits.

I am using application.round(number,2) to round up to 2 decimals. I am
getting 23.2 and 23.22.

But I want 2 digits after the decimal means for 23.2067 i want it to
be 23.20. Can it be possible in VBA?
when i am trying to do it by text(number,"0.00") its giving me an
error sub function not defined.

I have one alternative by using split function

temp = split(application.round(number,2),".")

and by calculating length of temp(1) I can know the number of digits
after decimal. If i have only one number after decimal I can
concatenate 0 with the number.

this is a long process and in my macro this case may come once in
10000 calculatons.

So can any body suggest an alternative way or easy way to handle this.


Regards,
Srinivas

The numeric value of 23.20 IS 23.2 and that's why you see it that way.

In VBA, to get the value to display formatted to two decimal places, you use
various methods:

?application.WorksheetFunction.Text(26.2,"#.00")
26.20

?format(26.2,"#.00")
26.20

Both of those methods return strings.

In a worksheet cell, of course, you can format the cell to show two decimal
places.

But the Value will still always be 26.2
--ron

 

[Jerry W. Lewis]

The VBA function Fix() is equivalent to the worksheet function ROUNDDOWN(,0),
so that Fix(number*100)/100 is equivalent to ROUNDDOWN(,2) without having to
know INT(LOG10(number)).

Also, From Excel 2000 on, VBA has a Round() function, although it does
differ slightly from the worksheet ROUND() function when the number to be
rounded is exactly half-way between two rounded numbers (such as .125 to be
rounded to 2 decimal places). In the case of such a tie, the worksheet
function always rounds up (.13), while the VBA function rounds up or down as
needed to produce an even rounded digit (.12).
http://en.wikipedia.org/wiki/Unbiased_rounding#Round-to-even_method

Jerry