How to record macro for Solver in Excel?

[Guest]

I tried to record a macro for Solver in Excel. After recording the macro, I
ran it and the message was that the "SolverOK" which is the first word in the
body is not a defined Sub or function.

 

[Tushar Mehta]

You need to set a reference to Solver. Check XL help for any Solver
function (such as SolverOK).

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Guest]

I am having exactly the same problem. The solver works fine when run
manually, but I get that error message (not a defined Sub or function) when
included in a Macro. I am using Office XP. I have also tried to run this same
macro using Office 2000 and it works fine!!! Any ideas of where the problem
is in Office XP?

Oscar

 

[Tom Ogilvy]

Have you set a reference to the solver addin? This is the usual cause for
this problem.

http://support.microsoft.com/default.aspx?scid=kb;en-us;843304
How to create Visual Basic macros by using Excel Solver in Excel 97

 

[Guest]

Tom,

I continued investigating and discovered that it seems to be a known bug in
Excel XP. The solution was to use Solv instead of Solver. When I wrote
SolvOk, SolvAdd, etc. the sentences where identified by VBA.

Thanks and regards,

Oscar

 

[Tom Ogilvy]

I have never heard of that. Do you have a reference/Link?

 

[Dana DeLouis]

.... When I wrote SolvOk, SolvAdd, etc.
Hi Tom. What the op is doing is bypassing Solver's Vba code, and calling
the class function directly.
I have seen a few others mention this, but I have never understood why this
would work. The vba code portion does very little processing of the input.
It does very little, and then turns around a calls it's internal functions
using the same parameters. So, I don't know?? Using SolverOk really
should work if SolvOk works.
Sometimes placing "Solver.Auto_open" at the beginning appears to help in
some situations. But all this does is delete an internal sheet, and
rebuilds that sheet with the same small amount of data. It really shouldn't
make a difference as far as I can tell.

In the vba editor, if you pull up the "Solver" Project/Library, names like
"SolvOk" are listed on the Solver4 Class module. We use the functions on
the VBA_Function module, but then again, this does nothing more that turn
around and calls functions like SolvOK.
So, I've never really understood why the difference. I just don't see it.

 

[Tom Ogilvy]

Thanks Dana. Prior to the posting to which you are responding, I did look
at the object browser and observed what you stated although I don't work
with Solver that much, so you have added quite a bit. Right after posting I
also did search these groups in Google and saw you had participated in some
threads on this issue where the conversation tended to allude to this being
a problem in the Swedish and Spanish and perhaps some other European
editions. But as with many empirical conclusions, the cause/true nature
could be misinterpreted.