Introduction

The Microsoft Excel Developer's Kit provides information for software developers who want to develop applications that interact with Microsoft Excel. The Microsoft Excel Developer's Kit is a programming and technical reference. It is not a complete guide to Visual Basic programming and discusses the Microsoft Excel macro language (XLM) only in the context of add-in functionality. It assumes an understanding of Microsoft Excel's capabilities as exposed to the user.

This book contains everything you need to know to use the Microsoft Excel Developer's Kit. It assumes that you already know C and that you are familiar with Microsoft Excel and Visual Basic, Applications Edition (VBA). Visual Basic is described in the Microsoft Excel Visual Basic User's Guide and the Microsoft Excel Visual Basic Language Reference.

If you plan to develop applications for Microsoft Windows, you should know the basics of Microsoft Windows programming and how to write DLLs.

A CD accompanies this book. It contains examples and sample files to support the topics discussed in the following chapters. For information on the contents of this CD, see "Excel Developer's Kit CD-ROM," later in this chapter.

Because Microsoft Excel includes two macro languages and other extension mechanisms, it is not always easy to determine which parts of Microsoft Excel are best suited for your application. The following sections outline the available components, typical scenarios for use, and advantages and disadvantages of each.

Extending Microsoft Excel

Chapter 1

Provides a strong foundation for writing great add-ins and handling the common issues related to integration with Microsoft Excel. This includes some transitional issues from previous versions along with conceptual frameworks for your add-ins.

Chapter 2

Focuses on real-world Wizards using VBA. This includes a strong structured strategy for handing initialization and validation for your Wizards. The source code example is included on the CD.

Chapter 3

Discusses the transitional issues from Microsoft Excel 5/95 to Microsoft Excel 97 from a developer's perspective. This includes VBA issues, converting the Microsoft Excel Applications Programming Interface (C API) to 32-bit code, and porting guidelines for converting C API applications to use OLE Automation.

Chapter 4

Describes OLE concepts, including early and late binding, in process versus out of process, and the performance and programming implications of each option.

Calling DLLs from VBA

Chapter 5

Provides examples of DLL functions written in C that can be called from Visual Basic in Microsoft Excel. Text and examples show how Visual Basic data types (such as arrays, objects, and variants) are passed to DLL functions from Microsoft Excel.

Chapter 6

Discusses the OLE Automation Interface in the Microsoft Excel 97 context.

Because VBA is more powerful than the Microsoft Excel macro language (XLM), you can often write almost all of your custom functions and procedures in Visual Basic. Even with the added power of Visual Basic, however, it may be faster to perform some tasks from a C-language DLL. You may also need to write an external DLL function in C because you need to call a system-level function with a parameter-passing style or memory-management constraint not supported by Visual Basic.

Examples of tasks that must be performed in C include calls to functions that use arrays of structures contained within an enclosing structure or functions that require dynamic memory allocation. Visual Basic's built-in data types and memory-management systems cannot support these complex tasks; therefore a Visual Basic macro that needs to call these functions must call an intermediate C-language function that builds the structures or manages memory to match the function's calling conventions.

It is also appropriate to write DLL functions that are called from Visual Basic if you need to perform speed-intensive calculations (which is one of the same reasons you'd use the C API). If you need to use Microsoft Excel objects as a part of the calculation, you can use the techniques in Chapter 7 to pass objects and other complex parameters to a DLL function and then use the techniques in Chapter 8 to manipulate the Microsoft Excel objects.

OLE vs. C API

Chapter 7

Describes the OLE versus the C API. The strengths and weaknesses of each are highlighted.

Chapter 8

Is a function reference for the C API.

You can use the C API to create new custom worksheet functions (functions that can be entered into a cell on a worksheet), to create new macro functions (optimized for use from Microsoft Excel XLM macro sheets), and to create add-ins written in C but that use Microsoft Excel commands and functions to perform actions and calculate values.

Although you can write worksheet functions in both Visual Basic and the Microsoft Excel macro language, if the function requires high-speed or data-intensive calculations, it may be more appropriate to write the function in C. The Microsoft Excel C API is the best way to integrate external custom worksheet functions with Microsoft Excel.

Applications that require high-speed data transfer from an external source into Microsoft Excel can use the C API. A typical use of this capability is a macro function that retrieves data from an external database and puts the data on a worksheet. Other examples include a function that retrieves data from an external source, such as a stock ticker, or a calculation-intensive statistical analysis function.

Disadvantages of the C API include the following:

• Because the C API is optimized for use from the Microsoft Excel macro language and the worksheet, it is not a very good mechanism for writing external functions to be used by Visual Basic (although Visual Basic and the C API can be combined into hybrid solutions).
• The C API is not object-oriented (because it is based on the Microsoft Excel XLM macro language). You cannot use the C API to access Visual Basic objects, properties, and methods.
• Because the C API can be used only from a DLL loaded in the Microsoft Excel process, an external application cannot use the C API to control Excel.
• The C API cannot be used unless Microsoft Excel has explicitly called the DLL function in response to a menu, toolbar, cell calculation, or event.

Appendixes

Appendix A, "Dynamic Data Exchange and XlTable Format"

Documents the dynamic data exchange (DDE) formats supported by Microsoft Excel and provides detailed information about the high-performance XlTable DDE format.

Appendix B, "Excel 97 and the Registry"

Details the keys and values used with Microsoft Excel for add-ins and many of the program settings that are retained from session to session.

Appendix C, "Displaying Custom Help"

Provides information about integrating custom help files for your application.

Appendix D, "Wizard Source Code"

Is a complete source code listing for the structured Wizard example provided in Chapter 2.

Excel Developer's Kit CD-ROM

included CD-ROM contains the following:

• The INCLUDE folder contains the header file XLCALL.H, which must be included in all C source files that call Microsoft Excel functions. The workbook Intlmap.xls in this folder contains the XLCALL.H constants translated into several languages.
• The LIB folder contains the library file XLCALL32.LIB. If you need to call Microsoft Excel functions, this library file is linked to your code. XLCALL32.LIB is a Windows import library; it doesn't contain actual code. You can use IMPORT statements in your linker definition (.DEF) file instead of linking to this library. Also included is XLCALL.LIB, which is the 16-bit version of this library used for DLLs written to be used by 16-bit versions of Excel. Lastly, FRMWRK32.LIB is the compiled library of the framework sample.

• The SAMPLES folder contains several subdirectories with sample source code, which is intended as a learning resource and includes extensive comments. Refer to Samples\ReadMe.txt for a description of each sample.
• The BIFF folder contains the BIFF viewing utility, BIFFVIEW.EXE.
• The HELP folder contains sample files for Appendix C, "Displaying Custom Help."
• The XLM folder contains the Microsoft Excel XLM Macro Function Reference help file.

Microsoft Technical Support

In the event you have a technical question about the Microsoft Excel 97 Developer's Kit, Microsoft offers technical support and services ranging from no-cost and low-cost online information services to annual support plans with a Microsoft technical engineer. Below is a brief description of these services to help you decide which option is right for you.

For more information about this and other services available in the United States and Canada, visit our Web site at:

http://www.microsoft.com/support/

Note

The services and prices listed here are available in the United States and Canada only. Services and prices outside these countries may vary.