Solver via VBA

[mhenk]

A speadsheet I currently have has a macro that runs a goalseek. I would like to switch this so that my client can input options like the number of iterations, and the maximum amount of time.

I understand that i can do this via the solver.

My question is, is there anything that I (and the client) would need to install in VBA in order to use a macro that utilizes the solver code?

 

[Jon Peltier]

You only need to make sure Solver is installed.

I have a web page with some hints for programming Solver:

http://peltiertech.com/Excel/SolverVBA.html

 

[mhenk]

Is there anything that I can add to a workbook open event that checks to make sure that the workbook will be able to use solver via VBA?

 

[mhenk]

And I'm not seeing "SOLVER" under "References..." Where should I browse to to find this?

 

[Jon Peltier]

If you don't see the reference to SOLVER, it must not be installed. In Excel, go to the Tools menu > Add-Ins, and look for Solver in the list. If it's there, check it and click OK. If it's not there, browse to the Excel Library directory, which by default for Excel 2003 is:

C:\Program Files\Microsoft Office\OFFICE11\Library\SOLVER

Select solver.xla and click OK or Open (I forget which), check the box for SOLVER in the Add-Ins list, and press OK again.

Once Solver is installed, it should appear in the list of references that you can check.

 

[mhenk]

Alright, i got everything working the way I want, except I'm having a hard time figuring out how some of the solver parameters. I'm using this code, and it seems to be taking MUCH more time that it should:

    SolverReset
    SolverOk Setcell:=Setcell, maxminval:=3, valueof:=equalto, bychange:=Bychanging
    solveroptions maxtime:=maxtimeip, iterations:=iterationsip, AssumeNonNeg:=True
    
    slvrres = Solversolve(True)

Is there something not clear about how the "Maximum Time" or the "Number of Iterations" works with the XL Solver Options? is time in seconds?

 

[Jon Peltier]

MaxTime is in seconds, default 100, can be up to 32767.
Iterations default 100, can be up to 32767.

Also check your precision. If you make it too small, it will increase the calculation time.

I can't see anything that jumps right out at me as the slow factor. What happens if you use different solver options? What happens if you run the same analysis manually?