Memory Management In Excel Programming
 

Hello All,

I am working on an Excel project wherein a number of workbooks (upto 25
) are created every month and populated from a Master List - with say 500
rows each. I am running into serious memory issues. It would help to know how
Excel alocates and uses memory and frees it up once complete.

Im my particular application, I do a lot of block copy and paste -
possibly using the clip board (I use the copy and paste methods in VBA). How
do I release this memory when done.

Is there some guidance on good VBA programming for efficient memory
utilisation?

Jai

Memory Management In Excel Programming
 

1) You could copy and paste without the clipboard by using one line
mysourcerange.copy mydestinationrange

or 2) you could empty the clipboard after each use
Application.CutCopyMode = False

Additionally have you turned off screenupdating and auto calculation?

"Jai" wrote:

Hello All,

I am working on an Excel project wherein a number of workbooks (upto 25
) are created every month and populated from a Master List - with say 500
rows each. I am running into serious memory issues. It would help to know how
Excel alocates and uses memory and frees it up once complete.

Im my particular application, I do a lot of block copy and paste -
possibly using the clip board (I use the copy and paste methods in VBA). How
do I release this memory when done.

Is there some guidance on good VBA programming for efficient memory
utilisation?

Jai

Memory Management In Excel Programming
 

Thanks a ton for the pointer. I have since explored other commands too and I
now see how much is possible without selects and activates. It was hard going
though until I came to this forum. There doesn't seem to be much material
available by the way of a comprehensive Object model for Excel. Any pointers
on that ?

Jai


"Vacation's Over" wrote:

1) You could copy and paste without the clipboard by using one line
mysourcerange.copy mydestinationrange

or 2) you could empty the clipboard after each use
Application.CutCopyMode = False

Additionally have you turned off screenupdating and auto calculation?

"Jai" wrote:

Hello All,

I am working on an Excel project wherein a number of workbooks (upto 25
) are created every month and populated from a Master List - with say 500
rows each. I am running into serious memory issues. It would help to know how
Excel alocates and uses memory and frees it up once complete.

Im my particular application, I do a lot of block copy and paste -
possibly using the clip board (I use the copy and paste methods in VBA). How
do I release this memory when done.

Is there some guidance on good VBA programming for efficient memory
utilisation?

Jai

Memory Management In Excel Programming
 

Actually you can get full documentation of the object model from ms

best way to work in VBA is to search some keywords on this site and follow
the links posted in answers.

MVP sites are particularly helpful and trustworthy.

depends on what you are trying to do but ifyou have 1 hour to surf / research
you could start on Chip's link page

If you want a good book with lots of code examples - John's book is
available in the big book stores.
http://www-j-walk.com

http://www.cpearson.com/excel/links.htm



"Jai" wrote:

Thanks a ton for the pointer. I have since explored other commands too and I
now see how much is possible without selects and activates. It was hard going
though until I came to this forum. There doesn't seem to be much material
available by the way of a comprehensive Object model for Excel. Any pointers
on that ?

Jai


"Vacation's Over" wrote:

1) You could copy and paste without the clipboard by using one line
mysourcerange.copy mydestinationrange

or 2) you could empty the clipboard after each use
Application.CutCopyMode = False

Additionally have you turned off screenupdating and auto calculation?

"Jai" wrote:

Hello All,

I am working on an Excel project wherein a number of workbooks (upto 25
) are created every month and populated from a Master List - with say 500
rows each. I am running into serious memory issues. It would help to know how
Excel alocates and uses memory and frees it up once complete.

Im my particular application, I do a lot of block copy and paste -
possibly using the clip board (I use the copy and paste methods in VBA). How
do I release this memory when done.

Is there some guidance on good VBA programming for efficient memory
utilisation?

Jai