help with speeding this up...

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
--
Instead of building bigger and better weapons of mass destruction, we should
be trying to get better use out of the ones we've already got....

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,

"Simon" wrote:

> 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
> --
> Instead of building bigger and better weapons of mass destruction, we should
> be trying to get better use out of the ones we've already got....
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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
> --
> Instead of building bigger and better weapons of mass destruction,
> we should
> be trying to get better use out of the ones we've already got....

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

--
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....

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)


"Simon" <[email protected]> wrote in message
news:[email protected]...
> 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,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
>
> --
> 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....

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

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" <[email protected]> wrote in message
news:[email protected]...
> 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,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
>
> --
> 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....

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" <[email protected]> wrote in message
news:[email protected]...
>
> 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,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

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")
>
> End Sub

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" <[email protected]> wrote in message
> news:[email protected]...
> >
> > 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,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

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" <[email protected]> wrote in message
news:[email protected]...
> 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,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
>
> --
> 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....

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

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

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 :

> S
>
> --
> Instead of building bigger and better weapons of mass destruction,
> we should be trying to get better use out of the ones we've already
> got....

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

--
Don Guillett
SalesAid Software
[email protected]
"Simon" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>
> --
> Instead of building bigger and better weapons of mass destruction,
> we should be trying to get better use out of the ones we've already
got....

In our last episode keepITcool wrote:

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

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

In our last episode Don Guillett wrote:

> 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

In our last episode keepITcool wrote:

> 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

r u using a worksheet change event?