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 .. 118 119 120 121 122 123 < 124 > 125 126 127 128 129 130 .. 168 >> Next

// Run a registered XLL function. The name of the function is the // 1st element of ArgArray, and NumArgs is 1 + the number of args // the XLL function takes. Function can only take and return // Variant types that are supported by Excel.
HRESULT OLE_RunXllFunction(VARIANT &RetVal, int NumArgs,
VARIANTARG *ArgArray)
{
if(ipExcelDisp)
return S_FALSE;
static DISPID dispid = 0;
DISPPARAMS Params;
HRESULT hr;
Params.cArgs = NumArgs;
Params.rgvarg = ArgArray;
Params.cNamedArgs = 0;
if(dispid == 0)
{
wchar_t *ucName = L"Run";
hr = pExcelDisp->GetIDsOfNames(IID_NULL, &ucName, 1, LOCALE_SYSTEM_DEFAULT, kdispid);
Miscellaneous Topics 303
if(hr != S_OK) return hr;
}
if(dispid)
{
VariantInit(&RetVal);
hr = pExcelDisp->Invoke(dispid, IID_NULL,
LOCALE_SYSTEM_DEFAULT, DISPATCH_METHOD, &Params, &RetVal, NULL, NULL);
}
return hr;
}
9.5.5 Calling XLM functions using COM
This can be done using the ExecuteExcel4Macro method. This provides access to less of Excel’s current functionality than is available via VBA. However, there may be times where it is simpler to use ExecuteExcel4Macro than COM. For example, you could set a cell’s note using the XLM NOTE via ExecuteExcel4Macro, or you could perform the COM equivalent of the following VB code:
With Range("A1")
.AddComment
.Comment.Visible = False .Comment.Text Text:="Test comment." End With
Using late binding, the above VB code is fairly complex to replicate. Using early binding, once set up with a capable compiler, programming in C++ is almost as easy as in VBA.
The syntax of the ExecuteExcel4Macro method is straightforward and can be found using the VBA online help. The C/C++ code to execute the method is easily created by modifying the OLE_RunXl 1 Command ( ) function above to use this method instead of L " Run ".
9.5.6 Calling worksheet functions using COM
When using late binding, worksheet functions are mostly called using the Evaluate method. This enables the evaluation, and therefore the calculation, of anything that can be entered into a worksheet cell. Within VB, worksheet functions can be called more directly, for example, Exce1.WorksheetFunction.LogNormDist(...). Using late binding, the interface for WorksheetFunction would have to be obtained and then the dispid of the individual worksheet function. As stated above, using early binding, once set up with a capable compiler, programming in C++ is almost as easy as in VBA.
The following example function evaluates a string expression placing the result in the given Variant, returning S_OK if successful.
304
Excel Add-in Development in C/C++
#define MAX_COM_EXPR_LEN 1024
HRESULT CallVBAEvaluate(char *expr, VARIANT &RetVal)
{
static DISPID dispid = 0;
VARIANTARG String;
DISPPARAMS Params;
HRESULT hr;
wchar_t w[MAX_COM_EXPR_LEN +1]; char cErr[64]; int expr_len;
if(ipExcelDisp || iexpr || i*expr || (expr_len = strlen(expr)) > MAX_COM_EXPR_LEN) return S_FALSE;
try
{
VariantInit(&String);
// Convert the byte string into a wide char string mbstowcs(w, expr, expr_len + 1);
String.vt = VT_BSTR;
String.bstrVal = SysAllocString(w);
Params.rgdispidNamedArgs = NULL;
Params.rgvarg = &String;
Params.cArgs = 1;
Params.cNamedArgs = 0;
if(dispid == 0)
{
wchar_t *ucName = L"Evaluate";
hr = pExcelDisp->GetIDsOfNames(IID_NULL, &ucName, 1, LOCALE_SYSTEM_DEFAULT, kdispid);
if(FAILED(hr))
{
sprintf(cErr, "Error, hr = 0x%08lx", hr); MessageBox(NULL, cErr, "GetIDsOfNames",
MB_OK | MB_SETFOREGROUND);
SysFreeString(String.bstrVal); return hr;
}
}
// Initialise the VARIANT that receives the return value, if any.
// If we don't care we can pass NULL to Invoke instead of &RetVal VariantInit(&RetVal);
hr = pExcelDisp->Invoke(dispid,IID_NULL,LOCALE_SYSTEM_DEFAULT, DISPATCH_METHOD, &Params, &RetVal, NULL, NULL);
if(FAILED(hr))
{
sprintf(cErr, "Error, hr = 0x%08lx", hr);
MessageBox(NULL, cErr, "Invoke",
MB_OK | MB_SETFOREGROUND);
SysFreeString(String.bstrVal); return hr;
Miscellaneous Topics 305
}
// Success.
}
catch(_com_error &ce)
{
// If COM throws an exception, we end up here. Most probably we will // get a useful description of the error. You can force arrival in // this block by passing a division by zero in the string
MessageBoxW(NULL, ce.Description(), L"Evaluate",
MB_OK | MB_SETFOREGROUND);
// Get and display the error code in case the message wasn't helpful hr = ce.Error();
sprintf(cErr, "Error, hr = 0x%08lx", hr);
MessageBox(NULL, cErr, "The error code",
MB_OK | MB_SETFOREGROUND);
}
SysFreeString(String.bstrVal); return hr;
}
9.6 MAINTAINING LARGE DATA STRUCTURES WITHIN
THE DLL
Suppose you have a DLL function, call it UseArray, that takes as an argument a large array of data or other data structure that has been created by another function in the same DLL, call it MakeArray. The most obvious and easiest way of making this array available to UseArray would be to return the array from MakeArray to a range of worksheet cells, then call UseArray with a reference to that range of cells. The work that then gets done each time MakeArray is called is as follows:
Previous << 1 .. 118 119 120 121 122 123 < 124 > 125 126 127 128 129 130 .. 168 >> Next