troubleshooting Question

EXCEL VBA App Memory Leak

Avatar of GeekMan
GeekMan asked on
Visual Basic Classic
17 Comments1 Solution3069 ViewsLast Modified:
EXCEL produces a virtual memeory error after approximately 8 hours of continual use. The EXCEL process in task manager shows usage sometimes in excess of 100,000K.

App Description:
From a Visual Basic standpoint, my application is relatively simple. It is an automated Rolodex allows a user to search a database (SQL Server) for names and paste the associated telephone numbers into an EXCEL Spreadsheet.

There are a few additional features that allow a user to build filters and bull-lists.

The application has a class object that performs all the database interaction. The class Object uses the ADO connection and recordset Objects to connect to the database.

Recordsets are converted to a Complex array structure (Array of Arrays) which are passed on to the application. The recordset and connection Objects are then destroyed within the class. Recordset size is 3 fields max. and around 5,000 rows.

The app is fired from an imbedded button on "Sheet1" of the WorkBook. Once the app pastes the phone numbers, The class object is destroyed and the program Ended. The Spreadsheet persists.

The master application resides on a server where a shortcut is created from each of the client workstations.

What we have found so far:
I had a senior colleague look at the code with me and together we did not find any sloppy housekeeping with regard to the ADO Objects.
We placed the app in a loop and were unable to simulate the memeory escalation problem on our test machines.
Perhaps we are dancing around the problem or are too close to see the obvious.
Minimizing the application will release the memory usage!

Does anyone have any leads as far as what we should be considering? Is EXCEL/VBA doing a poor job of managing memory for the App? Is this a diplay thing??
Does that fact that the app is share from a central server have any bearing? Could there be underlying database issues that are actually causing this??
Again the problem is not immediate, it takes about 8 hours or so of constant use to create the issue.

Any help would be appreciated.
IT Operations

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 17 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 17 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros