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.

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.

Maybe this:

' ================================================== =================
Function Backward(ValueToBeFound As Double, MoreArguments As Double, _
Optional ReasonableGuess, Optional MaxNumberIters, _
Optional MaxDiffPerc) As Double
'
' Niek Otten, March 22 2006
'
' This EXAMPLE function goalseeks another function,
' called Forward. It works for almost any continuous function,
' although if that function has several maximum and/or minimum
' values, the value of the ReasonableGuess argument becomes
' important.
' It calculates the value for ReasonableGuess and for
' 1.2 * ReasonableGuess.
' It assumes that the function's graph is a straight line and
' extrapolates that line from these two values to find the value
' for the argument required to achieve ValueToBeFound.
' Of course that doesn't come out right, so it does it again for
' this new result and one of the other two results, depending on
' the required direction (greater or smaller).
' This process is repeated until the maximum number of calculations
' has been reached, in which case an errorvalue is returned,
' or until the value found is close enough, in which case
' the value of the most recently used argument is returned

Dim LowVar As Double, HighVar As Double, NowVar As Double
Dim LowResult As Double, HighResult As Double, NowResult As Double
Dim MaxDiff As Double
Dim NotReadyYet As Boolean
Dim IterCount As Long

If IsMissing(ReasonableGuess) Then ReasonableGuess = 1.5 ' use default Values
If IsMissing(MaxNumberIters) Then MaxNumberIters = 20 ' that make sense in the
If IsMissing(MaxDiffPerc) Then MaxDiffPerc = 0.001 ' context of the function

MaxDiff = ValueToBeFound * MaxDiffPerc
NotReadyYet = True
IterCount = 1
LowVar = ReasonableGuess
LowResult = Forward(LowVar, MoreArguments)
HighVar = LowVar * 1.2
HighResult = Forward(HighVar, MoreArguments)

While NotReadyYet
IterCount = IterCount + 1
If IterCount MaxNumberIters Then
Backward = CVErr(xlErrValue) 'or some other errorvalue
Exit Function
End If

NowVar = ((ValueToBeFound - LowResult) * (HighVar - LowVar) + LowVar _
* (HighResult - LowResult)) / (HighResult - LowResult)
NowResult = Forward(NowVar, MoreArguments)
If NowResult ValueToBeFound Then
HighVar = NowVar
HighResult = NowResult
Else
LowVar = NowVar
LowResult = NowResult
End If
If Abs(NowResult - ValueToBeFound) < MaxDiff Then NotReadyYet = False
Wend

Backward = NowVar

End Function
' ================================================== =================

Function Forward(a As Double, b As Double) As Double
' This is just an example function;
' almost any continous function will work
Forward = 3 * a ^ (1.5) + b
End Function
' ================================================== =================




--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Murthy" wrote in message ...
| 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.
|

Hi Niek:

Thanks for gettingn back to me. Is there something I am missing to see in
your example? I wanted to create a UDF to take inputs(constraints) then
trigger solver and return the output to the cell. I am fairly (no prior VB
experience) new to these kind of advanced things and I would appreciate if
you can help me understand in a few words.

Regards.

"Niek Otten" wrote:

Maybe this:

' ================================================== =================
Function Backward(ValueToBeFound As Double, MoreArguments As Double, _
Optional ReasonableGuess, Optional MaxNumberIters, _
Optional MaxDiffPerc) As Double
'
' Niek Otten, March 22 2006
'
' This EXAMPLE function goalseeks another function,
' called Forward. It works for almost any continuous function,
' although if that function has several maximum and/or minimum
' values, the value of the ReasonableGuess argument becomes
' important.
' It calculates the value for ReasonableGuess and for
' 1.2 * ReasonableGuess.
' It assumes that the function's graph is a straight line and
' extrapolates that line from these two values to find the value
' for the argument required to achieve ValueToBeFound.
' Of course that doesn't come out right, so it does it again for
' this new result and one of the other two results, depending on
' the required direction (greater or smaller).
' This process is repeated until the maximum number of calculations
' has been reached, in which case an errorvalue is returned,
' or until the value found is close enough, in which case
' the value of the most recently used argument is returned

Dim LowVar As Double, HighVar As Double, NowVar As Double
Dim LowResult As Double, HighResult As Double, NowResult As Double
Dim MaxDiff As Double
Dim NotReadyYet As Boolean
Dim IterCount As Long

If IsMissing(ReasonableGuess) Then ReasonableGuess = 1.5 ' use default Values
If IsMissing(MaxNumberIters) Then MaxNumberIters = 20 ' that make sense in the
If IsMissing(MaxDiffPerc) Then MaxDiffPerc = 0.001 ' context of the function

MaxDiff = ValueToBeFound * MaxDiffPerc
NotReadyYet = True
IterCount = 1
LowVar = ReasonableGuess
LowResult = Forward(LowVar, MoreArguments)
HighVar = LowVar * 1.2
HighResult = Forward(HighVar, MoreArguments)

While NotReadyYet
IterCount = IterCount + 1
If IterCount MaxNumberIters Then
Backward = CVErr(xlErrValue) 'or some other errorvalue
Exit Function
End If

NowVar = ((ValueToBeFound - LowResult) * (HighVar - LowVar) + LowVar _
* (HighResult - LowResult)) / (HighResult - LowResult)
NowResult = Forward(NowVar, MoreArguments)
If NowResult ValueToBeFound Then
HighVar = NowVar
HighResult = NowResult
Else
LowVar = NowVar
LowResult = NowResult
End If
If Abs(NowResult - ValueToBeFound) < MaxDiff Then NotReadyYet = False
Wend

Backward = NowVar

End Function
' ================================================== =================

Function Forward(a As Double, b As Double) As Double
' This is just an example function;
' almost any continous function will work
Forward = 3 * a ^ (1.5) + b
End Function
' ================================================== =================




--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Murthy" wrote in message ...
| 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.
|

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.

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.

Hi Murthy,

Mine doesn't seem to be the right solution then. It is based more on Goal Seek requirements than on Solver; no constraints etc.
Maybe Gary's Student's links are more in the right direction.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Murthy" wrote in message ...
| Hi Niek:
|
| Thanks for gettingn back to me. Is there something I am missing to see in
| your example? I wanted to create a UDF to take inputs(constraints) then
| trigger solver and return the output to the cell. I am fairly (no prior VB
| experience) new to these kind of advanced things and I would appreciate if
| you can help me understand in a few words.
|
| Regards.
|
| "Niek Otten" wrote:
|
| Maybe this:
|
| ' ================================================== =================
| Function Backward(ValueToBeFound As Double, MoreArguments As Double, _
| Optional ReasonableGuess, Optional MaxNumberIters, _
| Optional MaxDiffPerc) As Double
| '
| ' Niek Otten, March 22 2006
| '
| ' This EXAMPLE function goalseeks another function,
| ' called Forward. It works for almost any continuous function,
| ' although if that function has several maximum and/or minimum
| ' values, the value of the ReasonableGuess argument becomes
| ' important.
| ' It calculates the value for ReasonableGuess and for
| ' 1.2 * ReasonableGuess.
| ' It assumes that the function's graph is a straight line and
| ' extrapolates that line from these two values to find the value
| ' for the argument required to achieve ValueToBeFound.
| ' Of course that doesn't come out right, so it does it again for
| ' this new result and one of the other two results, depending on
| ' the required direction (greater or smaller).
| ' This process is repeated until the maximum number of calculations
| ' has been reached, in which case an errorvalue is returned,
| ' or until the value found is close enough, in which case
| ' the value of the most recently used argument is returned
|
| Dim LowVar As Double, HighVar As Double, NowVar As Double
| Dim LowResult As Double, HighResult As Double, NowResult As Double
| Dim MaxDiff As Double
| Dim NotReadyYet As Boolean
| Dim IterCount As Long
|
| If IsMissing(ReasonableGuess) Then ReasonableGuess = 1.5 ' use default Values
| If IsMissing(MaxNumberIters) Then MaxNumberIters = 20 ' that make sense in the
| If IsMissing(MaxDiffPerc) Then MaxDiffPerc = 0.001 ' context of the function
|
| MaxDiff = ValueToBeFound * MaxDiffPerc
| NotReadyYet = True
| IterCount = 1
| LowVar = ReasonableGuess
| LowResult = Forward(LowVar, MoreArguments)
| HighVar = LowVar * 1.2
| HighResult = Forward(HighVar, MoreArguments)
|
| While NotReadyYet
| IterCount = IterCount + 1
| If IterCount MaxNumberIters Then
| Backward = CVErr(xlErrValue) 'or some other errorvalue
| Exit Function
| End If
|
| NowVar = ((ValueToBeFound - LowResult) * (HighVar - LowVar) + LowVar _
| * (HighResult - LowResult)) / (HighResult - LowResult)
| NowResult = Forward(NowVar, MoreArguments)
| If NowResult ValueToBeFound Then
| HighVar = NowVar
| HighResult = NowResult
| Else
| LowVar = NowVar
| LowResult = NowResult
| End If
| If Abs(NowResult - ValueToBeFound) < MaxDiff Then NotReadyYet = False
| Wend
|
| Backward = NowVar
|
| End Function
| ' ================================================== =================
|
| Function Forward(a As Double, b As Double) As Double
| ' This is just an example function;
| ' almost any continous function will work
| Forward = 3 * a ^ (1.5) + b
| End Function
| ' ================================================== =================
|
|
|
|
| --
| Kind regards,
|
| Niek Otten
| Microsoft MVP - Excel
|
| "Murthy" wrote in message ...
| | 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.
| |
|
|
|

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.

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.

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.

Please let me know how to send my workbook.

Sure. In your workbook menu, do File | SendTo..

and feel free to send it.
I'll do my best to help out.
Remove xyz from the address...
--
Dana DeLouis

Windows XP & Office 2007

<snip

Thanks Dana. I have just send it to you.

Kind Regards.

"Dana DeLouis" wrote:

Please let me know how to send my workbook.

Sure. In your workbook menu, do File | SendTo..

and feel free to send it.
I'll do my best to help out.
Remove xyz from the address...
--
Dana DeLouis

Windows XP & Office 2007

<snip