I was solving a very simple model found in a forum: given a total cost in € (or $\$$, if you are reading from across the ocean) for a set of projects, and the hourly cost of 3 technician involved, find the integer number of hours each one should spend on it. The aim is minimizing the wasted time, that is, solve the following
$$
\min \left| TC - \sum_{i=1}^{3} c_{i} k_{i} \right| \; \; s.t. k_{i} \in \mathbb{N}
$$
The problem was to be solved for a number of rows, with different costs.
The first approach was using the native GRG Engine. Unfortunately, even setting an upper bound on $k_{i}$ (based on the ratio between max total cost and min tech cost, set at 300), solving time was awful: almost 10 seconds for row.
So, I decided to switch first to a linear model. I always had trouble converting an objective function with absolute values to a straight linear one, but it is actually very simple if you remember the definitions.
Given two variables $a$ and $b$, when $a \gt b$ we have that $ \left| a - b \right| = a-b$, otherwise we have $ \left| a - b \right| = b-a$. Using a dummy variable $v$, we must have $b-a \leq v \leq a-b$, and this means we can simply minimize $v$ subject to two additional constraints. Solving time was down to 3 seconds, then I decided I could also test OpenSolver.
Having never tried to program it, I was a little worried this may turn into a nightmare, but actually the changes were minimal. Most of the instructions were left untouched, and only resetting and solving commands needed to be updated.
Sub OS_solve_rows()
Dim i As Integer
Dim start_row As Integer, end_row As Integer
Dim max1 As Long, max2 As Long, max3 As Long
Dim rng1 As Range, rng2 As Range, rng3 As Range
Dim constr1 As Range, constr2 As Range
Dim constr3 As Range, constr4 As Range, constr5 As Range
Dim obj As Range, vars As Range
Dim ModelSheet As Worksheet
Set ModelSheet = ActiveWorkbook.Worksheets("Foglio1")
' Starting and ending row
start_row = 12
end_row = 16
For i = start_row To end_row
OpenSolver.ResetModel Sheet:=ModelSheet
' Range setting
Set rng1 = Range("D" & i)
Set rng2 = Range("E" & i)
Set rng3 = Range("F" & i)
Set vars = Range("D" & i & ":F" & i)
Set obj = Range("I" & i)
Set constr1 = Range("J" & i)
Set constr2 = Range("K" & i)
Set constr3 = Range("L" & i)
Set constr4 = Range("M" & i)
Set constr5 = Range("N" & i)
max1 = CDbl(WorksheetFunction.RoundUp(Range("B" & i).Value / Range("tec_cost_1"), 0))
max2 = CDbl(WorksheetFunction.RoundUp(Range("B" & i).Value / Range("tec_cost_2"), 0))
max3 = CDbl(WorksheetFunction.RoundUp(Range("B" & i).Value / Range("tec_cost_3"), 0))
' General call - Engine is Simplex LP.
' Must add .Address to store values as strings.
SolverOk SetCell:=obj.Address, MaxMinVal:=2, ByChange:=vars.Address, Engine:=2
' Constraints on max quantities
SolverAdd CellRef:=rng1.Address, Relation:=1, FormulaText:=CStr(max1)
SolverAdd CellRef:=rng2.Address, Relation:=1, FormulaText:=CStr(max2)
SolverAdd CellRef:=rng3.Address, Relation:=1, FormulaText:=CStr(max3)
' Constraints on objective cell to linearize absolute value
SolverAdd CellRef:=obj.Address, Relation:=3, FormulaText:=constr1.Address
SolverAdd CellRef:=obj.Address, Relation:=1, FormulaText:=constr2.Address
' (optional) Constraints on minimum percentage of hour worked by each technician
SolverAdd CellRef:=rng1.Address, Relation:=3, FormulaText:=constr3.Address
SolverAdd CellRef:=rng2.Address, Relation:=3, FormulaText:=constr4.Address
SolverAdd CellRef:=rng3.Address, Relation:=3, FormulaText:=constr5.Address
' Integer variables
SolverAdd CellRef:=rng1.Address, Relation:=4
SolverAdd CellRef:=rng2.Address, Relation:=4
SolverAdd CellRef:=rng3.Address, Relation:=4
' We want an exact solution, if available
SetToleranceAsPercentage (0)
RunOpenSolver False
Next i
End Sub
There isn't a real advantage in using OpenSolver on such a small problem, but it is useful to know that switching to it from an already built model is a smooth process.
File
No comments:
Post a Comment