You need some basic objects to create Excel XLL Add-Ins with Delphi. At first, we need a structure to exchange data with Excel. Therefore, Microsoft describes the XLOPER and XLOPER12 structures inside the Excel Software Development Kit (SDK).

You should download and install the current Excel SDK to become a licensee. It contains very useful information. Please, take notice of the enclosed EULA. The Excel 97 Software Development Kit (3 parts!) is still available if you like.

The XLOPER type is a simple structure, which consists of two parts. That’s an 8-byte data part and a field, which defines the type of that data. It’s not recommended to use the packed keyword in your definition. The standard alignment {$A+} or {$ALIGN 8} makes sure that your record will get the right size. As a result, your XLOPER has a size of 16 bytes. You can read more about data alignment here.

You can do a line-by-line transformation. The variable data part, which is defined as a C union, will be converted to a variant record with the case statement. We can use the xltype constants within the case statement for better reading. Include Windows.pas to get no trouble with types.

unit dXLCall; // Delphi XLCALL

interface

uses
  Windows;

{$A+}

{...}

type
  LPXLOPER = ^XLOPER;
  XLOPER = record
    val : record
      case Word of
      xltypeNum:  (num  : Double);
      xltypeStr:  (str  : LPSTR);
      {...}
      xltypeSRef: (sref : record
                     count : Word;
                     ref   : XLREF;
                   end);
      {...}
    end;
    xltype: Word;
  end;

We can do the same with the XLOPER12 type. This type will result in a 32-byte structure. And we can use it to benefit from the features introduced with Excel 2007.

type
  LPXLOPER12 = ^XLOPER12;
  XLOPER12 = record
    val: record
      case Word of
      xltypeNum:  (num  : Double);
      xltypeStr:  (str  : LPWSTR);
      {...}
      xltypeSRef: (sref : record
                     count : Word;
                     ref   : XLREF12;
                   end);
      {...}
    end;
    xltype: LongWord;
  end;

implementation

end.

There are many pitfalls in converting C code. I suggest the good article I read on this topic. The Excel 97 Developer’s Kit gives advice on the size issue mentioned above (note on page 152.) But even the greatest book about Excel XLL Development assumes a size of 20 byte for a XLOPER12 (page 127.) I have cited a possible translation of the SRef-part in the exemplification for that reason. I count 18 bytes plus 2 bytes for alignment. The data part (val field) has a size of 24 bytes because the record is aligned to the largest alignment of the fields in the record (xltypeNum.)

At this point, we have caught the first objects and more to follow.