Delphi XLL Basics – Hello World!

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.

12 thoughts on “Delphi XLL Basics – Hello World!

  1. H.JUE LEE

    Hello. I’m Korean and a delphi man.
    I working xll on delphi thease days.
    Your greatful threads are very good contents.
    especially about xll contents is very useful to me.
    So I try to make DXLCALL.pas unit with your comment, but failed.
    my DXLCALL.pas cannot compile on 64platform.
    do you have an idea about to convert XLCALL.h to dXLCALL.pas or more hint?
    I hate my poor delphi…..

    Reply
    1. Edgar Post author

      You can take the dXLCall.dcu from sample project in the download section. A pre-compiled unit is included for Delphi XE2.

      Reply
  2. Vaclav

    Interesting articles, thanks.
    I’m looking for the information/example of writing XLL UDF (not using automation) which takes as parameters Excel RANGEs. Would you have some example of this? It would be very helpful.
    I think that XLL UDFs are where the non-COM approach gives most benefits – speed.
    Thank you again,
    Vaclav

    Reply
    1. Edgar Post author

      I will give you an example for dealing with Ranges as a parameter to a function. But, if you need the attributes of the range you will need its IDispatch interface. In that case, you will have to take the COM approach.

      Reply
  3. Vaclav

    No, all I need is the contents (data) of the range for some calculations inside the DLL (XLL) the reason being the speed increase compared to what I get using VBA. I use these functions for my calcs in Excel for bridge design.
    I have also looked at this article
    http://www.howtodothings.com/computers/a1263-writing-non-com-excel-add-ins-in-delphi.html
    which shows how to make XLL in Delphi but the example shows only the double parameter. So any example of using range as a parameter would be very helpful.
    Also a question regarding the dXLCall unit. I’m now on Delphi XE5 for which I can’t find the compiled unit in your downloads. Anyway, with the speed of updates of Delphi compilers, it could be a problem in the future – one can hardly expect/ask from you to compile the unit for every existing version of Delphi now and in the future. Obvious solution would be having the source code of the unit. As far as I know, the source is unavailable for download, is that correct? If so, is there a chance you could provide the compiled unit for XE5?
    Many thanks,
    Vaclav

    Reply
  4. Vaclav

    Yes, I have read it only briefly and will look at it more asap. But there’s a problem on my side – I don’t have any version of Delphi which you support with compiled dXLCalls (D7, 2007, 2009, 2010, XE, XE2) installed, I’m now on XE5 – see my earlier post. I will try to overcome this somehow but if you could help, it would be great.
    Thanks again,
    Vaclav

    Reply

Leave a Reply to H.JUE LEE Cancel reply

Your email address will not be published. Required fields are marked *