A DLL just needs to provide the xlAutoOpen function to become a valid XLL Add-In. It’s quite easy to say ‘Hello Excel!’ with Delphi that way. The xlAuto family is a set of interface functions Excel uses for XLL interaction.

The xlAutoOpen function is called by Excel whenever the Add-In is activated. It must return 1 for success. The following sample XLL displays a simple message box.

library XLLHello;

uses
  dXLCall, dXLUtils;

{$E XLL}

const s : ShortString = 'Hello World!';

var x : xloper;

function xlAutoOpen: integer; stdcall;
begin
  x.xltype:= xltypeStr;
  x.val.str:= @s;
  Excel4e(xlcAlert, nil, 1, [@x]);
  result:=1;
end;

exports
  xlAutoOpen;

end.

That’s not very useful. A second sample Add-in will demonstrate a XLL that registers a user defined function. This SampeFunc is registered as HelloFnc. It takes no arguments and returns always the same xltypeStr XLOPER.

The xlAutoOpen function is used to register the function. xlfRegister is called with essential parameters. As a result, the function is registered as worksheet function and will be displayed in the function wizard. You can call from a worksheet with =HELLOFNC(). The xlAddInManagerInfo function is implemented to tell Excel the Add-In’s name. HelloFnc is unregistered if the XLL is deactivated. This is done in the xlAutoRemove function. You don’t need to implement the xlAutoClose function to unregister functions. Excel will unregister the functions if they are not unregistered when xlAutoClose returns.

library XLLHello;

uses
  dXLCall, dXLUtils;

{$E XLL}

const
  XLLName : PAnsiChar = #12'Hello Add-In';
  Msg : PAnsiChar = #12'Hello World!';
  FncP : PAnsiChar = #10'SampleFunc';
  FncT : PAnsiChar = #01'P';
  FncF : PAnsiChar = #10'HelloFnc';

var
  isRegistered: Boolean;
  res, Arg1, Arg2, Arg3, Arg4 : xloper;

function SampleFunc: LPXLOPER; stdcall;
begin
  res.xltype:= xltypeStr;
  res.val.str:= Msg;
  result:=@res;
end;

function xlAddInManagerInfo(pxAction: LPXLOPER): LPXLOPER; stdcall;
begin
  if ((pxAction.xltype = xltypeInt)and(pxAction.val.w = 1))or
     ((pxAction.xltype = xltypeNum)and(pxAction.val.num = 1)) then begin
    res.xltype:= xltypeStr;
    res.val.Str:= XLLName;
  end
  else begin
    res.xltype:= xltypeErr;
    res.val.err:= xlerrValue;
  end;
  result:=@res;
end;

function xlAutoOpen: integer; stdcall;
begin
  if not isRegistered then begin
    Excel4e(xlGetName, @Arg1);        // dll name as pxModuleText
    Arg2.xltype:= xltypeStr;
    Arg2.val.str:= FncP;
    Arg3.xltype:= xltypeStr;
    Arg3.val.str:= FncT;
    Arg4.xltype:= xltypeStr;
    Arg4.val.str:= FncF;
    Excel4e(xlfRegister, nil, 4,      // registers the function
      [@Arg1, @Arg2, @Arg3, @Arg4]);
    Excel4e(xlFree, nil, 1, [@Arg1]); // free this string!
    isRegistered:= true;
  end;
  result:= 1;
end;

function xlAutoRemove: integer; stdcall;
begin
  if isRegistered then begin
    Excel4e(xlGetName, @Arg1);         // dll name as pxModuleText
    Arg2.xltype:= xltypeStr;
    Arg2.val.str:= FncP;
    Arg3.xltype:= xltypeStr;
    Arg3.val.str:= FncT;
    Arg4.xltype:= xltypeStr;
    Arg4.val.str:= FncF;
    Excel4e(xlfRegisterId, @res, 3,    // for Unregister
      [@Arg1, @Arg2, @Arg3]);
    Excel4e(xlfSetName, nil, 1, [@Arg4]);   // delete name
    Excel4e(xlfUnregister, nil, 1, [@res]); // unregister function
    Excel4e(xlFree, nil, 1, [@Arg1]);  // free this string!
    isRegistered:= false;
  end;
  result:= 1;
end;

exports
  SampleFunc,
  xlAddInManagerInfo,
  xlAutoOpen,
  xlAutoRemove;

begin
  isRegistered:=false;
end.

This should be enough hello if nobody’s responding. The next step could be the implementation of some useful functions that work similar to the functions provided in the framework library of the Microsoft Excel Software Development Kit.