Passing Array to Delphi DLL from Excel?

[josefl]

How can I pass an array to a Delphi DLL from Excel? I've read that u need to do it like this:

Delphi
Function Testing(MyArray:Array of Double; NoItems:Integer)ouble;stdcall;
begin
{somehow read the array and do something with it}
end;

Excel
Declare Function Testing Lib "ExcelTest" (ByRef MyArray as Double, ByVal NoItems as Integer)

Function Test(MyArray,NoItems)
ReDim MyTempArray(NoItems)
For a=1 to NoItems
MyTempArray(a-1)=MyArray(a-1)
Next a
Test = Testing(MyTempArray(0),NoItems)
End Function


This will put the array in sequential order in memory. You send only the first record. However, I can't get it to work. Excel locks up. Heeeeelp! What can I do? Any crum is valued dearly.

 

[rfedyk]

It sounds like a memory problem. The following notes are directly out of Delphi's help and may be of assistance to you.


"On Windows, if a DLL exports routines that pass long strings or dynamic arrays as parameters or function results (whether directly or nested in records or objects), then the DLL and its client applications (or DLLs) must all use the ShareMem unit. The same is true if one application or DLL allocates memory with New or GetMem which is deallocated by a call to Dispose or FreeMem in another module. ShareMem should always be the first unit listed in any program or library uses clause where it occurs.

ShareMem is the interface unit for the BORLANDMM.DLL memory manager, which allows modules to share dynamically allocated memory. BORLANDMM.DLL must be deployed with applications and DLLs that use ShareMem. When an application or DLL uses ShareMem, its memory manager is replaced by the memory manager in BORLANDMM.DLL."

 

[Ragnafar]

There's no strings there.

 

[josefl]

Thanks for your replies. I've tried using the sharemem, but I can't really tell excel to use sharemem. It didn't work. In C++, you re-write the array and send only the first object. Because it sequential in memory, the DLL can now read it. This is what I've been trying for a while, with no success. It's really frustrating.

 

[Ragnafar]

You don't need ShareMem, that's only for if you're passing dynamic strings, you have no strings in your function.

 

[petroffp]

Several years ago I had to write a delphi DLL COM object that accepted Excel "Ranges" in the function calls. Here is an excerpt that I tried to trim down (haven't checked that this snippet compiles). Can't remember exactly how to make this work in a non-COM DLL, but I think the key is passing OLEVariants.


//DELPHI CODE
TCompensationHistory = class(TAutoObject, ICompensationHistory)
private
fData : array of Double; // converted data from data provided by user

protected
function Get_Count: Integer; safecall;
procedure Set_Data(Value: OleVariant); safecall;
public
procedure Initialize; override;
destructor Destroy; override;
end;



procedure TCompensationHistory.Set_Data(Value: OleVariant);
var
I, J : Integer;
unk : IUnknown;
r : Range;
validNum : Integer;

begin
fData := nil; // reset
validNum := 0;

case varType(Value) of
varDispatch : begin
unk := Value;
try
r := unk as Range;
except
raise Exception.Create('Excel Range is only object supported. Passed unsupported object.');
end;

/ Now all properties of the Excel Range Object are available
For I := 1 to r.Rows.Count
for J := 1 to r.Columns.Count do
begin
// Process the data in r.Item[I,J].value
If vartype(r.Item[I,J]) = varDouble then
begin
fData[ValidNum] = r.Item[I,J].value;
Inc(ValidNum);
end;
end;
end;
if validNum > 0 then
fDataPassed := True;

end;

procedure TCompensationHistory.Initialize;
begin
inherited Initialize;
fData := nil;
fDataPassed := False;
end;

destructor TCompensationHistory.Destroy;
begin
fData := nil;
inherited Destroy;
end;


function TCompensationHistory.Get_Count: Integer;
begin
if fDataPassed then
result := High(fData)+1
else
result := 0;
end;


'EXCEL CODE

Function CountDoubles(Salaries As Range) As Variant

Dim x As New CompensationHistory

x.Data = Salaries
Result = x.Count

end function