Solver Using VBA

[Swoootie]

I am producing a template Excel based optamisation tool using Solver. I want to roll this out to other members of the team but I would like there to be as little manual intervention as possible. Ideally, where there are a number of possible solutions to a problem I would like VBA to select and output a solution rather than promting the user or producing numerous pop ups.
Is there a way to do this.

Thanks

 

[Swoootie]

It's a little complicated to explain the contents of the cells but they contain numbers that when multiplied to the contents of a table aim to match the target value (the target value is the sum of 3 cells) which is calculated on the Excel spreadsheet and referenced via solver

 

[Andrew Poulsom]

I can't help any further unless I can replicate your data, sorry.

 

[Swoootie]

Hi Andrew,

I think the problem lies in my VBA coding. I'm using "Application." as a prefix for each stage of the Solver activation process. All the solutions I have found on the internet do not have the "application.". I am unfamiliar with the syntax required for Solver. Am I doing somthing obviosuly wrong? I'm really struggling with this.

Application.DisplayAlerts = False
Application.ScreenUpdating = False
Application.Run "SolverAdd", "$U$46", 2, "$C$4"
Application.Run "SolverOk", "$G$86", 3, TARGET, "$B$50,$B$52"
Application.Run "SolverSolve", True

 

[Swoootie]

There are some alternative avenues that I have attempted including trying to disable the "Show Trial Solution" option, but no luck so far.

 

[Andrew Poulsom]

You should be able to use:

SolverAdd "$U$46", 2, "$C$4"
SolverOk "$G$86", 3, TARGET, "$B$50,$B$52"
SolverSolve True

 

[Swoootie]

Hi Andrew,

Thanks for your reply.

As soon asI replace my code with the code you suggest I get a "compile error: sub or function not defined". For some strange reason the code only runs (albeit without supressing the pop-ups) when I add "application.run" as a prefix.

 

[Andrew Poulsom]

Have you set a reference to Solver.xlam? More here:

Using the Solver VBA Functions

 

[Swoootie]

Hi Andrew,

I've done that but I'm still not able to prevent the "Show Trial Solution" pop-ups from appearing.
The VBA code now reads:
SolverAdd "$U$46", 2, "$C$4"
SolverOk "$G$86", 3, TARGET, "$B$50,$B$52"
SolverSolve True

Application.DisplayAlerts = False

 

[Andrew Poulsom]

You can use the ShowRef argument to call a function in lieu of displaying the Show Trial Solution dialog:

SolverSolve Function

 

[Swoootie]

Hi Andrew,

I've tried the following approaches, but none of them work to surpress the "Show Trials" popups:

SolverSolve UserFinish:=True, ShowRef:="ShowTrial"
SolverSolve UserFinish:=True
SolverSolve UserFinish:=True, ShowRef:=False

I've tried positioning these at different points of the execution code, added and removed the displayalerts option. Nothing is working and I can't find any other reference online or in textbooks of what to do in such cases.