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/Unbiase...to-even_method

Jerry

"Mike H" wrote:

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


"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