Offerings for the Millennium

By David Mendlen

It's a great time to be an Office and VBA developer. There are close to 90 shipping applications that use VBA, and Office 2000 is about to ship. In addition, Microsoft has added VBA to Outlook 2000 and FrontPage 2000, and created Office 2000 Developer, the edition of Office designed to meet the needs of professional developers building solutions with Office and VBA.

How many people use Office as part of their custom solutions? Over 53 percent of professional developers. That's over two million developers, making Office one of the largest development platforms in the world. It makes sense; solutions built with Office get built faster, require less end-user training, and deliver corporate data directly to the desktop. Of course, I don't need to tell you that; but I do need to tell you about Office 2000 Developer - why we created it, what it is, and why it will help you succeed in building solutions with Office 2000.

Developers are not the "quietest" group of folks. You tell us what you think, what you love, what you hate, and what you want. Over the past two years, we've attended conferences, monitored news groups, talked to customers, and read e-mails from customers. This feedback was incorporated into the design goals we had for Office 2000 Developer with the overall goal of providing everything needed to build solutions with Office 2000.

This article will cover the design goals for Office 2000 Developer and the ways in which we've delivered on those goals. It will also highlight some of the great new features of Office 2000 Developer that take advantage of the programmability enhancements in Office, specifically VBA throughout Office and third-party applications, enhanced access to data, and a consistent add-in model across the Office applications. First, I'll discuss the design goals for Office 2000 Developer, then drill down into how we implemented features in Office 2000 Developer to meet these needs.

Design goal one: Build solutions faster. We know that developers learn through trial and error. Just because you're good at Access development and know VBA doesn't mean you can successful jump to Word or Excel. While VBA introduced a consistency for the developer building solutions with Office, developers still needed to understand each application's specific object model when customizing the application. We need to provide better documentation and samples for you to quickly build solutions.

We know how frustrating it can be to buy a developer tool, then find you don't have all the pieces you need. Either you're missing the printed documentation, or you need to buy a different tool to do what you want. A key challenge is to provide everything you need to be successful in one box. Another is to provide developers with a repository for code snippets they use frequently.

With Office 97, if you want to write an add-in that works in all the Office applications, you have to write code in many places. For example, if you wanted to write an add-in that retrieves customer information from a centralized database, and you wanted it to function in Word, Excel, Access, and PowerPoint, you'd have to write the same code in a Word .DOT file, an Excel .XLA file, an Access .MDB file, and a PowerPoint .PPA file. The big issue is that you're not centralizing your source code. When you update the .PPA code, you also have to update the others, for example. And why can't you compile your code as a single DLL that all the Office applications use?

In brief, the biggest thing we heard from you is that you need tools that simplify the process of writing code. You told us to "Get back to basics."

Design goal two: Easy access to data. You need to connect your applications to corporate data. For that data to be meaningful, it needs to be turned into information that can be used within business processes by knowledgeable workers who can organize it, synthesize it, and eventually publish it to others.

One problem that has plagued Office developers is their own success. It turns out that you tend to build solutions for a handful of people. Then word spreads about how easy your solution is to use, and before you know it, your application needs to support hundreds or thousands of users. That means you have to wrestle with the Access Upsizing Wizard, which isn't always a 100 percent solution. You need tools that let you upsize without the hassle.

Given that developers are working with data, they need tools to graphically manipulate their queries. ActiveX Data Objects (ADO) 2.0 is a great way to programmatically manipulate both relational databases, such as Oracle and SQL Server, and unstructured data stores, such as the NT file system, your mail system, or Microsoft Index Server. Although ADO is easier to use than ODBC APIs, it still requires code and knowledge of SQL. Developers need tools that simplify their access to data.

Design goal three: Simplify deploying and managing solutions. Whether you're writing code in Visual C++ or Microsoft Excel, you're writing code. That means you need tools to manage the development process - tools that enable team development and support your ability to roll back to some prior known-good-version.

In the past, developers in large organizations have struggled with the task of deploying their finished solutions to a large number of desktop machines. To make matters worse, there are now more options for deploying applications. Programmers can target floppy disks, CD-ROMs, or even Web-based downloads. Therefore, the tools need to support all these options, and they need to make it easy to build these setups.

All the pieces you need. To provide Office 2000 developers with a complete set of tools, Office 2000 Developer includes Office 2000 Premium, the edition of Office that provides the full set of desktop productivity applications. Office 2000 Premium includes:

• Word 2000
• Excel 2000
• PowerPoint 2000
• Access 2000
• Outlook 2000
• Publisher 2000
• FrontPage 2000
• PhotoDraw 2000
• Small Business Tools

For a description of the programmability enhancements found in Office 2000 Premium, please refer to the Office 2000 article mentioned earlier. We also include Microsoft Visual SourceSafe 6.0 for source code control and to facilitate team development.

Provide better documentation and samples. One of the things that developers really liked in Office Developer Edition '97 was the printed documentation. We've delivered on that again, but have taken it a step further; we're still including printed docs (Microsoft Office 2000/Visual Basic Programmer's Guide), but we've also provided additional sample code and solutions, as well as the electronic form of the book - all on the Office Developer CD. This book is about using the applications and components in Office 2000 to build custom solutions. It's the definitive resource for developers creating custom solutions based on Office applications. From the overall design of your solution to the nitty-gritty details of implementing it, this book tells you what you need to know to get things done quickly and efficiently. All the sample code and solutions referenced in Microsoft Office 2000/Visual Basic Programmer's Guide are included on the Office 2000 Developer CD-ROM.

We also ship the Object Model Guide. This document consists of object model diagrams for each Office application, as well as components available for use within an Office application. The object model diagrams included show how the objects in an object model fit together. You can use these diagrams as a handy shortcut to finding the object you want to work with, and for understanding how that object fits into the overall object model exposed by an application.

Of course, you also need the MSDN libraries. The version of the MSDN libraries that ship with Office Developer 2000 includes the entire library of development knowledge on Microsoft platforms and technologies, plus specific Help files for Office 2000 Developer features.

Code reuse. Ever wish you could get your hands on that bit of code for reading in text files? How about that time you copied a recordset into Word and programmatically generated a table? The VBA Code Librarian is a new add-in available in VBA used to store and retrieve code modules, functions, and snippets. It's organized by categories and keywords (see FIGURE 1). In addition, developers can easily drag code from the Code Librarian into the Code Editor.

FIGURE 1: The Code Librarian displays a hierarchical tree of code modules.

For example, a developer creates a large number of code modules and functions that will be reused. In the past, the developer would store these items in separate folders and attempt to remember the folder locations and module names. As the developer's personal library grows larger, it becomes difficult to organize and remember what each module represents. With the Code Librarian add-in loaded, the developer can move existing reusable modules and functions into the Code Librarian, then categorize each piece of code. Furthermore, finding the right snippet by using the search capabilities to search through the code title and descriptions, as well as the code itself, is simple. Once the developer finds the right piece of code, it can be inserted directly into current modules.

After the proper code is located, a developer can drag the code snippet icon into the Visual Basic Editor. The inserted code is completely formatted, including comments. The VBA Code Librarian ships with a starter set of sample code to help developers work with Office 2000 and VBA. These samples are drawn from Microsoft Office 2000/Visual Basic Programmer's Guide,as well as from our Product Support group, which has included some of the most requested code snippets. Updates to this sample code will be available on the Office Developer Forum at http://www.microsoft.com/officedev.

Developers using Visual Studio will also benefit from the Code Librarian because it can run in stand-alone mode and drag code into the Visual Studio IDE.

Single add-in model/code compilation. With Office 2000 Developer, you can write code once, then use that code across all the Office 2000 productivity tools. The COM Add-In Designer enables developers to create add-ins for any or all the Office applications (Word, Excel, Access, PowerPoint, Outlook, and FrontPage). A COM Add-in is a dynamic-link library (DLL) specially registered so it can be loaded by the Office 2000 applications. This DLL uses the VB6 compiler and targets the VB6 run-time DLLs, so for the first time, Office developers can compile their code into DLLs from within VBA.

COM Add-ins are useful for adding functionality at the application level, where the code applies to any document, spreadsheet, or presentation. For example, a developer could use a COM Add-in to build an add-in that could query customer information from a SQL database and insert that information into whatever Office document was open - Word, Excel, PowerPoint, and even an Outlook message. The nice thing about building a COM Add-in is that your source code is centralized. All the code, forms, and class modules live in a single .VBA file. This means you only have to update the code in one place.

Because VBA 6.0 also supports COM Add-ins, you can now create add-ins for the Visual Basic Editor. By creating COM Add-ins for the Visual Basic Editor, you can customize your development environment and work with components in a VBA project from code. For example, one of the interesting new features of Office 2000 is the ability to use the new Office Clipboard to collect and paste multiple items. This feature isn't included in the Visual Basic environment, but you could easily reuse the functionality of Office 2000 to include that functionality in the VBA Editor.

When you create a COM Add-in for the Visual Basic Editor, it will also appear in the Visual Basic Editor in Access, Excel, PowerPoint, FrontPage, and any other VBA host applications on the computer where the COM Add-in DLL is registered. If you would rather develop your add-in in Visual Studio 6.0, Office 2000 Developer also provides add-in code templates for:

• Microsoft Visual C++ 6.0
• Microsoft Visual J++ 6.0
• Microsoft Visual Basic 6.0

Simplify the process of writing code. It's happened to all of us: you either hacked together some code or inherited someone else's code, and now you're trying to make sense of it. The VBA Code Commenter lets you document that code quickly and easily.

The VBA Code Commenter is designed to add structured headers to the procedures within a VBA project (see FIGURES 2 and 3). It functions by merging a user-supplied template with the code in a project. If you have a corporate coding standard, or you're simply trying to enforce consistency in documentation on your project, the Code Commenter will simplify this task. You can apply specific comment syntax at the Project, Module, or specific Procedure level.

FIGURE 2: Developers can select the project's scope and the templates to be used for the comment form.

FIGURE 3: The Code Commenter automatically detects parameters and documents them.

The VBA Error Handler, shown in FIGURE 4, is designed to help developers avoid one of the more tedious tasks of VBA development: adding standardized error handlers to every procedure. Both the Error Handler and Code Commenter derive formatted text from a default template included with Office 2000 Developer (see FIGURE 5). This template can be edited. It can also be replaced with custom templates (.txt files) that might be required for specific projects or teams.

FIGURE 4: New error handlers can be added or updated with the Error Handler add-in.

FIGURE 5: Error Handler text is displayed from the default template.

Because both the Error Handler and the Code Commenter are template-based, you can change how you want them to work. In fact, the Error Handler is re-entrant. That means you can implement standard error handling today using the provided templates. If you ever update the template, you can re-apply the new error handler, and it will automatically replace your old code with the new code.

Upsize without the hassle. The big news here is the Microsoft Database Engine (MSDE). The MSDE is a 100 percent Microsoft SQL Server 7-compatible database engine that can be used for storing data using Access 2000. For a back-end data store, developers can now choose:

• MSDE,
• an improved version of Jet 4.0 (the default Access 2000 data engine), or
• Access as a front end to Microsoft SQL Server versions 6.5 or 7.0.

Programmers can choose the data engine that best addresses their specific needs, while continuing to enjoy the ease of use of Access 2000. MSDE offers customers 100 percent code compatibility with SQL Server 7, protecting customer investments as database needs grow. MSDE enables a developer to create a single Access 2000 client/server solution that scales from a PC running the Windows 95/98 operating system and Access 2000, to multi-processor clusters running Microsoft SQL Server 7.0 and Windows NT, Enterprise Edition.

Using MSDE as the Access data engine means that all information, tables, queries, reports, application code, and stored procedures will automatically work with Microsoft SQL Server 7; you never have to use any upsizing tools if you start building your solution with MSDE as your back end. The right to re-distribute MSDE as part of your solution comes with Office 2000 Developer.

New Access 2000 tools allow users to easily create and manage server-side objects, including tables, views, stored procedures, and database diagrams from the Access 2000 design view. These tools help Access power users and developers extend their database knowledge to the client/server environment.

Tools to graphically manipulate their queries. Office 2000 Developer provides two designers created to support solutions that access and present corporate data. These designers are available when you create a stand-alone project as described previously, and are accessed from the Insert menu.

Data Environment Designer. One of the more powerful new features in VBA is the Data Environment Designer, which provides an easy-to-use design-time interface for graphically manipulating data. This designer connects to any OLEDB driver, and we ship all the big ones in the box, including Microsoft SQL Server, Oracle, and Jet 4.0.

Developers can use the Data Environment user interface to create views of data within the development environment. In fact, one type of query might be a hierarchical (master-detail) recordset. This gives developers the ability to build components that easily handle relationships between recordsets. For example, a shop owner may want to be able to look at a particular customer's master information, such as the customer's address, then drill down into more detailed information, such as the customer's order history (see FIGURE 6).

FIGURE 6: The Data Environment Designer enables you to visually create, modify, and reuse hierarchical recordset command objects without having to write a single line of code.

Working with a Data Environment object is exactly like working with any other COM object. From the outside, an object created with the Data Environment Designer is accessed using the familiar object.property or object.method syntax. On the inside, Data Environment objects interact with OLE DB and ADO on behalf of the developer. With only a few lines of code, developers can reuse a Data Environment object created by another developer (see FIGURE 7). Reuse with other comparable tools often involves copying, pasting, and hard-coding the entire data structure into the new project.

FIGURE 7: With just a few lines of code, developers can reuse a Data Environment object created by another developer.

Rather than requiring developers to independently create their own data access objects, they can be created once and reused. This means you can have one developer who is the SQL guru who writes all the Data Environments and hands them off to other developers who don't know SQL, but can then use the objects.

Data Report Designer. The Data Report Designer is a new, integrated report writer that makes it easy for developers to create sophisticated printed reports, and provides programmatic control at run time (see FIGURE 8). The Data Report Designer is fully integrated with the Data Environment Designer. Developers can use a drag operation to move individual data fields, or entire recordsets from the Data Environment to easily create new reports. For a greater level of control, developers can use code to manage formatting, printing, previewing, calculation, and saving of their hierarchical reports.

FIGURE 8: Use the new, banded Report Writer to create sophisticated reports using drag functionality.

The Data Report Designer is ideal to use with the hierarchical commands created in the Data Environment Designer. The Data Report Designer is a versatile data report generator that enables developers to create reports from a live data source. In addition to creating printable reports, you can also export the report to HTML or text files.

Data-bound ActiveX controls. You are working with data - you need all the data-bound controls you can get your hands on. We ship a bunch of them in Office 2000 Developer. One of the more interesting is the enhanced FlexGrid control (see FIGURE 9). It has been integrated with the Data Environment Designer for easy on-screen display of hierarchical data. When the new hierarchical FlexGrid control is bound to an object created by the Data Environment Designer containing nested recordsets, additional display options become available. These additional display options make it possible to display grouped, related, and calculated recordsets.

FIGURE 9: This is an example of the new hierarchical FlexGrid in its expanded display format.

The ADO Data Control (ADODC) will enable developers to quickly create connections between data-bound controls and data providers using the simplicity of ADO. For Office developers who were comfortable with Remote Data Objects (RDO), ADODC is similar in function, but significantly more powerful.

Another new control that greatly expands the ways developers can display data is the new Data Repeater. Using this control, developers can easily repeat ActiveX controls to provide the user with an easily scrollable set of records. FIGURE 10 shows an example of the Data Repeater that enables the user to scroll between different records.

FIGURE 10: The Data Repeater control makes creating intuitive interfaces easy.

The Office 2000 Developer data tools simplify working with data by weaving data intelligence directly into the fabric of the design environment. By making the entire environment data-aware and providing a tightly integrated set of data design, programming, and reporting tools, Office 2000 Developer dramatically simplifies building applications that work with data.

Tools to manage the development process. Office 2000 Developer provides integrated source code control, code sharing, and code reuse for Office projects. Developers not only have easy-to-use version control for private Office projects, but when working in a team environment, they can automate the process of managing the source code with read/write security, file sharing, source code browsing, and many other shared code services. In addition, Visual SourceSafe 6.0 is in the box.

Office 2000 Developer integrates the Visual SourceSafe version control system into VBA, offering a secure environment for source code. Visual SourceSafe protects your team's most valuable assets, and gives you the tools you need to work efficiently within complex development and authoring environments. Store current files along with past changes to documents, source code, and Web content, so you can easily recreate previous versions and maintain an audit trail for any file.

In a team environment, the ability to maintain tight control of code versions shared by a number of developers reduces the chance for lost work. Different members of a development group, perhaps from different business units, can effectively contribute to a solutions framework by checking their code in or out, without disrupting the integrity of trusted source code.

Tools to support deployment. It's often hard to understand all the dependencies in Office solutions. The Package and Deployment Wizard (see FIGURE 11) was originally built for Visual Studio to address identifying dependencies and building setup applications. The Package and Deployment Wizard was re-architected for Office 2000 Developer. This wizard will make it easy for Office developers to build professional setup routines that can be used to install their solutions on end-user computers. It offers developers complete control over what changes will be made to end-user computers.

FIGURE 11: Select an option for distributing a custom solution from the Package and Deployment Wizard dialog box.

The wizard automatically includes files referenced by the project, such as ActiveX controls and referenced documents, spreadsheets, or databases (see FIGURE 12). The Package and Deployment Wizard will work in any of the VBA hosts in Office 2000. In addition, nearly all of its functionality will be available to any third-party VBA host that licenses VBA 6.0.

FIGURE 12: Files to be associated with the solution can be changed at any time, including the Access Runtime.

The Package and Deployment Wizard also includes an option to install the Access 2000 run-time files. If you target MSDE in your solution, the Package and Deployment Wizard will automatically include the run-time files for it, as well. When you purchase Office Developer 2000, you get the rights to re-distribute your front end (the Access Runtime), as well as your back end (MSDE).

You select whether to target a single .CAB file for a CD-ROM, FileShare, or Web installation, or you can target floppy disks. Once the installation is complete, you can deploy your solution to a network share or to a Web server (internal or external) for end-user Web download.

As you can see, we listened to your requests and have delivered powerful tools in three key areas: productivity, data, and deployment and management. In terms of productivity, you get Office 2000 Premium, the ability to compile your code into a single DLL that targets all of Office, the Code Commenter, and the Error Handler. In terms of data, you now have MSDE, the Data Environment Designer, the Data Report Designer, and additional data-bound controls to let you tap into your corporate databases, whether that's in Oracle, Microsoft SQL Server, or some other format, and deliver that data directly to the desktop. In terms of deployment and management, you can manage your source code with Visual SourceSafe 6.0 integration, and you can now build setup applications that can be installed from CD-ROMs, network shares, or the Web, using the Package and Deployment Wizard.

Microsoft has created Office 2000 Developer for you - professional developers building solutions with Office and VBA. For the latest peek at Office 2000 Developer, visit http://msdn.microsoft.com/officedev/preview. In addition, see the table in FIGURE 13 for key features and descriptions of Office 2000 Premium/Developer.

FIGURE 13: Key features and descriptions of Office 2000 Premium/Developer.

David Mendlen is the Technical Product Manager for Microsoft Office 2000 Developer. He was the Technical Product Manager for Visual Studio 6.0 and, prior to Microsoft, he was the Director of Development for Ameritech Cellular. David is a Microsoft Certified Solution Developer and a certified trainer in Visual Basic. He is also the founder and former president of the Chicago Corporate Visual Basic User Group. David can be reached via e-mail at mailto:davemen@microsoft.com.

Okay, so there's a special edition of Office 2000 made just for VBA developers, but what separates it from the regular Office 2000 suite? Or Office 2000 Premium, for that matter? Quite a bit actually - from Visual SourceSafe to the Data Report Designer - as Mr Mendlen describes.

Copyright © 1999 Informant Communications Group. All Rights Reserved. • Send feedback to the Webmaster • Important information about privacy