Title Details

Preface to Second Edition xvii

Preface to First Edition xix

Acknowledgements for the First Edition xxi

Acknowledgements for the Second Edition xxiii

1 Introduction 1

1.1 Typographical and code conventions used in this book 1

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

1.3 To which versions of Excel does this book apply? 5

1.4 The future of Excel: Excel 2007 (Version 12) 5

1.5 About add-ins 7

1.6 Why is this book needed? 8

1.7 How this book is organised 9

1.8 Scope and limitations 10

2 Excel Functionality 11

2.1 Overview of Excel data organisation 11

2.2 A1 versus R1C1 cell references 12

2.3 Cell contents 13

2.4 Worksheet data types and limits 13

2.5 Excel input evaluation 15

2.6 Data type conversion 16

2.7 Strings 23

2.8 Excel Terminology: Active and current 27

2.9 Commands versus functions in Excel 28

2.10 Types of worksheet function 29

2.11 Complex functions and commands 31

2.12 Excel recalculation logic 33

2.13 The Add-in Manager 46

2.14 Loading and unloading add-ins 46

2.15 Paste function dialog 47

2.16 Good spreadsheet design and practice 49

2.17 Problems with very large spreadsheets 54

2.18 Conclusion 54

3 UsingVBA 55

3.1 Opening the VB editor 55

3.2 Using VBA to create new commands 56

3.3 Assigning VBA command macros to control objects in a worksheet 58

3.4 Using VBA to trap Excel events 59

3.5 Using VBA to create new functions 61

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

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

3.8 Commands versus functions in VBA 86

3.9 Creating VB add-ins (XLA files) 87

3.10 VBA versus C/C++: some basic questions 88

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

4.1 Windows library basics 89

4.2 DLL basics 89

4.3 DLL memory and multiple DLL instances 90

4.4 Multi-threading 90

4.5 Compiled function names 91

4.6 Function calling conventions: __cdecl,__stdcall,__fastcall 93

4.7 Exporting DLL function names 94

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

4.9 Creating a DLL using Visual C++ 6.0 98

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

4.11 Accessing DLL functions from VB 108

4.12 Accessing DLL functions from excel 110

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

5.1 The xlcall32 library and the C API functions 111

5.2 What does the Add-in manager do? 114

5.3 Creating an XLL: The xlAuto interface functions 115

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

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

6 Passing Data Between Excel and the DLL 127

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

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

6.3 Defining constant xlopers/xloper12s 144

6.4 A C++ class wrapper for the xloper/xloper12 - cpp_xloper 146

6.5 Converting between xloper/xloper12s and C/C++ data types 154

6.6 Converting between xloper/xloper12 types 154

6.7 Converting between xlopers and variants 155

6.8 Converting between xlopers and xloper12s 159

6.9 Detailed Discussion of xloper types 163

6.10 Initialising xloper/xloper12s 198

6.11 Missing arguments 201

7 Memory Management 203

7.1 Excel stack space limitations 203

7.2 Static add-in memory and multiple Excel instances 204

7.3 Getting Excel to free memory allocated by Excel 205

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

7.5 Returning data by modifying arguments in place 211

7.6 Making add-in functions thread safe 212

8 Accessing Excel Functionality using the C API 223

8.1 The Excel 4 macro language (XLM) 223

8.2 The Excel4(), Excel12() C API functions 226

8.3 The Excel4v()/Excel12v() C API functions 233

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

8.5 Wrapping the C API 238

8.6 Registering and un-registering DLL (XLL) functions 244

8.7 Registering and un-registering DLL (XLL) commands 271

8.8 Functions defined for the C API only 274

8.9 Working with binary names 285

8.10 Workspace information commands and functions 289

8.11 Working with Excel names 316

8.12 Working with Excel menus 326

8.13 Working with toolbars 344

8.14 Working with custom dialog boxes 351

8.15 Trapping events with the C API 356

8.16 Miscellaneous commands and functions 361

8.17 The XLCallVer() C API function 364

9 Miscellaneous Topics 365

9.1 Timing function execution in VBA and C/C++ 365

9.2 Relative performance of VBA, C/C++: Tests and results 369

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

9.4 Detecting when a worksheet function is called from an Excel dialog 373

9.5 Accessing Excel functionality using COM/OLE automation using C++ 376

9.6 Maintaining large data structures within the DLL 385

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

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

9.9 Passing references to Excel worksheet functions 398

9.10 Multi-tasking, Multi-threading and asynchronous calls in DLLs 401

9.11 A background task management class and strategy 406

9.12 How to crash Excel 417

9.13 Add-in Design 419

9.14 Optimisation 433

10 Example Add-ins and Financial Applications 451

10.1 String functions 451

10.2 Statistical functions 463

10.3 Matrix functions – eigenvalues and eigenvectors 474

10.4 Interpolation 477

10.5 Lookup and search functions 485

10.6 Financial markets date functions 493

10.7 Building and reading discount curves 502

10.8 Building trees and lattices 505

10.9 Monte Carlo simulation 506

10.10 Calibration 511

10.11 CMS derivative pricing 513

10.12 The SABR stochastic volatility model 519

10.13 Optimising the SABR implementation for CMS derivatives 528

Appendix 1 Contents of the CD ROM 531

Related reading 535

Web Links and Other Resources 537

Index 539