Excel Add-in Development in C/C++ Applications in Finance phần 2 pps

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

Commands are allowed to do just about anything in Excel. Functions are given far less

freedom. VB functions are given a little more freedom than DLL add-ins. (Some of the

details of the differences between these two are discussed in the later chapters on VB

and C/C++.) It is easy to see why there needs to be some difference between functions

and commands: it would be a bad thing to allow a function in a worksheet cell to press

the undo icon whenever it was calculated. On the other hand, allowing a user-defined

command to do this is perfectly reasonable.

Most (but not all) of this book is concerned with writing functions rather than commands

simply because commands are better written in VB and may well require dialog boxes

and such things to interact with the user. Chapter 3 Using VBA on page 41 does talk

about VB commands, but not in great detail; there are plenty of books which talk at great

length about these things. Later chapters concerning the C API do talk about commands,

but the focus is on worksheet functions.

Table 2.11 gives a non-exhaustive summary of the things that commands can do that

functions can’t.

Table 2.11 Capabilities of commands versus functions

Action Command Function

Open or close a workbook Yes No

Create or delete a worksheet Yes No

Change the current selection Yes No

Change the format of a cell, worksheet or other object Yes No

Take arguments when called No Yes

Return a value to the caller No Yes

Access a cell value (not via an argument) Yes C API:

Sometimes2

VBA: Yes

Change a cell value Yes Only the

calling cell or

array and only

by return value

Read/write files Yes Yes

Start another application or thread Yes Yes

Set up event-driven Windows call-backs Yes Yes

Call a command-equivalent Excel 4 macro, C API function,

or Excel object method

Yes No

2 Worksheet functions are more limited than macro sheet functions in their ability to access the values of other

cells not passed in as arguments. For more details on this subject see section 8.5.4 Giving functions macro

sheet function permissions on page 188.

Excel Functionality 21

2.9 TYPES OF WORKSHEET FUNCTION

This book assumes a frequent-user level of familiarity with Windows, Windows applica￾tions, Excel and its user interface. This section assumes that readers are familiar with the

most common commands, menus, functions, how to use them, how to use Excel help and

so on. This section says nothing about these standard features, but instead discusses how

functions fall into certain types. When considering writing your own, it is important to

be clear about what kind of function you are creating.

2.9.1 Function purpose and return type

Individual worksheet cells are either empty or are evaluated to one of four different

data types:

• Numbers;

• Boolean (TRUE/FALSE);

• Strings;

• Error values.

(See section 2.4 Worksheet data types and limits on page 10.) Functions, however, can

evaluate to arrays and range references as well as to these four types. (The functions

INDIRECT(), OFFSET() and ADDRESS(), for example, all return references.)

Functions that return references are generally only of use when used to create range

(or array) arguments to be passed to other functions. They are not usually intended as the

end-product of a calculation. Where such a function returns a single cell reference, Excel

will attempt to convert to a value, in the same way that =A1 on its own in a cell will be

reduced to the value of A1. The formula =A1:A3 on its own in a cell will produce a #VALUE!

error, unless it is entered as an array formula into one or more cells (see next section).

As shown by examples later in this book, you can create functions that do useful things,

without needing to return anything important, except perhaps a value that tells you if they

completed the task successfully or not. A simple example might be a function that writes

to a data file whenever a certain piece of information changes.

In thinking about what you want your own functions to do, you should be clear about

the purpose of the function and therefore of its return type and return values, before you

start to code it.

2.9.2 Array formulae – The Ctrl-Shift-Enter keystroke

Functions can return single values or arrays of values, and many can return either. For

example, the matrix formula, MMULT(), returns an array whose size depends on the sizes

of the input arrays. Such functions need to be called from a range, rather than from a

single cell, in order to return all their results to the worksheet.

To enter an array formula you need to use the Ctrl-Shift-Enter keystroke. Instead of the

usual Enter to commit a formula to a single cell, Ctrl-Shift-Enter instructs Excel to accept

the formula as an array formula into the selected group of cells, not just the active cell.

The resulting cell formula is displayed in the formula bar as usual but enclosed within

curly braces, e.g., {=MMULT(A1:D4,F1:I4)}. The array formula can then only be modified as

a whole. Excel will complain if you attempt to edit or move part of an array, or if you

try to insert or delete rows or columns within it.

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

The all-or-nothing edit feature of array formulae makes them useful for helping to

protect calculations from being accidentally overwritten. The worksheet protection feature

of Excel is stronger. It allows precise control over what can be modified with password

protection. However, it also disables other features that you might want to be accessible,

such as the collapse and expansion of grouped rows and columns. Array formulae provide

a half-way house alternative.

Functions and operators that usually take single cell references can also be passed

range arguments in array formulae. How Excel deals with these is covered above in

section 2.6.8.

2.9.3 Required, optional and missing arguments and variable argument lists

Some functions take a fixed number of arguments, all of which need to be supplied

otherwise an error will be returned, for example DATE(). Some take required and optional

arguments, for example, VLOOKUP(). Some take a variable number such as SUM(). A few

functions have more than one form of argument-list, such as INDEX(), equivalent to the

concept of overloading in C++.

With C/C++ DLL functions, Excel handles variable length argument lists by always

passing an argument, regardless of whether the user provided one. A special missing data

type is passed. If the argument can take different types, say, a string or a number, the

function can be declared in such a way that Excel will pass a general data type. It is then

up to the function’s code whether to execute or fail with the arguments as provided. This

and related subjects are covered in detail in Chapter 6 Passing Data between Excel and

the DLL on page 105.

2.10 COMPLEX FUNCTIONS AND COMMANDS

2.10.1 Data Tables

Data Tables provide a very useful way of creating dynamic tables without having to

replicate the calculations for each cell in the table. Once the calculation has been set

up for a single result cell (not in the table), a table of results for a range of inputs is

produced. Excel plugs your inputs in one-by-one and then places the resulting value in

the Data Table. Data Tables can be based on one input to produce a single row or column

of results, or on two inputs to produce a 2-dimensional table.

Tables are set up with the Data/Table... command, invoking a simple wizard that prompts

you to specify the input row and/or column for the table. This book doesn’t go into any

detail (refer to Excel’s help to find out more), but it is worth considering what they are.

If you look at the formula that Excel puts in part of the table where the results are placed,

you will see that there is an array formula {=TABLE(...)}. On the face of it, therefore, it

looks like a Data Table is just another function entered as an array formula. It gives the

appearance of being recalculated like a function, except that Excel enables you to turn

the automatic recalculation of tables off using Tools/Options.../Calculation.

However: you can’t edit and re-enter the cells under the TABLE() function, even if you

have changed nothing; the Paste Function dialog does not recognise TABLE() as a valid

function; you can’t move the cells that are immediately above or to the left of the cells

Excel Functionality 23

occupied by the TABLE() function; you can’t set up a table other than with the Data

Table wizard.

The best way to think of a Data Table is as a completely different type of object that

allows a complex set of calculations in the worksheet to be treated as a user-defined

function in this very specific way. An example of where use of a Data Table might be

preferable to writing a VB or C/C++ function might be the calculation of net income after

tax. This depends on many pieces of information, such as gross income, tax allowances,

taxation bands, marital status, etc. Coding all this into a user-defined function may be

difficult, take an unjustifiably long time, involve the passing of a large number of argu￾ments, and might be hard to debug. A well laid-out spreadsheet calculation, complete with

descriptive labels for the inputs, and a Data Table, provide an excellent way of creating

a source for a lookup function.

One thing to watch is that Excel does not detect circular references resulting from the

input calculation depending on the table itself. In other words, it will allow them. Every

time the table is recalculated, the circular reference will feed back one more time. There’s

no reason someone in their right mind would want to do this, of course, but be warned.

Warning: Data Tables can recalculate much more slowly than repeated calculation of

cells. Excel’s recalculation logic can also be a little hard to fathom with large Data

Tables – it’s not always clear when the calculation is complete.

2.10.2 Goal Seek and Solver Add-in

Excel provides two ways of solving for particular static cell values that produce a certain

value in another cell. These are both commands, not functions, so you cannot automatically

re-solve when something in your sheet changes. To achieve this you would need to write

a user-defined function that will implement some kind of solver. The simplest of Excel’s

solvers is the Goal Seek (Tools/Goal seek...) which invokes the following dialog, and

provides a way of solving for one final numerical value given one numerical input.

Figure 2.1 Excel’s Goal Seek dialog

The second and more powerful method is the Solver Add-in, supplied with Excel and

accessible through the Tools/Solver... menu command once the add-in has been installed.