Solver - Application.Calculation

[James_Latimer]

Excel 2016 / Win 10

Hello Excellers,

I appreciate that i am opening myself up for mockery here, however... is there a way to disable application.calculation when running solver?

The reason i ask is that i have a sheet in a workbook that is calculating optimum vials to use based on cost. All items required for Solver are on a single sheet so it isn't necessary for the entire workbook to calculate each time (i appreciate that the one sheet for solver does need to calculate). It is quite a large workbook so solver is taking far longer than it really needs to and i will be unable to have this sheet external to the existing workbook.

Feel free to hand out the abuse... this question probably deserves it.

Thanks.

 

[joeu2004]

If Solver is doing Application.Calculation, I don't believe there is any way to prevent it from doing it.

However, you can disable calculations on all other worksheets.

Add the following macros to a normal module (not a worksheet object):

Sub disableAll
For Each ws in Worksheets
If ws.Name <> Sheets("solverSheet").Name Then ws.EnableCalculation = False
Next
End Sub

Sub enableAll
For Each ws in Worksheets
If ws.Name <> Sheets("solverSheet").Name Then ws.EnableCalculation = True
Next
End Sub

Replace "solverSheet" with the actual worksheet name of the Solver references.

Then execute disableAll before using Solver, and execute enableAll after using Solver.

Caveat: When we exectute ws.EnableCalculation=True, the worksheet will be recalculated. Although the order of recalculation should not matter, it might cause additional recalculations of some worksheet due to cross-worksheet references. To avoid that, you might replace For Each ws In Worksheets so that you enable worksheet calculations in a more-favorable order. Only you can decide what that order might be. For example:

For Each s in Array("Sheet2","Sheet1","Sheet4","Sheet3")
Sheets(s).EnableCalculation = False
Next

 

[joeu2004]

Correction, too late to edit....

Caveat: [....] you might replace For Each ws In Worksheets so that you enable worksheet calculations in a more-favorable order
[....]
For Each s in Array("Sheet2","Sheet1","Sheet4","Sheet3")
Sheets(s).EnableCalculation = False
Next

Change False to True. Klunk!

 

[James_Latimer]

You legend!

Thanks mate, sincerely appreciated!