Running Solver from Function

[krepa]

Hi all,
I'm having famous problem with solver when calling from function.
Solver: An unexpected internal error occurred, or available memory was exhausted

The idea running solver from function is that I will pass the setcell, valueof, bychange as parameters of the function. That way I can fill the formula in the cell and drag it and it will solve the rest automatically.

I have Office Excel 2k3(11.6560.6568)SP2

I do have a reference set.
When I run solver from Sub (macro) I do not have any issue.
But when I try to use it in function I'm getting that error.
I tried to put there SOLVER.Auto_open - it did not help.

Here is the function:

Please Login or Register  to view this content.

It gives error on any slover function.

Any hint please?

[MrShorty]

From what I can gather, this isn't possible if you're calling the function from a spreadsheet. Solver works by varying the value in the "by changing" cell, which is a forbidden operation for a function being called from a spreadsheet.

When I've wanted to do this, it was on relatively simple equations, so I simply hard-coded in my own algorithm (Newton-Raphson like Solver uses). This might be a viable approach for you, I don't know. In other cases, you might be limited to calling Solver from a Sub procedure.

[krepa]

Aaah,
that may be the reason - unfortunately. But in some way if makes sense.
The issue I have is that the number of cells I need to solve is varying so I need to modify the macro any time # of cells is changed.

Another option I guess would be to get somewhere some dll or other solver wich does not change cells.

[MrShorty]

The people who make Solver (www.solver.com) claim to sell additional libraries that will function in other environments (including visual basic). I've never seen or evaluated their product, so I can't say how well it works, or if it would be suitable for your situation, but it might be worth looking into.