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

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