Excel Add-in Development in C/C++ -- Contents -- Preface -- 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.2.1 VBA macros and add-ins -- 1.2.2 C/C++ DLL add-ins -- 1.2.3 C/C++ DLLs that can access the C API and XLL add-ins -- 1.2.4 C/C++/C# .NET 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 organised -- 1.7 Scope and limitations -- 2 Excel Functionality -- 2.1 Overview of Excel data organisation -- 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.6.1 The unary = operator -- 2.6.2 The unary - operator (negation) -- 2.6.3 Number-arithmetic binary operators: + - */^ -- 2.6.4 Percentage operator: % -- 2.6.5 String concatenation operator: & -- 2.6.6 Boolean binary operators: =, , =, -- 2.6.7 Conversion of single-cell references -- 2.6.8 Conversion of multi-cell range references -- 2.6.9 Conversion of defined range names -- 2.6.10 Explicit type conversion functions: N(), T(), TEXT(), VALUE() -- 2.6.11 Worksheet function argument type conversion -- 2.6.12 Operator evaluation precedence -- 2.7 Excel terminology: Active and current -- 2.8 Commands versus functions in Excel -- 2.9 Types of worksheet function -- 2.9.1 Function purpose and return type -- 2.9.2 Array formulae - The Ctrl-Shift-Enter keystroke -- 2.9.3 Required, optional and missing arguments and variable argument lists -- 2.10 Complex functions and commands -- 2.10.1 Data Tables -- 2.10.2 Goal Seek and Solver Add-in -- 2.11 Excel recalculation logic -- 2.11.1 Marking dependents for recalculation -- 2.11.2 Triggering functions to be called by Excel - the trigger argument.

2.11.3 Volatile functions -- 2.11.4 Cross-worksheet dependencies - Excel 97/2000 versus 2002/2003 -- 2.11.5 User-defined functions (VB Macros) and add-in functions -- 2.11.6 Data Table recalculation -- 2.12 The Add-in Manager -- 2.13 Loading and unloading add-ins -- 2.13.1 Add-in information -- 2.14 Paste Function dialog -- 2.14.1 Function category -- 2.14.2 Function name, argument list and description -- 2.14.3 Argument construction dialog -- 2.15 Good spreadsheet design and practice -- 2.15.1 Filename, sheet title and name, version and revision history -- 2.15.2 Magic numbers -- 2.15.3 Data organisation and design guidelines -- 2.15.4 Formula repetition -- 2.15.5 Efficient lookups: MATCH(), INDEX() and OFFSET() versus VLOOKUP() -- 2.16 Some problems with very large spreadsheets -- 2.17 Conclusion -- 3 Using VBA -- 3.1 Opening the VB Editor -- 3.2 Using VBA to create new commands -- 3.2.1 Recording VB macro 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.5.1 Function scope -- 3.5.2 Declaring VB functions as volatile -- 3.6 Using VBA as an interface to external DLL add-ins -- 3.6.1 Declaring DLL functions in VB -- 3.6.2 Call-by-reference versus call-by-value -- 3.6.3 Converting argument and return data types between VB and C/C++ -- 3.6.4 VB data types and limits -- 3.6.5 VB/OLE Currency type -- 3.6.6 VB/OLE Strings -- 3.6.7 Passing strings to C/C++ functions from VB -- 3.6.8 Returning strings to VB from a DLL -- 3.6.9 Variant data type -- 3.6.10 Variant types supported by VBA -- 3.6.11 Variant types that Excel can pass to VB functions -- 3.6.12 User-defined data types in VB -- 3.6.13 VB object data type -- 3.7 Excel ranges, VB arrays, SafeArrays, array Variants -- 3.7.1 Declaring VB arrays and passing them back to Excel.

3.7.2 Passing arrays and ranges from Excel to VB to C/C++ -- 3.7.3 Converting array Variants to and from C/C++ types -- 3.7.4 Passing VB arrays to and from C/C++ -- 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.5.1 Name decoration -- 4.5.2 The extern "C" declaration -- 4.6 Function calling conventions: __cdecl, __stdcall, __fastcall -- 4.7 Exporting DLL function names -- 4.7.1 Definition (*.DEF) files -- 4.7.2 The _declspec(dllexport) keyword -- 4.8 What you need to start developing add-ins in C/C++ -- 4.9 Creating a DLL using Visual C++ 6.0 -- 4.9.1 Creating the empty DLL project -- 4.9.2 Adding code to the project -- 4.9.3 Compiling and debugging the DLL -- 4.10 Creating a DLL using Visual C++ .NET 2003 -- 4.10.1 Creating the empty DLL project -- 4.10.2 Adding code to the project -- 4.10.3 Compiling and debugging the DLL -- 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.2.1 Loading and unloading installed add-ins -- 5.2.2 Active and inactive add-ins -- 5.2.3 Deleted add-ins and loading of inactivate add-ins -- 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 -- 5.5.1 xlAutoOpen -- 5.5.2 xlAutoClose -- 5.5.3 xlAutoAdd -- 5.5.4 xlAutoRemove -- 5.5.5 xlAddInManagerInfo -- 5.5.6 xlAutoRegister.

5.5.7 xlAutoFree -- 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.2.1 Native C/C++ data types -- 6.2.2 Excel floating-point array structure: xl_array -- 6.2.3 The xloper structure -- 6.2.4 The xlref structure -- 6.2.5 The xlmref structure -- 6.2.6 The oper structure -- 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.8.1 Freeing xloper memory -- 6.8.2 Worksheet (floating point) number: xltypeNum -- 6.8.3 Byte-counted string: xltypeStr -- 6.8.4 Excel Boolean: xltypeBool -- 6.8.5 Worksheet error value: xltypeErr -- 6.8.6 Excel internal integer: xltypeInt -- 6.8.7 Array (mixed type): xltypeMulti -- 6.8.8 Worksheet cell/range reference: xltypeRef and xltypeSRef -- 6.8.9 Empty worksheet cell: xltypeNil -- 6.8.10 Worksheet binary name: xltypeBigData -- 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.3.1 Freeing xloper memory within the DLL call -- 7.3.2 Freeing xloper memory returned by the DLL function -- 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.1.1 Commands, worksheet functions and macro sheet functions -- 8.1.2 Commands that optionally display dialogs - the xlPrompt bit -- 8.2 The Excel4() C API function -- 8.2.1 Introduction -- 8.2.2 Excel4() return values.

8.2.3 Calling Excel worksheet functions in the DLL using Excel4 () -- 8.2.4 Calling macro sheet functions from the DLL using Excel4 () -- 8.2.5 Calling macro sheet commands from the DLL using Excel4 () -- 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.5.1 The xlfRegister function -- 8.5.2 Specifying which category the function should be listed under -- 8.5.3 Specifying argument and return types -- 8.5.4 Giving functions macro sheet function permissions -- 8.5.5 Specifying functions as volatile -- 8.5.6 Returning values by modifying arguments in place -- 8.5.7 The Paste Function dialog (Function Wizard) -- 8.5.8 Function help parameter to xlfRegister -- 8.5.9 Argument help parameters to xlfRegister -- 8.5.10 Managing the data needed to register exported functions -- 8.5.11 Getting and using the function's register ID -- 8.5.12 Un-registering a DLL function -- 8.6 Registering and un-registering DLL (XLL) commands -- 8.6.1 Accessing XLL commands -- 8.6.2 Breaking execution of an XLL command -- 8.7 Functions defined for the C API only -- 8.7.1 Freeing Excel-allocated memory within the DLL: xlFree -- 8.7.2 Getting the available stack space: xlStack -- 8.7.3 Converting one xloper type to another: xlCoerce -- 8.7.4 Setting cell values from a command: xlSet -- 8.7.5 Getting the internal ID of a named sheet: xlSheetId -- 8.7.6 Getting a sheet name from its internal ID: xlSheetNm -- 8.7.7 Yielding processor time and checking for user breaks: xlAbort -- 8.7.8 Getting Excel's instance handle: xlGetInst -- 8.7.9 Getting the handle of the top-level Excel window: xlGetHwnd -- 8.7.10 Getting the path and file name of the DLL: xlGetName -- 8.8 Working with binary names -- 8.8.1 The xltypeBigData xloper -- 8.8.2 Basic operations with binary names.

8.8.3 Creating, deleting and overwriting binary names.