Excel Memory Leak

[AndrewKent]

Hi,

I have quite a large program that I'm working on that seems to be developing a few problems. Becuase the program is quite large it would be difficult to post up any specific examples of coding so instead I'm just looking for a bit of "theory" or a point in the right direction. Everytime a user loads up my program they are doing the following...

1) Loading data from Access into Excel
2) Carrying out a series of tasks that relate to our business, such as checking the data, editing it, printing out application forms populated with the data
3) Sending emails using the data
4) Sending the data back to Access from Excel

...now when this loop of routines completes, it take the user back to a starting userform (99% of all the userforms in my program are modal) and they start the loop again.

The problem is after about 6-7 of these loops the program starts getting slower and slower when it's trying to carry out its tasks (normally when writing back to Access). Now, at no point in this loop does the process end and a userform is always showing, that is until the user switches the program off. So my guess is that when my routines are using memory, I don't have a way to free it up. I know this because if the user shuts the program and re-opens (or when I Alt-F4 and break the loop then F5 in VBE) it instantly goes back to the speed it would at first time use.

Now, there are places in my program where I have made mistakes, using public variables and NOT setting them to Empty after use would be an example.

So, with all this in mind I know I have my work cut out and don't have clear sight of what the issue is.

Firstly I would ask, is there ANY routine that will CLEAR Excel's memory usage without having to "un-declare" every variable that I have set (long shot I know!), as it would if my program was closed or encountered an "End"

Secondly, If anyone has any advice I would be MOST grateful!

Andy

 

[AndrewKent]

I found this link:

http://www.vbaexpress.com/kb/getarticle.php?kb_id=793

is this clearing the memory usage in one go?

Andy

 

[Hermanito]

The code in that link seems overly complicated and it would not really help you further since you would still need to pass every variable you want to set to nothing into the function. It doesn't magically find them for you...
In my opinion, if you do the research to know what to pass into the function, you might as well just set them to Nothing yourself.

Also, you only need to release object variables, not simple types like String, Double, Integer, etc.
Object types are those you assign with using the Set keyword, like Worksheet, Range, Workbook, etc...
When you don't need those anymore, it is good practice to set them to Nothing. Normally Excel cleans up after itself, but it can get sloppy, in my experience especially when you are instantiating your own forms instead of using the default instance.

I guess you will have to go through most of your code to find the issues...

If your workbook doesn't contain information you cannot share, and you are okay with showing your code, I'm willing to have a look and maybe (can't guarantee anything ) give some pointers on improvements...

If you would like to send the file to me, PM me and I will give you my email-address...