Memory Leak

[Sakkie]

I am using a VBA macro in Excel 2003 to download data from a server.
The macro opens up a workbook containing equations (functions) in an
Add-In provided by the company who's software is running on the server.


So the process is:
1. Opening the workbook containing the equations pointing to the Add
In, the functions are updated (i.e. data downloaded from server).
2. Copy the results, pastes it in a new workbook (as values), save the
new workbook and close it.

This process is repeated.

I cannot edit or see the code of the Add In (password protected). The
Add In apparently has a memory leak, because my memory is just filling
up (after a +- 30 cycles, I cannot open a new workbook any more - "Out
of memory"). I am sure it is not my code. If I replace all the
equations downloading data from the server with "Rand()", i.e. having
the same number of cells with the same amount of data, my memory usage
stays constant for endless(?) cycles.

So, what I need is some sort of workaround.

Closing Excel clears the memory.

I tried just de-selecting the add-in and then reselecting it
(Tools-->Add-Ins), this does not work.

So I think what I need is somehow to automatically close Excel, re-open
it and cary on with the macro. Any ideas?

 

[NickHK]

If you are convinced it is a memory leak then you can create an instance of
Excel for you add-in/processing to work with.
Close this instance every 10-15 runs and reopen another instance.
e.g. from an Excel VB

Const RUNS_REQUIRED As Long = 100
Dim InstanceCount As Long

Const MAX_RUNS As Long = 15
Dim RunCount As Long

Dim XLApp As Excel.Application

For InstanceCount = 1 To RUNS_REQUIRED / MAX_RUNS
Debug.Print InstanceCount
Set XLApp = New Excel.Application

'load the add-in, as is not loaded by default with automation

For RunCount = 1 To MAX_RUNS
'processing code
Debug.Print "Process : " & RunCount
Next

XLApp.Quit
Next

You would obviously need to calculate the number of loops correctly.

NickHK

 

[Sakkie]

Thanx for the advice Nick. I finally got around to testing this. It
works, but it doesn't...

I open a new instance of Excel as suggested:

Set xlAppNewInstance = CreateObject("Excel.Application")

xlAppNewInstance.Visible = True

Then I open the file that uses the Add-Inn in the new instance:

xlAppNewInstance.Workbooks.Open (HourTemplateFile)

This works fine, now the next problem is the Add-In is not
automatically loaded in the new instance of Excel. I tried to open it
the same way I opened the workbook above:

xlAppNewInstance.Workbooks.Open (AddInFile)

This does not seem to work, no idea why, It does not make the functions
of the Add-In available. If I interrupt the VBA code here and in the
new instance of Excel manually open the file "AddInFile), the functions
are available and everything works fine if I allow the code to
continue.

Does all of this solve the "Memory leak" problem?
Interestingly enough, when the new instance of excel is closed, the
memory used is NOT returned; it is only freed when the procedure that
opened the new instance ends. Hmmm

Any ideas on how to get that Add-in to work without manually
interfering would be greatly appreciated.

 

[NickHK]

Using automation, does not automatically load add-ins, you have to do that
yourself.
http://support.microsoft.com/kb/280290

As for you "memory leak", it sounds like you are still retaining a reference
to Excel somewhere in your code and consequently Excel cannot close.
Do not use unqualified references i.e. make sure everything goes through
your xlAppNewInstance variable.

NickHK

 

[Sakkie]

Hi Nick

Once again, thanks for the good advice. I got a new instance of Excel
open, opened my workbook and installed the add-in (for some strange
reason I had to set the installed parameter of the ad-in to false and
then to true, otherwise it would not work):

oAddin = xlAppNewInstance.AddIns.Add(AddInFile)
oAddin.Installed = False
oAddin.Installed = True

I had to set the sisplay alerts to false, so I do not get interupted by
messages during the "installation" of the add-in:

xlAppNewInstance.DisplayAlerts = False

Now my next problem is that I need to set the calculation mode to
manual for the new instance of excel. I tried the following:

xlAppNewInstance.Calculation = xlCalculationManual

But I get a "Run-time error 1004: Method 'Calculation' of object
'_Application' failed".

I do not realy understand this, as the "xlAppNewInstance.DisplayAlerts
= False" works instead of "Applicatio.DisplayAlerts = False", but the
"xlAppNewInstance.Calculation = xlCalculationManual " does not?

Sakkie

 

[Sakkie]

OK, don't worry, I solved it. I should have applied the advice given in
the article you pointed me to for this problem as well:

"Note that a workbook is added prior to calling the Add method in this
example. The Add-in Manager in Excel is not available unless there is
at least one visible workbook open. Without this line of code, you
receive one of the following error messages:
Run-time error '1004':
Unable to get the Add property of the AddIns class
-or-
Run-time error '1004':
Add method of addins class failed. "

http://support.microsoft.com/kb/280290

So all I did is I first opened my workbook and then set calculation to
manual, works like a charm