Optimizing memory, speed: Arrays vs. Cells, etc...

I've written 2 fairly large Excel programs (each having over 1000 lines of
code and several forms) and I'm concerned about optimizing memory/speed as
these programs continue to grow. I have some specific questions

1) Is it better to store temporary data within arrays in the code or to send
data to worksheet cells as much as possible?

2) Is it better to store text within the program code or to gather it from
some separate workbook containing the text? The text I'm talking about comes
from comments in my code that will become headings to rows and columns and
comments in those headings.

3) I tend to provide extensive documentation comments within my code for
future programmers and myself. Do i pay a price in memory or speed for doing
this?

4) I've stayed away from producing Excel charts in some cases because I
guessed they would degrade speed/memory usage. Without those charts the
workbooks generated by my programs are typically 500K in size. Am I being
too cautious?

Our company uses fairly modern computers with, for example, 1GB RAm
typically and the programs I'm making use Excel 2003 to drive AutoCAD, which
means both those programs are running at the same time when the programs are
being used.

Thanks for any advice,

-Tony

Answers interspersed

> I've written 2 fairly large Excel programs (each having over 1000 lines of
> code and several forms) and I'm concerned about optimizing memory/speed as
> these programs continue to grow. I have some specific questions
>
> 1) Is it better to store temporary data within arrays in the code or to
> send
> data to worksheet cells as much as possible?

There is a high overhead time for sending data to/from VBA and Excel, and
memory useage is probably higher on an Excel sheet because of the number of
properties each cell has. so keep temporary data in arrays.

>
> 2) Is it better to store text within the program code or to gather it
> from
> some separate workbook containing the text? The text I'm talking about
> comes
> from comments in my code that will become headings to rows and columns and
> comments in those headings.
>

Store text and formats as named ranges on a sheet in the workbook (.xls or
..xla) that contains the code. These can include comments. Then copy the
named ranges to the workbook you are generating.
This simplifies maintenance etc.

> 3) I tend to provide extensive documentation comments within my code for
> future programmers and myself. Do i pay a price in memory or speed for
> doing
> this?
>
No. (except that the .xls /.xla containing the code will be slightly larger
so will open slightly slower)

> 4) I've stayed away from producing Excel charts in some cases because I
> guessed they would degrade speed/memory usage. Without those charts the
> workbooks generated by my programs are typically 500K in size. Am I being
> too cautious?
>
Bit of an open-ended question, but generating up to say 10 charts I doubt
that you would notice the difference. Generating Charts is fast but you can
hit Excel internal memory limits.

> Our company uses fairly modern computers with, for example, 1GB RAm
> typically and the programs I'm making use Excel 2003 to drive AutoCAD,
> which
> means both those programs are running at the same time when the programs
> are
> being used.
>

I am not an autocad user so dont know how much memory it needs: suggest you
check using Task Manager. In any case I assume that autocad could be paged
out if neccessary bevause it wont be executing at the same time?

I assume you are turning off screenupdating and calculation at start then
restoring at end.

regards
Charles
______________________
Decision Models
FastExcel 2.2 Beta now available
www.DecisionModels.com

Thank you for your detailed, and very helpful response.

AutoCAD and Excel displays are alternately turned on and off at different
times during my program's execution depending on which needs to be visible
(Excel needs to visible when the forms are used, for example, but not when
it's switching sheets). But both programs need to be on throughout execution
so that means their both using memory. Not sure what "paging out" is or how
I'd do it?

Thanks again.
Tony



"Charles Williams" wrote:

> Answers interspersed
>
> > I've written 2 fairly large Excel programs (each having over 1000 lines of
> > code and several forms) and I'm concerned about optimizing memory/speed as
> > these programs continue to grow. I have some specific questions
> >
> > 1) Is it better to store temporary data within arrays in the code or to
> > send
> > data to worksheet cells as much as possible?
>
> There is a high overhead time for sending data to/from VBA and Excel, and
> memory useage is probably higher on an Excel sheet because of the number of
> properties each cell has. so keep temporary data in arrays.
>
> >
> > 2) Is it better to store text within the program code or to gather it
> > from
> > some separate workbook containing the text? The text I'm talking about
> > comes
> > from comments in my code that will become headings to rows and columns and
> > comments in those headings.
> >
>
> Store text and formats as named ranges on a sheet in the workbook (.xls or
> ..xla) that contains the code. These can include comments. Then copy the
> named ranges to the workbook you are generating.
> This simplifies maintenance etc.
>
> > 3) I tend to provide extensive documentation comments within my code for
> > future programmers and myself. Do i pay a price in memory or speed for
> > doing
> > this?
> >
> No. (except that the .xls /.xla containing the code will be slightly larger
> so will open slightly slower)
>
> > 4) I've stayed away from producing Excel charts in some cases because I
> > guessed they would degrade speed/memory usage. Without those charts the
> > workbooks generated by my programs are typically 500K in size. Am I being
> > too cautious?
> >
> Bit of an open-ended question, but generating up to say 10 charts I doubt
> that you would notice the difference. Generating Charts is fast but you can
> hit Excel internal memory limits.
>
> > Our company uses fairly modern computers with, for example, 1GB RAm
> > typically and the programs I'm making use Excel 2003 to drive AutoCAD,
> > which
> > means both those programs are running at the same time when the programs
> > are
> > being used.
> >
>
> I am not an autocad user so dont know how much memory it needs: suggest you
> check using Task Manager. In any case I assume that autocad could be paged
> out if neccessary bevause it wont be executing at the same time?
>
> I assume you are turning off screenupdating and calculation at start then
> restoring at end.
>
> regards
> Charles
> ______________________
> Decision Models
> FastExcel 2.2 Beta now available
> www.DecisionModels.com
>
>
>