Solver macro slow when recalculate

Le
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
  • Partager ce contenu :
Vos réponses
Trier par : date / pertinence
Clément Marcotte
Le #359932
Pour passer par Proxad, tu es surement capable de poser ta question en
français.


"Arno" news:404122ef$0$28460$
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
Le #359919
Et pis si t'avais été un Anglais, t'aurais pas mis
Clément Marcotte
Le #359918
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" news:404122ef$0$28460$
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




Tushar Mehta
Le #361058
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





gee-dee-
Le #361332
;-)))
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" de news:%234RhvQo$
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" news:404122ef$0$28460$
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







Poster une réponse
Anonyme