Memory management advice

[agibsonsw]

Hello.

I wonder if someone can give a little bit of advice concerning memory management of Excel to improve performance?

Someone is using VB6, and automation of Excel, to maintain a Monte Carlo model. I don't have any further details at the
moment.

I assume that interaction between the two apps should be reduced? I assume (again) that there is a memory burden each time
a call is made between the two apps? Is it better to manipulate arrays in VB and then transfer data to a (large) range in Excel,
or to place data in an Excel range and perform calculations on it?

The following question is not directly related, but are Excel array formulas more efficient than 'normal' formulas?

Thanks, Andy.

[Jan Karel Pieterse]

To start with the second question: Array functions CAN be more efficient, but seldom are. I've seen cases where replacing an intricate set of just some complex array functions by a whole bunch of intermediate formulas in separate columns speeded up the calc with a factor 3.

On VB6: Most important is to limit the number of times you have to pass information between VB6 and Excel.
For the remainder: VB6 and VBA are very similar, also in the way they interface with Excel. SO any efficiency improvements that apply to VBA, also apply to vb6.

Final remark: Office 2010 64 bit does not allow use of VB6 com addd-ins, you'll have to go .NET if you want to stay away from VBA.

[agibsonsw]

Thank you for these details, which confirm some of my own suspicions. Array functions can be useful but their
overuse can slow things down.
I also assumed that constant interaction between both applications would be a burden on resources. (Apparently
the only interaction is with Excel, which makes me wonder why they couldn't create the whole thing within Excel.)
I believe they will be migrating to .Net after six months. I'll need to stress that this migration is not trivial!
Ta, Andy.

[sdckapr]

To start with the second question: Array functions CAN be more efficient, but seldom are. I've seen cases where replacing an intricate set of just some complex array functions by a whole bunch of intermediate formulas in separate columns speeded up the calc with a factor 3.

This may be nit-picking from a "practical standpoint" (since the effect on recalc time is real), but from my experience, this does not seem to an inefficiency in the array formula per se but an inefficiency in the design and how many calculations the formula requires. Many array formulas do hundreds if not thousands of individual calculations when a workbook is calculated, and the intermediates may do only a few for each one. And the intermediate calcs generally are not recalculated for each worksheet recalc, but the design of many of the array formulas require the hundreds of recalcs whenever anything is changed in the workbook. I have seen times where a macro routine can be 10x faster than array formulas.

From an efficiency standpoint, it seems to me that understanding of the number of "individual calcs" used is more important. Often a pivot table is more efficient than the arrays, intermediate calcs or a the macro routine.

Steve

[BobH]

Shucks!

I thought you were gonna tell me how to manage my old-timer's memory problems!

[Jan Karel Pieterse]

This may be nit-picking from a "practical standpoint" (since the effect on recalc time is real), but from my experience, this does not seem to an inefficiency in the array formula per se but an inefficiency in the design and how many calculations the formula requires. Many array formulas do hundreds if not thousands of individual calculations when a workbook is calculated, and the intermediates may do only a few for each one. And the intermediate calcs generally are not recalculated for each worksheet recalc, but the design of many of the array formulas require the hundreds of recalcs whenever anything is changed in the workbook. I have seen times where a macro routine can be 10x faster than array formulas.

Actually, in this particular example the time difference was significant even for full workbook recalculations.