Excel Memory Leak

AndrewKent

Well-known Member
Joined
Jul 26, 2006
Messages
889
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
 

Some videos you may like

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

Hermanito

Well-known Member
Joined
Apr 4, 2007
Messages
1,238
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 :p) 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...
 

Watch MrExcel Video

Forum statistics

Threads
1,127,594
Messages
5,625,696
Members
416,127
Latest member
MALEPINZON

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top