Memory Leak or Excel behaviour

Add-in Express™ Support Service
That's what is more important than anything else

Memory Leak or Excel behaviour
 
Micha? G??bowski


Guest


Dear Sirs,
Every run of code below increases memory usage by 6-8MB when 140 000 cells are selected (Excel memory shown by task manager, W7/64, Office 2007/32). Here is the code.

procedure TetpClean.bMemoryTestClick(Sender: TObject);
var
  ARange     : ExcelRange;
  AreaData   : OleVariant;
begin
  ARange     := ExcelApp.ActiveWindow.RangeSelection;
  if ARange   = nil then exit;
  with ARange.Areas[1] do
  begin
    AreaData := Value;
    // here any code to deal with AreaData[r, c] in a loop
    Value    := AreaData;  // here it leaks
    AreaData := Unassigned;
  end;
  ARange     := nil;
end;


Could you tell me how to free memory on Delphi side to fix the problem?
Posted 26 Feb, 2016 05:05:17 Top
Andrei Smolin


Add-in Express team


Posts: 18793
Joined: 2006-05-11
Micha?,

Try to use SafeArrayDestroy instead of AreaData := Unassigned to release the SAFEARRAY behind AreaData.


Andrei Smolin
Add-in Express Team Leader
Posted 26 Feb, 2016 06:51:20 Top
Micha? G??bowski


Guest


Thank you Andrei for the hint,
I tried to use
SafeArrayDestroy(@AreaData);
but it does not help much. According to docs, that function works on PVarArray, but defining AreaData as TVarArray does not allow me to use AreaData := Value assignment. Another try is PSafeArray (Ole2) but I still don't have idea how to obtain Value (of range) then.

I thought such data exchange between Excel and Delphi shall be quite common and you could have provided an example, how to handle with OleVariant arrays gracefully.

Waiting patiently for a solution.
Michal
Posted 26 Feb, 2016 08:21:03 Top
Andrei Smolin


Add-in Express team


Posts: 18793
Joined: 2006-05-11
We will find a solution and respond on Monday.

Have a nice weekend.


Andrei Smolin
Add-in Express Team Leader
Posted 26 Feb, 2016 09:31:23 Top
Andrei Smolin


Add-in Express team


Posts: 18793
Joined: 2006-05-11
Hello Micha?,

After studying and testing this we think that your code is correct (contrary to what I said earlier; sorry for this). We cannot reproduce the issue in XE2 and XE3. That is, the memory usage does grow but then it decreases to the same (or almost the same) level.


Andrei Smolin
Add-in Express Team Leader
Posted 29 Feb, 2016 08:05:24 Top
Micha? G??bowski


Guest


Hello Andrei,
Thank you for your investigation. I wish you had better news. I am using XE7 and there it leaks. I have also installed XE2 and 2006 so I will try to test it on XE2 and if it helps the problem will be solved, or at least relocated to Embarcadero.
Michal
Posted 29 Feb, 2016 18:25:09 Top
Andrei Smolin


Add-in Express team


Posts: 18793
Joined: 2006-05-11
Hello Micha?,

We've tested this with XE7 and we cannot reproduce the issue. In our tests, the memory gets released sooner or later.


Andrei Smolin
Add-in Express Team Leader
Posted 01 Mar, 2016 04:00:26 Top
Micha? G??bowski


Guest


Hello Andrei,
I have made separate add-in only with this test functionality, and compiled it also under XE2 (and XE7). Then I checked it on Windows 7 (x64)/Office 2007 and Windows 7 (32bit)/Office 2013.

First run of code below takes about 7 MB, every next click takes next 2 MB. I waited half an hour but little memory was released (i.e. I started with 52 MB, clicked Leak several ties to get 100 MB and after some minutes it went down to 86 for a while, then came back to 100MB).

procedure TAddInModule.adxRibbonTab1Controls0Controls0Click(Sender: TObject;
  const RibbonControl: IRibbonControl);
var
  ARange       : ExcelRange;
  AreaData     : OleVariant;
begin
  ARange       := ExcelApp.Range['A1:A140000', EmptyParam];
  AreaData     := ARange.Value;
  // code to alter AreaData[r, c] ...
  ARange.Value := AreaData;   // here it leaks
  ARange.Value := Unassigned; // does not release any byte
  ARange       := nil;
end;

Did you check any special Excel version or which OS did you try?
Michal.
Posted 01 Mar, 2016 11:16:04 Top
Andrei Smolin


Add-in Express team


Posts: 18793
Joined: 2006-05-11
Hello Micha?,

We run our tests on latest builds of Excel 2013 and 2016 32 bit on windows 8.1 64bit (all updates).

Note that passing an array to Excel and releasing the array may not release the memory that Excel allocates for new data. In the code below, the memory is released correctly.

  ARange     := ExcelApp.ActiveWindow.RangeSelection;
  if ARange   = nil then exit;
  with ARange.Areas[1] do
  begin
    AreaData := Value;
    ShowMessage('!!!');
  end;
  ARange := nil;



Andrei Smolin
Add-in Express Team Leader
Posted 02 Mar, 2016 08:51:28 Top