Solver

[domylee]

Hi,
I'm trying to run Solver from vb with the following code:
SolverReset
SolverOptions precision:=0.001
SolverOKDialog setCell:=Worksheets("Feed Rate&quot.Range("EthyleneYield&quot, _
maxminval:=3, _
valueOf:=CTarget, _
bychange:=Worksheets("Input&quot.Range("StartDailyList&quot.Offset(3, 2)

SolverSolve userFinish:=False

However, I get the following error
"Solver: An unexpected internal error occurred, or available memory was exhausted"

Yet, I have no problems solving the same problem by running Solver from Tools -> Menu. I've already made sure that solver was added and checked in Tools|references. Can anyone help? Thanks in advance

 

[tab245]

Hi, domylee,

Did you find a way to solve the problem?
I am getting a similar error in my macro. I would appreciate if you advice me how to solve the problem.

Thank you in advance!

 

[Zathras]

I'm not familiar with Solver, but Excel has GoalSeek which sounds like it may do the same sort of thing.

For example, with the following setup:

A1: 'Theta
B1: 'Formula
A2: 3
B2: =A2-TAN(A2)

The macro listed below produces the result whereby
A2 = 4.49337715064346 and
B2 = 0.000652207859545229 (value of formula)

(The problem solved is to find the angle in the 3rd quadrant where the Tangent of the angle equals the angle expressed in radians.)

===========================================
Sub Macro1()
Range("B2&quot.GoalSeek Goal:=0, ChangingCell:=Range("A2&quot
End Sub

 

[RobBroekhuis]

Solver is more powerful than goalseek. If solver works from the user interface, then there must be some difference in the way it is set up in the VBA code. What happens if you record a macro while executing it from the user interface, and then re-run the macro? Solver can be very finnicky about tolerances, min/max values, etc, and can give very unhelpful error messages when things don't go its way...
Rob

 

[tab245]

Hi, RobBroekhuis, Zathras and Domylee,

Thank you for replying!

I tried to record the macro as you suggested ("Macro" - "Record Macro&quot. It works fine! Moreover, after this small test works fine - my other program stops giving the error!
I tried my program on Excel 2002 and Excel 2000. It works witout errors on Excel 2000.
So, I contacted Frontline Systems (the Solver developers), here is their reply:

>I didn't immediately see anything wrong with your code, >but if it does
>not happen Excel 2000, I think I know what might be >happening.
>Please try to add the following line of code before you >call any solver
>functions:
>Application.Run "Solver.xla!MenuUpdate"
>If that doesn't do it, try using:
>Application.Run "Solver.xla!Autpen"