Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA & Solver Memory Usage
My guess is that Solver itself will not cause your workbook to grow that
big. When you copy data, are you making your workbook that much larger? The Solver that comes with Excel can not handle very large models. Usually when you see the description as ".. runs Solver multiple times " one of the problems is that one keeps adding the same "Constraints" over and over till Solver crashes. However, I do not see any constraints in your model. (Could this be a problem?). I don't think this will help, but try using SolverReset at the beginning of each loop. -- Dana DeLouis Windows XP & Office XP = = = = = = = = = = = = = = = = = "WP" wrote in message . ca... I have a spreadsheet that runs Solver multiple times using VBA to optimize a range of problems. It works fine using the code below, but there is a problem. When I open the spreadsheet I can see that Excel's memory usage is about 27 MB. When I start the code below to run solver, it runs fine the first time. After one run I look back in task manager and the memory consumption is approaching 60 MB. If I attempt to run it again Excel crashes, or I get an "Out of Memory" error. I know excel 2000 can only use 64 MB for spreadsheets so I understand why it is crashing. What I don't understand is why Excel does not release the memory after Solver completes it's run. Are there any Solver experts out there that can shed some light on my problem ? Thank You '4. begin to solve the matrix of solutions ' solve by ROW. Solve across, then down For i = 0 To num_col - 1 DesignSht.Range(column_name).Value = col_values(i) row_offset = 0 For j = 0 To num_row - 1 'copy row value to design sheet DesignSht.Range(row_name).Value = row_values(j) ' calculate before running solver--not really necessary ? DesignSht.Calculate With DesignSht SolverOk .Range(ReturnName), 1, 0, $c$21,$C$8, $C$16, $C$15, $C$18" SolverSolve True ' solvfinish doesn't do anything...doesn't help memory error ' solvfinish End With SolveSht.Range("output_topleft").Offset(col_offset , row_offset).Value = _DesignSht.Range(ReturnName).Value ' increment rowoffset by 1 row_offset = row_offset + 1 Next j ' increment column offset col_offset = col_offset + 1 Next i Set SolveSht = Nothing Set DesignSht = Nothing |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA & Solver Memory Usage
The workbook increase in size in negligible. Solver runs to maximize
something (eg: horsepower) and the only result I save is the value of the maximized variable. There is a standard set of constraints that always get used with Solver so they don't change. I have added them in advance (in the worksheet) so that is why I do not add them in code. But now you have me second guessing myself. How does Solver know to use the constraints that I set up manually earlier in the worksheet ? I have to go check.... Dana DeLouis wrote: My guess is that Solver itself will not cause your workbook to grow that big. When you copy data, are you making your workbook that much larger? The Solver that comes with Excel can not handle very large models. Usually when you see the description as ".. runs Solver multiple times " one of the problems is that one keeps adding the same "Constraints" over and over till Solver crashes. However, I do not see any constraints in your model. (Could this be a problem?). I don't think this will help, but try using SolverReset at the beginning of each loop. -- Dana DeLouis Windows XP & Office XP = = = = = = = = = = = = = = = = = "WP" wrote in message . ca... I have a spreadsheet that runs Solver multiple times using VBA to optimize a range of problems. It works fine using the code below, but there is a problem. When I open the spreadsheet I can see that Excel's memory usage is about 27 MB. When I start the code below to run solver, it runs fine the first time. After one run I look back in task manager and the memory consumption is approaching 60 MB. If I attempt to run it again Excel crashes, or I get an "Out of Memory" error. I know excel 2000 can only use 64 MB for spreadsheets so I understand why it is crashing. What I don't understand is why Excel does not release the memory after Solver completes it's run. Are there any Solver experts out there that can shed some light on my problem ? Thank You '4. begin to solve the matrix of solutions ' solve by ROW. Solve across, then down For i = 0 To num_col - 1 DesignSht.Range(column_name).Value = col_values(i) row_offset = 0 For j = 0 To num_row - 1 'copy row value to design sheet DesignSht.Range(row_name).Value = row_values(j) ' calculate before running solver--not really necessary ? DesignSht.Calculate With DesignSht SolverOk .Range(ReturnName), 1, 0, $c$21,$C$8, $C$16, $C$15, $C$18" SolverSolve True ' solvfinish doesn't do anything...doesn't help memory error ' solvfinish End With SolveSht.Range("output_topleft").Offset(col_offset , row_offset).Value = _DesignSht.Range(ReturnName).Value ' increment rowoffset by 1 row_offset = row_offset + 1 Next j ' increment column offset col_offset = col_offset + 1 Next i Set SolveSht = Nothing Set DesignSht = Nothing |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2003 memory usage | Excel Discussion (Misc queries) | |||
Organization of Macros and Memory Usage | Excel Discussion (Misc queries) | |||
XL 2007 memory usage | Excel Discussion (Misc queries) | |||
Monitor memory usage | Links and Linking in Excel | |||
Monitor memory usage | Excel Discussion (Misc queries) |