CODEWITHSUNDEEP
vbaexcelsolver
PMac
PMac

Reputation: 11

Using VBA commands to work with Solver Dialogue box

Currently I'm creating a program in excel that runs solver. I have set a maximum time limit on the solver command. If the program exceeds the time limit, a solver dialogue box comes up that asks whether to continue or stop. I was wondering if there was a way to code into VBA to automatically select stop rather than having to have a user click the option.

Thanks in advance!

Upvotes: 1

Views: 1570

Answers (1)

ainwood
ainwood

Reputation: 1048

Yes, you can. You need to set some options on the Solver object. You can read more about it In the MSDN documentation

SolverSolve(UserFinish, ShowRef)

UserFinish Optional Variant. True to return the results without displaying the Solver Results dialog box. False or omitted to return the results and display the Solver Results dialog box.

ShowRef Optional Variant. You can pass the name of a macro (as a string) as the ShowRef argument. This macro is then called, in lieu of displaying the Show Trial Solution dialog box, whenever Solver pauses for any of the reasons listed

You need to define a macro that runs, which must take an integer argument. Here is a code example, straight from the linked page (posted here in case the link is broken in the future)

You call SolverSolve, passing it the arguments above:

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

You then need to define the ShowTrail macro that runs, and it must have the correct signature:

Function ShowTrial(Reason As Integer) 
'Msgbox Reason <= commented out, as you just want it to end, with no user input
ShowTrial = 0 'See comment below on the return value.
End Function

The return value is important. Return 0 if you want solver to just carry on regardless, or 1 if you want it to stop.

You could then get it to have different behavior on different reasons it finishes. Eg, if it reaches maximum time limit (your case), then stop. Otherwise, carry on:

Function ShowTrial(Reason As Integer)
Select Case Reason
    Case 1 '//Show iterations option set
        ShowTrial = 0  '//Carry on
        Exit Function
    Case 2 '//Max time limit reached
        ShowTrial = 1  '//Stop
        Exit Function
    Case 3 '//Max Iterations limit reached
        ShowTrial = 0  '//Keep going
        Exit Function
    Case 4 '//Max subproblems limit reached
        ShowTrial = 0  '//Keep Going
        Exit Function
    Case 5 '//Max feasible solutions limit reached
        ShowTrial = 0  '//Keep going
        Exit Function
End Select
End Function

Upvotes: 1

Related Questions