Using Solver in VBA for excel without cell references

[Tom]

Hi!

I want to create a function that does the following
1. Is a function
2. gets data from external datasources
3. does calculations
4. optimizes using the solver (minimize the square sum of errors of a formula)
5. returns the value (as a normal function)

All this is straightforward, however, I would like not to use cell
references and
use variables directly in the VBA code instead of i.e. creating a new
spreadhseet,
inserting the values, running the solver from vba, reading the result and
deleting the
worksheet, and then returning the result. In other words I would like to run
the solver internally in the code without using cells in Excel.

I hope this was somewhat clear, since I'm not a programmer. All other steps
than the solver in my code is relatively easy, but I don't seem to be able to
put my mind as to how the solver should work without cell references (using
variables instead).

Does anyone have a suggestion?

 

[SteveM]

Hi!

I want to create a function that does the following
1. Is a function
2. gets data from external datasources
3. does calculations
4. optimizes using the solver (minimize the square sum of errors of a formula)
5. returns the value (as a normal function)

All this is straightforward, however, I would like not to use cell
references and
use variables directly in the VBA code instead of i.e. creating a new
spreadhseet,
inserting the values, running the solver from vba, reading the result and
deleting the
worksheet, and then returning the result. In other words I would like to run
the solver internally in the code without using cells in Excel.

I hope this was somewhat clear, since I'm not a programmer. All other steps
than the solver in my code is relatively easy, but I don't seem to be able to
put my mind as to how the solver should work without cell references (using
variables instead).

Does anyone have a suggestion?

You can't, because Frontline formulations are dependent on Excel range
equations/inequalities. You can do what you are suggesting in VBA,
but you have to place the data in cells and then you also have to call
the Solver from your function. Which I guess would be the return
value. The Frontline solver has a set VBA functions you can access by
referencing its Library in your project. You use those to manage the
model.

If you don't know VBA, you are either outta luck unless you hire
someone help you or you do become a programmer soon.

SteveM