Function cost(dur, rate, min, Flag, CapPer, CapVal)
If CapPer = 0 And ((rate / 60) * dur) <= min Then
cost = min * 1.1
ElseIf CapPer = 0 And ((rate / 60) * dur) min Then
cost = ((rate / 60) * dur) * 1.1
ElseIf CapPer 0 And dur < CapPer And ((rate / 60) * dur) <= min Then
cost = min * 1.1
ElseIf CapPer 0 And dur < CapPer And ((rate / 60) * dur) min And
((rate / 60) * dur) <= CapVal Then
cost = ((rate / 60) * dur) * 1.1
ElseIf CapPer 0 And dur < CapPer And ((rate / 60) * dur) CapVal Then
cost = CapVal * 1.1
ElseIf CapPer 0 And dur CapPer Then
cost = (((rate / 60) * (dur - CapPer)) + CapVal) * 1.1
Else
cost = 0
End If
End Function
Function costTyped(dur#, rate#, min#, Flag&, CapPer#, CapVal#) As Double
If CapPer = 0 And ((rate / 60) * dur) <= min Then
costTyped = min * 1.1
ElseIf CapPer = 0 And ((rate / 60) * dur) min Then
costTyped = ((rate / 60) * dur) * 1.1
ElseIf CapPer 0 And dur < CapPer And ((rate / 60) * dur) <= min Then
costTyped = min * 1.1
ElseIf CapPer 0 And dur < CapPer And ((rate / 60) * dur) min And
((rate / 60) * dur) <= CapVal Then
costTyped = ((rate / 60) * dur) * 1.1
ElseIf CapPer 0 And dur < CapPer And ((rate / 60) * dur) CapVal Then
costTyped = CapVal * 1.1
ElseIf CapPer 0 And dur CapPer Then
costTyped = (((rate / 60) * (dur - CapPer)) + CapVal) * 1.1
Else
costTyped = 0
End If
End Function
Function costX(dur#, rate#, min#, Flag, CapPer#, CapVal#) As Double
Dim dPrice#
dPrice = ((rate / 60) * dur)
If CapPer <= 0 Then
If dPrice <= min Then
costX = min * 1.1
Else
costX = dPrice * 1.1
End If
Else
If dur <= CapPer Then
If dPrice <= min Then
costX = min * 1.1
ElseIf dPrice <= CapVal Then
costX = CapVal * 1.1
Else
costX = dPrice * 1.1
End If
Else
costX = (dPrice - (rate / 60 * CapPer) + CapVal) * 1.1
End If
End If
End Function
Sub Test()
Dim dur#, rate#, min#, Flag, CapPer#, CapVal#
Dim t!(2), r#(2)
Dim n, m&
dur = 1.1
rate = 0.04
min = 0.8
CapPer = 1
CapVal = 5
m = 2 ^ 16
t(0) = Timer
For n = 1 To m
r(0) = cost(dur, rate, min, 0, CapPer, CapVal)
Next
t(0) = Timer - t(0)
t(1) = Timer
For n = 1 To m
r(1) = costTyped(dur, rate, min, 0, CapPer, CapVal)
Next
t(1) = Timer - t(1)
t(2) = Timer
For n = 1 To m
r(2) = costX(dur, rate, min, 0, CapPer, CapVal)
Next
t(2) = Timer - t(2)
End Sub
--
keepITcool
|
www.XLsupport.com | keepITcool chello nl | amsterdam
Bob Phillips wrote :
Can you post your testing/timing code, as I (surprisingly) don't get
speed improvements of anything like 10-15 times, in fact it is
repeatedly slower?
Thanks
Bob
"keepITcool" wrote in message
ft.com...
40 Megs per workbook is an awful lot and CAN/MUST be streamlined.
does it need to be recalculating formulas? or is it just to
calculate invoice lines, which when calculated can be converted to
Values?
How many different functions have you defined?
What does your workbook look like
# of sheets
# of rows
Generally speaking using VBA functions will NOT speed things up,
although with complex calculations it make things easier to read..
you could also use Named formulas or Array formulas to bring down
calculation times and size.
I'm fairly certain than many of us could bring this baby back
to size and speed (< 1 minute) ...
though most will not do it for free, as the formulas need to be
analysed and rewritten, which takes time.
A very simple tip might help your functions sepped up tremendously
TYPE your arguments as Long or DOUBLE iso as variant.
your function becomes 5 times faster when defined as:
Function cost(dur#, rate#, min#, Flag&, CapPer#, CapVal#) As Double
Just rewriting the VBA functions might help.
Following is far more efficient (10 to 15) than yours:
please be sure to check the if's and ands..
ADDING capval in the last statement brings same results as yours,
but it doesnt make business-sense)
Function costX(dur#, rate#, min#, Flag, CapPer#, CapVal#) As Double
Dim dPrice#
dPrice = ((rate / 60) * dur)
If CapPer <= 0 Then
If dPrice <= min Then
costX = min * 1.1
Else
costX = dPrice * 1.1
End If
Else
If dur <= CapPer Then
If dPrice <= min Then
costX = min * 1.1
ElseIf dPrice <= CapVal Then
costX = CapVal * 1.1
Else
costX = dPrice * 1.1
End If
Else
costX = (dPrice - (rate / 60 * CapPer) + CapVal) * 1.1
End If
End If
End Function
--
keepITcool
www.XLsupport.com | keepITcool chello nl | amsterdam
Simon wrote :
In our last episode Tim Williams wrote:
Simon,
Without seeing exactly what kind of calculations you're
performing and how your code is structured it's going to be
difficult to offer any suggestions.
Tim
Tim
apologies, it's always difficult to know just how much info to
provide...
hope this is sufficient
<quote
'old' formula looked like this (mind the wrap)...
=IF(OR($A4="",$L$1=0),"",IF(OR(LEFT(F4,6)="XXXXXX" ,E4="YYYY"),"",I
F((( ($L$1/6
0)*$K4)*1.1)<($L$2*1.1),($L$2*1.1),(($L$1/60)*$K4)*1.1)))
new formula looks like this...
=IF(OR(A7="",Rate=0),"",Cost($L7,Rate,Min,Flag,Cap Per,CapVal))
which calls this function ...
Function cost(dur, rate, min, Flag, CapPer, CapVal)
If CapPer = 0 And ((rate / 60) * dur) <= min Then
cost = min * 1.1
ElseIf CapPer = 0 And ((rate / 60) * dur) min Then
cost = ((rate / 60) * dur) * 1.1
ElseIf CapPer 0 And dur < CapPer And ((rate / 60) * dur) <= min
Then cost = min * 1.1
ElseIf CapPer 0 And dur < CapPer And ((rate / 60) * dur) min
And ((rate / 60) * dur) <= CapVal Then
cost = ((rate / 60) * dur) * 1.1
ElseIf CapPer 0 And dur < CapPer And ((rate / 60) * dur)
CapVal Then cost = CapVal * 1.1
ElseIf CapPer 0 And dur CapPer Then
cost = (((rate / 60) * (dur - CapPer)) + CapVal) * 1.1
Else
cost = 0
End If
End Function
</quote
the given example is in a sheet where this is calculated once per
row, but is indicative of the type of calculations we're doing
(another sheet calls this function twice per row, and a third
calls a similar function 10 times per row)
many thanks
S