Excel Add-in Development in C/C++ Applications in Finance phần 9 pptx

Miscellaneous Topics 299

The following code shows how these steps can be accomplished:

void UninitExcelOLE(void)

{

// Release the IDispatch pointer. This will decrement its RefCount

pExcelDisp->Release();

pExcelDisp = NULL; // Good practice

OleUninitialize();

}

Once this is done, the Excel application’s methods and properties can fairly straight￾forwardly be accessed as demonstrated in the following sections. Note that access to

Excel’s worksheet functions, for example, requires the getting of the worksheet functions

interface, something that is beyond the scope of this book.

9.5.2 Getting Excel to recalculate worksheets using COM

This is achieved using the Calculate method exposed by Excel via the COM interface.

Once the above initialisation of the pExcelDisp IDispatch object has taken place,

the following code will have the equivalent effect of the user pressing the {F9} key.

Note that the call to the GetIDsOfNames() method is executed only once for the

Calculate command, greatly speeding up subsequent calls.

HRESULT OLE_ExcelCalculate(void)

{

if(!pExcelDisp)

return S_FALSE;

static DISPID dispid = 0;

DISPPARAMS Params;

char cErr[64];

HRESULT hr;

// DISPPARAMS has four members which should all be initialised

Params.rgdispidNamedArgs = NULL; // Dispatch IDs of named args

Params.rgvarg = NULL; // Array of arguments

Params.cArgs = 0; // Number of arguments

Params.cNamedArgs = 0; // Number of named arguments

// Get the Calculate method's dispid

if(dispid == 0) // first call to this function

{

// GetIDsOfNames will only be called once. Dispid is cached since it

// is a static variable. Subsequent calls will be faster.

wchar_t *ucName = L"Calculate";

hr = pExcelDisp->GetIDsOfNames(IID_NULL, &ucName, 1,

LOCALE_SYSTEM_DEFAULT, &dispid);

if(FAILED(hr))

{

// Perhaps VBA command or function does not exist

sprintf(cErr, "Error, hr = 0x%08lx", hr);

MessageBox(NULL, cErr, "GetIDsOfNames",

300 Excel Add-in Development in C/C++

MB_OK | MB_SETFOREGROUND);

return hr;

}

}

// Call the Calculate method

hr = pExcelDisp->Invoke(dispid, IID_NULL, LOCALE_SYSTEM_DEFAULT,

DISPATCH_METHOD, &Params, NULL, NULL, NULL);

if(FAILED(hr))

{

// Most likely reason to get an error is because of an error in a

// UDF that makes a COM call to Excel or some other automation

// interface

sprintf(cErr, "Error, hr = 0x%08lx", hr);

MessageBox(NULL, cErr, "Calculate", MB_OK | MB_SETFOREGROUND);

}

return hr; // = S_OK if successful

}

Note that calls to Invoke do not have to be method calls such as this. Invoke is also

called for accessor functions that get and/or set Excel properties. For a full explanation

of Invoke’s syntax, see the Win32 SDK help.

9.5.3 Calling user-defined commands using COM

This is achieved using the Run method exposed by Excel via the COM interface. Once

the above initialisation of the pExcelDisp IDispatch object has taken place, the

following code will run any command that takes no arguments and that has been reg￾istered with Excel in this session. (The function could, of course, be generalised to

accommodate commands that take arguments.) Where the command is within the XLL,

the required parameter cmd name should be the same as the 4th argument passed

to the xlfRegister function, i.e., the name Excel recognises the command rather

than the source code name. Note that the call to the GetIDsOfNames() method to

get the DISPID is done only once for the Run command, greatly speeding up subse￾quent calls.

#define MAX_COM_CMD_LEN 512

HRESULT OLE_RunXllCommand(char *cmd_name)

{

static DISPID dispid = 0;

VARIANTARG Command;

DISPPARAMS Params;

HRESULT hr;

wchar_t w[MAX_COM_CMD_LEN + 1];

char cErr[64];

int cmd_len = strlen(cmd_name);

if(!pExcelDisp || !cmd_name || !*cmd_name

Miscellaneous Topics 301

|| (cmd_len = strlen(cmd_name)) > MAX_COM_CMD_LEN)

return S_FALSE;

try

{

// Convert the byte string into a wide char string. A simple C-style

// type cast would not work!

mbstowcs(w, cmd_name, cmd_len + 1);

Command.vt = VT_BSTR;

Command.bstrVal = SysAllocString(w);

Params.rgdispidNamedArgs = NULL;

Params.rgvarg = &Command;

Params.cArgs = 1;

Params.cNamedArgs = 0;

if(dispid == 0)

{

wchar_t *ucName = L"Run";

hr = pExcelDisp->GetIDsOfNames(IID_NULL, &ucName, 1,

LOCALE_SYSTEM_DEFAULT, &dispid);

if(FAILED(hr))

{

sprintf(cErr, "Error, hr = 0x%08lx", hr);

MessageBox(NULL, cErr, "GetIDsOfNames",

MB_OK|MB_SETFOREGROUND);

SysFreeString(Command.bstrVal);

return hr;

}

}

hr = pExcelDisp->Invoke(dispid,IID_NULL,LOCALE_SYSTEM_DEFAULT,

DISPATCH_METHOD, &Params, NULL, NULL, NULL);

if(FAILED(hr))

{

sprintf(cErr, "Error, hr = 0x%08lx", hr);

MessageBox(NULL, cErr, "Invoke",

MB_OK | MB_SETFOREGROUND);

SysFreeString(Command.bstrVal);

return hr;

}

// 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.

MessageBoxW(NULL, ce.Description(), L"Run",

MB_OK | MB_SETFOREGROUND);

// Get and display the error code in case the message wasn't helpful

hr = ce.Error();

302 Excel Add-in Development in C/C++

sprintf(cErr, "Error, hr = 0x%08lx", hr);

MessageBox(NULL, cErr, "The Error code",

MB_OK|MB_SETFOREGROUND);

}

SysFreeString(Command.bstrVal);

return hr;

}

9.5.4 Calling user-defined functions using COM

This is achieved using the Run method exposed by Excel via the COM interface.

There are some limitations on the exported XLL functions that can be called using

COM: the OLE Automation interface for Excel only accepts and returns Variants of

types that this interface supports. It is not possible to pass or retrieve Variant equiva￾lents of xloper types xltypeSRef, xltypeSRef, xltypeMissing, xltypeNil

or xltypeFlow. Only types xltypeNum, xltypeInt, xltypeBool, xltypeErr

and xltypeMulti arrays of these types have Variant equivalents that are supported.

Therefore only functions that accept and return these things can be accessed in this way.

(The cpp xloper class contains xloper-VARIANT conversion routines.)

Once the above initialisation of the pExcelDisp IDispatch object has taken place,

the following code will run any command that has been registered with Excel in this

session. Where the command is within the XLL, the parameter CmdName should be

same as the 4th argument passed to the xlfRegister function, i.e. the name Excel

recognises the command by rather than the source code name. Note that the call to the

GetIDsOfNames() method to get the DISPID is executed only once for the Run

command, greatly speeding up subsequent calls.

// 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(!pExcelDisp)

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, &dispid);