Excel Add-in Development in C/C++ Applications in Finance phần 8 pptx

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

nth 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 nth 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

to run the command, at which point an alert dialog with the message “The macro

'command−name' cannot be found.” is displayed.

• The third column would contain a short-cut key for Macintosh systems and is therefore

not used in Windows DLLs.

• The fifth column contains a help reference in the form HelpFile!TopicNum where

HelpFile is a standard Windows help file.

• The third, fourth and fifth columns are all optional.

If CommandRef is simply the text of a previously deleted built-in command on this

menu, the command is restored in the position specified by CommandPosition and Sub￾CommandPosition.

If CommandPosition is omitted, the command is placed at the end of the menu and the

function returns the position number of the added command.

If argument SubMenuPosition is given, the function adds the command to the sub-menu

at CommandPosition. SubMenuPosition specifies the position on the sub-menu at which

Accessing Excel Functionality Using the C API 259

to place the command. Again this can be a number or text specifying the line before which

the commands will be placed. If SubMenuPosition is zero, the command is placed at the

end sub-menu. If omitted, the command is added to the main menu, not the sub-menu.

Example 1

The following code fragment adds a new command to the bottom of the Tools menu. The

code creates an array of strings for the CommandRef parameter in an xltypeMulti

xloper using the cpp_xloper class.

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

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

cpp_xloper Menu(4); // the worksheet cells short-cut menu

cpp_xloper CmdRef(cmd_txt, (WORD)1, (WORD)2); // 1 row, 2 columns

cpp_xloper CmdPos(0);

Excel4(xlfAddCommand, &RetVal, 4, &BarNum, &Menu, &CmdRef, &CmdPos);

Example 5

The following code fragment restores the deleted Goal Seek ... command on the Tools

menu in its default position just above Scenarios....

cpp_xloper BarNum(10); // the worksheet menu bar

cpp_xloper Menu("Tools");

cpp_xloper CmdRef("Goal Seek...");

cpp_xloper CmdPos("Scenarios...");

Excel4(xlfAddCommand, &RetVal, 4, &BarNum, &Menu, &CmdRef, &CmdPos);

8.11.7 Displaying a custom menu bar: xlfShowBar

Overview: Displays a custom menu bar or the default built-in menu for the

sheet type.

Enumeration value: 157 (x9d)

Callable from: Commands only.

Return type: Boolean or error.

Arguments: 1: MenuID: (Optional.)

When you create a custom menu bar using xlfAddBar, it is not automatically dis￾played. This function takes one optional argument, the menu bar ID number returned by

xlfAddBar. It replaces the currently displayed menu with the specified one. If the argu￾ment is omitted, Excel displays the appropriate built-in menu bar for the active sheet type.

If the menu bar ID corresponds to a built-in menu bar, Excel only allows the DLL to

display the appropriate type. For example, you could not display the chart menu bar when

a worksheet is active.

Displaying a custom menu bar disables Excel’s automatic switching from one menu bar

to another when the active sheet type changes. Displaying a built-in menu bar reactivates

this feature.

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");

cpp_xloper Check(show_check);

Excel4(xlfCheckCommand, &RetVal, 4, &BarNum, &Menu, &Cmd, &Check);

Example 2

The following code fragment toggles a check-mark on the command XLL command 1 on

the sub-menu XLL on the Data menu.

static bool show_check = false;

show_check = !show_check;

cpp_xloper BarNum(10); // the worksheet menu bar

cpp_xloper Menu("Data");

cpp_xloper Cmd("XLL test");

cpp_xloper Check(show_check);

cpp_xloper SubMenuCmd("XLL command 1");

Excel4(xlfCheckCommand, &RetVal, 5, &BarNum, &Menu, &Cmd, &Check,

&SubMenuCmd);