|
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
|
|