The following lines will have been inserted into your code:
// Function: HISTVOL
// Purpose: Returns the historical volatility of a series of daily prices
//{{XLP_SRC(HISTVOL)
// NOTE - the FunctionWizard will add and remove mapping code here.
// DO NOT EDIT what you see in these blocks of generated code!
IMPLEMENT_XLLFN2(HISTVOL, "RP", "HISTVOL", "Prices", "Statistical"
"", "Returns the historical volatility of a series of daily"
" prices", "Series of daily prices", "B()Prices Series of"
" daily prices\0", 1)
extern "C" __declspec( dllexport )
LPXLOPER HISTVOL(const COper* Prices)
{
CXlOper xloResult;
BOOL bOk = TRUE;
std::vector<double> vecPrices;
bOk = bOk && Prices->ReadVector(vecPrices, "Prices", xloResult);
if (!bOk)
return xloResult.Ret();
//}}XLP_SRC
// TODO - Set the value of xloResult
return xloResult.Ret();
}
As you can see, the Wizard has generated quite a lot more code this time. Let us examine the new features.
The argument type has been declared as const COper*. COper is a C++ class which handles Excel data, like CXlOper. It differs from CXlOper in a number of ways:
Because COper is a generic type, we cannot tell in advance what it will contain. The user may pass a single value instead of a series, or a string value, or they may omit the value altogether. It is the responsibility of the add-in function to ensure that the argument contains the expected type of data. The Wizard generates code to handle this.
The function is going to have do some processing of input to a more useful form. In particular, the data in Prices has to be extracted from the COper and put into an array of doubles. A local flag bOk has been added, to monitor the success or failure of this processing.
An STL vector of doubles, vecPrices, has been declared. This will be used as a convenient variable-size buffer for the data contained in the COper Prices.
The best thing about using an STL vector is that we don't have to worry about memory cleanup. As soon as vecPrices goes out of scope (i.e. at the end of the function), all its memory will be released. We do not have to remember to use free() or delete.
LPXLOPER HISTVOL(const COper* Prices)
{
CXlOper xloResult;
BOOL bOk = TRUE;
std::vector<double> vecPrices;
bOk = bOk && Prices->ReadVector(vecPrices, "Prices", xloResult);
if (!bOk)
return xloResult.Ret();
//}}XLP_SRC
// TODO - Set the value of xloResult
return xloResult.Ret();
}
The COper method ReadVector() is used to do the data extraction. If the extraction fails for any reason, then a description of the error will be placed in xloResult as a string. For example, depending on input, it might contain the following:
#Error in Prices: cell 6 is not numeric
If the extraction process has failed, then the function returns early, returning the error string which is now contained in xloResult.
LPXLOPER HISTVOL(const COper* Prices)
{
CXlOper xloResult;
BOOL bOk = TRUE;
std::vector<double> vecPrices;
bOk = bOk && Prices->ReadVector(vecPrices, "Prices", xloResult);
if (!bOk)
return xloResult.Ret();
//}}XLP_SRC
// TODO - Set the value of xloResult
return xloResult.Ret();
}
If it succeeds, then we can be confident that the vector vecPrices is now populated with an array of numeric values, and we can safely call our implementation function, HistVol().
Since the CalcHistVol() function expects two arguments for the price data, (i) a pointer to the array and (ii) the size of the array, you might ask "Why doesn't the Excel function need two arguments?". You can see from the code above that the wizard takes care of this for you, by generating code that reads the size of the array as well as its contents.
The call to Prices->ReadVector(...) also does some other useful work. If the user has left some of the cells of the input range empty, ReadVector() will truncate them, rather than putting null values into vecPrice.
Add code to the function to call our stand-alone function, as shown below:
extern "C" __declspec( dllexport )
LPXLOPER HISTVOL(const COper* Prices)
{
CXlOper xloResult;
BOOL bOk = TRUE;
std::vector<double> vecPrices;
bOk = bOk && Prices->ReadVector(vecPrices, "Prices", xloResult);
if (!bOk)
return xloResult.Ret();
//}}XLP_SRC
double dHistVol;
if (CalcHistVol(&vecPrices[0], vecPrices.size(), 250.0, &dHistVol))
xloResult = dHistVol;
else
xloResult = xlerrNum;
return xloResult.Ret();
}
Depending on whether CalcHistVol() succeeds we will either set CXlOper to contain the result of the calculation, or set it to contain the error code xlerrNum, indicating that the inputs were out of range is some way.
The code above makes use of the STL vector template class method size(). If you are not familiar with the vector class have a look at the help for vector in DevStudio help.
Our add-in function is now complete and we're ready to build and test it.