SOLVER "error in model" error from VBA

Hello Excel VBA experts,

I have a multi-variable problem with multiple constraints that is solved successfully and quickly with SOLVER directly in Excel. However, I have the following issues when trying to do the same within a loop in VBA.

Firstly, recording a macro while executing SOLVER was little help for determining the proper syntax. I pieced code together from examaples found on-line.

I do have a reference set to SOLVER within VBA by Tools<References<check the "solver" box. However, I get compile errors using lines like:

Please Login or Register  to view this content.

If I preceed the solver commands with Application.Run, put the commands within quotes, and omit the text preceeding the arguments (SetCell:=, for example), I avoid the compile errors. Here is my code:

Please Login or Register  to view this content.

Each line executes, but an "error in model" is generated after the first SolverSolve command.

Why do I need Application.Run?
What is wrong with the syntax in my example? The column references in the SolverAdd commands are correct.


I'm using Excel 2010 with Windows XP if that matters.

Thanks for suggestions.

Is it possible to upload a sample file?

Alf

Alf,

I didn't attach the file because it is very large with many pages of code. I'll try to extract just the troubled parts and post later today.

thanks
T

Here is a reduced spreadsheet that shows the same behavior. A screen shot of how the problem is posed directly in Excel is in the sheet. Hit the 'solve it' button to execute the macro. A solution is found when run from Excel. An 'error in model' is returned when attempting to solve from VBA. The loop in VBA does start on row 21.

thanks,
T

Very strange!

A macro like this should be sufficient to run Solver after adding "Solver" in "Tools" -> "References"

Please Login or Register  to view this content.

But when I try to run it I get the error message "Compiler Error: Can't find project or library" even if I've reset my solver reference! And running Solver from Excel with your proposed values works just fine!

I've even blocked the "Private Sub" from Peltier Technical Services just in case this set a reference to Solver.xlam in another folder than where I have this file but no go.

Alf

What recommendations are there about which extensions to use with and without macros and with which versions of Excel? I have long been using only .xls extensions with and without macros in 2003 AND 2010 versions of Excel with no issues. I converted the attachment to an .xlsm to be consistent with recent versions not because I use the .xlsm extension regularly.

There is some peculiar behavior here. I saved the .xlsm file as an .xlsx in order to follow your steps exactly and was prompted that "VB projects can not be saved in macro free workbooks". If you go ahead and save, the macros and reference to Solver stay -unexpected. If you close and re-open the .xlsx, the macros and reference to Solver are gone. You then have to rebuild the macro and re-set the Solver reference. Then, macro3() works.

When you save the .xlsm as an xls, you don't get prompted that "VB projects can not be saved in macro free workbooks". The macro and reference to Solver stays after the save-as, but macro(3) does not work. If you close and re-open, the macros and reference are still there, and macro3() still doesn't work. Only if you remove the macro and reference and re-create them will macro3() work.

So if I want to add Solver commands to macros and make the workbooks usable to those who are still using Excel 2003, I have to remove all modules, delete the reference to Solver, then re-import all modules, re-reference Solver, then save as an .xls. Does this seem right?

I suspect you problem are set by the “Private Sub Workbook_Open()” where a reference is set to solver.

Perhaps you could test with a “clean” workbook adding all you modules and then “manually” set the solver vba references for the different environment you wish to test it in excel 2003, excel 2007 and excel 2010.

Since solver needs a reference set to the "Solver.xlam" file to work in a macro you have to take in account that in excel 2010 this file is normally found in

"C:\Program Files\Microsoft Office\Office14\Library\Solver\Solver.xlam".

If on the other hand you are running Windows 7 64 bit as you OS and your Office version is 32 bit then the file is found at:

"C:\Program Files\Microsoft Office (x86)\Office14\Library\olver\Solver.xlam".

For Excel 2007 the Solver.xlam file if normally found in this folder:

"C:\Program Files (x86)\Microsoft Office\Office12\Library\SOLVER\SOLVER.XLAM"

And finally for excel 2003 the Solver.xla file is found in the folder:

"C:\Program Files (x86)\Microsoft Office\OFFICE11\Library\SOLVER\SOLVER.XLA"

As you model is build as a "GRG-Nonlinear" it should work in all 3 environment (excel 2003, excel 2007 and excel 2010) with the right solver reference and if you let the user set the reference then there should be no problem in my opinion.

Alf

shg,

I tried multiple times getting compile errors with the VB Solver commands each time. In the end, I had to:

- Use CodeCleaner to export all code
- manually delete each module, form, and code on individual worksheets
- Save
- Close and re-open file
- Create a dummy module (just so there is some VB for CodeCleaner to work)
- Use CodeCleaner to import all code files
- re-set the reference to Solver

and now macros with the Solver commands work in my .xls workbook.

thanks
T

It looks like it does do the remove, save, and re-import, but it does not close after saving and re-open. This I had to do manually to avoid compile errors. The reference to solver does not seem to get recognized unless the file is saved and closed with the reference turned off, then re-opened and re-set. Weird

thanks for the help. I'll mark this as solved

T