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 .. 61 62 63 64 65 66 < 67 > 68 69 70 71 72 73 .. 168 >> Next

xloper’s 10 bytes.
How you can avoid using them
If you only want to access values from ranges of cells in a spreadsheet then declaring DLL functions as taking xloper arguments but registering them as taking oper arguments forces Excel to convert xltypeSRef and xltypeRef xlopers to one of the value types (or xltypeNil in some cases). (See section 8.5 Registering and un-registering DLL (XLL) functions on page 182.) However, Excel may not call your code if this conversion fails for some reason, and there is an unnecessary overhead if the argument is only to be passed as an argument to a C API function.
If you only want to access numbers from ranges of cells, then you do have the option of using the xl_array data type described in section 6.2.2 on page 107.
If you want to access information about ranges of cells in a spreadsheet, or you want complete flexibility with arguments passed in from Excel, then you cannot avoid their use.
Examples
The first example, count_used_cells(), creates a simple reference (xltypeSRef) to a range on the sheet from which the function is called. (Note that this will always be the current sheet, but may not be the active sheet.) It then calls the C API function Excel4(xlfCount,...), equivalent to the worksheet function COUNT(), to get the number of cells containing numbers. (The pointer p_xlErrValue points to a static xloper initialised to #VALUE!. See section 6.3 Defining constant xlopers on page 121 for more detail.)
xloper * stdcall count_used_cells(int first_row, int last_row,
int first_col, int last_col)
{
if(first_row > last_row || first_col > last_col) return p_xlErrValue;
// Adjust inputs to be zero-counted and cast to WORDs and BYTEs.
WORD fr = (WORD)(first_row - 1);
Passing Data between Excel and the DLL
155
WORD lr = (WORD)(last_row - І);
BYTE fc = (BYTE)(first_col - І);
BYTE lc = (BYTE)(last_col - І);
cpp_xloper InputRange(fr, lr, fc, lc); cpp_xloper RetVal;
Excel4(xlfCount, &RetVal, І, &InputRange); return RetVal.ExtractXloper(false);
І
The second example count_used_cells2() does the same as the first except that it creates an external reference (xltypeRef) to a range on a specified sheet before calling the C API function. Note that this sheet may not be the one from which the function is called. Note also that a different constructor is used.
xloper * stdcall count_used_cells2(char *sheetname, int first_row,
int last_row, int first_col, int last_col)
{
if(first_row > last_row || first_col > last_col) return p_xlErrValue;
// Adjust inputs to be zero-counted and cast to WORDs and BYTEs.
WORD fr = (WORD)(first_row - 1);
WORD lr = (WORD)(last_row - 1);
BYTE fc = (BYTE)(first_col - 1);
BYTE lc = (BYTE)(last_col - 1);
cpp_xloper InputRange(sheetname, fr, lr, fc, lc); cpp_xloper RetVal;
Excel4(xlfCount, &RetVal, 1, &InputRange); return RetVal.ExtractXloper(false);
}
6.8.9 Empty worksheet cell: xltypeNil
When you will encounter it
The xltypeNil xloper will typically turn up in an array of xlopers that has been created from a range reference, where one or more of the cells in the range is completely empty. Many functions ignore nil cells. For example, the worksheet function =AVERAGE() returns the sum of all non-empty numeric cells in the range divided by the number of such cells. If a DLL function is registered with Excel as taking an oper argument and the function is entered on the worksheet with a single-cell reference to an empty cell, then Excel will also pass an xloper of this type. If registered as taking an xloper argument, then the passed-in type would be xltypeSRef or xltypeRef. (See section 8.5 Registering and un-registering DLL (XLL) functions on page 182.)
When you need to create it
There’s an obvious contradiction if a worksheet function tries to return an xloper of this type to a single cell: the cell has a formula in it and therefore cannot be empty. Even if
156
Excel Add-in Development in C/C++
the cell is part of an array formula, it’s still not empty. If you return an array of xlopers (xltypeMulti) containing xltypeNil elements, they will be converted by Excel to numeric zero values. If you want to return a neutral non-numeric cell in an array, you will need to convert to an empty string. If, however, you want to clear the contents of a cell completely, something that you can only do from a command, you can use the C API function xlSet - see section 8.7.4 on page 203 - and pass an xltypeNil xloper.
How you create an instance of it
The following example shows how to do this in straight C code:
Xloper Op;
op.xltype = xltypeNil;
Or...
xloper op = {0.0, xltypeNil};
The default constructor for the cpp_xloper class initialises its xloper to xltypeNil. The class has a few methods for setting the xloper type later, which can also be used to create an xloper of type xltypeNil. For example:
cpp_xloper op; // initialised to xltypeNil op.SetType(xltypeNil);
// array elements are all initialised to xltypeNil cpp_xloper array_op((WORD)rows, (WORD)columns);
Previous << 1 .. 61 62 63 64 65 66 < 67 > 68 69 70 71 72 73 .. 168 >> Next