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 .. 140 141 142 143 144 145 < 146 > 147 148 149 150 151 152 .. 168 >> Next

if(high == 0)
{
ret_val.val.num = yy->array[0]; return &ret_val;
}
Example Add-ins and Financial Applications
357
if(x < xx->array[0] || x > xx->array[high])
{
if(dont_extrapolate) return p_xlErrNum; ret_val.val.num = yy->array[x < xx->array[0] ? 0 : high]; return &ret_val;
}
while(high - low > 1)
{
i = (high + low) >> 1; if(xx->array[i] > x) high = i; else
low = i;
}
ret_val.val.num = yy->array[low] +
(x - xx->array[low]) * (yy->array[high] - yy->array[low]) / (xx->array[high] - xx->array[low]);
return &ret_val;
}
10.5 LOOKUP AND SEARCH FUNCTIONS
Lookup and search functions, especially those where the input arrays contain strings, are far more efficiently coded in C/C++ than the alternatives. Where you need to use two- or higher-dimensional lookups or searches, or where more complex search or match criteria are needed, on large amounts of data, you should seriously consider using C/C++. The following table briefly outlines the limitations of Excel’s own lookup and search functions.
Table 10.2 Excel’s lookup and search functions
Function Limitations
VLOOKUP() HLOOKUP() Left-most column (top row) needs to be in ascending order for the function to work. Lookup value and returned value need to be in the same single range. Only one lookup value can be matched and only against the left-most column (top row).
LOOKUPO Form: LOOKUP(Lookup_value,Lookup_vector,Result_vector): left-most column needs to be in ascending order for the function to work. Only one lookup value can be matched.
MATCH() Only one lookup value can be matched.
COUNTIFO SUMIFQ Only one criterion can be applied
Excel includes a number of database functions which do provide a way around many, if not all, of these limitations, albeit at the expense of more complex workbooks. These functions are also available via the C API.
358
Excel Add-in Development in C/C++
The primary extension in these examples is to allow for a search on more than one range, so, for example, a value can be retrieved from a row in a table when values of two or more elements in that row match specified search criteria. The function MatchMulti() returns the same kind of information as MATCH() - the offset into the range where the match was found or #N/A if none found - and, if used in conjunction with the INDEX() function, extends VLOOKUP() functionality. The functions SumIfMulti() and CountIfMulti() similarly extend the functions COUNTIF() and SUMIF() respectively.
These functions rely heavily on the cpp_xloper class, making the code far cleaner than it would otherwise be if only xlopers had been used. There is only a very small performance cost in using the class, but you could re-implement these things using xlopers directly if this were a concern. Code for these functions is listed in the example project source file Lookup.cpp.
Function name
match_multi (exported)
MatchMulti (registered with Excel)
Description
Returns the offset corresponding to the position in one to five search ranges that match the corresponding supplied values. The offset counts from 1 so that it can be used with the INDEX() function to retrieve values from, say, an associated data table. Input search ranges are expected to be either single columns or single rows, and all search ranges must be the same shape and size and have at least 2 elements each. Search ranges do not need to be sorted or all of the same data type. The function looks for exact matches and is case-sensitive when comparing strings. The function returns #VALUE! if inputs are not valid and #N/A if a match cannot be found.
Prototype
xloper * stdcall match_multi(
xloper *value1, xloper *range1, xloper *value2, xloper *range2, xloper *value3, xloper *range3, xloper *value4, xloper *range4, xloper *value5, xloper *range5);
Type string
"RPPPPPPPPPP"
Notes
Function arguments are declared as xlopers but registered as opers. This causes Excel to convert range references to xltypeMulti, simplifying the type-checking and conversion in the DLL. (If a search range reference is a single cell it will be converted to a single value, rather than an array, and the function will fail.) The function returns an xloper so that errors can be returned.
The code for this function is as follows. The function relies heavily on the cpp_xloper class to simplify the code, in particular for comparing xlopers (the overloaded ! = operator) and for handling arrays.
Example Add-ins and Financial Applications
359
xloper * stdcall match_multi(
xloper *value1, xloper *range1, xloper *value2, xloper *range2, xloper *value3, xloper *range3, xloper *value4, xloper *range4, xloper *value5, xloper *range5)
{
// Get the arguments into a more manageable form.
// Arguments are registered as opers so that range references are // already converted to xltypeMulti.
cpp_xloper args[5][2] = {{value1, range1}, {value2, range2}, {value3, range3}, {value4, range4}, {value5, range5}};
// Find the last non-missing value/range pair int num_searches = 0;
Previous << 1 .. 140 141 142 143 144 145 < 146 > 147 148 149 150 151 152 .. 168 >> Next