round down without worksheet function

MetLife

Active Member
Joined
Jul 2, 2012
Messages
283
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
 
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
VBA Code:
 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.
 
Upvote 0

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
I would need some sample data. It works on data held in array, it shouldn’t matter how it got there.
 
Upvote 0
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
 
Upvote 0
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:
Excel Formula:
=ROUNDUP(A2,-INT(LOG10(A2)))
 
Upvote 0
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
VBA Code:
 arr = Application.RoundUp(arr,  2)
both functions use a loop, I used 10000 iterations to get the time up to several seconds
 
Upvote 0
@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.
 
Upvote 0
@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
 
Upvote 0

Forum statistics

Threads
1,213,560
Messages
6,114,306
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top