Excel Add-in Development in C/C++ Applications in Finance Steve Dalton Excel Add-in Development in C/C++ Wiley Finance Series For other titles in the Wiley Finance Series please see www.wileyeurope.com/finance Excel Add-in Development in C/C++ Applications in Finance Steve Dalton Copyright2005 JohnWiley&SonsLtd,TheAtrium,SouthernGate,Chichester, WestSussexPO198SQ,England Telephone(+44)1243779777 Email(forordersandcustomerserviceenquiries):[email protected] VisitourHomePageonwww.wileyeurope.comorwww.wiley.com AllRightsReserved.Nopartofthispublicationmaybereproduced,storedinaretrievalsystemor transmittedinanyformorbyanymeans,electronic,mechanical,photocopying,recording,scanningor otherwise,exceptunderthetermsoftheCopyright,DesignsandPatentsAct1988orunderthetermsofa licenceissuedbytheCopyrightLicensingAgencyLtd,90TottenhamCourtRoad,LondonW1T4LP,UK, withoutthepermissioninwritingofthePublisher.RequeststothePublishershouldbeaddressedtothe PermissionsDepartment,JohnWiley&SonsLtd,TheAtrium,SouthernGate,Chichester,WestSussexPO19 8SQ,England,[email protected],orfaxedto(+44)1243770620. Designationsusedbycompaniestodistinguishtheirproductsareoftenclaimedastrademarks.Allbrand namesandproductnamesusedinthisbookaretradenames,servicemarks,trademarksorregistered trademarksoftheirrespectiveowners.ThePublisherisnotassociatedwithanyproductorvendormentioned inthisbook. Thispublicationisdesignedtoprovideaccurateandauthoritativeinformationinregardtothesubjectmatter covered.ItissoldontheunderstandingthatthePublisherisnotengagedinrenderingprofessionalservices.If professionaladviceorotherexpertassistanceisrequired,theservicesofacompetentprofessionalshouldbe sought. OtherWileyEditorialOffices JohnWiley&SonsInc.,111RiverStreet,Hoboken,NJ07030,USA Jossey-Bass,989MarketStreet,SanFrancisco,CA94103-1741,USA Wiley-VCHVerlagGmbH,Boschstr.12,D-69469Weinheim,Germany JohnWiley&SonsAustraliaLtd,33ParkRoad,Milton,Queensland4064,Australia JohnWiley&Sons(Asia)PteLtd,2ClementiLoop#02-01,JinXingDistripark,Singapore129809 JohnWiley&SonsCanadaLtd,22WorcesterRoad,Etobicoke,Ontario,CanadaM9W1L1 Wileyalsopublishesitsbooksinavarietyofelectronicformats.Somecontentthatappears inprintmaynotbeavailableinelectronicbooks. LibraryofCongressCataloging-in-PublicationData Dalton,Steve. Exceladd-indevelopmentinC/C++:applicationsinfinance/SteveDalton. p.cm. Includesbibliographicalreferencesandindex. ISBN0-470-02469-0 1. MicrosoftExcel(Computerfile)2. Business–Computerprograms.3. C(Computerprogram language)4. C++(Computerprogramlanguage)5. Computersoftware–Development. I.Title. HF5548.4.M523D35 2004 005.54–dc22 2004016908 BritishLibraryCataloguinginPublicationData AcataloguerecordforthisbookisavailablefromtheBritishLibrary ISBN0-470-02469-0 Typesetin10/12ptTimesbyLaserwordsPrivateLimited,Chennai,India PrintedandboundinGreatBritainbyAntonyRoweLtd,Chippenham,Wiltshire Thisbookisprintedonacid-freepaperresponsiblymanufacturedfromsustainableforestry inwhichatleasttwotreesareplantedforeachoneusedforpaperproduction. Contents Preface xv Acknowledgements xvii 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.2.1 VBA macros and add-ins 3 1.2.2 C/C++ DLL add-ins 3 1.2.3 C/C++ DLLs that can access the C API and XLL add-ins 4 1.2.4 C/C++/C# .NET add-ins 4 1.3 To which versions of Excel does this book apply? 4 1.4 About add-ins 5 1.5 Why is this book needed? 6 1.6 How this book is organised 7 1.7 Scope and limitations 8 2 Excel Functionality 9 2.1 Overview of Excel data organisation 9 2.2 A1 versus R1C1 cell references 9 2.3 Cell contents 10 2.4 Worksheet data types and limits 10 2.5 Excel input parser 12 2.6 Data type conversion 12 2.6.1 The unary = operator 13 2.6.2 The unary − operator (negation) 13 2.6.3 Number-arithmetic binary operators: + - */^ 13 2.6.4 Percentage operator: % 13 2.6.5 String concatenation operator: & 13 2.6.6 Boolean binary operators: =,<,>,<=, >=,<> 13 2.6.7 Conversion of single-cell references 14 2.6.8 Conversion of multi-cell range references 14 2.6.9 Conversion of defined range names 15 2.6.10 Explicit type conversion functions: N(), T(), TEXT(), VALUE() 16 vi Contents 2.6.11 Worksheet function argument type conversion 16 2.6.12 Operator evaluation precedence 18 2.7 Excel terminology: Active and current 19 2.8 Commands versus functions in Excel 19 2.9 Types of worksheet function 21 2.9.1 Function purpose and return type 21 2.9.2 Array formulae – The Ctrl-Shift-Enter keystroke 21 2.9.3 Required, optional and missing arguments and variable argument lists 22 2.10 Complex functions and commands 22 2.10.1 Data Tables 22 2.10.2 Goal Seek and Solver Add-in 23 2.11 Excel recalculation logic 24 2.11.1 Marking dependents for recalculation 25 2.11.2 Triggering functions to be called by Excel – the trigger argument 26 2.11.3 Volatile functions 26 2.11.4 Cross-worksheet dependencies – Excel 97/2000 versus 2002/2003 27 2.11.5 User-defined functions (VB Macros) and add-in functions 29 2.11.6 Data Table recalculation 31 2.12 The Add-in Manager 32 2.13 Loading and unloading add-ins 32 2.13.1 Add-in information 33 2.14 Paste Function dialog 33 2.14.1 Function category 34 2.14.2 Function name, argument list and description 34 2.14.3 Argument construction dialog 34 2.15 Good spreadsheet design and practice 35 2.15.1 Filename, sheet title and name, version and revision history 35 2.15.2 Magic numbers 35 2.15.3 Data organisation and design guidelines 36 2.15.4 Formula repetition 37 2.15.5 Efficient lookups: MATCH(), INDEX() and OFFSET() versus VLOOKUP() 37 2.16 Some problems with very large spreadsheets 40 2.17 Conclusion 40 3 Using VBA 41 3.1 Opening the VB Editor 41 3.2 Using VBA to create new commands 42 3.2.1 Recording VB macro commands 42 3.3 Assigning VB command macros to control objects in a worksheet 44 3.4 Using VBA to trap Excel events 45 3.5 Using VBA to create new functions 47 3.5.1 Function scope 47 3.5.2 Declaring VB functions as volatile 47 Contents vii 3.6 Using VBA as an interface to external DLL add-ins 48 3.6.1 Declaring DLL functions in VB 48 3.6.2 Call-by-reference versus call-by-value 48 3.6.3 Converting argument and return data types between VB and C/C++ 49 3.6.4 VB data types and limits 50 3.6.5 VB/OLE Currency type 51 3.6.6 VB/OLE Strings 52 3.6.7 Passing strings to C/C++ functions from VB 54 3.6.8 Returning strings to VB from a DLL 55 3.6.9 Variant data type 57 3.6.10 Variant types supported by VBA 58 3.6.11 Variant types that Excel can pass to VB functions 59 3.6.12 User-defined data types in VB 62 3.6.13 VB object data type 64 3.7 Excel ranges, VB arrays, SafeArrays, array Variants 64 3.7.1 Declaring VB arrays and passing them back to Excel 66 3.7.2 Passing arrays and ranges from Excel to VB to C/C++ 68 3.7.3 Converting array Variants to and from C/C++ types 69 3.7.4 Passing VB arrays to and from C/C++ 70 3.8 Commands versus functions in VBA 71 3.9 Creating VB add-ins (XLA files) 72 3.10 VB versus C/C++: Some basic questions 72 4 Creating a 32-bit Windows (Win32) DLL Using Visual C++ 6.0 or Visual Studio .NET 75 4.1 Windows library basics 75 4.2 DLL basics 75 4.3 DLL memory and multiple DLL instances 76 4.4 Multi-threading 76 4.5 Compiled function names 77 4.5.1 Name decoration 77 4.5.2 The extern "C" declaration 78 4.6 Function calling conventions: cdecl, stdcall, fastcall 79 4.7 Exporting DLL function names 80 4.7.1 Definition (*.DEF) files 81 4.7.2 The _declspec(dllexport)keyword 82 4.8 What you need to start developing add-ins in C/C++ 82 4.9 Creating a DLL using Visual C++ 6.0 83 4.9.1 Creating the empty DLL project 83 4.9.2 Adding code to the project 84 4.9.3 Compiling and debugging the DLL 86 4.10 Creating a DLL using Visual C++ .NET 2003 87 4.10.1 Creating the empty DLL project 87 4.10.2 Adding code to the project 91 4.10.3 Compiling and debugging the DLL 91