Solver activates other worksheets - why?

  • Thread starter Thread starter Ken Dahlberg
  • Start date Start date
K

Ken Dahlberg

Solver in Excel 2002: I have a solver model on the DeNorm worksheet as
shown in code below. Note the two lines setting a global variable
called BypassEvents. On the DeNorm, FitPlot and CapPlot worksheets,
in their Worksheet_Activate and Worksheet_Deactivate modules, I have
the beginning line

If BypassEvents then Exit Sub

Why do I need this? Because the SolverSolve routine activates and
de-activates each of these worksheets at least once each time it is
called, creating all kinds of havoc unless the event modules are
bypassed. The FitPlot worksheet has another solver model on it, but
the CapPlot worksheet doesn't. The cells in the DeNorm solver model
do not refer in any way to anything on those other worksheets.

What gives? Why is SolverSolve doing this, and is there a more direct
way to stop it?

Thanks,
Ken Dahlberg

Private Sub FitButton_Click()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
BypassEvents = True
SolverReset
Application.ScreenUpdating = False
SolverOK setCell:=Range("Q40"), _
MaxMinVal:=2, _
ByChange:=Range(ParmRange) ' ParmRange is a string defining
Application.ScreenUpdating = False ' Changing Cells for the model.
SolverSolve UserFinish:=False
BypassEvents = False
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub
 
Hi Ken,

The ability to use Solver from another application appears to be a
feature that was cobbled on as an afterthought, with no real consideration
given for how the calling application might be affected. Therefore, it tends
to do things like this. Rather than using a global flag variable in this
case, though, why not use Excel's built-in mechanism for disabling events:

Application.EnableEvents = False
''' Call Solver here.
Application.EnableEvents = True

Just make sure EnableEvents does get set back to True, because it's a
persistent setting, and all Excel events will be disabled while it's False,
even if your code is no longer running.

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *
 
Back
Top