Solver with VBA

[Don Gulcher]

Hi Jon,

I am trying to minimize reference issues between Excel versions. When I use the following code I get the message:

Run-time error '1004'Application-defined or object-defined error

Private Sub CommandButton1_Click()
Application.Run "solver.xla!solverreset"
Run "Solver.xla!SolverOk", "$O$22", 3, 0, "$A$22"
Result = Application.Run("solver.xla!solversolve", True)
Application.Run "solver.xla!solverfinish"
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

End Sub

Any ideas?

Thanks,
Don
(e-mail address removed)




Jon Peltier wrote:

It may be an issue of mismatched references.
07-Feb-07

It may be an issue of mismatched references. For instance, when used in
Excel 2000, there would be a reference to Solver 2000. When it is next used
in 2003, Excel updates the reference in the workbook. Then when it is sent
back to someone on Excel 2000, Excel cannot find the reference to Solver
2003 and doesn't know to downdate the reference, and you get the error. You
could manually update the reference every time, which is a pain after the
second or third time you go through it; you can even programmatically update
references, but this is loaded with issues, including security settings.
Alternatively you could use Aplication.Run to call the solver functions,
which is more straightforward and doesn't rely on references, only that the
addin is installed

Instead of these

SolverRese
SolverOk SetCell:=Range("TotalVolume"), MaxMinVal:=3, Valueof:=27,
ByChange:=Range("Mass"
SolverAdd CellRef:=Range("Mass1"), Relation:=3, FormulaText:=
Result = SolverSolve(UserFinish:=True
SolverFinis

use these

Application.Run "solver.xla!solverreset
Application.Run "solver.xla!solverok", "TotalVolume", 3, 27, "Mass
Application.Run "solver.xla!solveradd", "Mass1", 3,
Result = Application.Run("solver.xla!solversolve", True
Application.Run "solver.xla!solverfinish

- Jo
------
Jon Peltier, Microsoft Excel MV
Tutorials and Custom Solution
http://PeltierTech.co
______


Previous Posts In This Thread:

Solver in VBA
Client is using Excel 03/WinXP. She has some code that uses the Solver. It
worked fine with Excel 2000 but now is giving : Compile error
Sub or Function not defined.

The solver addin is turned on in Tools, Addins. We even readded the Solver
addin by navigating to the xla file to make sure it was looking at the
correct file. Macro security is at Medium and macros are enabled.

When we go into the References in VB Editor, we check the Solver reference,
save the code but when go back into Excel, the reference is once again
unchecked. We even used the browse button here to locate the correct file but
it still won't stick.

We've also checked the Analysis Toolpack and Analysis Toolpack VBA addins
and still having problems

What else can we try??
--
maryj

maryj,You could try showing us the code and where the error occurs <g>.
maryj

You could try showing us the code and where the error occurs <g>

hth

Doug

It may be an issue of mismatched references.
It may be an issue of mismatched references. For instance, when used in
Excel 2000, there would be a reference to Solver 2000. When it is next used
in 2003, Excel updates the reference in the workbook. Then when it is sent
back to someone on Excel 2000, Excel cannot find the reference to Solver
2003 and doesn't know to downdate the reference, and you get the error. You
could manually update the reference every time, which is a pain after the
second or third time you go through it; you can even programmatically update
references, but this is loaded with issues, including security settings.
Alternatively you could use Aplication.Run to call the solver functions,
which is more straightforward and doesn't rely on references, only that the
addin is installed:

Instead of these:

SolverReset
SolverOk SetCell:=Range("TotalVolume"), MaxMinVal:=3, Valueof:=27,
ByChange:=Range("Mass")
SolverAdd CellRef:=Range("Mass1"), Relation:=3, FormulaText:=0
Result = SolverSolve(UserFinish:=True)
SolverFinish

use these:

Application.Run "solver.xla!solverreset"
Application.Run "solver.xla!solverok", "TotalVolume", 3, 27, "Mass"
Application.Run "solver.xla!solveradd", "Mass1", 3, 0
Result = Application.Run("solver.xla!solversolve", True)
Application.Run "solver.xla!solverfinish"

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______



Re: Solver in VBA
Hi Jon,

Thanks for you suggestions.

Here is how we modified our code:

Application.Run "solver.xla!solverreset"
Application.Run "solver.xla!solverok", SetCell:="$B$10", MaxMinVal:=3,
ValueOf:="0", ByChange:="$B$11"
Result = Application.Run("solver.xla!solversolve", True)
Application.Run "solver.xla!solverfinish"

It stops at SetCell and gives the error: Compile error: Named argument not
found.

--
maryj


:

Hi. Under Help for "Run"...
Hi. Under Help for "Run"...

Remarks
You cannot use named arguments with this method. Arguments must be passed by
position.
Therefore...

Run "Solver.xla!SolverOk", "B10", 3, 0, "B11"

--
HTH :>)
Dana DeLouis
Windows XP & Office 2003




Submitted via EggHeadCafe - Software Developer Portal of Choice
Build a Selected Text Favorites Utility for your Web Site
http://www.eggheadcafe.com/tutorial...c-86feb39cae83/build-a-selected-text-fav.aspx

 

[Skiffle]

Don,

Do you realize you responded to a 3 year old thread ?

Secondly, you included your unmunged email address, dgulcher at gmail dot
com. You have just opened yourself up to more spam than you bargained for.