Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code that includes Solver taking extremely long time to execute
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 -- Robert |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code that includes Solver taking extremely long time to execute
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. -- Regards, Bill Renaud |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code that includes Solver taking extremely long time to execute
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") -- HTH :) Dana DeLouis Windows XP & Excel 2007 "robs3131" wrote in message ... 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 -- Robert |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code that includes Solver taking extremely long time to execut
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! -- Robert "Dana DeLouis" wrote: 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") -- HTH :) Dana DeLouis Windows XP & Excel 2007 "robs3131" wrote in message ... 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 -- Robert |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Processing taking a really long time | Excel Discussion (Misc queries) | |||
Subtotalling taking long time to complete | Excel Worksheet Functions | |||
Loop works correctly but takes extremely long to execute | Excel Programming | |||
Excel Taking Long TIme to Start | Excel Discussion (Misc queries) | |||
Why does this code take so long to execute. | Excel Programming |