Code that includes Solver taking extremely long time to execute

[robs3131]

Hi all,

I have an issue where code that includes using Solver is taking extremely
long to complete on a user's system (30 min) whereas on my system it
completes quickly (4 min). The user has a very similar system to mine -- in
fact, they have a slightly faster processor and slightly more RAM.

Any idea on why it may be taking so long on my user's system? FYI - the
code automatically intsalls Solver and the user verified that Solver was
checked in VBA References prior to executing the code. Below are my user's
system details:

- VOSTRO1700 system (I have a Dell Inspiron 1420 -- this is the only
significant difference between our systems)
- Intel(R) Core(TM)2 Duo CPU T5470 @ 1.6GHz processor
- 2046 MB RAM
- 32Bit Operating System

Below is the code specific to Solver in the macro:

With Sheets("Transaction Summary")
'Sort so that clear hold transactions are first on the sheet
.Cells.Sort Key1:=.Range("AI2"), Order1:=xlDescending, Header:=xlYes,
MatchCase:= _
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
.AutoFilterMode = False
.Columns("A:AI").AutoFilter
.Rows("1:1").AutoFilter Field:=35, Criteria1:="here"
Sheets("Transaction Summary").Activate
'Set the binary range to be used in the sumproduct calc
ActiveWorkbook.Names.Add Name:="holdclearbin", _
RefersToR1C1:=.Range("AJ2", .Range("AI65536").End(xlUp).Offset(0, 1))
'Set the commission range for the sumproduct calculation
ActiveWorkbook.Names.Add Name:="holdclearcomm", _
RefersToR1C1:=.Range("L2", .Range("L65536").End(xlUp))
'Set the sumproduct calculation cell and range
.Range("AK1").Formula = "=SUMPRODUCT(holdclearcomm,holdclearbin)"
ActiveWorkbook.Names.Add Name:="holdclearsumprod", _
RefersToR1C1:=.Range("AK1")
'Set the sum of the commission calculation
.Range("AL1").Value = A.Offset(0, -5).Value
'Set and execute Solver
Sheets("Transaction Summary").Activate
SolverOk SetCell:="holdclearsumprod", MaxMinVal:=3,
ValueOf:=Range("AL1").Value, ByChange:="holdclearbin"
SolverAdd CellRef:="holdclearbin", Relation:=5, FormulaText:="binary"
SolverSolve userfinish:=True
End With

 

[Bill Renaud]

Just a quick guess, but did you set all of the options (i.e. Max Time,
Iterations, Precision, Tolerance, Convergence, etc.) in your macro so that
everything is the same on the other user's system?

Maybe the other user did some work on another project, and the options are
still set to values that require far more calculation time and/or
difficulty converging.

 

[Dana DeLouis]

Solver is taking extremely long to complete

Hi. Just guessing here, but one thing that comes to mind are Upper/Lower
bounds on the model.
I just see a Binary Constraint. Do you need to limit any other variables?
(ie no variables drifting off towards infinity)

Don't hold me to this, but I "think" Solver checks to see if the sheet is in
AutoFilter mode. I think it causes problems for Solver. It's undocumented.
Don't hold me to it, but try removing the AutoFilter prior to running
Solver.

RefersToR1C1:=.Range("

This is just a Flag for a "possible" error. Excel 97 and after do not work
when using R1C1 notation. I see that you are not using it, but it's a good
habit to drop the R1C1 notation in code. Some programs that I use would
Flag this as a warning.

ActiveWorkbook.Names.Add Name:="holdclearbin", _
RefersToR1C1:=.Range("AJ2", .Range("AI65536").End(xlUp).Offset(0, 1))

Just a comment. These are your Binary changing cells. I note that there is
no error checking on its size. Without knowing your model, it could be set
to more than 200 Changing cells. (Solver's Limit)

The size of Range "holdclearcomm" is set differently. There is no check
that the size of "holdclearcomm" and "holdclearbin" are the same for your
"SumProduct" to work as expected.

I see the code: Sheets("Transaction Summary").Activate
listed 2 times later in code. It "appears" this was recorded, and one went
to other sheets for data.
You can remove these two lines of code.
However, do put "Sheets("Transaction Summary").Activate" at the beginning of
the code.
Note that your code is doing stuff to whatever sheet is active at the time.

Although not necessary, I like to write that long Sort command like this...

.Cells.Sort _
Key1:=.Range("AI2"), _
Order1:=xlDescending, _
Header:=xlYes, _
MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

Just another idea:
ActiveWorkbook.Names.Add "HoldClearSumProd", Range("AK1")

 

[robs3131]

Thanks for the input Bill and Dana -- I'm going to make your suggested
changes and check with my user on Monday to see if that helps. I'll post the
results then.

Thanks!