Developers of Visual Basic for Applications code have often asked for enhanced programming functionality to ease their work. Office 2000 Developer meets this need and more with a comprehensive suite of new features.

If you're a productive software developer, you're not continually reinventing the wheel. When you need to solve specific business problems that involve users, messaging, databases, publishing, collaboration, and the Web, Microsoft® Office 2000 can be a powerful ally. Object reuse makes Office-based solutions faster to develop. In addition, you can incur lower training costs because users are on familiar ground. Microsoft Office 2000 Developer makes it even easier for you to create Office-based solutions.     Microsoft Office 2000 Developer is a comprehensive set of applications and tools for building and deploying solutions with Office 2000. It includes Microsoft Office 2000 Premium, which is comprised of Microsoft Excel 2000, Word 2000, Outlook® 2000, PowerPoint® 2000, Microsoft Access 2000, FrontPage® 2000, PhotoDraw™ 2000, Publisher 2000, Office 2000 Server Extensions, and Microsoft Internet Explorer 5.0. Office 2000 Developer also includes the Visual Basic® for Applications (VBA) productivity tools, Visual SourceSafe™ 6.0, HTML Help Workshop 1.2, Answer Wizard Builder 2.0, the Microsoft Developer Network (MSDN) Library for Office 2000 Developer, and—believe it or not—printed documentation.     In my December 1998 article, " Speed Your Documents to the Web with Office 2000 ," I mainly focused on enhanced Web capabilities. In this article I'll explore the Office 2000 Developer productivity tools, and I'll show you how the new extensibility architecture can be used to develop more maintainable solutions by creating a single COM add-in that can be shared across several Office 2000 applications. The Common Language     Office 2000 exposes over 600 objects that can be programmed with almost any development tool that supports COM. But you don't need special tools or a deep understanding of COM to program with Office 2000. That's because each application in Office 2000 that exposes its functionality speaks a common language: Visual Basic. In any Microsoft Office 2000 application except PhotoDraw and Publisher, simply press Alt+F11 (or select Tools | Macro | Visual Basic Editor) and you'll be in the Visual Basic Editor, the standard programming shell for Office 2000 applications and Visual Basic for Applications 6.0 (see Figure 1).

Figure 1: The Visual Basic Editor

VBA has appeared in over 70 different applications since Microsoft began licensing it in 1996. Because an increasing number of applications are exposing their functionality through VBA, it's worth your while to learn this simple-yet-powerful language, particularly since it will allow you to integrate Office-based solutions with line-of-business applications that support VBA.     Microsoft Office 2000 Developer enhances Visual Basic for Applications 6.0 with productivity tools for writing solid code, creating COM add-ins, managing versions, and then digitally signing and distributing your finished product. Some of these productivity enhancements are tightly integrated applications, like Visual SourceSafe 6.0 and the Code Librarian, and some are COM-based extensions for the VBA code programming environment like the VBA Code Commenter and DataEnviron­ment Designer. These add-ins are not automatically enabled when you install Office 2000 Developer; you'll need to do this explicitly.

Figure 2: The Add-In Manager

To enable the Visual Basic Editor add-ins, click Add-Ins | Add-In Manager in the Visual Basic Editor. This will open the Add-In Manager (see Figure 2). For each add-in, check the Load on Startup checkbox to load each add-in when you open the Visual Basic Editor. Once the add-ins are enabled and loaded, they will be displayed in the Add-Ins menu (see Figure 3).

Figure 3: Accessing Add-ins

Code Creation and Management Tools     Before I dive into how you can build your own COM add-ins, let's take a look at some of the available code management tools. The Visual Basic Editor is the built-in coding and debugging environment for Office 2000. It's similar to the programming environment found in previous versions of Microsoft Excel and Word. By itself, the Visual Basic Editor is an excellent tool for extending an individual application via macros. With Office 2000 Developer, it becomes much more powerful because you can compile your code into COM add-in DLLs. As a coding tool this editor has all the nice features you've come to know and love, like syntax highlighting, IntelliSense® code-completion, and object browsing. Microsoft Office 2000 Developer adds even more bells and whistles.     For the more monotonous coding tasks, Office 2000 Developer provides three useful add-ins: the VBA Code Commenter, the VBA Error Handler, and the VBA String Editor. The VBA Code Commenter (see Figure 4) helps you comment your code in a standard way. You can personalize the style by creating templates to control how the VBA Code Commenter marks up your code. This works by means of tokens, which the parser recognizes and expands. You can apply the VBA Code Commenter at the procedure, module, or project level (see Figure 5).

Figure 4: The VBA Code Commenter

Except for the dialog title, the VBA Error Handler looks exactly like the Code Commenter. The VBA Error Handler helps alleviate the monotonous task of adding error handling to every function or procedure.

Figure 5: Adding Comments to Your VBA Code

Dealing with long strings can be a pain in any programming language, especially strings that contain variables. This has always been a particular annoyance in Visual Basic, where lines of code don't wrap by default. For those times when the length of a string would surely exceed your edit window's width—for example, a SQL statement or a long HTML string—you can use the VBA String Editor (see Figure 6). This seemingly simple add-in can break down a string into multiple concatenated lines so it's more readable. It also lets you work with variable-laden strings in a more natural way. If you've spent any time hunting for syntax errors in a long string concatenation, you'll find this feature was long-overdue.

Figure 6: VBA String Editor

For more complex code management tasks, Microsoft Office 2000 Developer offers three more tools: the Code Librarian, the WinAPI Viewer, and Visual SourceSafe 6.0. If you've ever needed to hunt for a code snippet from your past because it's exactly what you need to solve a current problem, then you'll appreciate the Code Librarian (see Figure 7). The Code Librarian, which can also be used as a standalone application, lets you store, index, search, find, and share those useful snippets.

Figure 7: The Code Librarian

The Code Librarian stores code in Jet (Microsoft Access) databases. Office 2000 Developer comes with one such database (codelib.mdb) that's packed with VBA code to perform tasks ranging from finding the volume of a sphere to creating an appointment in Outlook. You can search by key­word or do a full-text search to find the code you need. You can then copy the code to the clipboard, or have the Code Librarian insert it into the active module. Since the Code Librarian can be run as a standalone application, it can be used in other development environments. You can create your own code libraries to store whatever code you want, even in other languages like C++ or Java.     The WinAPI Viewer (see Figure 8) is similar to the API viewer that came with older versions of Visual Basic and more recent versions of Visual Studio®. It's a read-only tool that lets you browse through Declares, Constants, and Types that are included in an API text file or a Jet database. This new version has been updated to insert code directly into your VBA modules so you don't have to copy and paste using the clipboard.

Figure 8: The WinAPI Viewer

Visual SourceSafe 6.0 is tightly integrated with the Visual Basic Editor environment (see Figure 9). It lets you store and track multiple versions of VBA code at the module or document level (see Figure 10). Although integrated with the Visual Basic Editor, it is a fully functional release of Visual SourceSafe 6.0. As such, you can use it as a standalone or with other development tools.

Figure 9: Using Source Code Control

Figure 10: Checking in Files

Office 2000 Developer also includes the Multi-Code Export and Multi-Code Import add-ins for exporting and importing VBA code. The Multi-Code Export (see Figure 11) can be used to extract Visual Basic for Applications code modules or class objects from the current project. When you export modules or classes, Multi-Code Export saves them as .bas or .cls files, respectively. These can later be imported into different VBA projects with the Multi-Code Import add-in.

Figure 11: Multi-Code Export

Documentation Galore     Having an editor, debugger, and code management tools without good documentation is like sailing without a compass. If you've ever tried to extend something as complex as Microsoft Outlook, you'll appreciate the comprehensive printed documentation that comes with Office 2000 Developer. Included are an Object Model Guide, a Visual Basic Program­mer's Guide, and a special version of the MSDN Library for Office 2000 Developer.     The Microsoft Office 2000 Visual Basic Programmer's Guide contains reams of sample code. Weighing in at over 800 pages, it's heavy reading (pun intended). There are detailed explanations of when and why you might want to take a certain approach, and step-by-step instructions down to the menu item/checkbox level for most tasks. It also includes a section entitled "Writing Solid Code" that does a good job of explaining how to make your code more readable and maintainable. There are also chapters on other useful topics, including the Windows® API, adding HTML Help to your program, working with Dynamic HTML (DHTML), and Data Access Technologies. In general, it's a great read: not so low-level that it's distracting, and not so high-level that it's useless.     The Microsoft Office 2000 Object Model Guide contains over 50 pages of graphical object model diagrams for the major Office applications. It also contains object model diagrams for the not-so-obvious technologies that are available to Office developers, such as Internet Explorer 5.0, the Scripting Runtime, and the Data Source Control. This invaluable guide gives a hierarchical overview of each object model, the name and location of its type library, when and how it's installed, and the name of the help file that contains more information about the object. Defining a Problem     To fully appreciate Office 2000 Developer, you need a frame of reference, so let's define and solve a real-world problem. Suppose you wanted to give users of Microsoft Excel, Word, and Front­Page in your company a way to query and publish daily sales information. You want the information to be delivered in a way that's appropriate for the application they're using. So if they're using Word, then the information should be delivered in a Word-friendly format; if they're using Microsoft Excel, then the information should be delivered in spreadsheet format.     Let's also require that the user be able to choose the date for the query, just so you have an excuse to create a dialog that's shared across applications. When you require the solution to work within other Office applications, you'll begin to see the value of the COM add-in architecture in Office 2000. COM Add-ins Versus App-specific Add-ins     An add-in extends the capabilities of an application in a tightly integrated way by taking advantage of that application's extensibility model. In Office 2000, there are two different types of add-ins: the older-style ap­plication-specific add-ins and the newer-style COM add-ins. Choosing between the two will depend on the problem you're trying to solve and the tools you have at hand. With Office 2000 you can create only application-specific add-ins. With Office 2000 Developer you can create both kinds.     An application-specific add-in works for only one application and is easy to distribute. You can simply ship an Office document that contains some code, usually in the form of a macro. Viruses like the infamous Melissa have given macros a bad name. However, they're still useful because they're easy to create. In other words, you don't need extra development tools.     But to solve a problem like the one I've defined, you'd have to create and maintain an application-specific add-in for each Office application. A better solution is to make use of the COM add-in architecture in Office 2000 to create a single COM object that can be loaded and used by many Office applications.     So what exactly is a COM add-in? Simply put, COM add-ins are DLLs or EXEs that are specially registered so they can be loaded by Office 2000 applications at startup. More tech­nically, a COM add-in for Office 2000 is any in-process or out-of-process COM object that implements the IDT­Exten­sibility2 interface and is set up properly in the registry.     The documentation discusses the appropriate registry settings for building COM add-ins with development tools that don't support COM add-in designers. This feature of Office 2000 Developer handles these mundane registry details for you. To create a COM add-in, you must have a tool like Microsoft Office 2000 Developer, Visual Basic, Visual C++®, or Visual J++®.     As I've mentioned, the main advantage of COM add-ins is that they can function in more than one Office 2000 application. Code that would normally be replicated across multiple application-specific macros can be written once and shared by different Office applications, or even other COM add-ins. This makes the code easier to maintain, though you must still write application-specific code to interface your COM add-in with the host applications it extends.     COM add-ins can also extend the functionality of the Visual Basic Editor environment; in other words, you can write code to help others write code. When you create a COM add-in for the Visual Basic Editor it appears in all instances of the Visual Basic Editor, no matter which Office application it's started from. Security and Add-ins     If Office 2000 Developer and COM add-ins are such a powerful combination, couldn't they be used to harm a user's data? Microsoft is well aware of the potential for problems caused by macro viruses over the last few years, and has taken steps to make Office 2000 safer for users. Office 2000 can be configured for dif­fer­ent trust levels (see Figure 12), a much-needed security en­hance­ment that will help prevent damage by hackers. The highest security level only runs macros from trusted sources. As a developer of Office-based apps, you can digitally sign your add-ins once you obtain a digital certificate from a certificate authority. Then users can tell their Office 2000 applications to trust your add-ins. Building a COM Add-in     Let's build a COM add-in that integrates with Microsoft Excel, Word, and FrontPage via a CommandBar to query and report sales information. For this solution I created a simple Microsoft Access database (see Figure 13) that stores information containing the date, product, salesperson, and amount of each sale.

Figure 13: A Sample Database

Start an Office 2000 application (I'm using Word 2000) and open up the Visual Basic Editor. In the editor, select File | New Project. From the New Project dialog, select Add-In Project and click OK. The Visual Basic Editor will create a Visual Basic for Applications project with an add-in designer. The finished project will have an add-in designer for each Office application you want to extend. Let's make this first add-in designer interface with Word (see Figure 14).

Figure 14: Add-in Designer

When your COM add-in is loaded, it needs to display a command bar with a button that users can click to invoke the sales report dialog. It should remove this command bar when it's unloaded to keep from cluttering the application's interface or confusing the user. The default im­ple­mentation provided by the add-in designer notifies you when the add-in is loaded, but not when it's unloaded. To catch that event, you must implement the IDTExten­sibility2 interface. COM Interfaces     An interface is like a contract. If you declare that your object implements a given interface, then you must implement all of the methods in the interface, even if they don't do anything. In this example, it means that all four IDTExten­sibility2 interface methods must be present in your mod­ule, even though you are only using On­Connec­tion and OnDiscon­nec­tion to add and remove a command bar. Furthermore, since the Visual Basic for Applications compiler removes empty procedures, there has to be at least one line of code (or a comment) in the implementation of each method of the interface.     Since you've implemented the IDTEx­tensibility2 interface, your COM add-in will be notified via the OnConnection method when it's loaded by an Office application. When this method is called, it's your chance to create a command bar in the parent Office application, add a button to it, and register an event handler to process click events on that button. To make the add-in more maintainable, I've placed the common command bar routines in a separate module, Common­Routines.bas, that can be reused by add-in designers for other Office applications (see Figure 15).     Add­Command­Bar functions as its name suggests. When the add-in designer's OnCon­nection method is invoked by the calling application (in this case Word), it creates a new command bar with one button, Daily Sales, and docks it at the top (see Figure 16).

Figure 16: Daily Sales Add-in

Adding this same command bar to Microsoft Excel and FrontPage is a simple matter of creating an add-in designer targeted at those applications. The way I've structured this particular add-in, most of the add-in designer code is an implementation of the IDTExtensibility2 interface. The only difference is which function is called when the user clicks the button (see Figure 17).     You can add designers for as many Office applications as you want to support. Later on I'll return to the code in each designer and modify the handler ButtonEventHandler_Click so that it calls the appropriate application-specific function.     At this point, it's probably a good idea to compile and test your COM add-in. You should have three add-in designers: one for Microsoft Excel, one for FrontPage, and one for Word. Compile your add-in by selecting Make from the File menu. Alternatively, you can customize your Visual Basic Editor toolbar as I've done here so that building the add-in is only a click away. Finding the COM Add-in Button     By default, Office 2000 applications don't seem to have an option to load COM add-ins. The capability is there—it's just buried deep inside the Customize dialog. To make it easy to load and unload COM add-ins, you should find the COM Add-Ins command and drag and drop it onto one of your toolbars (see Figure 18).

Figure 18: Customizing the Toolbar

Enable your COM add-in in each application (see Figure 19). Make sure your add-in loads properly in all three applications, and places a new command bar at the top of each window. When you remove the add-in from each application, this command bar should be removed as well.

Figure 19: Enabling Add-ins

Next, add a UserForm that your COM add-in can display and that lets the user select a date for the daily sales report. You've already created shared code in your add-in. This UserForm will be a shared GUI. UserForms can be containers for ActiveX® controls like the Calendar control I've used here (see Figure 20).     The rest is basic plumbing. You need one common procedure to query data based on the date selected in the form, and you need three application-specific procedures that can properly format the data for the parent application. Take a look at the code in Figure 21 to see my implementation of this part of the program.     I created an application-inde­pendent function called GetRecords that gets a date from the user via the User­Form, and returns an ADO re­cordset with the results. I used a Data­En­vironment designer to make it simpler to connect to the data source. If you download the code for this article, then you'll need to update the Connection1 object in this designer (deSalesDatabase) to point to the location of the sales.mdb file.     The application-specific portions of your add-in will handle it from there. When your COM add-in is running in Word, it displays the date-picking dialog shown in the center of Figure 20 and calls the GetDailySalesReportForWord function. This function creates a table at the current insertion point in Word and populates it with sales data (see Figure 22).

Figure 22: Populating the Table with Data

Similarly, when the add-in is running in Microsoft Excel it displays the same dialog, but calls the routine Get­Daily­Sales­Report­ForExcel, which populates cells in the current spreadsheet (see Figure 23).

Figure 23: Populating Cells

And finally, when running in FrontPage, the GetDailySalesReportForFrontPage function creates an HTML table in the current Web page (see Figure 24). Attacking the problem this way allows you to reduce the complexity of working with three separate Office applications. There is only one module to modify to maintain the solution.

Figure 24: An HTML Table

Visual Basic Editor Stability     The Visual Basic Editor runs in the same process as the Office application that spawns it. If your COM add-in misbehaves and crashes the host Office application, it will take down the Visual Basic Editor with it. My advice, therefore, is to start the Visual Basic for Applications 6.0 editor environment from a different Office application than the one you're currently targeting in your edit/build/test cycle; this way your code will be safer.     Note that I said different application instead of different instance. This is because some Office applications like Word don't start a new process each time you run them from the Windows Start menu. Remember that you also can use Visual Basic 6.0 to create COM add-ins. The documentation covers that topic extensively.     I'll leave it to you to add a help file. You can do that with the HTML Help Workshop, which has a wizard to guide you through the process. Once completed, you can package your solution for other Office 2000 users with the Package and Deployment Wizard (see Figure 25). This lets you distribute your Office 2000 solution as a standalone setup program. It will even deploy your solution to the Web so other Office users can easily download and install it.

Figure 25: Package and Deployment Wizard

Conclusion     The problem I defined and solved for this article demonstrates the value of Office 2000 Developer beyond the already significant capabilities of Office 2000. The fact that most users are already familiar with Office is a compelling reason to create solutions that complement the suite. I have shown you several of the new productivity tools, and covered the basics for extending Office 2000 with COM add-ins—an ideal way to extend the Office 2000 applications. Feel free to build on the code I provided to create your own solutions.

http://msdn.microsoft.com/library/welcome/dsmsdn/gilbert_qa.htm and http://msdn.microsoft.com/library/techart/COMAddins.htm

From the July 1999 issue of Microsoft Internet Developer.