Turn off calculating multiple workbooks in a macro

[Peter Davison]

Hi,
I've got a macro that does a "Solver" calculation that updates many cells in 5 different workbooks.
I think the macro would run quicker if the workbooks were not updating all the time. (They are all open)
Is there a way of adding code to the macro so any updating of the 5 workbooks are completed at the end of the macro?
I've attached the Macro for information

Thanks for your assistance

 

[Zot]

I did not see anything attached.

 

[Peter Davison]

Private Sub CommandButton1_Click()



Dim i As Long

Set wsstart = Sheets("Start Data1")
Set wsanalyse = Sheets("Analyse1")
Set wsresult = Sheets("Result1")


'Clear cells and copy formula as a value to bring in the attribute 1 data
Worksheets("Start Data1").Range("E17").Select
Worksheets("Start Data1").Range("E17:AW500").Clear
Worksheets("Start Data1").Range("D15").Select
Selection.Copy
Worksheets("Start Data1").Range("E17:AW500").Select
ActiveSheet.Paste
Worksheets("Start Data1").Range("E17:AW500").Value = Worksheets("Start Data1").Range("E17:AW500").Value
Application.CutCopyMode = False


wsanalyse.Activate

'Do Clusters 10 - 2
For i = 10 To 2 Step -1

wsstart.Range("b2:b10").Copy wsanalyse.Range("D2")
wsstart.Range("c1:c" & i).Copy wsanalyse.Range("D1")

SolverReset

SolverOk SetCell:="$DG$5", MaxMinVal:=2, ValueOf:="0", ByChange:=("$D$1:$D$" & i), Engine:=3, EngineDesc:="Evolutionary"

SolverAdd CellRef:=("$D$1:$D$" & i), Relation:=1, FormulaText:="100"
SolverAdd CellRef:=("$D$1:$D$" & i), Relation:=4, FormulaText:="Integer"
SolverAdd CellRef:=("$D$1:$D$" & i), Relation:=3, FormulaText:="1"

SolverOptions MaxTime:=120, Iterations:=0, PopulationSize:=False, RandomSeed:=1, MutationRate:=0.075, _
RequireBounds:=True, MaxIntegerSols:=0

SolverSolve UserFinish:=True

wsresult.Range("EE3:Eq500").Offset(0, 13 * (10 - i)) = wsanalyse.Range("CV3:Dh500").Value
Next i

wsresult.Activate
wsresult.Range("IM1").Select

'ActiveWorkbook.Save

MsgBox "Complete"



End Sub

 

[Peter Davison]

Hi,
Just wondered if you were able to advise on the above yet?
Thanks

 

[Zot]

Hi,
Just wondered if you were able to advise on the above yet?
Thanks

I was hoping those who are familiar with Solver to reply. I have zero experience with Solver.

 

[Peter Davison]

Do you think it is as simple as - in the macro at the beginning activating each workbook in turn and switching off automatic updating and then at the end of the macro switching automatic updating back on?
If you think it might be what would be the code to do that?

Thanks

 

[Zot]

Do you think it is as simple as - in the macro at the beginning activating each workbook in turn and switching off automatic updating and then at the end of the macro switching automatic updating back on?
If you think it might be what would be the code to do that?

Thanks

That would be

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

<your program>

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

Let me know if it works but I think switching between workbooks will negate it.

 

[Peter Davison]

Yes you are correct it does not work switching off workbook updating.
Not to worry, I will have to think again.
Thanks for your help.

 

[Zot]

Yes you are correct it does not work switching off workbook updating.
Not to worry, I will have to think again.
Thanks for your help.

Maybe it is possible to use xlMinimized. Perhap this will speed up. Open other workbooks minimized. Try something like this:

Dim wb as Workbook
Dim wbName as Window

Set wb = ActiveWorkbook
Set wbName = Windows(<workbook name>)
or
Set wbName = Windows(wb.Name)

wb.ScreenUpdating=False
wbName.WindowState = xlMinimized

.... your code .....

wb.ScreenUpdating=True

I think no need to use wb.ScreenUpdating=False since it did not work before.

The wb.ScreenUpdating=False will work on current workbook that runs macro. Once switching control to another workbook, the setting get nullified, thus no longer take effect even when the macro return control to current workbook.

 

[Peter Davison]

Thank you for this. I am away on work over the next couple od days but when I return I will test it.
Thank you for all you help