Solver within VBA

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?

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

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.

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.

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.

Please Login or Register  to view this content.

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.

Please Login or Register  to view this content.

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

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