On 4 May 2007 04:42:17 -0700, srinivas wrote:

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