Download (direct link):
cpp_xloper Address("R1C1"); // Cell A1 cpp_xloper Sheet("Sheet1"); cpp_xloper RetVal;
Excel Add-in Development in C/C++
Excel4(xlfGetDef, &RetVal, 2, &Address, &Sheet); RetVal.SetExcelToFree();
Where the range name is defined within the DLL, only the first argument should be provided as in the following code fragment:
cpp_xloper Address("[Book1.xls]Sheet1!R1C1"); cpp_xloper RetVal;
Excel4(xlfGetDef, &RetVal, 1, &Address); RetVal.SetExcelToFree();
8.10.8 Getting a list of named ranges: xlfNames
Overview: Returns a horizontal array of all the names defined in the
specified workbook. (Unfortunately, this function does not return Excel names created within the DLL using xlfSetName. For this reason the DLL should maintain an internal list of the hidden DLL names it has created.)
If no names match the criteria, the function returns #N/A.
Enumeration value: 122 (x7a)
Callable from: Commands and macro sheet functions.
Return type: Horizontal array (xltypeMulti) of strings (xltypeStr).
Arguments: 1: Workbook/Worksheet: (Optional.) A string in the form
Book1.xls or [Book1.xls]Sheet1. If omitted the current workbook is searched.
2: NameType: (Optional.) Integer indicating the type of names to select: 1 or omitted = unhidden names, 2 = hidden names, 3 = all names.
3: Mask: (Optional.) A wildcard match string. For example “S*” will return all names starting with S. (Note: Searches are not case-sensitive). If omitted all names of NameType are returned.
Note: This function will not return the names of any binary storage blocks created with the xlDefineBinaryName function (see section 8.8 Working with binary names on page 209). Nor does it list names defined by a DLL within this session of Excel using xlfSetName. The DLL should therefore maintain its own list of such names using, for example, one of the C++ Standard Template Library containers or a simple linked list coded in C.
Where a workbook contains distinct sheets which have duplicate defined names, as in the example in section 8.10.6 on page 245, the function will behave slightly differently
Accessing Excel Functionality Using the C API
depending on whether the first argument is omitted or not. If omitted, the function returns an array of the names in the current workbook with no duplicates. If the workbook is explicitly provided in the first argument, the function returns the array with duplicate names repeated.
8.11 WORKING WITH EXCEL MENUS
Excel displays one menu bar for each sheet type, the most familiar being the default worksheet menu bar which normally contains nine menus:
File Edit View Insert Format Tools Data Window Help
Customising this and other menu bars, the menus they contain and the commands that the menus contain, enables the DLL to make its own command functions easily accessible. (Remember that commands can perform operations that worksheet functions cannot.) Creating menus using the XLM functions via the C API is fairly easy, as this section aims to show, but complex commands, especially those with complex dialogs and so on, are far better developed in VB. Including a few commands within an XLL can greatly simplify the provision of functionality of a DLL that primarily exists to provide worksheet functions. For example, a command that displays a simple dialog showing DLL version information or that allows configuration of one or more worksheet functions, can make the DLL functionality very much more user-friendly.
The highest level menu object is the menu bar, such as the one shown above, containing one or more menus, e.g. File, with each menu in turn providing access to one or more commands or sub-menus, the latter with its own commands. Excel has a number of built-in menu bars relating to different types of sheet, for example, there is a worksheet menu bar and a chart menu bar. Excel switches automatically between these when the user changes the active sheet.
As well as the add-in developer being able to change existing menu bars, they can also create custom menu bars. The creation of a custom menu bar does not automatically display it - it must be explicitly invoked, replacing the previous menu bar in the process. The display of a custom menu bar also suppresses the automatic switching between menu bars when the sheet type changes. So, unless you deliberately want to restrict the user in what they can do with Excel, it is better to add menus and/or commands to existing menu bars than to use custom bars.
Menus and commands can be accessed with Alt-key sequences. These are defined at the point that the new menu or command is registered with Excel, using an ampersand ‘&’ before the relevant letter in the displayed string. When adding menus or commands care should be taken to avoid conflicts with existing items, especially Excel’s built-in menus and commands.
8.11.1 Menu bars and ID numbers and menu and command specifiers
Internally, Excel represents each of the built-in menu bars by an ID number as shown in Table 8.22. Custom menu bars are assigned an ID number outside this range.