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

ISBN 0-470-02469-0

**Download**(direct link)

**:**

**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;

**146**> 147 148 149 150 151 152 .. 168 >> Next