Minimize the number of workers/test rigs, with a capacity constraint, using Excel VBA Solver loop I created a code that runs 60 optimizations through solver through 4 series of loops (each with 15 'iterations'). The code works great but it is taking FOREVER to run through (over an hour). Each optimization is a simple linear model (global solution is found), just changing which month I am looking at.

Problem setup: I am trying to minimize the number of workers/test rigs that will satisfy the intake with the constraint of being under capacity.

I am not sure how I can make it go any faster, but I cannot send this to other people and expect them to use it. Does anybody have any suggestions? I have time sequenced it and each iteration takes equally as long. There is no bottleneck.

Below is my code:

Sub Optimization()

Application.ScreenUpdating = False

'Unlocks workbook to allow updating
Call Unlock_Workbook

'Makes visible and selects the tab where the optimization problem is set up
Sheets("Optimization").Visible = True
Sheets("Optimization").Select

'Clear variable ranges that solver will change
Range("Worker_All[[1]:[15]]").Clear
Range("TestRig_All[[1]:[15]]").Clear
Range("Worker_787[[1]:[15]]").Clear
Range("TestRig_787[[1]:[15]]").Clear

'Install the Add-in for users who have no done this already
AddIns("Solver Add-in").Installed = True


'Optimized All Workers

For i = 1 To 15

'Cell address for objective
Min = Cells(3, 2 + i).Address
'Cell adress for variable
Variable = Range("Worker_All[" &amp; i &amp; "]").Address
'Cell address for constraint range
ConstraintRange = Range("IntakeHours_NonKeyWO[" &amp; i &amp; "]").Address
'Cell address for constrants
Constraint = Range("IntakeHours_NonKeyWOC[" &amp; i &amp; "]").Address

SolverReset

SolverOk SetCell:=Min, MaxMinVal:=2, ValueOf:=0, ByChange:=Variable, _
    Engine:=2, EngineDesc:="Simplex LP"
SolverAdd CellRef:=ConstraintRange, Relation:=1, FormulaText:=Constraint
SolverSolve True

Next i

'Optimized All Test Rigs

For i = 1 To 15

Min = Cells(4, 2 + i).Address
Variable = Range("TestRig_All[" &amp; i &amp; "]").Address
ConstraintRange = Range("IntakeHours_NonKeyMO[" &amp; i &amp; "]").Address
Constraint = Range("IntakeHours_NonKeyMOC[" &amp; i &amp; "]").Address

SolverReset

SolverOk SetCell:=Min, MaxMinVal:=2, ValueOf:=0, ByChange:=Variable, _
    Engine:=2, EngineDesc:="Simplex LP"
SolverAdd CellRef:=ConstraintRange, Relation:=1, FormulaText:=Constraint
SolverSolve True

Next i

'Optimized 787 Workers

For i = 1 To 15

Min = Cells(5, 2 + i).Address
Variable = Range("Worker_787[" &amp; i &amp; "]").Address
ConstraintRange = Range("IntakeHours_Key787WO[" &amp; i &amp; "]").Address
Constraint = Range("IntakeHours_Key787WOC[" &amp; i &amp; "]").Address

SolverReset

SolverOk SetCell:=Min, MaxMinVal:=2, ValueOf:=0, ByChange:=Variable, _
Engine:=2, EngineDesc:="Simplex LP"
SolverAdd CellRef:=ConstraintRange, Relation:=1, FormulaText:=Constraint
SolverSolve True

Next i

'Optimized 787 Test Rigs

For i = 1 To 15

Min = Cells(6, 2 + i).Address
Variable = Range("TestRig_787[" &amp; i &amp; "]").Address
ConstraintRange = Range("IntakeHours_Key787MO[" &amp; i &amp; "]").Address
Constraint = Range("IntakeHours_Key787MOC[" &amp; i &amp; "]").Address

SolverReset

SolverOk SetCell:=Min, MaxMinVal:=2, ValueOf:=0, ByChange:=Variable, _
    Engine:=2, EngineDesc:="Simplex LP"
SolverAdd CellRef:=ConstraintRange, Relation:=1, FormulaText:=Constraint
SolverSolve True

Next i

Sheets("Cell Summary").Select
Sheets("Optimization").Visible = False

Call Lock_Workbook

Application.ScreenUpdating = True

End Sub
Let's talk about simple things that can be fixed by tools and then let's talk about performance:



I ran your code through Rubberduck's Online Analyzer [1] and got a lot of results. Most of them were related to the missing definitons for SolverAdd, SolverReset, SolverSolve and SolverOk but there were some more results:


You're not using Option Explicit. This means you could mistype variable (and function/sub) names and only realize at runtime. This is especially dangerous with variables, since VBA just "on-the-fly" declares that mistyped variable as a Variant.
You're using implicit references to ActiveSheet and ActiveWorkbook in your calls to Range and Sheets
Sheets may contain elements you don't expect. Generally it's more correct to use WorkSheets instead
Optimization is "implicitly public". While this is probably exactly what you need, it's usually a better idea to be upfront about this and explicitly declare it as public.


Additionally the code inside the Sub is not indented by one level, which makes it hard to distinguish the Sub from surrounding procedures. Furthermore Rubberduck should've flagged the use of Call, which is obsolete.

Since we've cleared about everything that can be easily addressed by more or less intelligent tools, let's get into the meat of the code and start optimizing:



First things first, we stop working with ActiveSheet and Select, because they are slow and can result in really funny undesired behaviour:

Dim optimizationSheet As Worksheet
'still references ActiveWorkbook, but I don't know what your workbook is called :/
Set optimizationSheet = Worksheets("Optimization") 


Then we can make the clearing of "working ranges" a bit cleaner and faster:

With optimizationSheet
    .Range("Worker_All[[1]:[15]]").Clear
    .Range("TestRig_All[[1]:[15]]").Clear
    .Range("Worker_787[[1]:[15]]").Clear
    .Range("TestRig_787[[1]:[15]]").Clear
End With


This is minimally faster, because VBA knows it can reuse the optimizationSheet reference. In addition to that it doesn't access the ActiveSheet anymore, which is really good.

The interesting part of optimization comes now. Unfortunately you did not provide the code for your Solver, which is most probably the actual bottleneck of this, but even so we can get some minor speed out of this by declaring the variables we use with their proper types. 

The true improvement will lie within the fact that you're abusing the EXCEL-Sheet as a holder for your variables. Instead of doing that (which is degrading performance more than you might imagine) the Solver should be rewritten to perform it's task on Arrays instead of taking the Address of a Range on the sheet and then use the cells of the Worksheet.
A general rule is to avoid using the Worksheet whenever you can, because it's so mindbogglingly slow.

I strongly recommend that you post the code of the solver after refactoring it to not use the Worksheet as data storage for a proper review ;)


  [1] I'm involved in Rubberduck as Translator and occasional Dev