passing VBA Array from excel to Delphi DLL as SAFEARRAY

[Darryl Mullins]

I see most of the questions are going from delphi to excel but this one is going the otherway.

I have an excel VBA module that needs to call a DLL built in delphi.  For single value functions every thing works of fine.  You can pass in one or two values and get a return value.  Where my problem comes from is passing an array.  The excel developers guide states that VBA passed arrays as a special OLE2 data type called a SAFEARRAY.  When OLE automation passes a SAFEARRAY to the DLL you get a pointer to a pointer to the array itself (yes a pointer to a pointer).  Once you have the safearray there are functions built into the OLE2.pas file in delphi that allow you to get bounds of array and access elements of the array.  The samples in the xl Dev guide are in C and will work but I want to use delphi.

Below is the Code.
This function
    myhresult := SafeArrayGetUBound(aintValues, 1, lUbound);
should return the Upper bound of the array telling me the size.  It does not.
This function
    myhresult := SafeArrayGetElement(aintValues, intValueCount, intSAValue);
should return element intValueCount from safearray aintValues into intSAValue.  Id does not.

I think part of the problem comes from the pointer to the pointer.  I do not know how delphi dereferences a pointer to a pointer.

This is the section from the OLE2.pas where the PSafeArray is built.
PSafeArray = ^TSafeArray;
  {$EXTERNALSYM tagSAFEARRAY}
  tagSAFEARRAY = record
    cDims: Word;
    fFeatures: Word;
    cbElements: Longint;
    cLocks: Longint;
    pvData: Pointer;
    rgsabound: array[0..0] of TSafeArrayBound;
  end;
  TSafeArray = tagSAFEARRAY;
  {$EXTERNALSYM SAFEARRAY}
  SAFEARRAY = tagSAFEARRAY;

The PSafeArray = ^TSafeArray; seems to tell me that it is getting a pointer not a pointer to a pointer.  It should be more like this:PSafeArray = ^^TSafeArray but that does not work.  (In c it is passed in as SAFEARRAY **psa and used as *psa)

Any help on this would be much appreciated

Thanks

Darryl Mullins

This is my unit

unit SumArray;

interface

uses
   OLE2, Dialogs;
 

var
   a: integer;
   function SumRange(aintValues: PSafeArray): integer; StdCall;

implementation
exports
  SumRange        index 1;

function SumRange(aintValues: PSafeArray): integer; StdCall;
var
   intSum: integer;
   lUbound: Longint;
   intValueCount: integer;
   intSAValue: integer;
   myhresult: HRESULT;

begin
   lUbound := 0;
   intSAValue := 0;

   try

      myhresult := SafeArrayGetUBound(aintValues, 1, lUbound);
      //function SafeArrayGetUBound(psa: PSafeArray; nDim: Integer; var lUbound: Longint): HResult; stdcall;

      intSum := 0;
      for intValueCount := 0 to 9 do
      begin
         myhresult := SafeArrayGetElement(aintValues, intValueCount, intSAValue);
         //function SafeArrayGetElement(psa: PSafeArray; const rgIndices; var pv): HResult; stdcall;

         intSum := intSum + intSAValue;
      end;

   except
      ShowMessage('Erorr');
   end;

   SumRange := intSum;
end;

end.

This is my VBA

Declare Function SumRange Lib "D:\Download\Delphi\XLArray\XLArray.dll" (lpStore() As Integer) As Integer
 

Sub TestSumRange()

    Dim intStore(10) As Integer
    Dim intResult As Integer
 
    'intStore = 0
    For intRow = 0 To 9
        intStore(intRow) = ThisWorkbook.Sheets("Sheet1").Range("D" & intRow + 1)
    Next intRow
 
    intResult = SumRange(intStore())
    ThisWorkbook.Sheets("Sheet1").Range("E11").Value = intResult
End Sub

[Binh Ly]

This in C:

foo (SAFEARRAY **psa);

is this in Delphi

type
PPSafeArray = ^PSafeArray;

foo (psa: PPSafeArray);

or

foo (var psa: PSafeArray);

have fun
--
Binh Ly
http://www.techvanguards.com

"Darryl Mullins" <dmul...@forzani.com> wrote in message
news:39E787DE...@forzani.com...