Clear VBA memory cache.

[SQLBI]

Hi,

I have various routines that loop through approx 30,000 rows of a data in an Excel 2000 worksheet.

After a while of runtime the process begins to slow to a fraction of the speed it satarted at and i'm pretty sure this is a memory issue.

Is there a way that i can clear the VBA memory cache other than saving the file after each pass?

Would setting all the variables in the completed routine to 'Nothing' accomplish this?

Thanks in advance.

Leigh Moore
LJM Analysis Ltd

http://www.ljm-analysis.co.uk

 

[Luis939]

When I had a program using collection expert Zanthras told me that I should clear out those collections to nothing incase of memory leakage, which as the name implies means that memory fails to be allocated to the system (at least thats my best interpretation)

so at the end of my program i have a for loop setting each collection = nothing,
however he didnt say anything about the other variables, so I'm not sure, just try it I suppose

 

[jcrater]

I might be memory related, but I have had similar results in the past when I was processing many rows of data. Could it be that your logic is looping through a successively larger amount of data in each iteration? That would make it start out quickly and then become progressively slower. You might find that you can eliminate some unnecessary processing and speed up your routine.