Download (direct link):
The book is primarily focused on writing add-in worksheet functions. The reasons for this are gone into in later sections, such as section 2.8 Commands versus functions in Excel on page 19. One reason is that commands often rely on the creation of user-defined dialogs, which is a task far better suited to VBA. Even if the functionality that your command needs is already written in C/C++ code in a DLL, it can still easily be accessed from VB. Another reason is that, in general, commands do not have the same speed of execution requirements as worksheet functions - one of the main reasons for using a C/C++ DLL for functions.
Commands are covered to a certain extent, nevertheless. They can be a useful part of a well planned interface to a DLL. Knowing how to create and access them without the use of VB is important. Knowing how to create menus and menu items is important if you want DLL commands to be accessed in a seamless way. Chapter 8 Accessing Excel Functionality Using the C API on page 169 covers these topics.
The Excel COM interface is largely beyond the scope of this book, mainly to keep the book focused on the writing of high performance worksheet function, which COM does not help with. The other main reason is that if you need functionality that COM provides and the C API does not, for example, access to certain Excel objects, you are probably better off using VBA.
This book is not intended to be industry-specific or profession-specific except in the final chapter where applications of particular interest in certain areas of finance are discussed. It should be noted that the book is not intended to be a finance text book and deliberately avoids laborious explanations of things that finance professionals will know perfectly well. Nor are examples intended to necessarily cover all of what is a very broad field. It is hoped that readers will see enough parallel with their own field to be able to apply earlier sections of the book to their own problems without too much consternation.
2.1 OVERVIEW OF EXCEL DATA ORGANISATION
Excel organises data, formulae and other objects into a 2-dimensional grid of cells (65,536 = 216 rows by 256 = 28 columns), one grid per worksheet, with as many sheets per workbook as system resources allow. Each cell can contain several different types of data as well as format information and embedded comments. (A workbook can also contain VB code modules associated with a particular worksheet object or the workbook object.)
Excel, like all Microsoft Office applications, provides two types of command-access objects: menu bars and toolbars. There are many other Windows objects, but cells, worksheets, workbooks and command-access objects are of most interest to an add-in developer. The hierarchy of these objects, simply represented, is as follows:
Table 2.1 Simple representation of primary Excel objects
Workbooks Menu bars Toolbars
Worksheets and other sheet types Menus Toolbar buttons
Ranges of cells and individual cells Charts, drawings and other Excel and non-Excel objects Control objects (Command buttons, etc.) Menu items
2.2 A1 VERSUS R1C1 CELL REFERENCES
Excel supports two styles of cell reference, both used for display and input. The default (and by far most commonly used) is the A1 style where the alphabetic part of the reference represents the column (from A to IV) and the numeric part represents the row (from 1 to 65,536). The other is referred to as the R1C1 style. The main reason spending any time discussing these is that some of the C API functions require or return range addresses in one form only. Some of Excel’s VBA functionality also requires R1C1 notation, for example, when setting graph source-data ranges. Table 2.2 summarises both styles.
Table 2.2 A1 and R1C1 style comparisons
A1 style R1C1 style
Row-column order Column then row Row then column
Top row in sheet 1 R1
Bottom row in sheet 65536 R65536
(continued overleaf )
Excel Add-in Development in C/C++
Table 2.2 (continued)
A1 style R1C1 style
Left-most column in sheet A C1
Right-most column in sheet IV C256
Relative reference style as shown by formula =A2 entered into cell B1. =A2 =RC[-1]
Absolute reference style as shown by formula =$A$2 entered into cell B1. =$A$2 =R2C1
Mixed reference style as shown by formula =A$2 entered into cell B1. =$A2 =R2C[-1]
Relative reference in same row or column as shown by formula =A2 entered into cells B2 and A1. =A2 =RC (in cell A1) =R[-1]C (in cell B2)
Note: The index in square brackets in relative references in R1C1 style can be any number from -65,535 to +65,535 inclusive.
2.3 CELL CONTENTS
Internally, a cell within Excel has a great deal of data associated with it. This includes the display format, attached comments (notes), protection status, etc. The two most important properties for someone wishing to write functions are:
1. The cell’s formula - a text string that Excel parses to an internal compiled form, and which is then used to re-evaluate the cell in a recalculation.