Solver Runs from Code, but not from UI (XL2003)

Topic

New Reply

Hi,

I have encountered some strange behaviour. I have a spreadsheet within which I have been manually running Solver, exploring solutions. I have also coded the whole process to run from a cmdbutton. Until yesterday, both were working fine. Now, the solver routine runs perfectly from the VBA code, but I am no longer able to access Solver from the Tools/Solver menu. If I create a new, blank file, Tools/Solver does bring up the Solver Parameters dialog, but it refuses to appear in my original spreadsheet. Solver is listed in the available (checked add-ins) and because I am calling it from code, it is also referenced in the VBA project.

I have experienced the reverse of this with Excel 2000, but this was a bug documented in an MS KB article where solver wouldn’t run from code, but it was available in the UI. If I recall correctly, it was related to O2k SP1a and I think SP3 fixed that problem.

Any ideas gratefully accepted,

Thanks,

Reply | Quote

Replies

Do you mean that
a) The Tools | Solver menu item is absent from this particular workbook, or
The Tools | Solver menu item is present, but it doesn’t do anything?

Reply | Quote

Hi Hans,

The Tools/Solver menu item is there and available, but when I select it, absolutely nothing happens.

I have tried deselecting it and then I normally get a warning because it’s referenced in the VBA project. I then close Excel and re-open it and Solver has been removed from the list of available add-ins. When I reload Solver, the same thing happens – from code it’s OK, but from the UI, nothing happens,

Thanks,

Adrian

Reply | Quote

It would seem that there is something wrong with the installation of Solver. You can try Help | Detect and Repair from within Excel. If that doesn’t help, try removing Solver completely, then reinstalling it. You will need to have the Office 2003 CD at hand for this.

– Quit Excel (and other Office apps).
– Select Start | Control Panel.
– Open Add/Remove Programs.
– Click on Microsoft Office 2003.
– Click Change.
– Select Add/Remove Components (or something similar), then click Next.
– Expand Excel > Add-Ins > Solver.
– Select Not Available in the dropdown list.
– Click Update.
– When Solver has been removed, repeat the process, but now set Run from this computer for Solver.

Reply | Quote

Thanks for the suggestion, I will try it.

I think it is more likely that there is something wrong with the actual Excel file, since it displays this behaviour on my work PC and both my PC’s at home.

I managed to work around the problem by calling the relevant Solver procedure directly from some code :

Sub ShowSolverDialog()
‘had to implement this work around because the “Solver” menu item stopped functioning !!

‘ Run the Solver macro to display the main Solver dialog box.
Application.Run (“Solver.xla!Main”)

End Sub

I can now run Solver manually by assigning the above to a cmdButton. Not ideal, but ultimately the entire Solver workings will run from code in my app, so the users of this particular file won’t need to run it manually.

Regards,

Reply | Quote

You could try saving the workbook as a HTML file, then opening the HTML file and saving it as a workbook again. Can you run the solver in the new workbook?

Reply | Quote

Is there a change in the code to make this code for Excel 2002? When I initially ran the code, it couldn’t find the solver application (it was installed). Once I added the full path to “solver.xla”, I get an application error when I run it.

Thanks!

Larry

Reply | Quote

I don’t see why this code shouldn’t work in Excel 2002.

In your immediate window, type “? Application.LibraryPath”. I get “C:Program FilesMicrosoft OfficeOFFICE11LIBRARY”. WIthin the Library folder is the Solver folder which contains the Solver.xla and Solver32.dll files.

I think your system will probably give you something like “C:Program FilesMicrosoft OfficeOFFICE10LIBRARY”. If your solver files are present in the Solver folder in the library, then I don’t see why the code shouldn’t work for you.

Regards,

Reply | Quote

It shouldn’t be necessary to include the full path. Are you absolutely sure that the check box for “Solver add-in” in Tools | Add-ins… has been ticked? If so, SOLVER (SOLVER.XLA) should be listed in the Project Explorer in the Visual Basic Editor – see screenshot below.

Reply | Quote

Thanks to both if you – not sure why it works now and not before…..but it works….I lowered the security settings to Medium, but then it worked after I raised it again. I ran it from both the local workbook and personal macro workbook. Who knows……

Thanks again!

Larry

Reply | Quote