round down without worksheet function

[MetLife]

Hi,

I wanted to round down/up in vba without using the worksheet function, as this is kind of slow. Is there a premade function someone has access too?

I'm kind of surprised excel never made one for vba.

for example in C++ I wrote:

double dblRoundUp(double x, int pp)
{
int i = 1;
double xx = 1;
for (i = 1; i <= pp; i++) //This is faster then using the power function
{ xx = 10 * xx; }
return int(x * xx + .9999999) / xx;
}

Thanks
Jeff

 

[MetLife]

You are not using my code from post # 6. You are using a For i = 1 to 1000 loop,
The speed improvement comes from bypassing using a loop.

This 1 line replaces the loop

 arr = Application.RoundUp(arr,  2)

Alex,

I can't use your code because the data which needs to be rounded isn't in the worksheet.

Your code does not work with random inputs, or from data read into the excel workbook.

 

[Alex Blakenburg]

I would need some sample data. It works on data held in array, it shouldn’t matter how it got there.

 

[MetLife]

It does matter how the data got there since it takes time to read the data.

Compare the arrays vs. the range. Then use applicaiton.roundup

 

[offthelip]

The two methods are both good ways of rounding, it depends on what is needed , if you want a whole array rounded to the same number of places then Alex method is great , however if you wanted to replicate this formula in VBA then the VBA function would be better:

=ROUNDUP(A2,-INT(LOG10(A2)))

 

[MetLife]

You are not using my code from post # 6. You are using a For i = 1 to 1000 loop,
The speed improvement comes from bypassing using a loop.

This 1 line replaces the loop

 arr = Application.RoundUp(arr,  2)

both functions use a loop, I used 10000 iterations to get the time up to several seconds

 

[MetLife]

both functions use a loop, I used 10000 iterations to get the time up to several seconds

OK I see, usually the function is used with single values but this is an improvement with arrays.

 

[Peter_SSs]

@MetLife
I have removed the 'Mark as solution' from post #15 as "both functions use a loop, I used 10000 iterations to get the time up to several seconds" is not a solution to your original thread question.
If one of the other posts in the thread is, then you can mark that post, otherwise, please do not mark a post that doesn't contain a solution.

 

[MetLife]

@MetLife
I have removed the 'Mark as solution' from post #15 as "both functions use a loop, I used 10000 iterations to get the time up to several seconds" is not a solution to your original thread question.
If one of the other posts in the thread is, then you can mark that post, otherwise, please do not mark a post that doesn't contain a solution.

The rounddown customized function is faster than the application.round

I think the code I posted proves this because they both go through the loop and are called the same number of times. The thing is that the application.round can look at an entire array and the rounddown function cannot. So application.round has some additional features.

I think Microsoft should focus on mathematical things like this rather than user experience things like the ribbon