Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Solver in a User-Defined Function
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Solver in a User-Defined Function
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Solver in a User-Defined Function
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Solver in a User-Defined Function
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" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Solver in a User-Defined Function
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
User Defined Function | Excel Worksheet Functions | |||
Excel Solver as User Defined Funcntion | Excel Worksheet Functions | |||
Need to open the Function Arguments window from VBA for a user defined function. | Excel Programming | |||
User-defined data type; Error: Only User-defined types... | Excel Programming | |||
User-Defined Function pre-empting Built-in Function? How to undo???? | Excel Programming |