Calling up the Solver function in a VBA macro

[Wayne Stewart]

I am trying to automate the Solver routine (Under the
Tools pull-down menu) using a VBA macro. I have succeeded
at setting the constraints and the various options using
the "SolverAdd" and "SolverOptions" commands. However,
the problem seems to be with the "SolverOK" command, where
the "Set Target Cell", "MaxMinValue", and "By Changing
Cells" fields are set. Solver does not seem to register
these values when the "SolveOK" command is used. Also,
when I use the "SolverReset" command followed
by "SolverAdd", and "SolverOptions" commands, no
parameters are passed to solver either.

The only way I can get the "By Changing Cells" cells
actually changing on the excel worksheet is to first
invoke solver manually (outside the macro), get the
solution by clicking "solve", then deleting the fields in
the solver dialog box, closing solver, and then running
the macro!

Is there a glitch with Excel? Is there a patch that can
be downloaded to fix this problem? I'm positive that my
syntax is correct (the help examples are very straight
forward).

I don't get any error messages when I run the macro. It
simply just doesn't present the solution on the worksheet.

Hope someone can help me!

 

[Ken Wright]

Do you mean you still have to hit OK on the dialog box for it to take the value?
Have you referenced the Solver addin from your project (tools / References /
Check 'Solver')

Does your code look something like this:-

Sub ExampleSolv()
SolverOk SetCell:="$A$3", MaxMinVal:=3, ValueOf:="10", ByChange:="$A$2"
SolverSolve (True)
End Sub

Note you need the parameter True set on the SolverSolve bit if you want it to
accept the value within the code without a dialog box.

Does the above code work for you if you try that with say 10 in A1, 5 in A2 and
=A1*A2 in A3?

 

[Wayne Stewart]

-----Original Message-----
Do you mean you still have to hit OK on the dialog box for it to take the value?
Have you referenced the Solver addin from your project (tools / References /
Check 'Solver')

Does your code look something like this:-

Sub ExampleSolv()
SolverOk SetCell:="$A$3", MaxMinVal:=3,

ValueOf:="10", ByChange:="$A$2"

 

[Wayne Stewart]

Thanks for the response Ken.

I just loaded up a new excel workbook and inserted your
ExampleSolv macro in a module within this new workbook. I
established the Solver reference as instructed since the
module is new (this was done already on my original macro).

When I run the macro nothing happens (just like my
original macro). The screen seems to blink for a
microsecond, as if something is being calculated in the
background. However, cell A2 doesn't change from 5 to 1
like it's supposed to.

This is a very basic macro test and solver doesn't
respond. Is the problem with Excel?

For you reference, I am running Windows 2000, Excel 2000
SP-3

Any other thoughts?

Thanks.
-Wayne

-----Original Message-----
Do you mean you still have to hit OK on the dialog box for it to take the value?
Have you referenced the Solver addin from your project (tools / References /
Check 'Solver')

Does your code look something like this:-

Sub ExampleSolv()
SolverOk SetCell:="$A$3", MaxMinVal:=3,

ValueOf:="10", ByChange:="$A$2"

 

[Ken Wright]

Hmmm - Just tried it on XL2000 and it worked fine. You haven't got any event
macros that might be conflicting as it was a new workbook.

Do other macros work OK, eg just doing basic stuff. Is this peculiar to just
Solver?

 

[Dana DeLouis]

For you reference, I am running Windows 2000, Excel 2000
XL2000: The Solver Add-in May Not Work When You Start Solver by Using a
Macro

http://support.microsoft.com/default.aspx?scid=kb;en-us;821430&Product=xlw

CAUSE
This problem may occur after you install Microsoft Office 2000 Service Pack
3 (SP-3).

HTH.

As a side note, although it works, it may be slightly better to not include
() around the "True" statement. They are "usually" used when testing the
results of Solver, as in the following idea...

Sub ExampleSolve()
Dim Result
SolverReset '<- Clean up
SolverOk [A3], 3, 10, [A1]
Result = SolverSolve(True)
'Or
SolverSolve True
End Sub

--
Dana DeLouis
Using Windows XP & Office XP
= = = = = = = = = = = = = = = = =

<snip>

 

[Ken Wright]

Thanks for the link and the 'heads up' Dana - much appreciated.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

XL2000: The Solver Add-in May Not Work When You Start Solver by Using a
Macro

http://support.microsoft.com/default.aspx?scid=kb;en-us;821430&Product=xlw

CAUSE
This problem may occur after you install Microsoft Office 2000 Service Pack
3 (SP-3).

HTH.

As a side note, although it works, it may be slightly better to not include
() around the "True" statement. They are "usually" used when testing the
results of Solver, as in the following idea...

Sub ExampleSolve()
Dim Result
SolverReset '<- Clean up
SolverOk [A3], 3, 10, [A1]
Result = SolverSolve(True)
'Or
SolverSolve True
End Sub

--
Dana DeLouis
Using Windows XP & Office XP
= = = = = = = = = = = = = = = = =

<snip>

 

[Wayne Stewart]

Thank-you Dana and Ken for your feedback to my Excel 2000
macro problem with the Solver Add-in.

I reviewed the website that Dana provided below and it
looks like the problem is with Excel 2000 SP-3. I would
like to download the patch as discussed on this website
but I can't figure out a "no charge" way of requesting the
patch. Every phone number listed seems to cost $$.

Is there a way I can download or have someone email me
this patch with incurring any cost? I can't really afford
to wait until the next service pack comes out (SP-4).

Thanks again.

-----Original Message-----
XL2000: The Solver Add-in May Not Work When You Start Solver by Using a
Macro

http://support.microsoft.com/default.aspx?scid=kb;en- us;821430&Product=xlw

CAUSE
This problem may occur after you install Microsoft Office 2000 Service Pack
3 (SP-3).

HTH.

As a side note, although it works, it may be slightly better to not include
() around the "True" statement. They are "usually" used when testing the
results of Solver, as in the following idea...

Sub ExampleSolve()
Dim Result
SolverReset '<- Clean up
SolverOk [A3], 3, 10, [A1]
Result = SolverSolve(True)
'Or
SolverSolve True
End Sub

--
Dana DeLouis
Using Windows XP & Office XP
= = = = = = = = = = = = = = = = =

<snip>


.