Roundup in VBA

[Jeff]

Hi,

I wanted to use the Roundup/RoundDown function in VBA. I wanted to avoid
using "Application.Roundup" because this is very slow.

Is there a function that does this? - or does anyone know how to write this?

Thanks for your help.

 

[Dave O]

VBA has a native Round function- it appears to use 4 and 5 as its
rounding decision points. Do you need to do something dramatically
different? If yes, and you don't want to use Application.Whatever, you
may need to use a Mid function to determine an amount to add to your
starting number in order to perform your custom work.

Dave O
Eschew obfuscation

 

[joeu2004]

I wanted to use the Roundup/RoundDown function in VBA.
I wanted to avoid using "Application.Roundup" because this
is very slow. Is there a function that does this? - or does
anyone know how to write this?

How flexible do you need it?

If you are simply trying to round up and down to an integral value,
use the Fix() function to round down, and use Fix(x+0.5) to round up.
Note: Ordinarily, I would expect to use Int(); but it does probably
does not do what you want with negative numbers (sigh).

If you want to round up or down to a predetermined number of decimal
places, the following might suffice: Fix(d*x)/d and Fix(d*x+0.5)/d,
where "d" is 10^(number of decimal places). Caveat: This might not
have the same result as the Excel RoundUp/Down functions due to
anomalies with floating point arithmetic. If that's an issue for you,
you will probably have to use Format and string functions. At that
point, I suspect that Application.RoundUp/Down is faster; it is
certainly easier.

 

[Jeff]

Thanks that is very helpful.

In most cases it is just 2 decimal places. I will look into fix and see
what can be done.