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

Et pis si t'avais été un Anglais, t'aurais pas mis

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.


"Arno" a écrit dans le message de
...
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

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

In article ,
says...
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

;-)))
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....
;-)))
"Clément Marcotte" a écrit dans le message
de ...
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.


"Arno" a écrit dans le message de
...
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