Solver activates other worksheets - why?
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 |
Solver activates other worksheets - why?
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 * "Ken Dahlberg" wrote in message om... I'm going to try posting this again - it was late in the day the first time. (Ken Dahlberg) wrote in message . com... With Solver in Excel 2002: I have a solver model activated by an embedded CommandButton on the DeNorm worksheet as shown in code below. Note the two lines that set 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, which creates 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 control this behavior? 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 |
All times are GMT +1. The time now is 12:22 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com