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