Solver macro slow when recalculate

[Arno]

Hi all,

I'm using the solver inside a simple macro to calculate a few cells in a
worksheet. When executed alone, this macro is fast (let's say
instantaneous).
What I'd like to do is to call this macro automatically everytime the sheet
is recalculated. I've tried this:

Private Sub Worksheet_Calculate()
Application.Calculation = xlCalculationManual
Solver_macro
Application.Calculation = xlCalculationAutomatic
End Sub

But the macro in this case is extremely slow ! I can't figure out what's the
problem.
Would you have a hint ? I only have a very limited knowledge in vba and
macros ...
Thanks a lot !
Arno

 

[Clément Marcotte]

Et pis si t'avais été un Anglais, t'aurais pas mis
<[email protected]>

 

[Clément Marcotte]

Puisque the Solveur fait des iteratives calculs et que each calcul
restarte la Solveur, c'est just normal.

On ne can pas get le butter et le money du beurre.

 

[Tushar Mehta]

Solver is designed so that it forces XL to recalculate whenever Solver
has a need for it. That recalculation would re-trigger your
Worksheet_Calculate event procedure again! I'm surprised you even get
anything done at the end, since my expectation would be a stack
overflow or an error from Solver indicating it cannot find a solution.

You could try something like the code below. However, I would be
uncomfortable embedding an optimization inside a recalculation. Also
note that the EnableEvents setting persists after code execution ends.
So, it is your responsibilty to re-enable events irrespective of what
happens in your code, in Solver_macro, or within Solver.


Private Sub Worksheet_Calculate()
Application.Calculation = xlCalculationManual
On Error GoTo ErrXIT
Application.EnableEvents = False
Solver_macro
Application.Calculation = xlCalculationAutomatic
ErrXIT:
Application.EnableEvents = True
End Sub

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[gee-dee-]

;-)))
Doucement Clément !!!!
tu arroses trop large, comme Arno qui à cross-posté également sur MPEP,
tes collégues MVP US n'apprécieront peut etre pas le coup de grogne comme
sur MPFE.

aprés une signature récente de Tom Ogilvy, la réponse de Tushar Mehta,
m'avait fait croire un instant que les Gourous US faisaient excursions chez
les "fromages qui puent"

dommage....
;-)))