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]

Did you try?

SolverSolve UserFinish:=True

 

[Swoootie]

Hi Andrew and thanks for your reply. Yes I did try that option, however, a pop up box shows up every time a feasible solution is reached. So for example, if there are more than 1 possible solutions, as each solution is reached a pop-up prompts the user saying "Show Trial Solution", with the option to continue or stop. I am trying to get away from any user manipulation, so would prefer that the 1st option (or most optimal option if that is feasible) is retained without the user having to deal with various pop-ups / prompts.


Thanks again for your help. </SPAN>

 

[Andrew Poulsom]

That's strange. According to this article setting UserFinish to True causes the results to be returned without displaying the Solver Results dialog:

Excel Solver - SolverSolve Function | Frontline Systems

 

[Swoootie]

Unfortunately that doesn't stop the pop-ups / prompts and neither does adding:
Application.DisplayAlerts = False

Really puzzled...

 

[Andrew Poulsom]

To help further I would need to see your setup.

 

[Swoootie]

Unfortunately I won't be able to send the front-end / interface related to this, as it contains some company - sensitive informtaion. However, the code below is executed through a button and the user is promted for a "target value" which must be matched subject to certain constraints that have been included.

Hope the majority of this is self explainitory, however, please let me know if you require any additional details. Basically I would like the "trial solutions" not to be displayed, but rather I would prefer it if Solver output the most optimal solution without being promted for it.

Thanks

Option Explicit</SPAN>
Sub RUNSOLVER()</SPAN>
Application.Run "SolverReset"</SPAN>
Dim TARGET As Variant</SPAN>
TARGET = Application.InputBox("ENTER TARGET % IN A DECIMAL FORMAT (E.G. A TARGET OF 0.25% SHOULD BE ENTERED AS 0.0025")</SPAN>
If Not IsNumeric(TARGET) Then</SPAN>
MsgBox "Please Enter a Valid Target Value"</SPAN>
End If</SPAN>
If TARGET > 1 Then</SPAN>
MsgBox "Please Enter a Target Value < 1"</SPAN>
End If</SPAN>
On Error GoTo 0</SPAN>
Application.Run "SolverAdd", "$U$46", 2, "$C$4"</SPAN>
Application.Run "SolverOk", "$G$86", 3, TARGET, "$B$50,$B$52"</SPAN>
Application.Run "SolverSolve", UserFinish:=False</SPAN>
Application.DisplayAlerts = False</SPAN>
On Error GoTo 0</SPAN>
Dim RESULT As Variant</SPAN>
RESULT = Application.Run("SolverSolve", True)</SPAN>
If RESULT <= 3 Then</SPAN>
MsgBox "SOLUTION FOUND", vbInformation, "SOLUTION FOUND"</SPAN>
ElseIf RESULT = 13 Then MsgBox "ERROR PLEASE CHECK PARAMETERS ENTERED AND TRY AGAIN"</SPAN>
Else: MsgBox "Solver was unable to find a solution.", vbExclamation, "SOLUTION NOT FOUND"</SPAN>

End If</SPAN>

End Sub</SPAN>

 

[Andrew Poulsom]

Shouldn't False be True here?

Application.Run "SolverSolve", UserFinish:=False</SPAN>

 

[Swoootie]

I've tried it with and without false (ie with "true" instead) - still doesnt work. I have also tried the code without the display alerts options - it still doesnt work.

 

[Andrew Poulsom]

Can you tell me what's in the cells that Solver is using?