Looping using Solver and VBA

[Angus Spiteri]

Hi

I need your expert help to have VBA coded in such a way Solver looping through all the rows in table tblProducts of the attached "Forecast" sheet.

The solver parameters should be as follows:
a) Minimise each value in a calculated field within the tblProducts table
b) By Changing each value in column tblProducts[Alpha]
c) Constraints: Value in column tblProducts[Alpha] should have the following constraints : >= 0 and <=1

My current code is quite inefficient and take a considerable amount of time to solve the value for one product.

Finally, ideally I would like to have the criteria in field tblProducts[Alpha] change to column tblProducts[Beta] or tblProducts[Gamma] based on the value in G26 of the same sheet (similar to what I have done in the sample code below)

Sub Solver_HW()
'
' Solver_HW Macro
'

'
    Dim dblMeanAbsoluteDeviationHW As Double
    dblMeanAbsoluteDeviationHW = WorksheetFunction.Sum(Range("tblForecast[ABS Error - HW]"))

 
    If Range("varHWVariableSelect") = "Alpha" Then
    SolverOk SetCell:=dblMeanAbsoluteDeviationHW, MaxMinVal:=2, ValueOf:=0, ByChange:=Range("tblProducts[Alpha]"), _
        Engine:=1, EngineDesc:="Evolutionary"
    SolverAdd CellRef:=Range("tblProducts[Alpha]"), Relation:=1, FormulaText:="1"
    SolverAdd CellRef:=Range("tblProducts[Alpha]"), Relation:=3, FormulaText:="0"
    SolverOk SetCell:=dblMeanAbsoluteDeviationHW, MaxMinVal:=2, ValueOf:=0, ByChange:=Range("tblProducts[Alpha]"), _
        Engine:=1, EngineDesc:="Evolutionary"
    SolverOk SetCell:=dblMeanAbsoluteDeviationHW, MaxMinVal:=2, ValueOf:=0, ByChange:=Range("tblProducts[Alpha]"), _
        Engine:=1, EngineDesc:="Evolutionary"
    SolverSolve
    End If
 

    If Range("varHWVariableSelect") = "Beta" Then
    SolverOk SetCell:=dblMeanAbsoluteDeviationHW, MaxMinVal:=2, ValueOf:=0, ByChange:=Range("tblProducts[Beta]"), _
        Engine:=1, EngineDesc:="Evolutionary"
    SolverAdd CellRef:=Range("tblProducts[Beta]"), Relation:=1, FormulaText:="1"
    SolverAdd CellRef:=Range("tblProducts[Beta]"), Relation:=3, FormulaText:="0"
    SolverOk SetCell:=dblMeanAbsoluteDeviationHW, MaxMinVal:=2, ValueOf:=0, ByChange:=Range("tblProducts[Beta]"), _
        Engine:=1, EngineDesc:="Evolutionary"
    SolverOk SetCell:=dblMeanAbsoluteDeviationHW, MaxMinVal:=2, ValueOf:=0, ByChange:=Range("tblProducts[Beta]"), _
        Engine:=1, EngineDesc:="Evolutionary"
    SolverSolve
    End If
 
    If Range("varHWVariableSelect") = "Gamma" Then
    SolverOk SetCell:=dblMeanAbsoluteDeviationHW, MaxMinVal:=2, ValueOf:=0, ByChange:=Range("tblProducts[Gamma]"), _
        Engine:=1, EngineDesc:="Evolutionary"
    SolverAdd CellRef:=Range("tblProducts[Gamma]"), Relation:=1, FormulaText:="1"
    SolverAdd CellRef:=Range("tblProducts[Gamma]"), Relation:=3, FormulaText:="0"
    SolverOk SetCell:=dblMeanAbsoluteDeviationHW, MaxMinVal:=2, ValueOf:=0, ByChange:=Range("tblProducts[Gamma]"), _
        Engine:=1, EngineDesc:="Evolutionary"
    SolverOk SetCell:=dblMeanAbsoluteDeviationHW, MaxMinVal:=2, ValueOf:=0, ByChange:=Range("tblProducts[Gamma]"), _
        Engine:=1, EngineDesc:="Evolutionary"
    SolverSolve
    End If
 
End Sub

Thank you in advance for your help.

 

[Nebu]

Hi:

There is no workbook attached.

Thanks

 

[Angus Spiteri]

Unfortunately it is 2.5mb and it exceeds the file size limit

 

[Nebu]

Hi:

Try saving your file as Xlsb extension, then you will be able to upload the file I guess.

Thanks

 

[Angus Spiteri]

Still did not manage to reduce the size below 1 MB. Instead, I'm sending a link to the file > https://onedrive.live.com/redir?res...831&authkey=!AJRCwbGORxIZIRg&ithint=file,xlsm

Thank you in advance

 

[Nebu]

Hi:

I guess your solver itself is slow not the macro.You have to clearly define your objective function and constraints before setting up the model. Here is a link which explains you about Evolutionary solver that you are trying to setup, http://www.solver.com/excel-solver-evolutionary-solving-method-stopping-conditions.
once you can fix your solver by doing it manually the macro will just work fine. I am hoping that you are well versed with solver and know exactly what you are doing. For me I will set up my model only after defining my objective and constraints clearly. Hope this will help you in the right direction.

Thanks