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