258 Excel Add-in Development in C/C++
4: CommandPosition: An optional argument specifying the
position of the menu item at which the command is to be
placed: a number or the text of an existing menu item. (The
n
th
separator line can be specified by a string of n dashes.)
5: SubMenuPosition: An optional argument specifying the
position on the sub-menu at which the command is to be
placed. This can be a number or the text of an existing
sub-menu item. (The n
th
separator line can be specified by a
string of n dashes.)
If CommandRef is simply the name of a built-in menu, the remaining arguments are not
required and the function restores the menu to its original default state, returning the
position number of the restored menu. To restore it to its original position, you need to
specify this in MenuPosition, otherwise it is placed at the right of the menu bar.
CommandRef is a horizontal array as that describes the menu to be added or extended
as shown in Table 8.25.
Table 8.25 Custom command definition array
Required columns Optional columns
Command text Command1 Name (not used) Status bar text Help reference
Notes:
• The array is the same as the 2nd (and subsequent) rows in the MenuRef array described
in the previous section.
• The first two columns are required.
• The second column contains the command name as passed to Excel in the 4th argument
to
xlfRegister or the name of some other command macro of VB function.
• If the command is not a recognised name Excel will not complain until the user attempts

cpp_xloper Menu("Tools");
cpp_xloper CmdRef(cmd_txt, (WORD)1, (WORD)2); // 1 row, 2 columns
xl4 = Excel4(xlfAddCommand, &RetVal, 3, &BarNum, &Menu, &CmdRef);
Example 2
The following code fragment adds a new command before the first separator on the Tools
menu.
char *cmd_txt[2] = {"&XLL command 1", "XLL_CMD1"};
cpp_xloper BarNum(10); // the worksheet menu bar
cpp_xloper Menu("Tools");
cpp_xloper CmdRef(cmd_txt, (WORD)1, (WORD)2); // 1 row, 2 columns
cpp_xloper CmdPos("-");
Excel4(xlfAddCommand, &RetVal, 4, &BarNum, &Menu, &CmdRef, &CmdPos);
Example 3
The following code fragment adds a new command to the end of the Macro sub-menu on
the
Tools menu.
char *cmd_txt[2] = {"&XLL command 1", "XLL_CMD1"};
cpp_xloper BarNum(10); // the worksheet menu bar
cpp_xloper Menu("Tools");
cpp_xloper CmdRef(cmd_txt, (WORD)1, (WORD)2); // 1 row, 2 columns
cpp_xloper CmdPos("Macro");
cpp_xloper SubMenuPos(0);
Excel4(xlfAddCommand, &RetVal, 5, &BarNum, &Menu, &CmdRef, &CmdPos,
&SubMenuPos);
Example 4
The following code fragment adds a new command to the end of the worksheet cells
short-cut menu (viewed by right-clicking on any cell).
260 Excel Add-in Development in C/C++
char *cmd_txt[2] = {"&XLL command 1", "XLL_CMD1"};
cpp_xloper BarNum(7); // the worksheet short-cut menu-group

8.11.8 Adding/removing a check mark on a menu command:
xlfCheckCommand
Overview: Displays or removes a check mark from a custom command.
Enumeration value: 155 (x9b)
Accessing Excel Functionality Using the C API 261
Callable from: Commands only.
Return type: Boolean or error.
Arguments: 1: MenuID: The menu bar ID number.
2: Menu: The menu as text or position number.
3: MenuItem: The command as text or position number.
4: DisplayCheck: A Boolean telling Excel to display a check if
true, remove it if false.
5: SubMenuItem: (Optional.) A sub-menu command as text or
position number.
The C API provides access to a more limited set of menu features than current versions of
Excel provide, and this function reflects this. With Excel 4.0, menus supported the display
of a check-mark immediately to the right of the command name as a visual indication
that something had been selected or toggled. The typical behaviour of such a command is
to toggle the check mark every time the command is run. This function, gives the add-in
developer access to this check-mark.
The function returns a Boolean reflecting the value that was set in DisplayCheck.
Example 1
The following code fragment toggles a check-mark on the custom command XLL command
1
on the Tools menu.
static bool show_check = false;
show_check = !show_check;
cpp_xloper BarNum(10); // the worksheet menu bar
cpp_xloper Menu("Tools");
cpp_xloper Cmd("XLL command 1");

If MenuItem is zero, the function enables or disables the entire menu provided that it
is also a custom menu. If SubMenuItem is zero and the specified MenuItem is a custom
sub-menu, the function toggles the state of the entire sub-menu.
Example 1
The following code fragment toggles the state of the command XLL command 1 on the
Tools menu.
static bool enable = false;
enable = !enable;
cpp_xloper BarNum(10); // the worksheet menu bar
cpp_xloper Menu("Tools");
cpp_xloper Cmd("XLL command 1");
cpp_xloper State(enable);
Excel4(xlfEnableCommand, &RetVal, 4, &BarNum, &Menu, &Cmd, &State);
Example 2
The following code fragment toggles the state of the command XLL command 1 on the
sub-menu
XLL on the Data menu.
static bool enable = false;
enable = !enable;
cpp_xloper BarNum(10); // the worksheet menu bar
Accessing Excel Functionality Using the C API 263
cpp_xloper Menu("Data");
cpp_xloper Cmd("XLL test");
cpp_xloper State(enable);
cpp_xloper SubMenuCmd("XLL command 1");
Excel4(xlfEnableCommand, &RetVal, 5, &BarNum, &Menu, &Cmd, &State,
&SubMenuCmd);
Example 3
The following code fragment toggles the state of the custom menu XLL test.
static bool enable = false;

Changing the name of a menu or command is a useful thing to do if the command’s
action is state-dependent and you want to reflect the next action in the command’s text.
This could be anything from showing a toggle that sets or clears some DLL state, or
may be more complex, cycling between many states. Such state-dependent commands are
particularly useful for managing background or remote processes.
If MenuItem is zero the menu is renamed. If the command could not be found the
function returns
#VALUE!, otherwise it returns true.
Example
The following code fragment changes the name of the command XLL command 1 on the
Tools menu.
static bool enable = false;
cpp_xloper BarNum(10); // the worksheet menu bar
cpp_xloper Menu("Tools");
cpp_xloper Cmd("XLL command 1");
cpp_xloper NewText("Ne&w name");
Excel4(xlfRenameCommand, &RetVal, 4, &BarNum, &Menu, &Cmd, &NewText);
8.11.11 Deleting a command from a menu: xlfDeleteCommand
Overview: Deletes a command or sub-menu from a menu.
Enumeration value: 159 (x9f)
Callable from: Commands only.
Return type: Various. (See below).
Arguments: 1: MenuID: The menu bar ID number.
2: Menu: The menu as text or position number.
3: MenuItem: The command as text or position number.
4: SubMenuItem: (Optional.) A sub-menu command as text or
position number.
If the command cannot be found the function returns
#VALUE!, otherwise it returns true
when deleting a custom command or an ID when deleting an Excel command. This ID

8.11.12 Deleting a custom menu: xlfDeleteMenu
Overview: Deletes a menu.
Enumeration value: 158 (x9e)
Callable from: Commands only.
Return type: Boolean or error.
Arguments: 1: MenuID: The menu bar ID number.
2: Menu: The menu as text or position number.
3: SubMenuItem: (Optional.) A sub-menu command as text or
position number.
Note:
Excel does not permit the deletion of short-cut menus, however, these can be
disabled and re-enabled with the
xlfEnableCommand function.
If the function cannot find or delete the menu, it returns
#VALUE!, otherwise it returns
‘true’.
266 Excel Add-in Development in C/C++
Warning: The action of SubMenuItem is intended, according to the XLM reference
manuals, to delete the specified sub-menu on the given menu. Instead it deletes the menu
itself. Use
xlfDeleteCommand to delete a sub-menu.
Note:
Remember to store the information needed to restore menus and undo changes,
especially when deleting built-in menus. Simply restoring Excel defaults may delete other
custom menu items.
Example 1
The following code fragment deletes the Data menu.
cpp_xloper BarNum(10); // the worksheet menu bar
cpp_xloper Menu("Data");
Excel4(xlfDeleteMenu, &RetVal, 2, &BarNum, &Menu);

first argument set to 1 and the second set to the ID, to obtain an array of all the tool
IDs on that toolbar.
The above section on customising menu bars provides a relatively easy way to provide
access to commands contained within the DLL if you need to.
8.12.1 Getting information about a toolbar:
xlfGetToolbar
Overview: Gets information about a toolbar.
Enumeration value: 258 (x102)
Callable from: Command and macro sheet functions.
Return type: Various. See Table 8.26 below.
Arguments: 1: InfoType: A number from 1 to 10 indicating the type of
information to obtain. (See table below.)
2: BarID: The name as text or the ID number of a toolbar.
Table 8.26 Information available using xlfGetToolbar
InfoType What the function returns
1 Horizontal array of all tool IDs on the toolbar. (Gaps = zero.)
2 Horizontal position in the docked or floating region.
3 Vertical position in the docked or floating region.
4 Toolbar width in points.
5 Toolbar height in points.
6 Docked at the top (1), left (2), right (3), bottom (4) or floating (5).
7 True if the toolbar is visible.
8 Horizontal array of toolbar IDs, names or numbers, all toolbars.
9 Horizontal array of toolbar IDs, names or numbers, all visible toolbars.
10 True if the toolbar is visible in full-screen mode.
Val ue s of InfoType 8 and 9 do not require a BarID argument.
8.12.2 Getting information about a tool button on a toolbar:
xlfGetTool
Overview: Gets information about a tool button on a toolbar.
Enumeration value: 259 (x103)

Optional for custom tools.
Tool ID Command
text
Default
state is
down
Default
state is
enabled
Face
graphic
reference
Status
text
Balloon
text
Help
topic
Tip text

Accessing Excel Functionality Using the C API 269
Note: Any arguments omitted from such a range should be passed as xloper array
elements of
xltypeNil.
Column notes (from left to right):
1. Can contain the ID of a built-in button, zero to represent a gap or the ID (text name
or number between 201 and 231 inclusive) of a custom tool.
2. The name of the DLL command as registered with Excel in the 4th argument of the
xlfRegister function.
3. A Boolean instructing Excel whether to display the button as depressed by default if

left if horizontal, or the top if vertical, at which tools are to be
inserted. Can be a built-in or custom button.
270 Excel Add-in Development in C/C++
3: Command: The name of the DLL command as registered with
Excel in the 4th argument of the
xlfRegister function.
If Command is omitted, the function removes the existing association between the tool
button and the command. If the button is a custom button then Excel prompts the user
to assign a command next time the button is pressed by displaying the Assign Macro
dialog. The user can manually enter a registered DLL command name to assign another
command if they wish. If the button is a built-in tool, the action reverts to the Excel
default action.
8.12.6 Enabling/disabling a button on a toolbar:
xlfEnableTool
Overview: Enables or disables a tool button on a toolbar.
Enumeration value: 265 (x109)
Callable from: Commands only.
Arguments: 1: BarID: A number of a built-in toolbar, or the text of a custom
toolbar.
2: Position: The position on the toolbar counting from 1 at the
left if horizontal, or the top if vertical, at which tools are to be
inserted. Can be a built-in or custom button.
3: Enable: A Boolean value enabling the button if true or
omitted, disabling it if false.
8.12.7 Moving/copying a command between toolbars:
xlcMoveTool
Overview: Moves or copies tools between toolbars and resizes drop-down
lists on toolbars.
Enumeration value: 33058 (x8122)
Callable from: Commands only.

3: Pressed: A Boolean value. The button is depressed if true, or
normal if false or omitted.
Note:
This function will not work on built-in buttons or buttons to which no command
has been assigned.
8.12.9 Displaying or hiding a toolbar:
xlcShowToolbar
Overview: Activates a toolbar.
Enumeration value: 32988 (x80dc)
Callable from: Commands only.
Arguments: 1: BarID: A number of a built-in toolbar, or the text of a
custom toolbar.
2: IsVisible: A Boolean value. The toolbar is visible if true,
hidden if false.
3: DockPosition: 1 top; 2 left; 3 right; 4 bottom; 5 floating.
272 Excel Add-in Development in C/C++
4: HorizontalPosition: The distance in points between the left
of the toolbar and (1) the left of the docking area if docked,
(2) the right of the right-most toolbar in the left docking area
if floating.
5: VerticalPosition: The distance in points between the top of
the toolbar and the top of (1) the docking area if docked,
(2) Excel’s workspace if floating.
6: ToolbarWidth: The width in points. If omitted, the existing
width is applied.
7: Protection: A number specifying the degree of protection
given to the toolbar. (See Table 8.29 below.)
8: ShowToolTips: Boolean. Mouse-over ToolTips are displayed
if true, not if false.
9: ShowLargeButtons: Boolean. Large buttons are displayed if

Callable from: Commands and macro sheet functions.
Arguments: 1: BarName: The text name of a custom toolbar
8.13 WORKING WITH CUSTOM DIALOG BOXES
IMPORTANT NOTE: The C API only provides access to the dialog capabilities of the
Excel 4.0 macro language which are very limited and awkward in comparison to those
of VB or MFC. The C API does not support different font sizes, colours, and lacks some
control objects: toggle buttons, spinner buttons, scroll bars, among others. Nevertheless,
getting input from users, say, to configure a DLL function or to input a username, is
something you might decide is most convenient to do using the C API. This section
provides a bare-bones description of the relevant functions. You should use an alternative
approach for more sophisticated interaction with the user.
8.13.1 Displaying an alert dialog box:
xlcAlert
Overview: Displays an alert dialog.
Enumeration value: 32886 (x8076)
Callable from: Commands only.
Return type: Boolean. See Table 8.30 below.
Arguments: 1: Message: The message text (max length 255 characters: the
limit of a byte-counted string).
2: AlertType: An optional number determining the type of alert
box. (See table below.)
3: HelpReference: An optional reference of the form
HelpFile!TopicNum. If this argument is given, a help button is
displayed in the dialog.
274 Excel Add-in Development in C/C++
Table 8.30 xlcAlert dialog types
AlertType Description Return value
1 Displays message with an OK and a Cancel button. True if OK pressed.
False if
Cancel pressed.

Usually blank,
with ref placed
in 7th col of
help button
Dialog
Horizontal
position
Dialog
Vertical
position
Dialog
width
Dialog
height
Dialog
name/title
[Default item
position]/Item
chosen as trigger
Item number Horizontal
position
Vertical
position
Item
width
Item
height
Item text Initial value/result

Accessing Excel Functionality Using the C API 275

Buttons
Selecting a cancel button (2 or 4) causes the dialog to terminate returning FALSE. Pressing
any other button causes the function to return the offset of that button in the definition
table in the 7th column, 1st row of the returned array.
276 Excel Add-in Development in C/C++
Where you just require OK and Cancel buttons, you should use either types 1 and 2
together, or 3 and 4, depending on which default action you want to occur if the user
presses enter as soon as the dialog appears.
If item width and/or item height are omitted, the button is given the width and/or height
of the previous button in the definition table, or default values if this is the first button in
the definition table.
Radio buttons
A group of radio buttons (12) must be preceded immediately by a radio group item
(11) and must be uninterrupted by other item types. If the radio group item has no text
label the group is not contained within a border. If the height and/or width of the radio
group are omitted but text is provided, a border is drawn that surrounds the radio buttons
and their labels.
List-boxes
The text supplied in a list box item row should either be a name (DLL-internal or on a
worksheet) that resolves to a literal array or range of cells. It can also be a string that
looks like a literal array, e.g.
"{1,2,3,4,5,\"A\",\"B\",\"C\"}" (where coded in
a C source file). List-boxes return the position (counting from 1) of the selected item
in the list in the 7th column of the list-box item line. Drop-down list-boxes (21) behave
exactly as list boxes (15) except that the list is only displayed when the item is selected.
Linked list-boxes
Linked list-boxes (16), linked file-boxes (18) and drop-down combo-boxes (22) should
be preceded immediately by an edit box that can support the data types in the list. The
lists themselves are drawn from the text field of the definition row which should be a
range name or a string that represents a static array. A linked path box (19) must be

7. Via the
Insert/Name/Define dialog, define the name DlgTest as a command and assign
a keystroke to it for easy running.
By modifying the contents of your named definition range and executing the command
macro, you can fairly easily design simple dialogs that can be recoded in C/C++ within
the DLL. (This is still a laborious process compared to the use of graphical design tools
such as those that now exist in VB.)
Creating a static initialisation of an array of
xlopers in C/C++, to hard-code your
table, is complicated by the fact that C only provides a very limited ability to initialise
unions, such as
val in the xloper. Section 6.9 Initialising
xlopers
on page 157
provides a discussion of this subject and an example of a dialog definition table for a
simple username and password dialog.
A more complex example dialog is included in the example project on the CD ROM
in the
Background.cpp source file. It is used to configure and control a background
thread used for lengthy worksheet function execution. The workbook used to design this
dialog,
XLM_ThreadCfg_Dialog.xls, is included on the CD ROM. It also generates
cpp_xloper array initialisation strings that can be cut and paste into a C++ source file.
8.13.3 Restricting user input to dialog boxes:
xlcDisableInput
Overview: Restricts all mouse and keyboard input to the dialog rather
than Excel.
Enumeration value: 32908 (x808c)
Callable from: Commands only.
Return type: Various. See table below.

recalculation of the worksheet resulting from the new data.
Enumeration value: 32907 (x808b)
Callable from: Commands only.
Arguments: 1: DataSourceSink: A string determining either the DDE data
source application or the worksheet to which the data is being
sent.
2: Command: The name of the command to be run as passed to
Excel in the 4th argument to
xlfRegister or the name of
some other command macro or VB function.
DataSourceSink should be in the format
[Book1.xls]Sheet1 if referring to a work-
sheet or, if referring to a DDE source application,
SourceApp|DataTopic!DataItem
or SourceApp|DataTopic or just SourceApp|, where the omission of the later parts
of the specifier implies a wildcard. The given command is run whenever data is being
sent to the sheet (if specified) or from the source application (if specified).
If the DataSourceSink argument is missing and
a valid Command argument is provided,
the given command is run whenever any DDE data is received provided that it is not
trapped by a previous, more specific, call to this function.
If Command is missing, the function clears the command associated with the Data-
SourceSink argument.
Accessing Excel Functionality Using the C API 279
8.14.2 Trapping a double-click event: xlcOnDoubleclick
Overview Instructs Excel to call a specified command whenever the user
double-clicks any object in the specified worksheet or chart,
overriding any default Excel action.
Enumeration value: 33047 (x8117)
Callable from: Commands only.

nation of event and sheet.
The use of other C API functions in the called command may be required to, say,
determine which cell was changed. (A call to
xlfActiveCell will determine this.)
280 Excel Add-in Development in C/C++
8.14.4 Trapping a keyboard event: xlcOnKey
Overview: Instructs Excel to call a specified command whenever the user
executes the given keystroke.
Enumeration value: 32936 (x80a8)
Callable from: Commands only.
Arguments: 1: Keystroke: A string that describes the keystroke to be trapped.
(See Table 8.33 below.)
2: Command: The name of the command to be run as passed to
Excel in the 4th argument to
xlfRegister or the name of
some other command macro or VB function.
If Keystroke is missing, the command is run whenever this event occurs on any sheet
where the event is not already trapped by a previous, more specific, call to this function.
If Command is an empty string (
"") the keystroke is effectively disabled. If Command
is missing, the function clears the command associated with this keystroke, or re-enables
it if it was disabled in previous call.
The Keystroke argument is constructed as follows: [modifier-key-symbol(s)][key-code],
for example
+{PGDN}.
The modifier key symbols are
+ (Shift), ^ (Ctrl) and % (Alt) and can be used in any
combination or not at all. The key code can be any one of the following:
• Any printable single-key character (e.g.
0 or ; or a or Z).

about to recalculate the specified worksheet, provided that this
recalculation is a result of the user pressing {F9} or the
equivalent via Excel’s built-in dialogs, or as the result of a
change in worksheet data. The command is not
called where the
recalculation is prompted by another command or macro. Unlike
other event traps, there can only be one trap for this event.
Enumeration value: 32995 (x80e3)
Callable from: Commands only.
Arguments: 1: SheetRef : A string of the format
[Book1.xls]Sheet1
specifying the sheet to which the event applies.
2: Command: The name of the command to be run as passed to
Excel in the 4th argument to
xlfRegister or the name of
some other command macro or VB function.
If SheetRef is missing, the command is run whenever this event occurs on any sheet.
If Command is missing, the function clears the command associated with this combi-
nation of event and sheet.
8.14.6 Trapping a window selection event:
xlcOnWindow
Overview: Instructs Excel to call a specified command whenever Excel is
about to switch to the specified worksheet. The command is not
called where the switch is the result of actions of another
command or macro or as a result of a DDE instruction.
Enumeration value: 32906 (x808a)
Callable from: Commands only.
Arguments: 1: WindowRef : A string of the format
[Book1.xls]Sheet1[:n]
specifying the window to which the event applies.

8.15.1 Disabling screen updating during command execution: xlcEcho
Overview: Disables screen updating during command execution.
Enumeration value: 32909 (x808d)
Callable from: Commands only.
Arguments: 1: UpdateScreen: Boolean. If true Excel updates the
worksheet screen, if false disables it. If omitted, Excel
toggles the state.
Note:
Screen updating is automatically re-enabled when a command stops executing.