Excel - Solver - Macro

[Assumptions]

Would appreciate some help on the following:
Creating a Macro that uses the Excel "Solver" function. When I run the Macro it returns with a notice

"Compile error:
Sub or Function not defined."

It highlights "SolverOK" in the code.

If someone could sugest a solution it would be appreciated.

Thanks

 

[IRstuff]

Are you sure that Solver is invoked in the library?

TTFN

FAQ731-376

 

[Denial]

A macro cannot run Solver unless VBA knows where to find the Solver's code, and so for each spreadsheet from which you want to run the Solver via a macro you have to tell VBA the magic location. This is done via what VBA calls "references".

Do the following:
(1) Get into VBA.
(2) Take Tools>References>Browse.
(3) You now need to locate the Solver code. With a "standard" installation of Office 10, you will find it in the directory
C:\Program Files\Microsoft Office\Office10\Library\Solver
so navigate your way to that location.
(4) The entity you wish to select is SOLVER.XLA and this will normally not be visible unless you activate the "all files" view.
(5) Select SOLVER.XLA and hit "open".

You will now return to the main references screen, where you should see the SOLVER has been added to the list and has been activated.

Other versions of Excel will be basically similar, mutatis mutandis.

 

[GregLocock]

...and that kludge is from Microsoft's /best/ programming team (reputedly).



Cheers

Greg Locock

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.

 

[Denial]

Yair. As the saying goes: "From the wonerful folks who gave you…".