Check for memory leaks in Excel VBA

[agarwaldvk]

Hi Everybody

This is my first post on this forum, so please bear with me if I haven't got everything right. In time, I will!

I have written a fairly large Excel based application - it actually has grown to become as big as it has now over a three (3) year period.

As the application goes through its cycle of processing record after record (row after row), it seems to slow down.

My understanding for this to be happening is that there is apparently some memory leak somewhere which I am not able to pinpoint. As a concequence of which, less and less memory is available to the program to do what it is supposed to be doing thereby has to go the disk more and more frequently - slowing the processing.

Is there a way to find out if there are any memory leaks and if yes, how do I go about do so?

Whilst there are some global variables, not a lot though and some global dynamic arrays (I erase these global dynamic arrays at the end of the process anyway), my understanding is that this couldn't be the contributing factor since if that were to be the case, then that would happen right at the start, wouldn't it?

Any suggestions, please?


Best regards


Deepak Agarwal

[Norie]

Deepak

Can you post your current code?

[agarwaldvk]

Noorie

Thanks for your quick response.

I would love but to honestly I can't - the reason not being that I don't want to but because it is 15000 lines of code spread over 32 module sheets.

That's exactly what the problem is - I don't know where to start checking for memory leaks!

Is there any other information etc. etc. I can provide you with to enable you to help you to help me!


Best regards


Deepak Agarwal

[JimmyTheHand]

Hi Deepak :hi:

If you want to share your code, you can do it via a free file delivery service. Google for the expression "upload your files" to find some such.
If I were you, I would compress your file into a password protected zip, upload somewhere, and post the download link here, along with the password.

Although, the "15000+ lines" is somewhat scary, someone might be willing and able to help.

Other than that, here's a layman's advice.

Scatter around some code-suspending messageboxes in your code, preferably into key positions, like before and after calling a large subroutine, or after every 1000 turns in a long running loop, etc.
Then open task manager, switch to process list, highlight Excel, and start your macro. Watch how Excel's memory consumption changes. Maybe this way you can delimit the problematic part of code.

Jimmy

[johnske]

I don't know what you're doing here, but 15000 lines of code seems definitely over the top...

First, try this (http://www.vbaexpress.com/kb/getarticle.php?kb_id=83&PHPSESSID=8f448957fbaa05683a7eb3c6e1d5eff0) and if that doesn't improve things, read this (http://www.vbaexpress.com/forum/showthread.php?t=9773) , paying particular attention to cleaning up unneccessary things on your PC (i.e. Toni Arts Easy Cleaner). These unnecessary items build up over time and really slow things down...