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


"Simon" wrote in message
...
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

--
Instead of trying to build bigger and better weapons of mass destruction,
shouldn't we be trying to get better use out of the ones we've already
got....