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

 

[offthelip]

this works in vba:

tt = 1.7
ts = Int(tt)
MsgBox ts

 

[MetLife]

But is there a roundup function?

 

[offthelip]

I have converted your C++ routine into vba and done tests to compare it with the worksheet function and the VBA version using 1000 random values A1 to A1000 done 1000 times, The worksheet fucntion ctook 937.5 millisecs and the VBa version of your funciton took 257.8125 millisecs more that 3 times as fast.

Sub test1()
inarr = Range("a1:a1000")
outarr = Range("B1:B1000")
tt = Timer
For j = 1 To 1000
 For i = 1 To 1000
   outarr(i, 1) = WorksheetFunction.RoundUp(inarr(i, 1), 4)
 Next i
Next j
ts = Timer
tm = (1000 * (ts - tt))
Range("D2") = tm
Range("B1:B1000") = outarr

End Sub

Sub test2()
inarr = Range("a1:a1000")
outarr = Range("B1:B1000")
tt = Timer
For j = 1 To 1000
 For i = 1 To 1000
   outarr(i, 1) = doublerndup(inarr(i, 1), 4)
 Next i
Next j
ts = Timer
tm = (1000 * (ts - tt))
Range("f2") = tm
Range("C1:c1000") = outarr

End Sub
Function doublerndup(x As Variant, pp As Integer)
Dim i As Integer
Dim xx As Double

i = 1
xx = 1
For i = 1 To pp     '  //This is faster then using the power function
xx = 10 * xx
Next i
doublerndup = Int(x * xx + 0.9999999) / xx

End Function

 

[BSALV]

roundup=int(your value+.9999)
your choice how many 9's (otherwise +1 - 1E-15)

without the function DoubleRndup, doing the job in the macro itself, it's still 2.5 times quicker

Sub test2()
inarr = Range("a1:a1000")
outarr = Range("B1:B1000")
tt = Timer
For j = 1 To 1000
 For i = 1 To 1000
   outarr(i, 1) = Int(inarr(i, 1) * 10000) / 10000
   'outarr(i, 1) = doublerndup(inarr(i, 1), 4)
 Next i
Next j
ts = Timer
tm = (1000 * (ts - tt))
Range("g2") = tm
Range("C1:c1000") = outarr

End Sub

 

[Alex Blakenburg]

I don't know if this only works in MS 365 or whether I am doing something wrong but this is much faster (around x10. more like 8ms).

Sub NoLoopRoundup()

    Dim rng As Range
    Dim arr As Variant
    Dim tt As Double, ts As Double, tm As Double
   
    tt = Timer
       
    Set rng = Range("A5").CurrentRegion   
    arr = rng.Value 

    arr = Application.RoundUp(arr, 2)
    Range("C1:C1000") = arr
   
    ts = Timer
    tm = (1000 * (ts - tt))
    Range("D2") = tm
   
End Sub

 

[MetLife]

Which is faster? The vba function or the application.worksheet function?

My experience has been a function coded within vba is faster. The question is why hasn't Microsoft added a roundup function to vba? Its not difficult.

They should also add matrix functions and the Mersenne Twister random number generator.

Thanks
Jeff

 

[Alex Blakenburg]

Which is faster? The vba function or the application.worksheet function?

My experience has been a function coded within vba is faster. The question is why hasn't Microsoft added a roundup function to vba? Its not difficult.

They should also add matrix functions and the Mersenne Twister random number generator.

Thanks
Jeff

Hello Jeff, I am a bit confused as to where you got to on this.

If you use a loop then using the VBA function is faster than using the worksheet function.
However you can apply the Worksheet function to the entire array without a loop and it is hands down the winner in a speed test. There is just no comparison, it is basically instant.

Did you not find that to be the case ?

 

[MetLife]

Hello Jeff, I am a bit confused as to where you got to on this.

If you use a loop then using the VBA function is faster than using the worksheet function.
However you can apply the Worksheet function to the entire array without a loop and it is hands down the winner in a speed test. There is just no comparison, it is basically instant.

Did you not find that to be the case ?

application.roundup is slower:

doublerndup = 9,918 ms

application.roundup = 163,365 ms

So the function is 16.5x times faster.

Sub TestRoundTime()
Dim x1(1 To 10000) As Double
Dim x2(1 To 10000) As Double
Dim x3(1 To 10000) As Double
Dim x4(1 To 10000) As Double
Dim y1(1 To 10000) As Double

For i = 1 To 10000
    x1(i) = Rnd
    x2(i) = Rnd * x1(i)
    x3(i) = Rnd * x2(i)
    x4(i) = Rnd * x3(i)
Next i

'1.) Round using function
tt = Timer
For j = 1 To 1000
For i = 1 To 10000
    y1(i) = doublerndup(x1(i), 3)
    y1(i) = doublerndup(x2(i), 3)
    y1(i) = doublerndup(x3(i), 3)
    y1(i) = doublerndup(x4(i), 3)
Next i
Next j
ts = Timer
tm = (1000 * (ts - tt))
Range("D2") = tm
MsgBox "done testing doublerndup!"


'2.) Round using application.function
tt = Timer
For j = 1 To 1000
For i = 1 To 10000
    y1(i) = Application.RoundUp(x1(i), 3)
    y1(i) = Application.RoundUp(x2(i), 3)
    y1(i) = Application.RoundUp(x3(i), 3)
    y1(i) = Application.RoundUp(x4(i), 3)
Next i
Next j
ts = Timer
tm = (1000 * (ts - tt))
Range("D4") = tm
MsgBox "done testing application worksheet function"

End Sub


Function doublerndup(x As Variant, pp As Integer)
Dim i As Integer
Dim xx As Double
i = 1
xx = 1
For i = 1 To pp     '  //This is faster then using the power function
xx = 10 * xx
Next i
doublerndup = Int(x * xx + 0.9999999) / xx
End Function

 

[Alex Blakenburg]

application.roundup is slower:

For i = 1 To 10000
y1(i) = Application.RoundUp(x1(i), 3) y1(i) = Application.RoundUp(x2(i), 3) y1(i) = Application.RoundUp(x3(i), 3) y1(i) = Application.RoundUp(x4(i), 3)
Next i

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)