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

"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
>
>

[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" <[email protected]> wrote in message
news:[email protected]...
> 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
>
> "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
>>
>>

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

[manoj_b118]

I don't know macro's so can you please advice if I can develop these formulas in a spread sheet?

Regards

Manoj Vijay

[MrShorty]

@manoj_b118: This forum can be rather strict about not posting your question in an old thread. Please start a new thread to pose your question. If this thread will help explain what you want to do, then include a link to this thread in your new thread.

[calvid]

Many thanks, years after the fact, for this succinct and useful illustration of a convergence function. Put it to use just now in an entirely different (financial) context.