Most efficient way to loop a solve statement (solving many small optimization problems)

[Dam0209]

Hi everyone!

I am writing a macro in which I need to solve a small linear optimization problem with binary variables many times. Each row of the spreadsheet corresponds to one hour of the year, for each hour (row) a very small (7 variables) MIP problem has to be solved. Once the problem has been solved for one row, the problem in the next row takes some values of the solution of the problem in the row above it, but each time the solver is called (one for each row) it only uses data from that particular row. Ideally, I would need to solve the spreadsheet for every hour of the year (8760 solves), but when I try a loop for 30 days (720 solves) it already takes a long time to solve.

My question then is, could anyone give me a hint on what would be the most efficient way to loop the solve statement so that it takes the least possible amount of time? The problem for each row is solved inmediately but I find that with the code I have now, solving 100 problems does not take 100 times the time it takes to solve one problem, but much longer, it probably has something to do with memory management and computational science stuff I'm no expert about. I think there must be a way in which solving time depends linearly on the amount of problems solved,but I haven't found it. Any ideas?

Here is the code I'm using:

Sub SolverEachHour()
    'State variables
    Dim cellchange As Range 'changing cells
    Dim cellGoal As Range ' objective value cell
    Dim cellendtime As Range
    Dim cellvb1 As Range
    Dim cellvb2 As Range
    Dim cellPdg1 As Range
    Dim cellPdg2 As Range
    Dim cellPmin1 As Range
    Dim cellPmax1 As Range
    Dim cellPmin2 As Range
    Dim cellPmax2 As Range
    Dim cellsum As Range
    Dim celldem As Range
    Dim cellcharge As Range
    Dim cellsoc As Range
    Dim cellfisrt As Range
    Dim cellsecond As Range
   
    'start variables
   
    Set cellchange = Sheets("optimization").Range("G4:L4")
    Set cellGoal = Sheets("optimization").Range("AK4")
    Set cellendtime = Sheets("optimization").Range("B4")
    Set cellvb1 = Sheets("optimization").Range("k4")
    Set cellvb2 = Sheets("optimization").Range("l4")
    Set cellPdg1 = Sheets("optimization").Range("G4")
    Set cellPdg2 = Sheets("optimization").Range("H4")
    Set cellPmin1 = Sheets("optimization").Range("x4")
    Set cellPmax1 = Sheets("optimization").Range("z4")
    Set cellPmin2 = Sheets("optimization").Range("y4")
    Set cellPmax2 = Sheets("optimization").Range("aa4")
    Set cellsum = Sheets("optimization").Range("w4")
    Set celldem = Sheets("optimization").Range("v4")
    Set cellcharge = Sheets("optimization").Range("q4")
    Set cellsoc = Sheets("optimization").Range("s4")
    Set cellfirst = Sheets("optimization").Range("ai4")
    Set cellsecond = Sheets("optimization").Range("aj4")
   
    'Optimization with SOLVER
    Do
    solverreset
    solverok setcell:=cellGoal.Address(True, True), maxminval:=2, bychange:=cellchange.Address(True, True), engine:=2 'call solver, objective function and changing cells
 
   
    'restricciones (3 >=; 1<=)
    solveradd cellref:=cellvb1.Address(True, True), relation:=5
    solveradd cellref:=cellvb2.Address(True, True), relation:=5
    solveradd cellref:=cellPdg1.Address(True, True), relation:=3, formulatext:=cellPmin1.Address(True, True)
    solveradd cellref:=cellPdg1.Address(True, True), relation:=1, formulatext:=cellPmax1.Address(True, True)
    solveradd cellref:=cellPdg2.Address(True, True), relation:=3, formulatext:=cellPmin2.Address(True, True)
    solveradd cellref:=cellPdg2.Address(True, True), relation:=1, formulatext:=cellPmax2.Address(True, True)
    solveradd cellref:=cellsum.Address(True, True), relation:=3, formulatext:=celldem.Address(True, True)
    solveradd cellref:=cellcharge.Address(True, True), relation:=3, formulatext:=240
    solveradd cellref:=cellsoc.Address(True, True), relation:=1, formulatext:=1
    solveradd cellref:=cellfirst.Address(True, True), relation:=3, formulatext:=cellsecond.Address(True, True)
   
    solversolve userfinish:=True
    solverfinish keepfinal:=1
       
    'Next variables
    Set cellchange = cellchange.Offset(1, 0)
    Set cellGoal = cellGoal.Offset(1, 0)
    Set cellendtime = cellendtime.Offset(1, 0)
    Set cellvb1 = cellvb1.Offset(1, 0)
    Set cellvb2 = cellvb2.Offset(1, 0)
    Set cellPdg1 = cellPdg1.Offset(1, 0)
    Set cellPdg2 = cellPdg2.Offset(1, 0)
    Set cellPmin1 = cellPmin1.Offset(1, 0)
    Set cellPmax1 = cellPmax1.Offset(1, 0)
    Set cellPmin2 = cellPmin2.Offset(1, 0)
    Set cellPmax2 = cellPmax2.Offset(1, 0)
    Set cellsum = cellsum.Offset(1, 0)
    Set celldem = celldem.Offset(1, 0)
    Set cellcharge = cellcharge.Offset(1, 0)
    Set cellsoc = cellsoc.Offset(1, 0)
    Set cellfirst = cellfirst.Offset(1, 0)
    Set cellsecond = cellsecond.Offset(1, 0)
   
    Loop While Trim(cellendtime.Text) <> "" 'until end time cell is empty

End Sub

Any comments would be much appreciated! I'm new in the forum and actually only been working with macros for a week but I will try to contribute to it when I have some time.

Many thanks in advance!

 

[NARAYANK991]

Hi ,

Is it possible to upload your file with the data for at least a month ?

If not , can you put timing statements within your code to see how much time the solver itself takes , and whether this time remains the same for every row ?

Your code can certainly be optimized , but since you say that the time for a hundred rows is much greater than 100 times the time for one row , I think it is the recalculation that is taking time.

Can you say how much time the worksheet recalculation takes , without the solver running ? Just press ALT CTRL F9 and see how much time it takes.

Narayan

 

[Dam0209]

Thank you very much for your reply!

Sure, I'm uploading the file with 3 month data here (actually is the data for a day repited 90 times as I'm only trying to get the spreadsheet working for now).

Yes, I was thinking about somehow displaying the time the solver takes to solve each row to check if it's actually the solver that takes that much time but I don't know how to code it, Could you tell me how?

Recalculating the spreadsheet after the macro has been run does not seem to take much time.

Thanks again for your comments.

 

[NARAYANK991]

Hi ,

I have inserted some Debug.Print statements for you to look at once the code is running ; you will find that the solver itself takes a variable amount of time to complete one set of iterations to find a solution ; sometimes it completes it in 3 seconds , while at other times it takes 10 seconds.

I will see how the code can be optimized , but in the meantime , you can go through these timing results for yourself.

Narayan

 

[Dam0209]

Thanks a lot!!