Books
in black and white
Main menu
Share a book About us Home
Books
Biology Business Chemistry Computers Culture Economics Fiction Games Guide History Management Mathematical Medicine Mental Fitnes Physics Psychology Scince Sport Technics
Ads

Excel add in development in C++ Aplications in finance - Dalton S.

Dalton S. Excel add in development in C++ Aplications in finance - Wiley publishing , 2005. - 425 p.
ISBN 0-470-02469-0
Download (direct link): exceladdindevelopmentincand2005.pdf
Previous << 1 .. 94 95 96 97 98 99 < 100 > 101 102 103 104 105 106 .. 168 >> Next

DLL names
Excel names that are defined as internal to a DLL (see function xlfSetName below for details) cannot be accessed directly in worksheet formulae, unlike worksheet names. They can only be accessed by the C API functions xlfSetName and xlfGetDef in the DLL.
How Excel resolves worksheet and DLL names
The steps Excel takes when interpreting a reference in a worksheet (such as Name) are:
1. Look for a definition of the name on the current worksheet.
2. If not found, look for a definition in the current workbook.
3. If still not found, return a #NAME? error.
If the name is referred to as Sheet1!Name then Excel looks for the name in the specified sheet in the current workbook and returns #REF! if the sheet does not exist or #NAME? if the name is not defined there.
If the name is referred to as [Book1.xls]Sheet1!Name then Excel looks for the name in the specified sheet in the specified workbook and returns #REF! if the workbook is not open or the sheet does not exist, or returns #NAME? if the name is not defined. If the workbook is closed, the full path name is required as follows (Excel will prompt for the worksheet name on a closed workbook, if omitted.):
='C:\Example Folder\[Book1.xls]Sheet1' iName
When accessing a worksheet named range from within the DLL using the xlfGetName function (see below), the name must be prefixed by ‘! ’ unless the worksheet name is specified. Otherwise Excel will look for the given name in a hidden name-space that is only accessible by DLLs running in this instance of Excel. (See DLL Names above.)
Accessing Excel Functionality Using the C API
241
8.10.2 Basic operations with Excel names
There are a number of things you might want to do with names. These operations, and the functions that you would use to execute them, are summarised here:
• Find out if a given name is defined and, if so, what its definition is (xlfGetName, not to be confused with xlGetName which returns the name of the DLL).
• Given a reference or value, find out the corresponding defined name if it exists (xlfGetDef).
• Create, define or redefine a name on a worksheet (xlcDefineName).
• Delete a defined name from a given worksheet (xlcDeleteName).
• Create, define or redefine a name in the DLL-space (xlfSetName).
• Delete a defined name from the DLL-space (xlfSetName).
• Get the value(s) corresponding to the defined name (xlfEvaluate).
• Set the value of cells in a given named range (xlfGetName and xlSet).
• Get a list of all defined worksheet names. (xlfNames).
All of these basic operations, except for the last, have been encapsulated in the xlName class in section 9.7. The class also provides simple member functions that inform the caller whether the name is defined and, if so, whether the range reference is still valid.
It is important to remember that Excel names can be valid in the sense that they are defined, but at the same time have invalid range definitions. This can come about when a named cell is deleted by a row or column deletion, a sheet deletion or as a result of a cell cut and paste.
8.10.3 Defining a name on a worksheet: xlcDefineName
Overview: Defines a name on a worksheet. The name can represent a
constant value (which can be a number, Boolean value or string but not an error value), an array of constant values or a reference to one or more cells.
The function performs the same operation as if the user had selected the menu option Insert/Name/Define... and will, in fact, display the dialog box if used in conjunction with the xlPrompt bit.
Enumeration value: 32829 (x803d)
Callable from: Commands only.
Return type: Boolean or error.
Arguments: 1: Name: A string satisfying the rules in section 8.10.
2: Definition: (Optional.) One of the following:
• A formula (as text using R1C1 style references)
242
Excel Add-in Development in C/C++
• A constant (as an xloper of that type or as text with or without a leading =)
• An array of values. (See note below.)
If Definition is omitted, the function defines the name as referring to the currently selected cell(s) on the active worksheet.
Note: There are two ways to specify a literal definition for a name that you wish to define as a constant. For example, a literal array can be passed as a string of the form "={1,2;3,4| ", or as an xloper of type xltypeMulti. The following example commands are equivalent and demonstrate this. Both create a name on the active sheet, so that the formula =SUM(XLL_test_name), if entered anywhere in the active workbook, would return 45.
int stdcall define_name_example_1(void)
{
cpp_xloper Name("XLL_test_name");
cpp_xloper Definition("={l,2,3;4,5,6;7,8,9}");
Excel4(xlcDefineName, 0, 2, &Name, ^Definition); return 1;
}
int stdcall define_name_example_2(void)
{
double array[9] = {1,2,3,4,5,6,7,8,9}; cpp_xloper Name("XLL_test_name"); cpp_xloper Definition(array, 3, 3);
Excel4(xlcDefineName, 0, 2, &Name, ^Definition); return 1;
}
8.10.4 Defining and deleting a name in the DLL: xlfSetName
Previous << 1 .. 94 95 96 97 98 99 < 100 > 101 102 103 104 105 106 .. 168 >> Next