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
Cette action est irreversible, confirmez la suppression du commentaire ?
Signaler le commentaire
Veuillez sélectionner un problème
Nudité
Violence
Harcèlement
Fraude
Vente illégale
Discours haineux
Terrorisme
Autre
Clément Marcotte
Pour passer par Proxad, tu es surement capable de poser ta question en français.
"Arno" a écrit dans le message de 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
Pour passer par Proxad, tu es surement capable de poser ta question en
français.
"Arno" <arno@azerty.uiop> a écrit dans le message de
news:404122ef$0$28460$636a15ce@news.free.fr...
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
Pour passer par Proxad, tu es surement capable de poser ta question en français.
"Arno" a écrit dans le message de 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
Et pis si t'avais été un Anglais, t'aurais pas mis
Et pis si t'avais été un Anglais, t'aurais pas mis
<arno@azerty.uiop>
Et pis si t'avais été un Anglais, t'aurais pas mis
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.
"Arno" a écrit dans le message de 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
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" <arno@azerty.uiop> a écrit dans le message de
news:404122ef$0$28460$636a15ce@news.free.fr...
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
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 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
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 <404122ef$0$28460$, 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
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 <404122ef$0$28460$636a15ce@news.free.fr>, arno@azerty.uiop
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
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 <404122ef$0$28460$, 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-
;-))) 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 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" a écrit dans le message de 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
;-)))
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" <clement.marcotte@sympatico.ca> a écrit dans le message
de news:%234RhvQo$DHA.552@TK2MSFTNGP11.phx.gbl...
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" <arno@azerty.uiop> a écrit dans le message de
news:404122ef$0$28460$636a15ce@news.free.fr...
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
;-))) 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 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" a écrit dans le message de 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
