Surprisingly, many visitors are interested in writing XLL add-ins for Excel. Today, I want to describe a Delphi sample add-in which adds a custom menu to the menu bar. This sample applies to Excel 11 and before. The custom menu will appear in the Add-Ins tab of new releases of Excel.

You cannot use XLL add-ins to add items to the Ribbon UI. Please, read my article about COM add-ins if you want to change the Ribbon. The following sample is based on the XLL add-in described here.

We start with adding a command which will be executed when an item of our custom menu is clicked.

function MenuCommand: integer; stdcall;
begin
  {...}
  result:=1;
end;

It is added to the exports list of the DLL. Next step is to register the command in the xlAutoOpen function. An additional call with xlfRegister is put to the function. Excel4v is called with six arguments. The fifth argument doesn’t matter for commands. But the sixth argument has a default value (1 = function) and we want to pass function type 2 = command.

const
  CmdP : String[20] = 'MenuCommand';
  CmdT : String[20] = 'J';
  CmdF : String[20] = 'SampleCommand';

function xlAutoOpen: integer; stdcall;
begin
  {...}
    // register menu command
    Arg2.val.str:= @CmdP;
    Arg3.val.str:= @CmdT;         // always "J"
    Arg4.val.str:= @CmdF;
    Arg5.xltype:= xltypeMissing; // not required for commands
    Arg6.xltype:= xltypeInt;
    Arg6.val.w:= 2;              // 2 = command
    Excel4e(xlfRegister, nil, 6, // registers the command
      [@Arg1, @Arg2, @Arg3, @Arg4, @Arg5, @Arg6]);
  {...}
end;

The xlAutoRemove function should unregister the command. Now, we add three routines which are used to add and remove a custom menu. First routine is a check if the custom menu already exists.

function CheckMenu: Boolean;
var
  res, mArg1, mArg2, mArg3: xloper;
begin
  result:= True;                  // menu does not exist
  mArg1.xltype:= xlTypeInt;
  mArg1.val.w:= 10;
  mArg2.xltype:= xlTypeStr;
  mArg2.val.str:= @cMenuItems[0]; // menu name or number
  mArg3.xltype:= xlTypeMissing;
  if Excel4e(xlfGetBar, res, 3,
     [@mArg1, @mArg2, @mArg3]) = xlretSuccess then
    if res.xltype <> xlTypeErr then
      result:= False;             // menu exists
end;

It calls the xlfGetBar function. This function returns an error value if the menu does not exist. Parameters are the bar id of the menu bar (10 = worksheet menu), the name or number of the menu (name is more save because number may change), and the menu position. The number or name of the menu is returned if menu position is passed as xlTypeMissing.

const
  cMenuItems : array[0..23] of String[20] =
  ('M&y Xtray Menu', '','','',
   'Menu &Item 01', 'SampleCommand', '', 'shows a msgbox',
   'Menu I&tem 02', 'SampleCommand', '', 'shows a msgbox',
   '-', '','','',
   'Menu It&em 03', 'SampleCommand', '', 'shows a msgbox',
   'Menu Ite&m 04', 'SampleCommand', '', 'shows a msgbox');

procedure AddMenu;
var
  mArg1, mArg2, mArg3, mArg4: xloper;
  mArgX: array[0..23] of xloper;
  i: Integer;
begin
  if CheckMenu then begin // check if already created
    mArg1.xltype:= xlTypeInt;
    mArg1.val.w:= 10;             // default menu bar
    mArg2.xltype:= xlTypeMulti;
    mArg2.val.xarray.columns:= 4; // size of
    mArg2.val.xarray.rows:= 6;    // array
    mArg2.val.xarray.lparray:= @mArgX[0];
    for i:=0 to 23 do begin
      mArgX[i].xltype:= xlTypeStr;
      mArgX[i].val.str:= @cMenuItems[i];
    end;
    Excel4e(xlfAddMenu, nil, 2,
            [@mArg1, @mArg2]); // add menu
    //*************************************************
    mArg3.xltype:= xlTypeStr;
    mArg3.val.str:= @cMenuItems[0]; // menu to add sub
    mArg4.xltype:= xlTypeInt;
    mArg4.val.w:= 4;                // pos for sub
    Excel4e(xlfAddMenu, nil, 4,
      [@mArg1, @mArg2, @mArg3, @mArg4]); // add submenu
  end;
end;

A custom menu is added with a call to the xlfAddMenu function. Parameters are the bar id and an array (xlTypeMulti) which describes the menu. Optional parameters are the menu and the position within the menu where array items are to be placed. The example routine creates a xlTypeMulti from a string array. Item names can include an ampersand to get a letter with underscore. Dashes as item names create a separator line. The menu is added at the end of the worksheet menu bar. A second call to xlfAddMenu is used to create a sub menu in the just created menu.

procedure DelMenu;
var
  mArg1, mArg2: xloper;
begin
    mArg1.xltype:= xlTypeInt;
    mArg1.val.w:= 10;               // default menu bar
    mArg2.xltype:= xlTypeStr;
    mArg2.val.str:= @cMenuItems[0]; // menu to delete
    Excel4e(xlfDeleteMenu, nil, 2,
      [@mArg1, @mArg2]);            // delete
end;

The DelMenu routine calls xlfDeleteMenu to delete the custom menu from the menu bar. Bar id and menu name are passed in. You could store the menu position when you create the menu and pass in the menu position to xlfDeleteMenu. But, because other add-ins could change the position of your menu it’s more save to use the menu name. Last but not least, a call to AddMenu is added to xlAutoOpen and a call to DelMenu to xlAutoRemove.

You can download the full source from the download page. Feel free to post any comment.