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 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 times), 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 let's say a 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 and computational 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:

Please Login or Register  to view this content.
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!