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 .. 69 70 71 72 73 74 < 75 > 76 77 78 79 80 81 .. 168 >> Next

16 (xlretStackOvf1) Excel’s pre-call stack check indicates a possibility that the stack might overflow. (See section 7.1 Excel stack space limitations on page 161.)
32 (xlretFailed) The xl fn command (not a function) that was being executed failed.
(continued overleaf)
174
Excel Add-in Development in C/C++
Table 8.3 (continued)
Returned value Meaning
64 (xlretUncalced) A worksheet function has tried to access data from a cell or range of cells that have not yet been recalculated as part of this workbook recalculation. Macro sheet-equivalent functions and commands are not subject to this restriction and can read uncalculated cell values. (See section 8.1.1 Commands, worksheet functions and macro sheet functions, page 170. for details.)
8.2.3 Calling Excel worksheet functions in the DLL using Excel4()
Excel exposes all of the built-in worksheet functions through Excel4(). Calling a worksheet function via the C API is simply a matter of understanding how to set up the call to Excel4() and the number and types of arguments that the worksheet function takes. Arguments are all passed as pointers to xlopers so successfully converting from C/C++ types to xloper is a necessary part of making a call. (See section 6.5 Converting between xlopers and C/C++ data types on page 126.)
The following code examples show how to set up and call Excel4() using xlopers directly, as well as with the cpp_xloper class defined in section 6.4 on page 121. The example function is a fairly useful one: the =MATCH() function, invoked from the DLL by calling Excel4() with xlfMatch.
Worksheet function syntax: =MATCH(/ootop_t'a/t/e, lookup_array, matchjype)
The following code accepts inputs of exactly the same type as the worksheet function and then sets up the call to the worksheet function via the C API. Of course, there is no value in this other than demonstrating how to use Excel4().
xloper * stdcall Excel4_match(xloper *p_lookup_value,
xloper *p_lookup_array, int match_type)
{
static xloper match_retval = {0, xltypeInt}; xloper match_type_oper;
// Convert the integer argument into an xloper so that a pointer // to this can be passed to Excel4() match_type_oper.val.w = match_type;
int xl4 = Excel4(
xlfMatch, // 1st arg: the
&match_retval,// 2nd arg: ptr 3, //3rd arg: number
p_lookup_value, // fn arg1
p_lookup_array, // fn arg2
&match_type_oper);// fn arg3
// Test the return value of Excel4() if(xl4 != xlretSuccess)
{
match_retval.xltype = xltypeErr; match_retval.val.err = xlerrValue;
function to be called to return value of subsequent args
Accessing Excel Functionality Using the C API
175
}
else
{
// Tell Excel to free up memory that it might have allocated for // the return value.
match_retval.xltype |= xlbitXLFree;
}
return &match_retval;
}
The above example shows how the following steps have been taken:
1. Conversion of arguments to the Excel4() function into xlopers. (Here the integer match_type is converted to an internal integer xloper. It could have been converted to a floating point xloper.)
2. Passing of the correct constant for the function to be called to Excel4(), in this case xlfMatch = 64.
3. Passing of a pointer to an xloper that will hold the return value of the function. (If the function does not return a value, passing NULL or 0 is permitted.)
4. Passing a number telling Excel4() how many subsequent arguments (the arguments for the called function) are being supplied. xlfMatch can take 2 or 3 arguments, but in this case we pass 3.
5. Passing of pointers to the arguments.
6. Collection and testing of the return value of Excel4().
In some cases, you might also want to test the type of the returned xloper to check that the called function completed successfully. In most cases a test of the xltype to see if it is xltypeErr is sufficient. In this case we are returning the xloper directly, so can allow the spreadsheet to deal with any error in the same way that it would after a call to the MATCH() function itself.
Note: If Excel was unable to call the function, say, if the function number was not valid, the return value xloper would be untouched. In some cases it may be safe to assume that Excel4() will not fail and simply test whether the xlfn function that Excel4() was evaluating was successful by testing the xltype of the return value xloper.
Some simplifications to the above code example are possible. The function Excel4_match() need not be declared to take an integer 3rd argument. Instead, it could take another xloper pointer. Also, we can be confident in the setting up of the call to Excel4() that we have chosen the right function constant, that the number of the arguments is good and that we are calling the function at a time and with arguments that are not going to cause a problem. So, there’s no need to store and test the return value of Excel4() and the xlfMatch return value can be returned straight away. If xlfMatch returned an error, this will propagate back to the caller in an acceptable way. The function could therefore be simplified to the following (with comments removed):
Previous << 1 .. 69 70 71 72 73 74 < 75 > 76 77 78 79 80 81 .. 168 >> Next