help with speeding this up...

[Simon]

this is long, and I apologise if my meaning isn't immediately clear

I have a template used by our group to do bulk calculations - this is an
ongoing project/learning tool for me and so far it's been quite
successful (on both counts), but...

Previous incarnations used a series of (nested) IF functions (up to about
10 separate calculations per row), but over time these were becoming
increasingly complex and unwieldy. Another disadvantage was that the
resulting filesize was considerably larger (40-60M workbooks are common).

In an attempt to reduce a) bloat, b) complexity, c) duplication I have
converted most of the formula's to VBA functions - while this has effectively
reduced the number of formula's used (instead of using the same formula with
different variables each cell in each row, each cell now calls the vba
function and passes the relevant var) it has introduced a substantial deficit
in speed (sheets routinely have 50K+ records and recalculation now takes 5-10
minutes)

what I'm looking for now (finally, I hear you say) is some way to speed
things up - this is a tool that is used daily by about 20 users and that sort
of response time is way too long (if this is not possible I'll be forced to
go back to doing things to 'old' way...)

any/all ideas gratefully received

many thanks (in advance)

S

 

[Guest]

Hi,

Really don't know exactly what you are doing, but another constuct often
used where an If is used is the Case. I don't knw if this will help or not?

Thanks,

 

[Tim Williams]

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

 

[Simon]

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,CapPer,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

 

[Bob Phillips]

I think we will only be able to offer generalisations, as the real thing is
too big and too complex to discuss here, but a couple of ways is to turn
screenupdating and automatic calculation off

Application.ScreenUpdating = False
Application.Calculation= xlCalculationManual

and reset at the end


Application.Calculation= xlCalculationAutomatic
Application.ScreenUpdating = True

Other than that, it is a case of identifying the bottlenecks and looking at
re-designing. You may be best to employ a professional to help you.

RP
(remove nothere from the email address if mailing direct)

 

[keepITcool]

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 :

 

[Charles Williams]

Simon,

To speed up and slim down your formulae you need to remove all the duplicate
stuff and put it in a single place somewhere else: for example $L$1/60*1.1
"XXXXXX" and "YYYY"

If you have a lot (several 1000) of occurrences of your function you can
speed up calculation a lot if you make sure that all the windows in the VBE
are closed, that the VBE itself is closed, and trap F9 so that it does the
calculation with an Application.Calculate (assuming you are in manual mode).
You can probably also speed up the execution of the function itself by using
VB6, but it does not look very slow: I think its just the overhead of
calling a VBA function which is slowing you down. For ultimate speed rewrite
the function in C.

see http://www.decisionModels.com/calcsecretsj.htm and the rest of the site
for further ideas.

regards
Charles
______________________
Decision Models
FastExcel 2.1 now available
www.DecisionModels.com

 

[Bob Phillips]

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]

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 :

 

[keepITcool]

i normally put a put a stop or breakpoint at the end
and check the locals window.

or a msgbox ..


--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


keepITcool wrote :

Next
t(2) = Timer - t(2)

msgbox format(t(0),"0.000") & vblf _
format(t(1),"0.000") & vblf _
format(t(2),"0.000")

 

[Patrick Molloy]

your code calculates the same thing over and over. do it once and it will
imprive performance. DIM variables too. if there are several tests, then
nest them...


Function cost(dur As Double, rate As Double, min As Double, Flag As Double,
CapPer As Double, CapVal As Double) As Double
Dim pay As Double
pay = (rate / 60) * dur
cost = 0 ' default answer

If CapPer = 0 Then
If (pay) <= min Then
cost = min * 1.1
Else
cost = (pay) * 1.1
End If
ElseIf CapPer > 0 Then
If dur < CapPer Then
If (pay) <= min Then
cost = min * 1.1
Else
If ((rate / 60) * dur) <= CapVal Then
cost = (pay) * 1.1
Else
cost = CapVal * 1.1
End If
End If
Else
cost = ((pay - (rate / 60) * CapPer) + CapVal) * 1.1
End If
End If

End Function

 

[Simon]

thanks to all for the advice (I'll have to work my way through it though...)

FYI, users have already been advised to set autocalculate off, and converting
to VB is not really an option at this time (I'm teaching myself as I go) - C
is even less likely...

Once again, many thanks!!

S

 

[keepITcool]

I'm puzzled by our "quotation":

imo the world should fear 'any' use of WMD. I honestly can't think of
'better' use. All I know is that if we have better use then they will
too, whoever we and they may be.

--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Simon wrote :

 

[Don Guillett]

It might help us if you posted your code for comments in speeding "this" up.

--
Don Guillett
SalesAid Software
(e-mail address removed)

thanks to all for the advice (I'll have to work my way through it though...)

FYI, users have already been advised to set autocalculate off, and converting
to VB is not really an option at this time (I'm teaching myself as I go) - C
is even less likely...

Once again, many thanks!!

S

got....

 

[Simon]

I'm puzzled by our "quotation":

imo the world should fear 'any' use of WMD. I honestly can't think of
'better' use. All I know is that if we have better use then they will
too, whoever we and they may be.

forget where I found this, but it just appealed to me (I liked the obvious
sarcasm)

Like you I can't think of a good use for these things, but think we should be
making an effort to come up with something (other than the original intended
use, of course). Likewise, no use building more of the things if you're not
using the ones you've already got...

S

 

[Simon]

It might help us if you posted your code for comments in speeding "this"
up.

Don

not sure what you're looking for here (example code was posted at the top of
the thread...)

S

 

[Simon]

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

I appreciate that it's big (hence my attempt to reduce the number of
formulas in the sheets by using functions) but that's a function of the
volume of data more than anything - FYI we are calculating costs and
comparing them with the invoiced charge. Average number of records per
sheet varies per cost type, but it's safe to say that the bigger sheets
(not books) would be around 40K records each (more is not uncommon).

the object of the excercise is to confirm the accuracy of the charges and
to provide some visibility of the calculation process

not sure what you mean by recalculating formulas - the formulas are
consistent within the context they are called - and the expected outcome
will always be a value

there are at present 3 sheets referencing functions (note that there are a
number of conditional formula's to satisfied before the functions are
called, so while there are 10 opportunities to call the cost function per
row in sheet 3, only 1 or 2 will pass the conditional statements and call
the function) -

1st sheet does 1 function call per row - calls the cost function (see
earlier post for details) - there are other calculations, but they are
done by formula's at the moment

2nd sheet does 1 function call per row - calls the cost function for 1 of 2
opportunities

3rd sheet does a maximum of 3 function calls per row - calls a function
similar to cost (but much simpler - for any given record there will be up
to 2 results from 5 opportunities) and another once (more complex -
calculates the variance between the calculated and invoiced amounts -
calculated amount would be the output from the aforementioned cost function)

at present there are 3 'main' functions - these replaced the use of the
equivalent formulas in each cell to be calculated, ie 11 times per row for
sheet 3 by XXXX rows...

hope this helps (it would probably be a lot easier to see the spreadsheet
than to try and work it out from my descriptions)

once again, many thanks for you're assistance (still working thru the
previous posts...)

S