Maximum Subproblems Issue with Suppressing Excel Solver dialog box with VBA (Excel 2010)

R

Rendeverance

New Member
Joined
Aug 17, 2011
Messages
3
Hi all,

I'm looking for some helpful suggestions of examples of how to modify my code example if possible.

Apologies if this seems in any way a simple fix but I am having trouble implementing maximum subproblem limits in my Excel 2010 macro.
I am aware of the StepThru workaround that is commonly given but I cannot seem to get it to work with a looped script such as this - as soon as I set stepthru=True the solver fails to budge from its starting values, and it does not seem to fix the issue either. My searching does not seem to provide another suitable option (I have come across some suggestions of a macro which looks for the dialog and presses the relevant option if the dialog is present - but this is messy and would make things slow)

This is a simplified version of my original code (with constraints and without stepthru function) in a working condition, it works very well but without the constraint but it takes a very long time to complete. The loop may have to cycle for some 10000 points so it is impossible for a user to press an 'end' dialog manually some 20000 times.

Any ideas?

Thanks in advance...
Code: 
<code style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, serif; white-space: inherit;">[COLOR=#00008B]Sub[/COLOR] GeneratorExample()
[COLOR=#808080]'
[/COLOR][COLOR=#808080]' 15/04/2014 Rendeverance
[/COLOR][COLOR=#808080]'
[/COLOR][COLOR=#00008B]Dim[/COLOR] datapoint_loop [COLOR=#00008B]As[/COLOR] [COLOR=#00008B]Long[/COLOR], next_datapoint_row [COLOR=#00008B]As[/COLOR] [COLOR=#00008B]Long
[/COLOR][COLOR=#00008B]On[/COLOR] [COLOR=#00008B]Error[/COLOR] [COLOR=#00008B]Resume[/COLOR] [COLOR=#00008B]Next
[/COLOR][COLOR=#808080]
'Define location of first row to paste to
[/COLOR]next_datapoint_row = [COLOR=#800000]3


[/COLOR][COLOR=#808080]'*** Some starter and preparation code here ***
[/COLOR][COLOR=#808080]

'Start Loop, defining start and finish datapoints (taken from upper and lower bounds provided)
[/COLOR][COLOR=#00008B]For[/COLOR] datapoint_loop = [COLOR=#800000]0[/COLOR] [COLOR=#00008B]To[/COLOR] [COLOR=#00008B]CLng[/COLOR](Cells([COLOR=#800000]10[/COLOR], [COLOR=#800000]4[/COLOR]).Value) 
   [COLOR=#808080]
'Disable screen updating to speed up calculations[/COLOR] 
   Application.ScreenUpdating = [COLOR=#800000]False[/COLOR] 
   [COLOR=#808080]
'Set defining cell value as per loop datapoint in question[/COLOR] 
   Cells([COLOR=#800000]11[/COLOR], [COLOR=#800000]4[/COLOR]).Value = datapoint_loop 
   Sheets([COLOR=#800000]"Dashboard"[/COLOR]).Activate 
   Cells([COLOR=#800000]12[/COLOR], [COLOR=#800000]4[/COLOR]).Value = Cells([COLOR=#800000]7[/COLOR], [COLOR=#800000]4[/COLOR]).Value + (Cells([COLOR=#800000]9[/COLOR], [COLOR=#800000]4[/COLOR]) * datapoint_loop) 
   [COLOR=#808080]
'Copy calculated values for this datapoint to output sheet[/COLOR] 
   Range([COLOR=#800000]"C40:AB40"[/COLOR]).Copy 
   Sheets([COLOR=#800000]"Data Output"[/COLOR]).Activate 
   Cells(next_datapoint_row, [COLOR=#800000]1[/COLOR]).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _            
     :=[COLOR=#800000]False[/COLOR], Transpose:=[COLOR=#800000]False[/COLOR]    
   Sheets([COLOR=#800000]"Dashboard"[/COLOR]).Activate 
  [COLOR=#808080]
'Set solver parameters and solve for BEST CASE scenario using GRG Nonlinear[/COLOR] 
   SolverReset 
   SolverOptions MaxTime:=[COLOR=#800000]0[/COLOR], Iterations:=[COLOR=#800000]0[/COLOR], Precision:=[COLOR=#800000]0.001[/COLOR], Convergence:= _        
[COLOR=#800000]      0.1[/COLOR], StepThru:=[COLOR=#800000]False[/COLOR], Scaling:=[COLOR=#800000]True[/COLOR], AssumeNonNeg:=[COLOR=#800000]False[/COLOR], Derivatives:=[COLOR=#800000]1[/COLOR] 
   SolverOptions PopulationSize:=[COLOR=#800000]800[/COLOR], RandomSeed:=[COLOR=#800000]1[/COLOR], MutationRate:=[COLOR=#800000]0.5[/COLOR], Multistart _        
      :=[COLOR=#800000]True[/COLOR], RequireBounds:=[COLOR=#800000]True[/COLOR], MaxSubproblems:=[COLOR=#800000]10[/COLOR], MaxIntegerSols:=[COLOR=#800000]0[/COLOR], _        
      IntTolerance:=[COLOR=#800000]1[/COLOR], SolveWithout:=[COLOR=#800000]False[/COLOR], MaxTimeNoImp:=[COLOR=#800000]30[/COLOR] 
   SolverAdd CellRef:=[COLOR=#800000]"$E$18"[/COLOR], Relation:=[COLOR=#800000]1[/COLOR], FormulaText:=[COLOR=#800000]"$F$18"[/COLOR] 
   SolverAdd CellRef:=[COLOR=#800000]"$E$18"[/COLOR], Relation:=[COLOR=#800000]3[/COLOR], FormulaText:=[COLOR=#800000]"$G$18"[/COLOR] 
   SolverAdd CellRef:=[COLOR=#800000]"$E$19"[/COLOR], Relation:=[COLOR=#800000]1[/COLOR], FormulaText:=[COLOR=#800000]"$F$19"[/COLOR] 
   SolverAdd CellRef:=[COLOR=#800000]"$E$19"[/COLOR], Relation:=[COLOR=#800000]3[/COLOR], FormulaText:=[COLOR=#800000]"$G$19"[/COLOR] 
   SolverAdd CellRef:=[COLOR=#800000]"$E$20"[/COLOR], Relation:=[COLOR=#800000]1[/COLOR], FormulaText:=[COLOR=#800000]"$F$20"[/COLOR] 
   SolverAdd CellRef:=[COLOR=#800000]"$E$20"[/COLOR], Relation:=[COLOR=#800000]3[/COLOR], FormulaText:=[COLOR=#800000]"$G$20"[/COLOR] 
   SolverAdd CellRef:=[COLOR=#800000]"$E$21"[/COLOR], Relation:=[COLOR=#800000]1[/COLOR], FormulaText:=[COLOR=#800000]"$F$21"[/COLOR] 
   SolverAdd CellRef:=[COLOR=#800000]"$E$21"[/COLOR], Relation:=[COLOR=#800000]3[/COLOR], FormulaText:=[COLOR=#800000]"$G$21"[/COLOR] 
   SolverAdd CellRef:=[COLOR=#800000]"$E$22"[/COLOR], Relation:=[COLOR=#800000]1[/COLOR], FormulaText:=[COLOR=#800000]"$F$22"[/COLOR] 
   SolverAdd CellRef:=[COLOR=#800000]"$E$22"[/COLOR], Relation:=[COLOR=#800000]3[/COLOR], FormulaText:=[COLOR=#800000]"$G$22"[/COLOR] 
   SolverAdd CellRef:=[COLOR=#800000]"$E$23"[/COLOR], Relation:=[COLOR=#800000]1[/COLOR], FormulaText:=[COLOR=#800000]"$F$23"[/COLOR] 
   SolverAdd CellRef:=[COLOR=#800000]"$E$23"[/COLOR], Relation:=[COLOR=#800000]3[/COLOR], FormulaText:=[COLOR=#800000]"$G$23"[/COLOR] 
   SolverAdd CellRef:=[COLOR=#800000]"$E$24"[/COLOR], Relation:=[COLOR=#800000]1[/COLOR], FormulaText:=[COLOR=#800000]"$F$24"[/COLOR] 
   SolverAdd CellRef:=[COLOR=#800000]"$E$24"[/COLOR], Relation:=[COLOR=#800000]3[/COLOR], FormulaText:=[COLOR=#800000]"$G$24"[/COLOR] 
   SolverAdd CellRef:=[COLOR=#800000]"$E$25"[/COLOR], Relation:=[COLOR=#800000]1[/COLOR], FormulaText:=[COLOR=#800000]"$F$25"[/COLOR] 
   SolverAdd CellRef:=[COLOR=#800000]"$E$25"[/COLOR], Relation:=[COLOR=#800000]3[/COLOR], FormulaText:=[COLOR=#800000]"$G$25"[/COLOR] 
   SolverOk SetCell:=[COLOR=#800000]"$Z$40"[/COLOR], MaxMinVal:=[COLOR=#800000]1[/COLOR], ValueOf:=[COLOR=#800000]0[/COLOR], ByChange:=[COLOR=#800000]"$E$18:$E$25"[/COLOR], _        
      Engine:=[COLOR=#800000]1[/COLOR], EngineDesc:=[COLOR=#800000]"GRG Nonlinear"[/COLOR] 
   SolverSolve ([COLOR=#800000]True[/COLOR]) 
   SolverFinish KeepFinal:=[COLOR=#800000]1[/COLOR] 
   [COLOR=#808080]
'Copy calculated values for this datapoint to output sheet[/COLOR] 
   Range([COLOR=#800000]"C40:AB40"[/COLOR]).Copy 
   Sheets([COLOR=#800000]"Best Case"[/COLOR]).Activate    
   Cells(next_datapoint_row, [COLOR=#800000]1[/COLOR]).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _            
      :=[COLOR=#800000]False[/COLOR], Transpose:=[COLOR=#800000]False[/COLOR] 
   Sheets([COLOR=#800000]"Dashboard"[/COLOR]).Activate 
  [COLOR=#808080]

'Set solver parameters and solve for WORST CASE scenario using GRG Nonlinear[/COLOR] 
   SolverReset 
   SolverOptions MaxTime:=[COLOR=#800000]0[/COLOR], Iterations:=[COLOR=#800000]0[/COLOR], Precision:=[COLOR=#800000]0.001[/COLOR], Convergence:= _        
[COLOR=#800000]      0.1[/COLOR], StepThru:=[COLOR=#800000]False[/COLOR], Scaling:=[COLOR=#800000]True[/COLOR], AssumeNonNeg:=[COLOR=#800000]False[/COLOR], Derivatives:=[COLOR=#800000]1[/COLOR] 
   SolverOptions PopulationSize:=[COLOR=#800000]800[/COLOR], RandomSeed:=[COLOR=#800000]1[/COLOR], MutationRate:=[COLOR=#800000]0.5[/COLOR], Multistart _        
       :=[COLOR=#800000]True[/COLOR], RequireBounds:=[COLOR=#800000]True[/COLOR], MaxSubproblems:=[COLOR=#800000]10[/COLOR], MaxIntegerSols:=[COLOR=#800000]0[/COLOR], _        
       IntTolerance:=[COLOR=#800000]1[/COLOR], SolveWithout:=[COLOR=#800000]False[/COLOR], MaxTimeNoImp:=[COLOR=#800000]30[/COLOR] 
   SolverAdd CellRef:=[COLOR=#800000]"$E$18"[/COLOR], Relation:=[COLOR=#800000]1[/COLOR], FormulaText:=[COLOR=#800000]"$F$18"[/COLOR] 
   SolverAdd CellRef:=[COLOR=#800000]"$E$18"[/COLOR], Relation:=[COLOR=#800000]3[/COLOR], FormulaText:=[COLOR=#800000]"$G$18"[/COLOR] 
   SolverAdd CellRef:=[COLOR=#800000]"$E$19"[/COLOR], Relation:=[COLOR=#800000]1[/COLOR], FormulaText:=[COLOR=#800000]"$F$19"[/COLOR] 
   SolverAdd CellRef:=[COLOR=#800000]"$E$19"[/COLOR], Relation:=[COLOR=#800000]3[/COLOR], FormulaText:=[COLOR=#800000]"$G$19"[/COLOR] 
   SolverAdd CellRef:=[COLOR=#800000]"$E$20"[/COLOR], Relation:=[COLOR=#800000]1[/COLOR], FormulaText:=[COLOR=#800000]"$F$20"[/COLOR] 
   SolverAdd CellRef:=[COLOR=#800000]"$E$20"[/COLOR], Relation:=[COLOR=#800000]3[/COLOR], FormulaText:=[COLOR=#800000]"$G$20"[/COLOR] 
   SolverAdd CellRef:=[COLOR=#800000]"$E$21"[/COLOR], Relation:=[COLOR=#800000]1[/COLOR], FormulaText:=[COLOR=#800000]"$F$21"[/COLOR] 
   SolverAdd CellRef:=[COLOR=#800000]"$E$21"[/COLOR], Relation:=[COLOR=#800000]3[/COLOR], FormulaText:=[COLOR=#800000]"$G$21"[/COLOR] 
   SolverAdd CellRef:=[COLOR=#800000]"$E$22"[/COLOR], Relation:=[COLOR=#800000]1[/COLOR], FormulaText:=[COLOR=#800000]"$F$22"[/COLOR] 
   SolverAdd CellRef:=[COLOR=#800000]"$E$22"[/COLOR], Relation:=[COLOR=#800000]3[/COLOR], FormulaText:=[COLOR=#800000]"$G$22"[/COLOR] 
   SolverAdd CellRef:=[COLOR=#800000]"$E$23"[/COLOR], Relation:=[COLOR=#800000]1[/COLOR], FormulaText:=[COLOR=#800000]"$F$23"[/COLOR] 
   SolverAdd CellRef:=[COLOR=#800000]"$E$23"[/COLOR], Relation:=[COLOR=#800000]3[/COLOR], FormulaText:=[COLOR=#800000]"$G$23"[/COLOR] 
   SolverAdd CellRef:=[COLOR=#800000]"$E$24"[/COLOR], Relation:=[COLOR=#800000]1[/COLOR], FormulaText:=[COLOR=#800000]"$F$24"[/COLOR] 
   SolverAdd CellRef:=[COLOR=#800000]"$E$24"[/COLOR], Relation:=[COLOR=#800000]3[/COLOR], FormulaText:=[COLOR=#800000]"$G$24"[/COLOR] 
   SolverAdd CellRef:=[COLOR=#800000]"$E$25"[/COLOR], Relation:=[COLOR=#800000]1[/COLOR], FormulaText:=[COLOR=#800000]"$F$25"[/COLOR] 
   SolverAdd CellRef:=[COLOR=#800000]"$E$25"[/COLOR], Relation:=[COLOR=#800000]3[/COLOR], FormulaText:=[COLOR=#800000]"$G$25"[/COLOR] 
   SolverOk SetCell:=[COLOR=#800000]"$Z$40"[/COLOR], MaxMinVal:=[COLOR=#800000]2[/COLOR], ValueOf:=[COLOR=#800000]0[/COLOR], ByChange:=[COLOR=#800000]"$E$18:$E$25"[/COLOR], _        
       Engine:=[COLOR=#800000]1[/COLOR], EngineDesc:=[COLOR=#800000]"GRG Nonlinear"[/COLOR] 
   SolverSolve ([COLOR=#800000]True[/COLOR]) 
   SolverFinish KeepFinal:=[COLOR=#800000]1[/COLOR] 
   [COLOR=#808080]
'Copy calculated values for this datapoint to output sheet[/COLOR] 
   Range([COLOR=#800000]"C40:AB40"[/COLOR]).Copy 
   Sheets([COLOR=#800000]"Worst Case"[/COLOR]).Activate    
   Cells(next_datapoint_row, [COLOR=#800000]1[/COLOR]).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _            
      :=[COLOR=#800000]False[/COLOR], Transpose:=[COLOR=#800000]False[/COLOR] 
   Sheets([COLOR=#800000]"Dashboard"[/COLOR]).Activate 
       [COLOR=#808080]
'Copy Original values back to start[/COLOR] 
   Range([COLOR=#800000]"C47:C54"[/COLOR]).Copy 
   Range([COLOR=#800000]"E18:E25"[/COLOR]).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _            
      :=[COLOR=#800000]False[/COLOR], Transpose:=[COLOR=#800000]False[/COLOR] 
   [COLOR=#808080]
'Re-enable screen updating to update progress bar and graph[/COLOR] 
   Application.ScreenUpdating = [COLOR=#800000]True[/COLOR] 
   [COLOR=#808080]
'Move to next datapoint[/COLOR]    next_datapoint_row = next_datapoint_row + [COLOR=#800000]1[/COLOR] 
   [COLOR=#808080]
'Repeat
[/COLOR][COLOR=#00008B]Next[/COLOR] datapoint_loop


' ***Some other code here ***


End Sub</code>
 
Last edited:

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
You must log in or register to reply here.

Similar threads

K
  • Solved
Loop Solver through columns
Replies
6
Views
682
Kaper
Kaper
Asbestos_Jen
  • Question
Compare multiple parameters of samples to reference values (Excel 2007)
Replies
2
Views
229
Asbestos_Jen
Asbestos_Jen
S
  • Question
VBA Loop for Solver
Replies
1
Views
3K
Fluff
Fluff
J
  • Solved
#N/A Error
2
Replies
14
Views
302
Jedi Master
J
N
  • Question
Automatic solving Markowitz in Excel
Replies
0
Views
1K
nillie
N

Forum statistics

Threads
1,221,310
Messages
6,159,176
Members
451,543
Latest member
cesymcox

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top