Excel Add-in Development in C/C++ Applications in Finance phần 6 docx

Accessing Excel Functionality Using the C API 179

The following example simply provides a worksheet interface to Excel4v()allowing

the function number and the arguments that are appropriate for that function to be passed

in directly from the sheet. This can be an extremely useful tool but also one to be used

with great care. This section outlines some of the things this enables you to do, but first

here’s the code with comments that explain what is going on.

xloper * __stdcall XL4(int xlfn, xloper *arg0, xloper *arg1,

xloper *arg2, xloper *arg3, xloper *arg4,

xloper *arg5, xloper *arg6, xloper *arg7,

xloper *arg8, xloper *arg9, xloper *arg10,

xloper *arg11, xloper *arg12, xloper *arg13,

xloper *arg14, xloper *arg15, xloper *arg16,

xloper *arg17, xloper *arg18)

{

xloper *arg_array[19];

static xloper ret_xloper;

// Fill in array of pointers to the xloper arguments ready for the

// call to Excel4v()

arg_array[0] = arg0;

arg_array[1] = arg1;

arg_array[2] = arg2;

arg_array[3] = arg3;

arg_array[4] = arg4;

arg_array[5] = arg5;

arg_array[6] = arg6;

arg_array[7] = arg7;

arg_array[8] = arg8;

arg_array[9] = arg9;

arg_array[10] = arg10;

arg_array[11] = arg11;

arg_array[12] = arg12;

arg_array[13] = arg13;

arg_array[14] = arg14;

arg_array[15] = arg15;

arg_array[16] = arg16;

arg_array[17] = arg17;

arg_array[18] = arg18;

// Find the last non-missing argument

for(int i = 19; --i >= 0;)

if(arg_array[i]->xltype != xltypeMissing)

break;

// Call the function

int retval = Excel4v(xlfn, &ret_xloper, i + 1, arg_array);

if(retval != xlretSuccess)

{

// If the call to Excel4v() failed, return a string explaining why

// and tell Excel to call back into the DLL to free the memory

// about to be allocated for the return string.

ret_xloper.xltype = xltypeStr | xlbitDLLFree;

ret_xloper.val.str = new_xlstring(Excel4_err_msg(retval));

}

else

{

// Tell Excel to free up memory that it might have allocated for

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

// the return value.

ret_xloper.xltype |= xlbitXLFree;

}

return &ret_xloper;

}

The function Excel4_err_msg() simply returns a string with an appropriate error mes￾sage should the call to Excel4v() fail, and is listed below. The function

new_xlstring() creates a byte-counted string from this.

char *Excel4_err_msg(int err_num)

{

switch(err_num)

{

case xlretAbort: return "XL4: macro halted";

case xlretInvXlfn: return "XL4: invalid function number";

case xlretInvCount: return "XL4: invalid number of args";

case xlretInvXloper: return "XL4: invalid oper structure";

case xlretStackOvfl: return "XL4: stack overflow";

case xlretUncalced: return "XL4: uncalced cell";

case xlretFailed: return "XL4: command failed";

default: return NULL;

}

}

The function XL4()takes 20 arguments (one for the C API function code, and up to 19

function arguments). The Excel worksheet limit for any function is 30 arguments, but the

means by which functions are registered (see section 8.5 below) imposes this limit on

exported XLL functions.

8.4 WHAT C API FUNCTIONS CAN THE DLL

CALL AND WHEN?

The C API was designed to be called from DLL functions that have themselves been

called by Excel while executing commands, during worksheet recalculations or during

one of the Add-in Manager’s calls to one of the xlAuto- functions. DLL routines can

be called in other ways too: the DllMain() function is called by the operating system;

VB can call exported DLL functions that have been declared within the VB module; the

DLL can set up operating system call-backs, for example, at regular timed intervals; the

DLL can create background threads.

Excel is not always ready to receive calls to the Excel4() or Excel4v() func￾tions. The following table summarises when you can and cannot call these functions

safely.

Accessing Excel Functionality Using the C API 181

Table 8.5 When it is safe to call the C API

When called Safe to call? Additional comments

During a call to the DLL from:

• an Excel command,

• a user-defined command in a

macro sheet,

• a user-defined command

subroutine in a VB code

module,

• the Add-in Manager to one

of the xlAuto- functions,

• an XLL command run using

the xlcOnTime C API

function.

Yes In all these cases Excel is

running a command, i.e., these

are all effectively called as a

result of a user action, e.g.,

starting Excel, loading a

workbook, choosing a menu

option, etc.

All xlf-, xlc- and the C

API-only functions are

available.

During a call to the DLL from

a user-defined VBA worksheet

function.

Yes DLL functions called from VB

in this way cannot call macro

sheet C API functions such as

the workspace information

function xlfGetWorkbook.

During a direct call to a macro

sheet equivalent function,

called as a result of

recalculation of a worksheet

cell or cells.

Yes Most of the xlf- functions and

the C API-only functions are

available. (A number of the

xlf- functions are, in fact,

command-equivalents and can

only be called from

commands.)

Note: Functions within VB

modules that are called as a

result of a worksheet

recalculation are worksheet

function equivalents not

macro-sheet equivalents.

During a direct call to a

worksheet equivalent function,

called as a result of

recalculation of a worksheet

cell or cells.

Yes Only worksheet equivalent

xlf- functions and the C

API-only functions are

available. A large number of

the xlf- functions are only

accessible to macro sheet

equivalent functions. Calling

these will either result in

Excel4() returning

xlretFailed.

(continued overleaf )