Solver within VBA

**lisavba** · 03-20-2018, 06:11 AM

we.tl/WejujzQFbZ -> put https before

Hi there,

You can download my Excel-file by using WeTransfer as I was not able to upload the file.

My problem is as following.
I have a hundred of arrays which are the inputs of a solver model. So a Solver model has to run a hundred times and each time the output has to be put in the corresponding array (column I and J).
I am totally new to VBA, so I don't have any idea what is technically possible.
Now I have tried to write a Solver code in a separate module, which is then called in the other module (within a loop).
When I execute this in VBA, I get error code 9: Subscript out of range.

Does anyone know how I can fix this?

Many thanks!!

Kind regards,

Lisa

**Kaper** · 03-20-2018, 06:18 AM

Hi Lisa,

Unfortunately, I do not trust wetransfer, so have not downloaded your file (pity that you couldn't attach it here - may be it was too big?)

When you see error dialog displayed, press Debug button. The line of code where execution stopped shall be highlighted yellow. It's often (not always) source of the problem. May be you refer there (in this line) to non-existing sheet, to illegal range, or collection element, etc.

What is in this line?

**lisavba** · 03-20-2018, 06:51 AM

Hi,

I hope I managed to upload the excel file now. Otherwise you can find parts of my code here.

When I debug, the highlighted line is the following: Sheets("Start").Cells(i, 9) = Sheets("Solver").Cells(4, 2). So this is the line, just behind executing the solver model.

Call Solvermacro
Worksheets("Start").Activate
'Solver output
Sheets("Start").Cells(i, 9) = Sheets("Solver").Cells(4, 2)
Sheets("Start").Cells(i, 10) = Sheets("Solver").Cells(4, 3)

Next i
End sub

This is full code of my solver module:

Sub Solvermacro()

    Worksheets("Solver").Activate
    SolverReset
    SolverOk SetCell:="$C$6", MaxMinVal:=2, ValueOf:=0, ByChange:="$B$4:$C$4,$C$6" _
        , Engine:=2, EngineDesc:="Simplex LP"
    SolverOk SetCell:="$C$6", MaxMinVal:=2, ValueOf:=0, ByChange:="$B$4:$C$4,$C$6" _
        , Engine:=2, EngineDesc:="Simplex LP"
    SolverSolve UserFinish:=False
   
End Sub

Best regards,

Lisa

**Alf** · 03-26-2018, 08:32 AM

Looking at you solver macro setup I do see some problems with it.

First of all the value in the target (object) cell should be a formula so when solver changes the value of the variable cells the value in the target cell should change.

In the cells to change you have specified B4:C4;C6. As target cell is C6 it should not be included as the change in B4:C4 should generate a change in C6 as
this cell should contain a formula. See comments above.

The constrains I see in the solver setup is not found in the macro so when macro is run solver will ignore this.

I do think you macro should look something like this:

Sub Solvermacro()

   Worksheets("Solver").Activate

   SolverReset
   SolverOk SetCell:="$C$6", MaxMinVal:=2, ByChange:="$B$4:$C$4", Engine:=2
   SolverAdd CellRef:="$B$7:$B$10", Relation:=1, FormulaText:="$D$7:$D$10"
   SolverAdd CellRef:="$B$11", Relation:=2, FormulaText:="$D$11"
  
    SolverSolve UserFinish:=True
   
End Sub

As for your "subscription out of range" problem I can't pin it down but as you file has links to other files (cells B3:C3) it may have something to do with this as solver works only on the active sheet.

I've also changed SolverSolve Userfinish to "True" as this will make solver loop through all values for i from 9 to 109 before it stops.

Alf

**lisavba** · 03-26-2018, 02:12 PM

Hi,

Thank you so much for your help.
First, I don't think that the target cell has to be a function, my LP is just built in that way that this value has to be minimized; which is used in the constraints. Actually, the largest proportional difference has to be minimized.
When I use the Solver function (without VBA, so manually), the model in this construction works perfectly!

Since I have added the constraints in the macro, the model is now running (I don't receive any error codes anymore).
However, it is not giving me the right solution.
I notice that the "<" constraints are fulfilled, but not the "=" constraint.
I don't understand the problem as the right relation (2) is used in the macro.

Enclosed, you can find the updated excel file.

Lisa

**MrShorty** · 03-26-2018, 03:11 PM

However, it is not giving me the right solution.
I notice that the "<" constraints are fulfilled, but not the "=" constraint.
I don't understand the problem as the right relation (2) is used in the macro.

I think it is impossible to meet all constraints. You are asking for:
1) values in row 4 to be less than the values in row 3 (B4<B3 and C4<C3), but
2) You want the values in row 4 to sum up to something greater than the sum of the values in row 3. (B4+C4>B3+C3). Intuitively, it should be apparent that both of these conditions cannot be met.

I note that, if I put something less than 11739 (say 8000 or 10000) into D11, that Solver seems to solve the problem without any difficulty with no changes to the Solver model. Perhaps more analysis of the problem is warranted. Do you really want values greater than 30000 in D11? Is the spreadsheet model as currently programmed able to reflect this desire?

I also note that your true objective function looks like a simple ABS() function =ABS(B4/B3-C4/C3). A little tinkering suggests to me that the minimum of this will always be 0. If we can prove to ourselves that this is true, I think this problem can simplify to a simple "solve a system of equations" problem:
EQN1) 0=ABS(B4/B3-C4/C3)
EQN2) D11=B4+C4

With a little algebra, that could be solved explicitly for B4 and C4, and we would not need Solver at all. That would be a lot easier to program into the spreadsheet. (A refresher if you have forgotten how to solve systems of equations: http://www.purplemath.com/modules/systlin1.htm

I would bet that there is more to analyze here, if you want. I think that's how I would approach this problem -- spend some more time with it algebraically until I understood the properties of the problem.

**Alf** · 03-26-2018, 04:53 PM

I don't think that the target cell has to be a function, my LP is just built in that way that this value has to be minimized; which is used in the constraints.

I do think the makers of solver beg to defer as they state that:

In Excel, Solver is part of a suite of commands sometimes called what-if analysis tools. With Solver, you can find an optimal (maximum or minimum) value for a formula in one cell — called the objective cell — subject to constraints, or limits, on the values of other formula cells on a worksheet. Solver works with a group of cells — called decision variable cells — that participate in computing the formulas in the objective and constraint cells. Solver adjusts the values in the decision variable cells to satisfy the limits on constraint cells and produce the result you want for the objective cell.

Alf

**lisavba** · 03-27-2018, 08:18 AM

Thank you all!
I found my mistake, I had to define the stand-alone costs (Sheet Solver) within my macro.
Now it works perfectly!

And yes I realized that in case of two players Solver doesn't have to be used as it works algebraically. But I have to expand the model to more players and in that case Solver is needed. So this was just a step to make sure that a more complicated model can work.

The only question that I now have is what if Solver doesn't find a feasible solution? When this happens Solver should give me a N/A value instead of giving me a solution that is not feasible.

Thanks!

Lisa

**MrShorty** · 03-27-2018, 09:07 AM

I don't think that Solver (by itself) can return an N/A error. I would expect that you would have your code look at the result of the SolverSolve function and use that information to edit the spreadsheet. I find this a good resource page (https://peltiertech.com/Excel/SolverVBA.html ), that includes a list of all of the outputs for the SolverSolve function (5 appears to represent the "Solver could not find a feasible solution" result, though there are other variations that mean Solver could not solve). As shown in Peltier's tutorial, you will need to edit your macro so that you can store the result of the SolverSolve function in a variable. Then, as illustrated in Peltier's tutorial, use a block If..Then..Else (I could also see using a Select Case) to determine why Solver finished and choose what to do based on that value.

**Alf** · 03-27-2018, 01:29 PM

Looking at the result from the solver run it seems that the only time you get an infeasible solution is when the formula in the K column (Total gain) is negative.

So why not add an if statement testing for a K value less than 0 and make solver skip these lines? Something like

If Sheets("Start").Cells(i, 11).Value < 0 Then
    GoTo skipper
End If

Call Solvermacro
Worksheets("Start").Activate
'Solver output
Sheets("Start").Cells(i, 9) = Sheets("Solver").Cells(4, 2)
Sheets("Start").Cells(i, 10) = Sheets("Solver").Cells(4, 3)

skipper:

Next i

Alf

**lisavba** · 03-27-2018, 03:12 PM

In this case, it is indeed noticeable when Solver will give an infeasible solution.
This is not the case in a game with three players, the gain may be positive but due to subcoalitions no feasible solution may be found.

I was just hoping there would exist a simple line in the macro, that would not give a return value when Solver can't find a feasible solution.
But seems too complex to let it work.

Lisa

**MrShorty** · 03-27-2018, 02:01 PM

If Sheets("Start").Cells(i, 11).Value < 0 Then
GoTo skipper

I'm going to date myself, but this statement brought up images of Gilligan running through the palm trees screaming "SKIPPEEEERRRRRR!!!!"

If you are unfamiliar with Gilligan's Island, put it into youtube's search engine and have fun.

My apologies. Enough personification of VBA today. Resume your regular discussion.

**Alf** · 03-27-2018, 05:50 PM

If you are unfamiliar with Gilligan's Island,

Not sure that program ran in Sweden, but I've heard of it. Funny how we associate with names. I was hoping the reference to Gilligan's Island would bring up
lush palm trees, white coral beaches and deep blue sea but it seems we can't always get things as we wish.

On the other hand I've had worse. On OP asked if a was a man or an alien life form which I thought was rater funny

and occasionally I do wonder about that myself.

My apologies. Enough personification of VBA today.

Don't worry about that, I do prefer comments like that as they tend to enlighten the threads.

Lisa I'm not sure you can capture infeasible solutions when you are running solver in a loop using VB. You could contact the makers of solver and see if they can/will help you.

https://www.solver.com/

Try their Support -> Technical Support. Problem is that they developed and sold the excel solver to Microsoft and there is no support agreement between them and the solver user of excel.

Perhaps you could use the freebie "OpenSolver" developed by the university of Auckland NZ. It integrates nicely with Excel and it do stop when it finds an infeasibility even if it is running in a loop with vb as opposed to the excel solver.

https://opensolver.org/

OpenSolver_inf.jpg

Alf

**Alf** · 03-27-2018, 06:24 PM

Don't know what happened but it seems I go a double posting of the same comments so I deleted this.

Solver within VBA

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Solver within VBA

Re: Solver within VBA

Re: Solver within VBA

Re: Solver within VBA

Re: Solver within VBA

Re: Solver within VBA

Re: Solver within VBA

Re: Solver within VBA

Re: Solver within VBA

Re: Solver within VBA

Re: Solver within VBA

Re: Solver within VBA

Re: Solver within VBA

Re: Solver within VBA

Thread Information

Users Browsing this Thread

Similar Threads

solver [This iterative problem doesn't always work with Solver; can it be solved with VBA]

Rolling Window Multiple Regression (Constrained) using Solver (Solver-VBA)

Trying to automate with a macro adding data to my solver model and triggering solver

Exel Solver (Solver Add-In/Toolpak) - Algorithms and options

Can't start Solver. Error message says Solver.xlam already open.

solver macro + simulation code + not updating solver values

Interesting Solver problem (Solver encounters an error)

Bookmarks

Bookmarks

Posting Permissions