MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Garbage Collection or Memory Issues

  Asked By: Josefina    Date: Sep 04    Category: MS Office    Views: 5010

I am currently writing complicated Excel VBA Code. The program itself
uses multiple named ranges to query the spreadsheet.

It roughly loops through 620 rows in a spreadsheet. Based on the
value of the first cell in that row, it creates a SQL statement to run
against a named Range on another spreadsheet. The results from the
SQL statement are then used to calculate 11 ratios.

This is causing Excel to die (I think directly due to RAM issues). Is
there a way in Excel VBA to force garbage collection? Do you have any
suggestions on a better way to structure the file?

When you hear this, I know the first thought is to use a DB, but it's
not an option.



3 Answers Found

Answer #1    Answered By: Terry Williams     Answered On: Sep 04

I could not find a way to force garbage  collection, but there is a
similar problem discussed here:
They mentioned using DoEvents and looking at memory  usage in the
task monitor. There are also MemoryTotal, MemoryFree, and MemoryUsed
properties you can look at.

Off hand, your app does not seem like enough load to crash VBA. Are
you using external functions? One of them may be the culprit. Or you
may have created something recursive by mistake.

Answer #2    Answered By: Casey Montgomery     Answered On: Sep 04

I tried using the DoEvents and it doesn't seem
to be working. I don't use any external functions, and I don't think
that I have anything recursive. I may be wrong about that last one.
Just as further background, the Mem Usage continually grows and the
only way to clean it out is the actually close the Excel application
itself. Not just the file. If Excel is still open, it still takes up
the same amount of memory.

Do you think you might be able to check out some items if I send you
the main sections of code? If not no worries. Any other ideas would
be helpful.

Answer #3    Answered By: Jonathan Brown     Answered On: Sep 04

Just wanted to update this thread for future reference if anybody runs
into the same issue. It's actually tied to a bug that Excel has with
ADO queries. The article describes how to work around it.

Didn't find what you were looking for? Find more on Garbage Collection or Memory Issues Or get search suggestion and latest updates.