Excel Solver VBA - Solver Violating Constraints

Hello everyone,

I am trying to use the Excel Solver to optimize 3 variables (smoothing parameters) for a statistical forecast model. In the attached file, you will see that I set up the Objective Function (minimize) in column AM. I have the 3 variables in columns AN, AO, and AP. The VBA code uses a "For" with a nested "If" to loop the Solver to execute for each row individually. You will see I have 17 rows it loops through, so it must find a solution for 17 problems when executing the macro.

I have two questions, both pertain to the SolverOptions function of the Solver:

1. You can see in my attached file that the Solver violates the lower and upper bounds I set for the variables. Why is this? Are theer SolverOptions settings I can tailor to ensure the solution is within the defined bounds?
2. The Solver runs through these problems very quickly. I fear that it is not converging on the "optimal" solutions (variable values). Again, are there SolverOptions I can set to ensure I am close to the Optimal? (I am trying to achieve variable values to the nearest hundreth (+/-0.01).

Also, here is the VBA:

Please Login or Register  to view this content.

example.xlsm

I had no problem with Solver violating the upper or the lower bounds but I had problem running you macro. So I wrote a macro that works in my environment (Excel 2010)

Perhaps this macro will work for you also?

Please Login or Register  to view this content.

Alf

Had a go again at your macro and got it to run changed the setup a bit and now it runs without violating any constraits.

Please Login or Register  to view this content.

Alf

Thank you so much, Alf! That was it. I appreciate the support!

You are welcome.

Thanks for feedback. As you problem now seems to be solved don't forget to mark thread "Solved" as pr forum rules.

Alf

Ps

To mark your thread solved do the following:
-Go to the top of the first post
-Select Thread Tools
-Select Mark thread as Solved

Hi Alf. I just marked the thread as Solved.

One follow up question. When I run the macro, I sometimes get the following error: "Objective Cell must be a single cell on the active sheet." Do you know if any part of the code is causing this? When I hit "OK" it appears the macro runs as intended.

Do you know if any part of the code is causing this?

No I can't see any reference to another sheet in the code. Could it be the "long" formula in range AM33 to AM49 that gives Solver "hiccups"?

The way solver is set up it needs to have all values in the active sheet, this according to the builders of solver increases the speed of solver.

Just out of curiousity can you try to run the code I used (post #2) and see if this works for you and if you get the same message. Would also be interesting to know what excel version you are running.

Alf

Hi Alf,

Maybe it's hiccuping on some formula, not sure. Sometimes the error shows, sometimes not. There are no other references in the macro to other sheets. My version is Excel 2013.

So in the end it boils down to "solver works in mysterious ways its miracles to perform."

Alf