Title Details

Acknowledgements.

1 Introduction.

1.1 Typographical and code conventions used in this book.

1.2 What tools and resources are required to write add-ins.

1.3 To which versions of Excel does this book apply?

1.4 About add-ins.

1.5 Why is this book needed?

1.6 How this book is organized.

1.7 Scope and limitations.

2 Excel Functionality.

2.1 Overview of Excel data organization.

2.2 A1 versus R1C1 cell references.

2.3 Cell contents.

2.4 Worksheet data types and limits.

2.5 Excel input parser.

2.6 Data type conversion.

2.7 Excel terminology: Active and current.

2.8 Commands versus functions in Excel.

2.9 Types of worksheet function.

2.10 Complex functions and commands.

2.11 Excel recalculation logic.

2.12 The Add-in Manager.

2.13 Loading and unloading add-ins.

2.14 Paste Function dialog.

2.15 Good spreadsheet design and practice.

2.16 Some problems with very large spreadsheets.

2.17 Conclusion.

3 UsingVBA.

3.1 Opening the VB Editor.

3.2 Using VBA to create new commands.

3.3 Assigning VB command macros to control objects in a worksheet.

3.4 Using VBA to trap Excel events.

3.5 Using VBA to create new functions.

3.6 Using VBA as an interface to external DLL add-ins.

3.7 Excel ranges, VB arrays, SafeArrays, array Variants.

3.8 Commands versus functions in VBA.

3.9 Creating VB add-ins (XLA files).

3.10 VB versus C/C++: Some basic questions.

4 Creating a 32-bit Windows (Win32) DLL Using Visual C++ 6.0 or Visual Studio .NET.

4.1 Windows library basics.

4.2 DLL basics.

4.3 DLL memory and multiple DLL instances.

4.4 Multi-threading.

4.5 Compiled function names.

4.6 Function calling conventions: cdecl, stdcall, fastcall.

4.7 Exporting DLL function names.

4.8 What you need to start developing add-ins in C/C++.

4.9 Creating a DLL using Visual C++ 6.0.

4.10 Creating a DLL using Visual C++ .NET 2003.

4.11 Accessing DLL functions from VB.

4.12 Accessing DLL functions from Excel.

5 Turning DLLs into XLLs: The Add-in Manager Interface.

5.1 Adding the Excel library and header files to a DLL project.

5.2 What does the Add-in Manager do?

5.3 Creating an XLL: The xlAuto interface functions.

5.4 When and in what order does Excel call the XLL interface functions?

5.5 XLL functions called by the Add-in Manager and Excel.

6 Passing Data between Excel and the DLL.

6.1 Handling Excel’s internal data structures: C or C++?

6.2 How Excel exchanges worksheet data with DLL add-in functions.

6.3 Defining constant xlopers.

6.4 A C++ class wrapper for the xloper – cpp_xloper.

6.5 Converting between xlopers and C/C++ data types.

6.6 Converting between xloper types.

6.7 Converting between xlopers and Variants.

6.8 Detailed discussion of xloper types.

6.9 Initialising xlopers.

6.10 Missing arguments.

7 Memory Management.

7.1 Excel stack space limitations.

7.2 Static add-in memory and multiple Excel instances.

7.3 Getting Excel to free memory allocated by Excel.

7.4 Getting Excel to call back the DLL to free DLL-allocated memory.

7.5 Returning data by modifying arguments in place.

8 Accessing Excel Functionality Using the C API.

8.1 The Excel 4 macro language (XLM).

8.2 The Excel4() C API function.

8.3 The Excel4v() C API function.

8.4 What C API functions can the DLL call and when?

8.5 Registering and un-registering DLL (XLL) functions.

8.6 Registering and un-registering DLL (XLL) commands.

8.7 Functions defined for the C API only.

8.8 Working with binary names.

8.9 Workspace information commands and functions.

8.10 Working with Excel names.

8.11 Working with Excel menus.

8.12 Working with toolbars.

8.13 Working with custom dialog boxes.

8.14 Trapping events.

8.15 Miscellaneous commands and functions.

8.16 The XLCallVer() C API function.

9 Miscellaneous Topics.

9.1 Timing function execution in VB and C/C++.

9.2 Relative performance of VB, C/C++: Tests and results.

9.3 Relative performance of C API versus VBA calling from a worksheet cell.

9.4 Detecting when a worksheet function is called from the Paste Function dialog (Function Wizard).

9.5 Accessing Excel functionality using COM/OLE Automation using C++.

9.6 Maintaining large data structures within the DLL.

9.7 A C++ Excel name class example, xlName.

9.8 Keeping track of the calling cell of a DLL function.

9.9 Multi-tasking, multi-threading and asynchronous calls in DLLs.

9.10 A background task management class and strategy.

9.11 How to crash Excel.

10 Example Add-ins and Financial Applications.

10.1 String functions.

10.2 Statistical functions.

10.3 Matrix functions – eigenvalues and eigenvectors.

10.4 Interpolation functions: lines, curves and splines.

10.5 Lookup and search functions.

10.6 Financial markets date functions.

10.7 Building and reading discount curves.

10.8 Building trees and lattices.

10.9 Quasi-random number sequences.

10.10 Generating correlated random samples.

10.11 Monte Carlo simulation.

10.12 Calibration.

References.

Web Links and Other Resources.

Index.

"This book is for anyone who wants to do any application development in Excel. Even for an old hand at Excel development such as myself, a brief skim through reveals valuable nuggets of information. Delving deeper into the text, richer veins are easily found. This book is destined to become an essential reference on Excel development."--Dr. Les Clewlow, Principal, Lacima Group Ltd.