Download (direct link):
1.3 TO WHICH VERSIONS OF EXCEL DOES THIS BOOK APPLY?
Table 1.2 shows the marketing names and the underlying version numbers to which this book applies. Excel screenshots in this book (worksheets, dialogs, etc.) are all those of Excel 2000. Most of the interface differences between versions 2000 and 2003 (the latest at the time of writing) are quite superficial. The workbooks on the CD ROM are Excel 2000 format. (Contact firstname.lastname@example.org if you require 97 format files.)
Table 1.2 Excel version numbers
Product marketing name Version number
Excel 97 (SR-1, SR-2) 8
Excel 2000 9
Excel 2002 10
Excel 2003 11
1.4 ABOUT ADD-INS
An add-in is simply a code resource that can be attached to a standard application to enhance its functionality. Excel is supplied with a number of add-ins that can be installed according to the user’s preference and need. Some provide specialist functions not needed by the average user, such as the Analysis ToolPak (sic), and some that provide complex additional functionality such as the Solver add-in.
Add-ins come in two main flavours: interpreted macros and compiled code resources. Version 4 of Excel introduced macro sheets which could contain macros written in the Excel macro language (XLM). These comprised columns of instructions and calculations that either led to a result being returned to the caller, if functions, or that performed some action such as formatting a cell, if commands. Macro sheets could be part of a workbook or saved and loaded separately so as to be accessible to any workbook. Despite their flexibility they were relatively slow and did not promote sensible structured coding. In fact they encouraged the exact opposite given that, for example, they could modify themselves whilst executing.
Version 5 introduced Visual Basic worksheets. This enabled coding of functions and commands as before but promoted better coding practices and made implementation of algorithms from other languages easier. Excel 97 replaced these VB-sheets with Visual Basic for Applications and the Visual Basic Editor (VBE) - a comprehensive IDE complete with context-sensitive object-oriented help, pre-compiler, debugger and so on.
Macros, be they XLM or VB, are interpreted. When run, the interpreter reads each line one-by-one, makes sense of it while checking for errors in syntax, compiles it and only then executes the instructions. Despite the fact that VBA does some of this work in advance, this is a slow process. The VBA approach avoids the need for tools to create fully pre-compiled code making the creation of add-ins possible for the non-expert programmer. VBA makes Excel application objects accessible and is therefore the obvious choice for a host of user-defined commands and functions where speed of development rather than speed of execution is the prime concern. Additionally, Microsoft have not updated the C API since the release of Excel 97 and only support XLM for backwards compatibility. New functionality and objects added since this release are only available to applications that can access Excel’s COM-exposed objects. This is not too serious as the type of functionality added is that which it is most appropriate to access from VBA (or VB), rather than via the C API, anyway.
The other main flavour of add-in is the pre-compiled code resource which has none of the execution overhead of interpreted languages and is therefore extremely fast by comparison. The cost is the need to use and so understand, another development language, and another compiler or IDE. In essence, this is no harder than using VBA and the VB
Excel Add-in Development in C/C++
editor. The additional requirement is to know what Excel expects from and provides to anything calling itself an Excel add-in. In other words, you need to understand the Excel interface. The two interfaces that have been available over recent years are the C API and COM (the Common Object Model also known as Automation). COM provides access to Excel’s exposed objects, their methods and properties. VBA itself is a COM Automation application. Section 9.5 Accessing Excel functionality using COM/OLE Automation, on page 295, discusses some very basic COM concepts.
VB macros can be saved as Excel add-ins with very little effort but the resulting code is still slower than, say, compiled C add-ins. (Some performance comparisons are given in section 9.2 Relative performance ofVB, C/C++: Tests and results on page 289). Despite the rapid development and flexibility of VB, it lacks some of the key language concepts present in C and C++, in particular, pointers. These can sometimes be critical to the efficient implementation of certain algorithms. One example of where this is especially true is with the manipulation of strings.
The advent of .NET changes a number of things. For example, VB code resources can be compiled and the functions contained made accessible directly from a worksheet, at least in Excel 2002 and later. C, C++ and C# resources can similarly be accessed directly from a worksheet without the need to use the C API.