[Excel][DLL] Writing a DLL custom function for Excel with Delphi

[Stéphane Santon]

Hi,

Sorry for my language...

Here are results about my searches (Win98SE, Excel 2000, Delphi 5):

1. -----------------------------------------------------------
* Creating an VBA-Excel Custom function which calls the DLL :

The purpose is to convert the Range-Type parameters to arrays of Variant
to :
- get faster access,
- allocate memory for the result,
- give this parameters ti the DLL function.

a) +++++++++ VBA :

Declare Function DLL_Matrix_Evolutions Lib "ExcelProd_Functions.dll" _
(ByRef avHeader As Variant, ByRef avTable As Variant, _
ByRef avDeparts As Variant, ByRef avResults As Variant) As Boolean

Function Matrix_Evolutions( arHeader As Range, arTable As Range, _
arDeparts As Range) As Variant
Dim laHeader() As Variant, laTable() As Variant,
Dim laDeparts() As Variant
Dim liLoRow&, liHiRow, i&

laHeader = arHeader 'Copy to variant to get faster access
laTable = arTable
laDeparts = arDeparts

liLoRow = LBound(laTable)
liHiRow = UBound(laTable)
ReDim laResults(liLoRow To liHiRow, 1 To 2)

DLL_Matrix_Evolutions laHeader, laTable, laDeparts, laResults

Matrix_Evolutions = laResults
End Function

b) +++++++++ Delphi :

function DLL_Matrix_Evolutions( var avHeader : OleVariant;
var avTable : OleVariant;
var avDeparts: OleVariant;
var avResults : OleVariant): Boolean;
stdcall;
var
liLoRow, liHiRow, liRow : Integer;
begin
liLoRow:= VarArrayLowBound( avTable, 1);
liHiRow:= VarArrayHighBound( avTable, 1);

for liRow := liLoRow to liHiRow do
avResults[ liRow, 1]:= liRow + liHiRow/100000;

Result:= true;
end;


2. -----------------------------------------------------------
* Creating and calling directly a DLL custom function

a) +++++++++ VBA :

Declare Function DLL_Evolutions Lib "ExcelProd_Functions.dll" _
(ByRef avHeader As Variant, ByRef avTable As Variant, _
ByRef avDeparts As Variant) As Variant

b) +++++++++ Delphi :

function DLL_Evolutions( var avHeader : OleVariant;
var avTable : OleVariant;
var avDeparts: OleVariant) : OleVariant;
stdcall;
var
liCols, liRows, i, j: Integer;
lvValue: OleVariant;
lsStr: String;
begin
liCols:= 2;
liRows:= avTable.Rows.Count;

result:= VarArrayCreate( [1, liRows, 1, liCols], varVariant);

for i:= 1 to liCols do
for j:= 1 to liRows do begin
lvValue:= avTable.Cells.Item[ j, 1].Value;
lsStr:= lvValue;
lsStr:= IntToStr( j) + '.' + IntToStr( i) + ':'+ lsStr;
result[ j, i]:= lsStr;
end;
end;

It runs !

c) +++++++++ The problem and question :

How does work memory management ?

Is the 'result'-variable created by VarArrayCreate freed by Delphi or by
Excel or by ???


Thank you for your help.