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