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

xloper * stdcall Excel4_match(xloper *p_lookup_value,
xloper *p_lookup_array, xloper *p_match_type)
{
static xloper match_retval;
176
Excel Add-in Development in C/C++
Excel4(xlfMatch, &match_retval, 3,
p_lookup_value, p_lookup_array, p_match_type);
return &match_retval;
}
As already mentioned, there is no point in writing a function like this that does exactly what the function in the worksheet does, other than to demonstrate how to call worksheet functions from the DLL. If you want to customise a worksheet function, a cloned function like this is, however, a sensible starting point.
8.2.4 Calling macro sheet functions from the DLL using Excel4()
Excel’s built-in macro sheet functions typically return some information about the Excel environment or the property of some workbook or cell. These can be extremely useful in an XLL. Two examples are the functions =CALLER() and =GET.CELL() and their C API equivalents xlfCaller and xlfGetCell. The first takes no arguments and returns a reference to the cell or object from which the function (or command) was called. The second takes a cell reference and an integer value and returns some information. What information depends on the value of the integer argument. Both of the C API functions are covered in more detail later on in this chapter.
The following code fragment shows an example of both functions in action. This function toggles the calling cell between two states, 0 and 1, every time Excel recalculates. (To work as described, the function needs to be declared a volatile function - see section 8.5.5 Specifying functions as volatile on page 189.)
xloper * stdcall toggle caller(void) { xloper Caller; xloper GetCell_param; static xloper RetVal;

GetCell_param.xltype = xltypeInt; GetCell_param.val.w =5; // contents of cell as number
Excel4(xlfCaller, &Caller, 0); Excel4(xlfGetCell, &RetVal, 2, &GetCell_param, &Caller);
if(RetVal.xltype == xltypeNum) RetVal.val.num = (RetVal.val.num == 0 ? 1.0 : 0.0);
Excel4(xlFree, 0, 1, &Caller); return &RetVal; }
An alternative method of getting the calling cell’s value is to use the C API xlCoerce function, also covered in more detail below, to convert the cell reference to the desired data type, in this case a number. The equivalent code written using the cpp_xloper
Accessing Excel Functionality Using the C API
177
class and xlCoerce would be:
xloper * stdcall toggle_caller(void)
{
cpp_xloper Caller;
Excel4(xlfCaller, &Caller, 0); Caller.SetExceltoFree();
cpp_xloper RetVal; cpp_xloper TypeNum(xltypeNum);
Excel4(xlCoerce, &RetVal, 2, &Caller, &TypeNum); RetVal = ((double)RetVal == 0.0) ? 1.0 : 0.0;
return RetVal.ExtractXloper();
}
Circular reference note: In the above example, the function gets information about the calling cell, its value, and then returns a function of it to that same cell. This gives Excel an obvious dilemma: the function depends on itself so there is a circular reference. How Excel deals with this depends on how the toggle_caller() was registered. If registered as a worksheet function, the call to xlfGetCell will return the error code 2 (xlretInvXlfn). Excel considers functions like xlfGetCell to be off-limits for normal worksheet functions, getting round this and other problems that can arise. This is the same rejection as you would see if you entered the formula =GET.CELL(5,A1) in a worksheet cell - Excel would display an error dialog saying “That function is not valid”. (Such functions were introduced only to be used in Excel macro sheets.) The equivalent code that calls xlCoerce would also fail, this time with an error code of 64 (xlretUncalced). In this case Excel is complaining that the source cell has not been recalculated. If toggle_caller() had been registered as a macro sheet function, Excel is more permissive; the function behaves as you would expect. Section 8.5.4 Giving functions macro sheet function permissions on page 188 describes how to do this.
Being able to give your XLL worksheet functions macro sheet function capabilities opens up the possibility of writing some really absurd and useless functions. Some potentially useful ones are also possible, such as the above example, and the following very similar one that simply counts the number of times it is called. In this case, the example uses a trigger argument, and effectively counts the number of times that argument changes.
xloper * stdcall increment_caller(int trigger)
{
xloper Caller; xloper GetCell_param; static xloper RetVal;
GetCell_param.xltype = xltypeInt;
GetCell_param.val.w =5; // contents of caller as number Excel4(xlfCaller, &Caller, 0);
Excel4(xlfGetCell, &RetVal, 2, &GetCell_param, &Caller);
if(RetVal.xltype == xltypeNum)
RetVal.val.num += 1.0;
178
Excel Add-in Development in C/C++
Excel4(xlFree, 0, 1, &Caller); return &RetVal;
}
8.2.5 Calling macro sheet commands from the DLL using Excel4()
XLM macro sheet commands are entered into macro sheet cells in the same way as worksheet or macro sheet functions. The difference is that they execute command-equivalent actions, for example, closing or opening a workbook. Calling these commands using Excel4() is programmatically the same as calling functions, although they only execute successfully if called during the execution of a command. In other words, they are off-limits to worksheet and macro sheet equivalent functions. Sections 8.11 onwards to the end of the chapter contain numerous examples of such calls.
Previous << 1 .. 70 71 72 73 74 75 < 76 > 77 78 79 80 81 82 .. 168 >> Next