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.

 

[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)