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

 

[Andrew Poulsom]

With the first line of code what is your ShowTrial function?

 

[Swoootie]

My full code currently reads:
SolverAdd "$U$46", 2, "$C$4"
SolverOk "$G$86", 3, TARGET, "$B$50,$B$52"
SolverSolve UserFinish:=True, ShowRef:="ShowTrial"

Is this wrong? I apologise if the solution is obvious but I'm not very well versed in VBA yet.
Thanks for your help.

 

[Andrew Poulsom]

Please post your ShowTrial function.

 

[Swoootie]

Function ShowTrial(Reason As Integer)
MsgBox Reason
ShowTrial = False
End Function

Just before running the Solver code I call this function, but it keeps erroring.

 

[Andrew Poulsom]

The function is called by Solver. Does this work for you?

Function ShowTrial(Reason As Integer)
    ShowTrial = False
End Function

 

[Swoootie]

Hi Andrew,

I'm convinced I'm doing something wrong. I am running the code that you have sent me outside my sub procedure and retaining the line:
SolverSolve UserFinish:=True, ShowRef:="ShowTrial"

Either I am overwriting what the function sets as false or I am not triggering the function at all. Not sure what is going on.

 

[Andrew Poulsom]

Please post your entire code as it stands now.

 

[Swoootie]

Function showtrial(Reason As Integer)
MsgBox Reason
showtrial = False
End Function

Sub runsolver()
Application.Run "SolverReset"

Dim TARGET As Variant

TARGET = Application.InputBox("ENTER TARGET % IN A DECIMAL FORMAT (E.G. A TARGET OF 0.25% SHOULD BE ENTERED AS 0.0025")

If Not IsNumeric(TARGET) Then


MsgBox "Please Enter a Valid Target Value"
End If
If TARGET > 1 Then
GoTo TERMINATE
On Error GoTo 0
End If

SolverAdd "$U$46", 2, "$C$4"
SolverOk "$G$86", 3, TARGET, "$B$50,$B$52"
SolverSolve UserFinish:=True, ShowRef:="ShowTrial"
Application.DisplayAlerts = False
TERMINATE:
MsgBox "A SOLUTION CANNOT BE CALCULATED. PLEASE TRY AGAIN AND ENTER A TARGET VALUE OF < = 1"
End Sub

 

[Andrew Poulsom]

Are you saying that with that code you get the Show Trial Solution dialog rather than a message box showing the reason number? I don't know why that would be the case, sorry.

By the way you should have Exit Sub before TERMINATE:.

 

[Swoootie]

Hi Andrew,
Yes unfortunately I keep getting the Show Trial Solution as well as the message box that contains the reason number. I have to click OK on all of the Show Trial Soultion boxes before the result is returned, which in effect defeats the objective of me trying to automate this procedure (basically I need to set up a monthloy process off the back of this piece of work but I also need to backfill some dates and if I'm clicking through it will take me literally months to run the process).
Is there any work around that you can think of where VBA clicks through boxes on behalf of the user? Alternatively are there any other avenues that I can approach/look into that you are aware of?
I'm really desperate to get this working, as I've already invested a lot of time in to this project.
Thanks