Hi Dana:
This is great. This is where I got struck up. Please see the code below:
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("X12")) = 1 Then
[x11] = SolverSolution
MsgBox 2
Else
'The cell you are monitoring has changed!
'Do whatever you need to do...
MsgBox 1
End If
End Sub
Function SolverSolution()
Dim Chk As Long
' Set up Solver...then
SolverOptions MaxTime:=300, Iterations:=1000, Precision:=0.000001,
AssumeLinear:=False, StepThru:=False, Estimates:=1, Derivatives:=1,
SearchOption:=1, IntTolerance:=5, Scaling:=False, Convergence:=0.0001,
AssumeNonNeg:=False
SolverOk SetCell:="$X$11", MaxMinVal:=1, ValueOf:="Max",
ByChange:="$B$5:$R$5"
SolverSolve (True)
Chk = SolverSolve(True)
' If Chk is valid...then
SolverSolution = [Target]
End Function
When I change X12 to 1 or 0, I get a compile error saying Sub or Function
not defined. I already have solver installed in my Tools menu.
PLEASE LET ME KNOW HOW TO SEND MY WORKBOOK.
Kind Regards.
"Dana DeLouis" wrote:
Getting X11 automatically as I enter X6:X10 is the problem in question.
Hi. There are a few ways to do this. Here's one of many:
Enter new data in X6:X9,
Entering anything in X10 will trigger a macro.
In the vba editor, select you sheet module, and enter something like the
following.
When X10 changes, the code runs.
I don't know how your model is set up, but here's an outline.
You will probably have questions, so post back.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address < "$X$10" Then Exit Sub
SolverReset
SolverOk [Target], 1, , [B5:R5] '1 Maximize Target Cell
SolverAdd [??], 1, [X6:X10] ' 1 is "LessThan or Equal
SolverSolve True
End Sub
Feel free to send the workbook. I'll be glad to take a look at it.
--
HTH :)
Dana DeLouis
Windows XP & Office 2007
"Murthy" wrote in message
...
Hi Dana:
Thank you very much. I appreciate your solution. I am getting close to
what
I want. I hope and wish you could be of more help to me.
The problem is set up like this:
Total 10 constraints. 5 set of constraints (X6:X10) - which I want to vary
to see how my objective value(X11) changes. X11 is the 'sumproduct' of
changing cells (B5:R5) and the objective value (B12:R12). I have setup the
solver and I have a correct solution for the base case.
Now I want to change (X6:X10) and see the resulting value of Objective
(X11). Getting X11 automatically as I enter X6:X10 is the problem in
question.
I am naive to programming and I dont know how to solve this.
If there is a way to send my file as attachment it would be great. Can you
please let me know if I can paste a screenshot?
Kind Regards.
"Dana DeLouis" wrote:
question is whether we can define a UDF that invokes a solver?
Hi. Internally, Solver is a Subrotine, so one can not call it like a
regular function, if that is what you mean.
If you take the above question literally, then "yes", with some setup
involved.
Solver's subroutines interact with the spreadsheet. You need to set up
everything first on a spreadsheet.
For Example, you must have a Target Cell, and have Changing cells.
One would never really program it as a function. The usualy way to to run
a
Sub, find a soluion, and if desired, copy the soluion to a desired
location.
Depending on what you are doing, does anything here give you any ideas??
Again, you must set up solver first.
Sub Demo()
[A1] = SolverSolution
End Sub
Function SolverSolution()
Dim Chk As Long
' Set up Solver...then
Chk = SolverSolve(True)
' If Chk is valid...then
SolverSolution = [Target]
End Function
--
HTH :)
Dana DeLouis
Windows XP & Office 2007
"Murthy" wrote in message
...
Thanks for getting back to me. What I want is close to this. But still
my
question is whether we can define a UDF that invokes a solver?
Regards.
"Gary''s Student" wrote:
see:
http://groups.google.com/group/micro...0b795e4792216f
--
Gary''s Student
gsnu200708
"Murthy" wrote:
Hi all:
Can someone give me an idea of how to define excel solver as an user
defined
function? Say A3 = CallSolver(Inputs, Retrun Output)... something in
those
lines.
Regards.