Automate Solver with VBA

QuickDraw · Aug 19th 2005

I'm using this code to run Solver with VBA. When I run it, there is no solution (or Solver Results dialog box). I check the Solver under "Tools" and see that the constraints and MaxMinVal are set OK. But there are no entries for SetCell or ByChange. What have I done wrong?

Code

Sub RunSolver()


'Prompt for month number
currMonth = Application.InputBox(Prompt:="Enter month number:", Type:=2)


'Clear previous Solver settings
SolverReset


' The Solver will run one month at a time. Using the first month as
' the starting point, use intOffset to determine which month (column)
' to solve for


    intOffset = currMonth - 1


    ' Set the target cell to a minimum value by changing cells C11:C22 or
    ' an offset of this range
    SolverOk SetCell:=Range("R23"), MaxMinVal:=2, _
        ByChange:=Range("C11:C22").Offset(0, intOffset)


    ' Add the constraint that Final Inventory <= Capacity
    SolverAdd CellRef:=Range("C25:C28").Offset(0, intOffset), Relation:=1, _
        FormulaText:=Range("C59:C62").Offset(0, intOffset)
    
    ' Add the constraint that Final Inventory >= Safety Stock
    SolverAdd CellRef:=Range("C25:C28").Offset(0, intOffset), Relation:=3, _
        FormulaText:=Range("C53:C56").Offset(0, intOffset)


    ' Add the constraint that shipments to customer = customer demand.
    SolverAdd CellRef:=Range("C42").Offset(0, intOffset), Relation:=2, _
        FormulaText:=Range("C8").Offset(0, intOffset)
    
    ' Add the constraint that shipments from PM = PM Production.
    SolverAdd CellRef:=Range("C37").Offset(0, intOffset), Relation:=2, _
        FormulaText:=Range("C5").Offset(0, intOffset)


    ' Add the constraint that shipments from WHSE >= WHSE Demand.
    SolverAdd CellRef:=Range("C38:C41").Offset(0, intOffset), Relation:=3, _
        FormulaText:=Range("C46:C49").Offset(0, intOffset)
    
    ' Solve the model 
    SolverSolve UserFinish:=False
    'SolverFinish KeepFinal:=1


End Sub

Display More

QuickDraw · Aug 19th 2005

Re: Automate Solver with VBA

I've searched all over and I'm at my wits end with this. I ran the code and no solution was given. So I went to the Solver under Tools. Only the constraints as defined by the VBA code are defined. So I manually entered the cell references for SetCell and ByChange. Then I ran the Solver manually and BINGO... it worked fine. Does anyone have any ideas as to why my code is not giving a solution? I should add that something is happening because the screen flickers when I run the code. But I neither get the Solver Results dialog box or a solution in cells C11:C22.

Seti · Aug 19th 2005

Re: Automate Solver with VBA

Check out this link for some things to try.

QuickDraw · Aug 19th 2005

Re: Automate Solver with VBA

Thanks. I saw that thread in one of my searches. There's nothing there that suggests a resolution to my problem.

QuickDraw · Aug 19th 2005

Re: Automate Solver with VBA

I deleted this message bcause I thought I found the problem when in fact I did not.

QuickDraw · Aug 19th 2005

Re: Automate Solver with VBA

Maybe my problem is not clear judging from the lack of responses. Attached is a screenshot of the Solver parameters after running the code. The constraints are fine. But I'm not able to get anything returned for "Set Target Cell" or "By Changing Cells". I found a site where someone else had this problem. Unfortunately, they (like me) had no replies posted back.

**Andy Pope** · Aug 20th 2005

Re: Automate Solver with VBA

I think your right about the problem being too difficult to solve without an example.
Looks like you need to add the Address property to your statements.
[vba]
SolverOk SetCell:=Range("R23").address, MaxMinVal:=2, _
ByChange:=Range("C11:C22").Offset(0, intOffset).address[/vba]

QuickDraw · Aug 20th 2005

Re: Automate Solver with VBA

Thanks Andy. I had tried adding the Address before but it didn't help. Could I PM you the file to look at? It really perplexes me as to why it is not working.

**Andy Pope** · Aug 20th 2005

Re: Automate Solver with VBA

send the file and I will take a look see.

QuickDraw · Aug 20th 2005

Re: Automate Solver with VBA

Tried the file you sent Andy and still the same problem You can get it to work and I can't which means it's not a coding problem. I think I'll get in touch with FrontLine Systems and see if they may know where the problem lies. :thanx:

BTW - this was Andy's fix

Code

SolverOk SetCell:=Range("Total_Cost"), MaxMinVal:=2, _
         ByChange:=Range("Ship").Offset(0, intOffset).Address

QuickDraw · Aug 20th 2005

Re: Automate Solver with VBA

An update. It turns out that there is an issue with the SP-3. "Problem parameters are not set. For example, parameters such as Changing Cells or Target Cell are not set." http://support.microsoft.com/d…?scid=kb;en-us;821430#kb3 Although there is a patch to fix it, it means others may have the same problem when I try to share the app. Is there a way that I can set intOffset to be global so that I can manually put the cell references (named ranges) in the Solver and have it recognize the offset?

**Andy Pope** · Aug 21st 2005

Re: Automate Solver with VBA

As I don't have the problem I can not confirm whether this will work as a way around the problem although it does still work in for me.
[vba] intoffset = currMonth - 1
ThisWorkbook.Names("ShipOffset").Value = intoffset

' Set the target cell to a minimum value by changing cells C11:C22 or
' an offset of this range
SolverOk SetCell:=Range("Total_Cost"), MaxMinVal:=2, _
ByChange:=ThisWorkbook.Names("ShipOffsetRange").RefersToRange
[/vba]
Named ranges,
ShipOffset: =3
ShipOffsetRange: =OFFSET(Ship,0, ShipOffset)

QuickDraw · Aug 21st 2005

Re: Automate Solver with VBA

Thanks Andy. Could you clarify how to define the Named Ranges? (I tried the Insert - Name, but the code hangs at SolverOK with the message "Application-defined or object-defined error).

QuickDraw · Aug 21st 2005

Re: Automate Solver with VBA

Tried the file you sent and still no luck. I suspected as much since the problem lies with the SolverOK not being compatible with the version of Service Pack 3 that I'm using. I've been in touch with Microsoft Support and they provided me with a hotfix mentioned in the link I put in an earlier post. They warned that it hasn't been tested, to backup my system, etc etc. So what I've done to resolve my problem is to use an earlier version of solver.xla and everything works fine. Many :thanx: for your help. Without your feedback, I would have never realized that the problem was with a bug in the software and not a problem with my code.

QuickDraw · Aug 24th 2005

Re: Automate Solver with VBA

Having sorted out my original problem, I have a question regarding the use of solver. The code I'm using is below. This runs the solver one column at a time using an offset. What I have done is import another worksheet into the workbook. In this sheet, I want to run the solver using all columns at once (i.e. there is no offset or For... Next loop). The sheet has the identical same range names, but instead of being associated with 1 column (month), they are associated with 12 columns (year). Therefore, I would have 2 subs - RunSolver_Month and RunSolver_Year.

I copied the code below and removed the offset. On each worksheet, I have a command button. For sheet1, I call RunSolver_Month and on sheet2 I call RunSolver_Year. The first sub runs fine but when I try to run RunSolver_Year, I get a message "Solver: An unexpected error occured or available memory was exhausted." I thought maybe it was because it didn't know which sheet the range names was referencing. Tried to put this in for example, but it didn't help.

Code

With Sheets("Main(2)")
    SolverOk SetCell:=.Range("Total_Cost"), MaxMinVal:=2, _
        ByChange:=.Range("Ship")
end with

I found that the program halts here:

Code

' Solver Options...
    Call SolverOptions(MaxTime:=100, Iterations:=200, Precision:=0.0001, AssumeLinear _
        :=True, StepThru:=False, Estimates:=1, Derivatives:=1, SearchOption:=1, _
        IntTolerance:=5, Scaling:=False, Convergence:=0.0001, AssumeNonNeg:=True)

Any ideas as to what is wrong?

Code

Sub RunSolver_Month()


Application.ScreenUpdating = False


'Clear previous Solver settings
SolverReset
         
    ' Solver Options...
    Call SolverOptions(MaxTime:=100, Iterations:=200, Precision:=0.0001, AssumeLinear _
        :=True, StepThru:=False, Estimates:=1, Derivatives:=1, SearchOption:=1, _
        IntTolerance:=5, Scaling:=False, Convergence:=0.0001, AssumeNonNeg:=True)
  


' The Solver will run one month at a time. Using the first month as
' the starting point, use intOffset to determine which month (column)
' to solve for


    'intOffset = currMonth - 1
For intoffset = 0 To 11
    ' Set the target cell to a minimum value by changing cells C11:C22 or
    ' an offset of this range
    SolverOk SetCell:=Range("Total_Cost"), MaxMinVal:=2, _
        ByChange:=Range("Ship").Offset(0, intoffset).Address
    
    ' Add the constraint that Final Inventory <= Capacity
    SolverAdd CellRef:=Range("Final_Inventory").Offset(0, intoffset), Relation:=1, _
        FormulaText:=Range("Capacity").Offset(0, intoffset)
    
    ' Add the constraint that Final Inventory >= Safety Stock
    SolverAdd CellRef:=Range("Final_Inventory").Offset(0, intoffset), Relation:=3, _
        FormulaText:=Range("Safety_Stock").Offset(0, intoffset)


    ' Add the constraint that shipments to customer = customer demand.
    SolverAdd CellRef:=Range("Net_Flow_Cust").Offset(0, intoffset), Relation:=2, _
        FormulaText:=Range("Demand_Cust").Offset(0, intoffset)
    
    ' Add the constraint that shipments from PM = PM Production.
    SolverAdd CellRef:=Range("Net_Flow_PM").Offset(0, intoffset), Relation:=2, _
        FormulaText:=Range("Supply_PM").Offset(0, intoffset)


    ' Add the constraint that shipments from WHSE >= WHSE Demand.
    SolverAdd CellRef:=Range("Net_Flow_WHSE").Offset(0, intoffset), Relation:=3, _
        FormulaText:=Range("Demand_WHSE").Offset(0, intoffset)
    
    ' Solve the model and keep the final results.
    SolverSolve UserFinish:=True
    'SolverFinish KeepFinal:=1
Next intoffset
Application.ScreenUpdating = True


End Sub

Display More

QuickDraw · Aug 24th 2005

Re: Automate Solver with VBA

I've narrowed the problem down. If I Rem the highlighted code below, it works fine. Unfortunately I can no longer Reset my Solver settings.

Code

Sub RunSolver_Year()


Application.ScreenUpdating = False


'***************************************************
 [COLOR=Red][B]   ' Clear previous Solver settings
    'SolverReset
         
    ' Solver Options...
    'Call SolverOptions(MaxTime:=100, Iterations:=200, Precision:=0.0001, AssumeLinear _
       ' :=True, StepThru:=False, Estimates:=1, Derivatives:=1, SearchOption:=1, _
        'IntTolerance:=5, Scaling:=False, Convergence:=0.0001, AssumeNonNeg:=True)[/B]  [/COLOR] 
'***************************************************


' The Solver will use all months to find a solution
With Worksheets("Year")
    SolverOk SetCell:=.Range("Total_Cost"), MaxMinVal:=2, _
        ByChange:=.Range("Ship")
    
    ' Add the constraint that Final Inventory <= Capacity
    SolverAdd CellRef:=.Range("Final_Inventory"), Relation:=1, _
        FormulaText:=.Range("Capacity")
    
    ' Add the constraint that Final Inventory >= Safety Stock
    SolverAdd CellRef:=.Range("Final_Inventory"), Relation:=3, _
        FormulaText:=.Range("Safety_Stock")


    ' Add the constraint that shipments to customer = customer demand.
    SolverAdd CellRef:=.Range("Net_Flow_Cust"), Relation:=2, _
        FormulaText:=.Range("Demand_Cust")
    
    ' Add the constraint that shipments from PM = PM Production.
    SolverAdd CellRef:=.Range("Net_Flow_PM"), Relation:=2, _
        FormulaText:=.Range("Supply_PM")
    ' Add the constraint that shipments from WHSE >= WHSE Demand.
    SolverAdd CellRef:=.Range("Net_Flow_WHSE"), Relation:=3, _
        FormulaText:=.Range("Demand_WHSE")
    
    ' Solve the model and keep the final results.
    SolverSolve UserFinish:=True
    'SolverFinish KeepFinal:=1
End With
Application.ScreenUpdating = True


End Sub

Display More

QuickDraw · Aug 24th 2005

Re: Automate Solver with VBA

I found a work-around. I set up the Options in the Solver dialogue. In the VBA code, removed the SolverReset and SolverOptions, then added a SolverDelete statement before each of the SolverAdd constraints. This last step ensures that I don't end up with replicated constraints each time I run Solver.

**Andy Pope** · Aug 24th 2005

Re: Automate Solver with VBA

Glad you found a work thru. and thanks for posting it.

BruceMutton · Feb 6th 2006

Solver VBA Isssues

Just to let you all know that this thread has helped confirm my suspicions that the intermitant problems I have been having with solver when activated via vba are with solver and not with my code
Thanks

BruceMutton · Feb 7th 2006

Re: Automate Solver with VBA

...further.

QuickDraw's link to MS's KB on this problem is the key. To reiterate, MS have a hotfix for Solver that works only(?) with Excel 2000 SP3, but you need to actually phone, fax or email them to get it. The whole process took 5 minutes from dialling to verifying it worked. Beautiful!
KB Article Number(s): 821430, or search for "Excel Solver vba hotfix"

My sordid history on this issue.
Almost 10 years ago wrote a spreadsheet/vba application used in around 20 sites, in Excel 5... it worked fine, provided users could figure out how to install Solver.
Upgraded to Excel 97. ...it worked fine, but sometimes on some machines Solver would quietly do nothing when called from vba. Could not resolve other that to use a different computer, which almost always worked.
Upgraded to Excel 2000. I suspect Solver ALWAYS quietly did nothing when called from vba, but luckily it was not needed much, and as the writer I at least could figure out how to incorporate a manual Solver run in the use of the workbook.
Eventually I thought I'd try to fix it a few days ago. I spent a hell of a lot of time debugging and tracking variables and monitoring and deleting hidden names, and learnt a quite a few things about ways to pass variables. I eventually decided the Solver addin was faulty. A few others on various forums have had the same problems, but it's only on OzGrid that I found the answer (which is not to say that it is not elsewhere, I just looked hardest here)

I 'spose now I should see how it goes on 2002 and 2003.
Duh!

Automate Solver with VBA

Participate now!

Share