Solver in a User-Defined Function

[Pflugs]

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

[MrShorty]

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?action=getinfo&userid=22181
View this thread: http://www.excelforum.com/showthread.php?threadid=561237

[Pflugs]

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 future:

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

[Dana DeLouis]

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" <Pfl...@discussions.microsoft.com> wrote in message
news:6B303B81-7731-4A18...@microsoft.com...

[Dana DeLouis]

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>