Business Apps

What You Need To Know About Using Office As A Development Platform

Andrew Whitechapel and John Peltonen

Contents

Office Platform Fundamentals
The Office Object Model
Extensibility Points
The Office Development Toolset
More Benefits of Office
Serious Development Platform

T here's little doubt that the 2007 Microsoft Office system provides the basis for some pretty sophisticated custom solutions, and there are thousands of such solutions running happily on Office today. But can Office also serve as a robust development platform? Does Office provide the building blocks for creating entirely new applications?

Office has traditionally been seen as a suite of discrete client-side applications—Word, Excel®, and Outlook®—each designed to provide a rich set of functionality. Since this functionality was directed toward a specific range of user tasks for each application, there was minimal explicit interoperability between the different applications. But Office has evolved to become a framework of applications and technologies that provides services across both clients and servers and embraces Microsoft InfoPath®, SharePoint®Services, Content Management Server, and Exchange Server.

Word, Excel, Outlook, and the rest of the Office apps were intentionally designed to explicitly expose most of the functionality for external consumption as COM servers. Clearly, the Office architects had custom solutions in mind. As a result, you can easily build a solution that seamlessly hooks one or even multiple Office applications. Moreover, an Office component like SharePoint brings portal services, team services, workflow functionality, and content management. You might argue that SharePoint is a pretty comprehensive development platform all on its own.

Figure 1** Platform Characteristics **

A true development platform must satisfy some basic requirements. We all have our own ideas on this, but a minimal set would probably include generic baseline business services, extensibility points for solutions to hook into the generic services, and a consistent toolset for building against the baseline services. You also need a base level of non-functional attributes that allow the functional services to be useful in building realistic enterprise solutions (see Figure 1). Not only does Office meet these requirements, it has two other things going for it: developers who've worked with Windows® already have the knowledge and experience they need to take advantage of the Office platform, and Microsoft has released a number of tools that will help developers build new applications based on the platform.

Office Platform Fundamentals

We can map the requirements of a development platform to the Office system, and start to fill in some of the specific features, as shown in Figure 2. As a development platform, Office must address a range of fundamental requirements. These include reliability, scalability, security, deployment and updating, reusability through componentization, a consistent object model, and version compatibility. Let's take a look at these requirements and see how the 2007 Office release addresses them.

Figure 2** Office Development Platform Landscape **

Office provides reliability in three main ways. First, the Office applications themselves are built with a high degree of robustness. Second, Office takes a number of steps to protect itself from rogue customizations. For example, if an add-in crashes its host application, Office detects this and blacklists that add-in so that it's not loaded in the future. Most exceptions that might be thrown by an Office customization or add-in are handled silently to avoid destabilizing the host. In some cases, the application will throw up a dialog box with a suitable error message so the user knows that Office is functioning correctly but the add-in might be in an undefined state. Third, Microsoft® Office SharePoint Server (MOSS) 2007 is built to the same exacting standards as other Microsoft server software, so services based on SharePoint get this high level of reliability, too.

There are two dimensions to scalability and performance: client-side and server-side. On the client, each Office application imposes certain limits, such as the maximum number of rows and columns in an Excel worksheet. This makes scalability predictable—you simply can't build a custom solution that exceeds these limits. However, it is possible to deploy solutions that exceed practical limits in a context where there is no defined hard limit. For example, there are no specific limits to the number of add-ins you can register for a single application, but clearly there would be practical limits—think of the performance penalty of loading 10,000 add-ins when Excel starts up. This is a good example of the type of decision a development platform should not make on behalf of consumers.

You probably shouldn't automate Office client applications on the server for the same reason you wouldn't load 10,000 add-ins: it's just not practical, even though it's technically possible. However, the 2007 Office release does expose the Excel calculation engine and other features on the server through SharePoint and Excel Services (see Figure 3). Windows SharePoint Services (WSS) 3.0 provides integrated workflow support, native support for ASP.NET 2.0, support for blogs, wikis, and RSS. MOSS 2007 builds on that foundation, introducing content management, business intelligence (BI), Web-based Excel Services spreadsheet capabilities, InfoPath forms support, document and list-centered workflows, and search enhancements. In addition, the MOSS Business Data Catalog (BDC) services allow retrieval of information from back-end systems such as customer relationship management (CRM) and enterprise resource planning (ERP) applications. (See Ted Pattison's article in this issue for more details about MOSS 2007.)

Figure 3** Excel Services Components **

In the past, Office security has been somewhat difficult for users to understand and for administrators to manage. The 2007 Office release has completely revamped its security model to be more understandable to users, more transparent, and easier to administer, without losing any of its strength. Visual Studio Tools for Office has always employed a stricter security model, but that strength made it difficult to administer.

Traditional Office-based solutions either automated Office externally, registered add-ins of some kind (COM add-ins, smart tags, or real-time data), or provided document-based solutions where the automation was handled in-process by using Visual Basic for Applications (VBA) embedded in the document. Deployment and updates for external automation solutions are pretty straightforward; any solution that requires registration in the Windows registry typically has a very well-defined set of deployment processes around it, and updating simply involves installing a new version.

Deployment is really a problem only with document-based solutions. Most documents are useless unless users can modify them, but changing documents opens the possibility of changing the VBA. Even if you prevent this, the VBA code can still (and typically will) behave differently depending on the data in the document. You can rapidly end up with multiple versions of the solution, and tracking them becomes a nightmare, VBA doesn't lend itself to source control, and auditing and regulatory compliance become all but impossible.

Visual Studio Tools for Office addresses these issues by providing a very flexible deployment model. Visual Studio Tools for Office solutions—both application-level add-ins and document-level customizations—allow the code to be deployed locally or remotely, and both incorporate automatic updates. If you deploy remotely, you have a simplified administrative task since the assemblies are on a server rather than propagated across every user's desktop. When Office loads a solution, the Visual Studio Tools for Office runtime performs a check for an updated version and, if it finds any, automatically downloads the updates to the local machine.

The Office Object Model

As noted earlier, Office applications expose COM interfaces. For example, while you can focus on the Word Document object and logically instantiate it independently, in practice the Word process will be executed to serve up this object. But if you really want a development platform then, though you might only use one component in a specific scenario you still want the rest of the platform to be available. You want that component to use whatever other platform services it requires without having to instantiate those services yourself manually.

Where is the right place for componentization in an Office solutions context? Many customers build their own domain-specific reusable components layered on top of Office. For example, a component that is specific to some investment banking functionality might use the Excel calculation engine. The solution-side interface of this component is domain specific, while the platform-side interface is Excel specific. This abstraction allows the component to work against multiple versions of Excel, which is a typical customer requirement.

Alternatively, you could build a generic reusable component that is host-application neutral. Consider, for example, a UserControl that uses Web services to display data in a custom task pane. That same control could be reused in multiple Office applications.

Office applications have evolved over time and they have very different sets of functionality, so they will inevitably expose different object models. That said, there is some consistency across the apps. The object model is mostly hierarchical, for example, and there is generally an Application object at the root. In Word you can start from the Application object, find the Documents collection, drill into individual Document objects, and then into individual Range objects. Similarly, in Excel you can start from the Application object, walk down to the Workbooks collection, find the Workbook objects within the collection, and then get individual Range objects within a workbook.

In addition, Visual Studio Tools for Office layers on a consistent, strongly typed object model. You only have to look at two very different Visual Studio Tools for Office solutions—say an Excel document-based solution and an Outlook application-based solution—to see the consistency. Both solutions present simple Startup and Shutdown methods so you can focus on the business requirements without worrying about the individual traits of the host application. While you still have complete access to the underlying object models, you can also choose to work at a higher level of abstraction.

With each new release, Office maintains a very high bar for backward compatibility. Office applications are COM servers, and COM imposes a number of rules such as the immutability of interfaces, which protects against incompatibility across versions. Office mostly plays by the rules. All Office interfaces are either dual interfaces or pure dispatch interfaces. Pure vtable interfaces (and by extension, dual interfaces) are immutable, but dispatch interfaces are not because the set of available functions and their signatures is discoverable at run time (therefore late binding). The Office team routinely takes advantage of this by adding additional methods and properties to the end of an interface when a new version is released. They also typically add new optional parameters to existing methods. Using these techniques, Office can preserve backward compatibility.

Another technique for maintaining version resilience is to use loose typing. The classic example is the IDTExtensibility2 interface that COM add-ins must implement. When an Office application loads an add-in, the application calls IDTExtensibility2::OnConnection and passes in a loosely typed object that represents the host application. In .NET code this parameter is typed as a System.Object while in C++ it is a generic IDispatch pointer. At run time, this will effectively be a pointer to the Application object exposed by the host application. When you build a traditional shared add-in, the code generated by the wizard is completely host-neutral. This confers two benefits: it allows the add-in to run in any application that supports COM add-ins, and it allows the add-in to run in any version of any of these applications.

The price you pay for this host-neutral model is the standard price for loose typing and late binding: you get no design-time or compile-time support for the specific types involved because they are unknown until run time. That makes it a lot easier to write code that will fail at run time. Also, late binding carries a performance penalty because each method invocation has to go through the discovery process to see if a matching method can actually be found at run time.

When you develop add-ins with the .NET Framework, you can continue to use this loose typing, but the advantages are marginal unless the add-in itself provides only host-neutral functionality. As soon as the add-in needs to provide host-specific functionality, you're likely to use the host's Primary Interop Assemblies (PIAs), which are version specific. Also, you can use Visual Studio Tools for Office to build add-ins with .NET code. Visual Studio Tools for Office mandates strong typing, giving you the benefits of design-time IntelliSense® and autocomplete in addition to compile-time type checking. Strong typing avoids the performance overhead of late binding.

Does strong typing mean you lose version resilience? Not necessarily. You can build a loosely typed add-in against some version of Office and the add-in will almost certainly work completely in later versions of Office. With the .NET Framework you can achieve the same results although the behavior is slightly more complicated. A .NET add-in built against a particular version of the Office PIAs should also work in later versions. There are actually two ways this can happen: either the add-in uses the version of the PIAs it was built against or you deploy a binding redirect for the PIAs so that at run time the add-in uses a later version of the PIAs that corresponds to the installed version of Office.

Extensibility Points

From the early days of Office it has been possible to extend its functionality virtue of the fact that Office client applications are COM servers and therefore expose their functionality for external automation. This automation can even be embedded in-process with the host application, traditionally by using VBA. Office offers a wide range of extensibility points, and we can drill down on our landscape diagram to provide the list in Figure 4. You could slice this list in a number of ways. Figure 5 describes the techniques that typically involve automation of the Office object models, while Figure 6 shows techniques that rely on other protocols.

Figure 6 Extensibility Points Not Reliant on Office Object Models

Figure 5 Extensibility Techniques Using the Office Object Models

Figure 4** Office System Extensibility Points **

This is a wide range of extensibility points. Of course, if you evolve a suite of applications over time, inevitably you'll end up with a varying range of behaviors—and this is true of extensibility points also. If you design a development platform from the ground up, you would obviously design the extensibility points to be as consistent as possible. We all know the history of Office, but are the extensibility points consistent across the breadth of the application suite? That is, can you hook into the same functional piece in the same way across different applications?

At one level, the techniques that involve some kind of automation of the Office object models are consistent by definition. That is, the way you use the object models is scoped by standard COM and automation rules. The individual Office application object models have a degree of consistency but ultimately different sets of functionality. Dropping down a level, the way you code a COM add-in is different from the way you code a smart tag, for example. This is because an add-in needs to implement IDTExtensibility2, while a smart tag needs to implement ISmartTagRecognizer or ISmartTagAction. Obviously, the only way these two sets of functionality could be made more consistent is if the interfaces were combined. While the idea of a single extensibility interface for all types of extensible functionality might seem attractive, it would be either very limited or very loose in scope.

Rather than combining interfaces, the 2007 Office release introduces five new extensibility interfaces covering four functional areas (see Figure 7). What's interesting about these new interfaces is that while they are very different in functionality, they can all be implemented through a standard COM add-in. This was a conscious design decision and is also an indicator for the future roadmap for Office extensibility. The COM add-in technology is tried and tested, well understood, and well supported by developer tools. The processes for deploying and maintaining add-ins are also well defined and familiar to administrators. It makes a lot of sense to provide a consistent extensibility point in this way—not by designing some single extensibility interface but instead by allowing a single implementation to employ one or more of the growing number of extensibility interfaces. Using this approach, developers and administrators would have only one model to worry about.

Figure 7 Office Extensibility Interfaces

This convergence of extensibility techniques into the add-in model looks to be the design for all future extensibility interfaces. Could it also be applied to existing interfaces? For example, could you implement a smart tag via an add-in? Could you implement an Excel function via an add-in? Technically, this might actually be feasible, but it is probably not the best thing to do. For new interfaces it makes sense, but the drawback of trying to apply this to old interfaces is that there are already many solutions that use the old approach. If you could implement Excel functions via regular add-ins as well as via automation add-ins, you'd then have two different ways to achieve the same results. So, the ideal of convergent design brings with it divergent deployment. This may be acceptable for a transition phase during which developers migrate their solutions from the old model to the new one. Right now, add-ins do not support any of the other old extensibility interfaces, but this is a possible direction for the future—perhaps depending on customer demand.

While we're looking at extensibility points, we should also consider the document file formats. One way Office makes itself extensible is by providing programmatic access to its documents. Over the last few releases, Office has introduced some level of support for XML. For example, Excel 2000 had minimal support for reading and writing XML. This support has increased incrementally and also spread to other applications, specifically Access, InfoPath, PowerPoint®, and Word. The 2007Office release provides support for completely open XML file formats for Excel, PowerPoint, and Word.

The Open XML formats create new opportunities for developers because you can extend solutions through deep integration with document contents. This follows the Open XML formats submission to ECMA International as a proposed open standard. Microsoft is working to support the opportunities for independent development in several promising areas. There's even a new open technical community of developers, known as the Open XML Formats Developer Group that includes 40 industry leaders such as Intel, Apple, BP, and Toshiba.

The use of XML offers the benefits of greater transparency and openness than possible with the previous binary file formats. The new formats allow Office documents to integrate easily with existing and future line-of-business systems, as the contents are now open and accessible. This clearly addresses server and scalability issues: for many solutions, you don't need to automate Office on the server because you can manipulate the files directly. The new formats are also designed with long-term robustness and accessibility in mind, so that file corruption will be easily repairable, and there is no reliance on any particular software application to provide access to the document contents. The 2007 release files are also much more efficient, taking up far less space than the previous formats and allowing for quicker transmission times and a smaller impact on storage.

The new file formats represent a major step forward and will be made available not only to customers who adopt the 2007 release but also to customers using previous versions of Office. Free conversion tools enable users of Office 2000, Office XP, and Office 2003 to open and save to the new formats, so that everyone can benefit from this innovation.

The Office Development Toolset

One perspective argues that a development platform merely needs to expose its extensibility points and doesn't also have to provide a toolset. Up to a point, the question is academic since Microsoft does, in fact, provide tools for development against Office. The relevant question thus becomes, does the provided toolset adequately support the platform?

Focusing on tools, you can build Office solutions with a variety of frameworks and languages. You can use any COM-aware language for externally automating Office. You can use VBA for internal (document-based) automation of Office. When working with the .NET Framework, developers can use Visual Studio Tools for Office if they want a structured, consistent toolset and runtime. A mapping between the extensibility points and the available tools is shown in Figure 8.

Figure 8 Mapping Extensibility Points to Toolsets

VBA covers a small subset of the available extensibility points, mainly because VBA is restricted to document-based solutions. Both traditional shared add-ins and Visual Studio Tools for Office solutions cover most of the extensibility points. Visual Studio Tools for Office covers slightly more because it also folds in some of the one-off extension types—for example, you cannot build a smart tag as a traditional add-in, but you can build it either as a one-off DLL or as a Visual Studio Tools for Office add-in.

The Visual Studio Tools for Office runtime provides essential services optimized for a wide range of solutions. Alternatively, you have the flexibility to build solutions without Visual Studio Tools for Office if you need to build a custom runtime infrastructure. Consider, for example, COM add-ins. Right now you have two main toolset choices for building add-ins: the traditional shared add-in project types and Visual Studio Tools for Office add-ins.

This situation is less than ideal, though. A better solution would be a single project type with all the capabilities of both shared add-ins and Visual Studio Tools for Office add-ins. This is clearly difficult to achieve, given that shared add-ins are fundamentally loosely typed while Visual Studio Tools for Office add-ins are fundamentally strongly typed. This difference gives the shared add-ins the ability to work across multiple versions of Office, and the Visual Studio Tools for Office add-ins, in contrast, a better design-time experience and significantly increased runtime robustness.

Failing the ideal, the optimal toolset would rationalize these two project types to make them as consistent as possible and to make the developer's choice simple. For example, Visual Studio Tools for Office add-ins are organized like all other project types in Visual Studio, with root nodes that correspond to the programming language. In contrast, the shared add-ins offer the developer the language choice as a step in the project wizard. Another example is that Visual Studio Tools for Office add-ins don't offer the developer a choice about the text string to use for the Description registry value because you can set or change this at any time after the wizard generates the project. This value is actually never used by Office. The shared add-ins, on the other hand, prompt you to supply this string and in almost all cases it is redundant.

Where does VBA fit in the Office developer toolset? VBA was originally introduced to support non-professional development—that is, power users (professionals in their own area, but not necessarily in software development) who needed to expand the base set of Office functionality to provide more domain-specific solutions. It was never intended to be used for highly sophisticated professional solutions, but has been used in that role for lack of a good alternative. Visual Studio Tools for Office is positioned at the other end of the spectrum. It is a professional development tool that brings the full power of Visual Studio and managed development to Office.

Of course, VBA and Visual Studio Tools for Office can coexist, and there's considerable overlap in the types of solutions built with these technologies. For many solutions you could use either or even both (though this approach is likely to be limited to transitional projects that are being migrated over time from VBA).

Visual Studio Tools for Office certainly covers the high end of the scale and extends down towards the low end, but it doesn't offer macro recording. This is one area where VBA is still the only tool for non-professional solutions based on user interaction. Again, the ideal would be one toolset to cover the entire spectrum. With this in mind, Microsoft is looking hard at the problem and considering ways in which Visual Studio Tools for Office might be used to cover the low-end macro recording capabilities of VBA.

More Benefits of Office

Thus far we've made a strong case for Office as a development platform, but in fact it offers much more. Consider that the primary information-worker user interface is Office. This isn't a requirement for a development platform, but it is clearly a huge bonus. Information workers demand applications that are built around Office. The Office UI is familiar, the Office application workflow is familiar, and many tasks require specific Office functionality. More than that, these workers need applications that let them interact seamlessly between the Office host and the custom solution functionality.

Can you build solutions with Office where your custom functionality is seamless with the native application's functionality? Do the custom solutions look as if they're part of the host? Consider the command UI—that is, the Ribbon in the 2007 Office release and the equivalent command bars in earlier versions. If you provide customization of the Ribbon or command bars, these custom controls and menu items behave in the same way as built-in controls. If you build an Excel user-defined function, it can be used in exactly the same way as a built-in function, although there is a limitation in the level of context-sensitive help support you can provide. Outlook custom form regions are designed to be seamlessly integrated with built-in forms and, in fact, you have several choices as to how this integration is represented.

The Office programmability team has taken great pains to ensure that extensibility features implemented by custom solutions are integrated within the host application as seamlessly as possible. The net result is that the end user sees the custom solution as a natural extension of Office.

One point worth mentioning here is that Office exposes extension points in an open-ended way, leading to potential abuse. For example, the Outlook folder view allows you to place HTML within the Outlook window, and the HTML could include ActiveX® controls. You can see how this could be taken to extremes, as Outlook has no knowledge of the controls you place here and can't impose any restraints on their behavior. Overall, the extensibility features of Office give you all the flexibility you need, but you should use the platform sensibility.

Serious Development Platform

We've seen that Office has all the attributes of a serious enterprise development platform. Office offers an extensive baseline set of functionality; a range of extensibility points and a choice of developer toolsets. It is not perfect and there are curious anomalies and inconsistencies, but considering the history of Office it is truly remarkable how far the suite of applications has converged into a cohesive platform. The baseline functionality is extremely comprehensive. The extensibility points are a little uneven and there could be more of them. The developer toolsets are still evolving and could do with some rationalization. So, there's still some way to go, and the supporting toolset needs to evolve in lock-step with the platform itself, but the indications are that the strategic direction for the future of Office is to evolve it into a world-class development platform.

Andrew Whitechapel spent many years as an architect consultant building enterprise solutions for a wide range of customers, and is now Program Manager Technical Lead for the VSTO team at Microsoft.

John Peltonen and his company, 3Sharp, are passionate Office system solutions architects and developers. Read more about them at blogs.3sharp.com