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"),"",IF(((
($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