Solver in a User-Defined Function
 

I have created two user-defined functions, inv(targetCell) and
RevInv(targetCell, angleCell). The trigonometric function inv(theta) =
tan(theta) - theta, and there is no explicit function for the reverse. I
wrote a macro that uses Solver to find the angle that sets the targetCell to
zero. Here is the code and the formulae for the inputs:

Function RevInv(targetCell, angleCell)
SolverOptions MaxTime:=100, 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:="$H$10", MaxMinVal:=3, ValueOf:="0", ByChange:="$I$10"
SolverSolve (False)
End Function

targetCell: =G10-inv(I10)
angleCell: some arbitrary initial value (usu. 0.2)
cell that calls RevInv: '=RevInv(H10,I10)

So you can see that I am calling a user-defined function from the solver
within my other user-defined function. When I test the solver code in a test
macro with the same cells, everything runs perfectly. When I try to use the
"RevInv" function, I get the error message: "Solver: An unexpected internal
error occurred, or available memory was exhausted."

Does anyone have any idea what's going on? I guess I could run it as a
macro, but I want to be able to use this anywhere.

Thanks for your help,
Pflugs

Solver in a User-Defined Function
 

When I first started programming my own UDF's, one thing I wanted to do
was use Solver to find X when X cannot be expressed as an explicit
function of Y. These cases require numerical methods (like Solver
uses) to solve. From my own experience, I don't think you can call
Solver from within a UDF that you want to use in a worksheet. UDF's
are not allowed to make changes to other cells. Solver works by
changing the value in "SetCell". Thus, you get an error.

Personally, since I knew the expressions for the functions I wanted to
solve, I chose to program my own convergence loop to solve the function
using the Newton-Raphson method. Something along the lines of:

thetanew=0.2
Do
f=tan(thetanew)-thetanew
df=1/(cos(thetanew))^2-1
thetaold=thetanew
thetanew=thetaold-f/df
loop until abs(thetaold-thetanew)<1e-6

I think that's right, been a while since I've programmed one. Also,
I've never used it on trig functions, so you may have to add some
stuff, or try a different algorithm to get the answer. Solver uses a
Newton-Raphson algorithm, though, so, if you can get the answer using
Solver, you should be able to come up with something.

Or, just call Solver from a Sub procedure. There are disadvantages to
this, but it might be the easiest solution.


--
MrShorty
------------------------------------------------------------------------
MrShorty's Profile: http://www.excelforum.com/member.php...o&userid=22181
View this thread: http://www.excelforum.com/showthread...hreadid=561237

Solver in a User-Defined Function
 

Hmmm, I understand, and I guess I can see why that is. Thanks for the idea
to use numerical methods to solve my function. I have studied a few
numerical methods during my engineering studies, and I implemented the
Newton-Rahpson method in my function code.

I modified your code slightly since your code solved for the "involute"
function, and I needed the "reverse involute" function, for which there is no
explicit formula. Here is the code for anyone else who may need it and read
this sometime in the futu

Function inv(angle)

inv = Tan(angle) - angle

End Function
Function RevInv(targetCell)

x = targetCell
theta = 0

Do
f = x - inv(theta)
df = x - 1 / (Cos(theta) ^ 2) - 1
old = theta
theta = theta - f / df
Loop Until (Abs(old - theta) < 0.0000001)

RevInv = theta

End Function

MrShorty, thanks again.

Pflugs

"MrShorty" wrote:


When I first started programming my own UDF's, one thing I wanted to do
was use Solver to find X when X cannot be expressed as an explicit
function of Y. These cases require numerical methods (like Solver
uses) to solve. From my own experience, I don't think you can call
Solver from within a UDF that you want to use in a worksheet. UDF's
are not allowed to make changes to other cells. Solver works by
changing the value in "SetCell". Thus, you get an error.

Personally, since I knew the expressions for the functions I wanted to
solve, I chose to program my own convergence loop to solve the function
using the Newton-Raphson method. Something along the lines of:

thetanew=0.2
Do
f=tan(thetanew)-thetanew
df=1/(cos(thetanew))^2-1
thetaold=thetanew
thetanew=thetaold-f/df
loop until abs(thetaold-thetanew)<1e-6

I think that's right, been a while since I've programmed one. Also,
I've never used it on trig functions, so you may have to add some
stuff, or try a different algorithm to get the answer. Solver uses a
Newton-Raphson algorithm, though, so, if you can get the answer using
Solver, you should be able to come up with something.

Or, just call Solver from a Sub procedure. There are disadvantages to
this, but it might be the easiest solution.


--
MrShorty
------------------------------------------------------------------------
MrShorty's Profile: http://www.excelforum.com/member.php...o&userid=22181
View this thread: http://www.excelforum.com/showthread...hreadid=561237

Solver in a User-Defined Function
 

Hi. Here's the same technique, only slightly different.

Sub TestIt()
Dim x
x = Tan(0.5) - 0.5
Debug.Print RevInv(x)
End Sub


Function RevInv(n)
Dim g As Double
Dim r
g = 0.785 'guess (Pi/4)
Do While r < g
r = g
g = g - (Tan(g) - g - n) / Tan(g) ^ 2
Loop
RevInv = g
End Function

(Returns 0.5)
--
HTH. :)
Dana DeLouis
Windows XP, Office 2003


"Pflugs" wrote in message
...
Hmmm, I understand, and I guess I can see why that is. Thanks for the
idea
to use numerical methods to solve my function. I have studied a few
numerical methods during my engineering studies, and I implemented the
Newton-Rahpson method in my function code.

I modified your code slightly since your code solved for the "involute"
function, and I needed the "reverse involute" function, for which there is
no
explicit formula. Here is the code for anyone else who may need it and
read
this sometime in the futu

Function inv(angle)

inv = Tan(angle) - angle

End Function
Function RevInv(targetCell)

x = targetCell
theta = 0

Do
f = x - inv(theta)
df = x - 1 / (Cos(theta) ^ 2) - 1
old = theta
theta = theta - f / df
Loop Until (Abs(old - theta) < 0.0000001)

RevInv = theta

End Function

MrShorty, thanks again.

Pflugs

"MrShorty" wrote:


When I first started programming my own UDF's, one thing I wanted to do
was use Solver to find X when X cannot be expressed as an explicit
function of Y. These cases require numerical methods (like Solver
uses) to solve. From my own experience, I don't think you can call
Solver from within a UDF that you want to use in a worksheet. UDF's
are not allowed to make changes to other cells. Solver works by
changing the value in "SetCell". Thus, you get an error.

Personally, since I knew the expressions for the functions I wanted to
solve, I chose to program my own convergence loop to solve the function
using the Newton-Raphson method. Something along the lines of:

thetanew=0.2
Do
f=tan(thetanew)-thetanew
df=1/(cos(thetanew))^2-1
thetaold=thetanew
thetanew=thetaold-f/df
loop until abs(thetaold-thetanew)<1e-6

I think that's right, been a while since I've programmed one. Also,
I've never used it on trig functions, so you may have to add some
stuff, or try a different algorithm to get the answer. Solver uses a
Newton-Raphson algorithm, though, so, if you can get the answer using
Solver, you should be able to come up with something.

Or, just call Solver from a Sub procedure. There are disadvantages to
this, but it might be the easiest solution.


--
MrShorty
------------------------------------------------------------------------
MrShorty's Profile:
http://www.excelforum.com/member.php...o&userid=22181
View this thread:
http://www.excelforum.com/showthread...hreadid=561237

Solver in a User-Defined Function
 

Hi. Kind of an interesting inverse function with the main function
Tan(x) - x

There are multiple values for an inverse, so we need to make some
assumptions.
If x is value Pi/2, then Tan(Pi/2) is infinity.

If given a number like 10, or 100, then the inverse is very close to Pi/2.
Therefore, if given a number like 10, the first loop of Newton's method most
likely will calculate a number on the other side of Pi/2, where the slope
reverses.
This will cause the calculation not to work.
It appears that if given a large number that is very close to a
discontinuity, it appears best to make a guess as close to Pi/2 as possible
so the next guess stays in the same quadrant.

Perhaps something like this if you think you may have numbers greater than
about 1.


Function RevInv(n)
Dim g As Double
Dim r As Double
Dim Limit As Double

Limit = WorksheetFunction.Pi / 2
If n < 1 Then n = Limit / 2 Else g = Limit - 1 / 1000000
Do While r < g
r = g
g = g - (Tan(g) - g - n) / Tan(g) ^ 2
Loop
RevInv = g
End Function

--
HTH. :)
Dana DeLouis
Windows XP, Office 2003

<snip