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