Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error message when using the Solver in a VBA macro loop
Hi everybody,
In Excel 2003 (Windows XP), I am trying to use the solver from a VBA macro to solve a formula referring to its own result. The formula is included in a spreadsheet table and is to be solved for every row of the table.. When running the macro, it is interrupted before completion and I get the message: "Solver: an error has occurred, or available memory is saturated". While trying to resolve the problem, I found out that the simple fact of opening the input parameter msgbox of the Solver from the menu Tool/Solver... and to exit it without having made any change solved the problem!?! Afterwards, the macro could be used any number of time without any problem. It seems that the simple fact of opening the input parameter msgbox of the Solver from the menu Tool/Solver... activated some paramèters of the Solver which are necessary to run my macro. Here is the VBA code for my procedu Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$B$2" Then Dim i As Long 'declare variable i loop Dim x As Long 'declare variable x row ' A loop that will solve the equation for the 26 rows of my table. i = 1 For i = 1 To 26 x = i + 6 'set row number ' Set up the parameters for the model. ' Set the target cell Ex to a maximum value by changing cell Fx. SolverReset ' Add constraint: Ex = Fx. SolverAdd CellRef:=Cells(x, 5).Address, Relation:=2, _ FormulaText:=Cells(x, 6).Address SolverOK SetCell:=Cells(x, 5).Address, MaxMinVal:=1, _ ByChange:=Cells(x, 6).Address ' Solve the model but do not display the Solver Results dialog box. SolverSolve UserFinish:=True ' Delete constraint: Ex = Fx. SolverDelete CellRef:=Cells(x, 5).Address, Relation:=2, _ FormulaText:=Cells(x, 6).Address ' Finish and keep the final results. SolverFinish KeepFinal:=1 Next i End If End Sub What would I need to change to get this macro to work right? (without having to first open the input parameter msgbox of the Solver from the menu Tool/Solver... and to exit it). I tried using the SolverOptions command to modify a few of the advanced options of the solver but it did not help my case. Thanks for your help, Mathieu Fournier |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error message when using the Solver in a VBA macro loop
Thanks for your tips, Tushar
It works just fine now. "Tushar Mehta" wrote in message ... Somewhere along the way (2000? 2002?), something happened between the XL VBA and Solver interface. Now, it appears one must force Solver to 'initialize' itself. Stick in a SOLVER.Auto_open before the For loop. Also, note that embedding a Solver optimization inside the Worksheet_Change procedure is a prescription to a guaranteed disaster. It will result in an infinite recursive loop -- or, if you are unlucky, in wrong results. How? Think of this: how did the code in the procedure get started? When you call Solver, what will it do? Change something in the worksheet, right? What will XL do because of that? And, you will be...back where? The common solution is to set EnableEvents to False. But, I don't know how Solver operates to confidently say that it would be safe to do so. That leaves you with a self implemented switch. Something along the lines of: Option Explicit Dim AlreadyBusy As Boolean Private Sub Worksheet_Change(ByVal Target As Range) If AlreadyBusy Then Exit Sub '<<<<< AlreadyBusy = True On Error GoTo ErrXIT 'do my stuff AlreadyBusy = False Exit Sub ErrXIT: 'clean up the results of the error AlreadyBusy = False End Sub But, honestly, you are best off not embedding solver optimizations inside worksheet change (or calculate or selectionchange or any such) events. -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article , says... Hi everybody, In Excel 2003 (Windows XP), I am trying to use the solver from a VBA macro to solve a formula referring to its own result. The formula is included in a spreadsheet table and is to be solved for every row of the table.. When running the macro, it is interrupted before completion and I get the message: "Solver: an error has occurred, or available memory is saturated". While trying to resolve the problem, I found out that the simple fact of opening the input parameter msgbox of the Solver from the menu Tool/Solver... and to exit it without having made any change solved the problem!?! Afterwards, the macro could be used any number of time without any problem. It seems that the simple fact of opening the input parameter msgbox of the Solver from the menu Tool/Solver... activated some paramèters of the Solver which are necessary to run my macro. Here is the VBA code for my procedu Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$B$2" Then Dim i As Long 'declare variable i loop Dim x As Long 'declare variable x row ' A loop that will solve the equation for the 26 rows of my table. i = 1 For i = 1 To 26 x = i + 6 'set row number ' Set up the parameters for the model. ' Set the target cell Ex to a maximum value by changing cell Fx. SolverReset ' Add constraint: Ex = Fx. SolverAdd CellRef:=Cells(x, 5).Address, Relation:=2, _ FormulaText:=Cells(x, 6).Address SolverOK SetCell:=Cells(x, 5).Address, MaxMinVal:=1, _ ByChange:=Cells(x, 6).Address ' Solve the model but do not display the Solver Results dialog box. SolverSolve UserFinish:=True ' Delete constraint: Ex = Fx. SolverDelete CellRef:=Cells(x, 5).Address, Relation:=2, _ FormulaText:=Cells(x, 6).Address ' Finish and keep the final results. SolverFinish KeepFinal:=1 Next i End If End Sub What would I need to change to get this macro to work right? (without having to first open the input parameter msgbox of the Solver from the menu Tool/Solver... and to exit it). I tried using the SolverOptions command to modify a few of the advanced options of the solver but it did not help my case. Thanks for your help, Mathieu Fournier |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
what does error message "too many adjustable cells" mean in solver | Excel Discussion (Misc queries) | |||
Solver val error message | Excel Discussion (Misc queries) | |||
Interesting Solver problem (Solver encounters an error) | Excel Discussion (Misc queries) | |||
Macro Error Message | Excel Programming | |||
Macro Error Message | Excel Programming |