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

do
{
if(args[num_searches][0].IsType(xltypeMissing | xltypeErr) || !args[num_searches][1].IsType(xltypeMulti)) break;
}
while(++num_searches < 5);
if(!num_searches)
return p_xlErrValue;
// Check that all the input arrays are the same shape and size WORD rows, columns;
WORD temp_rows, temp_columns;
args[0][1].GetArraySize(rows, columns);
// Check that input is either single row or single column if(rows != 1 && columns != 1) return p_xlErrValue;
for(int i = 1; i < num_searches; i++)
{
args[i][1].GetArraySize(temp_rows, temp_columns);
if(rows != temp_rows || columns != temp_columns) return p_xlErrValue;
}
DWORD limit = rows * columns;
DWORD offset;
// Simple search does not assume search ranges are sorted and // looks for an exact match
for(offset = 0; offset < limit; offset++)
{
for(i = 0; i < num_searches; i++)
if(args[i][0] != args[i][1].GetArrayElement(offset)) break;
if(i == num_searches) // Match found!
{
// Increment the offset as INDEX() counts from 1 cpp_xloper RetVal((double)(offset + 1));
360
Excel Add-in Development in C/C++
return RetVal.ExtractXloper(false);
І
І
return p_xlErrNa;
І
Function name s um_ i f _mu 11 i (exported) SumlfMulti (registered with Excel)
Description Returns the sum of all values in a sum range, where corresponding values in up to five search ranges match corresponding search values. Input 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 are not required to be sorted or all 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. Values in the sum range are converted to numbers if possible and skipped if not.
Prototype xloper * stdcall sum if multi(xloper *sum range, xloper *valuel, xloper *rangel, xloper *value2, xloper *range2, xloper *value3, xloper *range3, xloper *value4, xloper *range4, xloper *value5, xloper *range5;
Type string "RPPPPPPPPPPP"
Notes Function arguments are declared as xlopers but registered as opers. This causes Excel to convert from references to xltypeMulti xlopers, simplifying the type-checking and conversion that the DLL function needs to do. (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 is similar to the code for the function MatchMulti() above.
xloper * stdcall sum_if_multi(xloper *sum_range,
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
Example Add-ins and Financial Applications
361
// already converted to xltypeMulti. cpp_xloper SumRange(sum_range);
cpp_xloper args[5][2] = {{valuel, rangel}, {value2, range2}, {value3, range3}, {value4, range4}, {value5, range5}};
if(iSumRange.IsType(xltypeMulti)) return p_xlErrValue;
// Find the last non-missing value/range pair int num_searches = 0;
do
{
if(args[num_searches][0].IsType(xltypeMissing | xltypeErr) || iargs[num_searches][1].IsType(xltypeMulti)) break;
}
while(++num_searches < 5);
if(inum_searches)
return p_xlErrValue;
// Check that all the input arrays are the same shape and size WORD rows, columns;
WORD temp_rows, temp_columns;
SumRange.GetArraySize(rows, columns);
// Check that input is either single row or single column if(rows i= 1 && columns != 1) return p_xlErrValue;
for(int i = 0; i < num_searches; i++)
{
args[i][1].GetArraySize(temp_rows, temp_columns);
if(rows i= temp_rows || columns i= temp_columns) return p_xlErrValue;
}
DWORD limit = rows * columns;
DWORD offset;
double temp, sum = 0.0;
// Simple search does not assume first search range is sorted and // looks for an exact match
for(offset = 0; offset < limit; offset++)
{
for(i = 0; i < num_searches; i++)
if(args[i][0]i= args[i][1].GetArrayElement(offset)) break;
if(i == num_searches
&& SumRange.GetArrayElement(offset, temp)) sum += temp;
}
cpp_xloper RetVal(sum);
return RetVal.ExtractXloper(false);
}
362
Excel Add-in Development in C/C++
Function name count_if_multi (exported) CountlfMulti (registered with Excel)
Description Counts the number of cases where values in up to five search ranges match corresponding search values. Input 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 are not required to be sorted or all 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.
Prototype xloper * stdcall count if multi( xloper *valuel, xloper *rangel, xloper *value2, xloper *range2, xloper *value3, xloper *range3, xloper *value4, xloper *range4, xloper *value5, xloper *range5);
Previous << 1 .. 141 142 143 144 145 146 < 147 > 148 149 150 151 152 153 .. 168 >> Next