Developing Integrated Office Solutions
This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.
The ability to develop an integrated Office solution heavily depends on two technologies:
The Component Object Model (COM) software architecture
Visual Basic for Applications (VBA)
The COM software architecture allows software developers to build their applications and services from individual software components, collectively referred to as COM components or simply components. COM components consist of the physical, compiled files that contain classes, which are code modules that define programmable objects. There are two types of COM components: in-process components and out-of-process components. In-process components are either DLLs or ActiveX controls (.ocx files) and can run only within the process of another application. Out-of-process components are .exe files and run as free-standing applications. A COM component can serve either or both of the following roles in application development:
Sharing its objects with other applications. This role is called being an Automation server.
Using other components' objects. This role is called being an Automation Client. In earlier documentation, this role is called being an Automation controller.
The Windows operating system and Office suite of applications are examples of products that have been developed by using the COM software architecture. Just because software is developed by using COM doesn't necessarily mean that it can be programmed by using VBA. However, if an application or service supports the COM technology known as Automation, it can expose interfaces to the features of its components as objects that can be programmed from VBA, as well as many other programming languages. To support Automation, an application or service must provide either or both of two methods of exposing its custom interfaces:
By providing the IDispatch interface. In this way, the application or service can be queried for further information about its custom interfaces. Applications and services that support the IDispatch interface provide information about their custom interfaces at run time by using a method called late binding.
By allowing direct access at design time to the member functions in its virtual function table, or vtable, that implement its interfaces. Applications and services that support direct access to custom interfaces support what is called vtable binding or early binding.
An application can be said to support Automation if it supports either one, but not necessarily both, of these methods. Most contemporary applications and services provide support for both methods and are referred to as supporting dual interfaces.
For more information about the differences between late binding and early binding, see "Declaring Object Variables" later in this chapter.
To support early binding, an application or service must also supply a type library (also known as an object library). A type library is a file or part of a file that describes the type of one or more objects. Type libraries do not store objects; they store type information. By accessing a type library, a programming environment can determine the characteristics of an object, such as the interfaces supported by the object and the names and addresses of the members of each interface. With this information, the programming language can be used to work with the exposed interfaces.
In the VBA programming environment, you can establish a connection to a type library, which is called establishing a reference to a type library*.* Once you establish a reference to a type library, you can view information about the objects made available through the type library by using the Object Browser. Establishing a reference to a type library also allows VBA to perform error-checking at compile time to ensure that code written against the type library is free from errors due to improper declarations or from passing values of the wrong type. Additionally, referencing a type library allows you to take advantage of VBA features that simplify writing code, such as automatic listing of the properties and methods of objects exposed by the type library. Further, referencing a type library makes your code run faster because information about the objects you are programming is available to VBA at design time; this information can be used to optimize your code when it is compiled. For more information about referencing type libraries and using the Object Browser, see "Declaring Object Variables" later in this chapter.
The VBA programming environment can be incorporated into applications that support Automation to make them programmable. The suite of Microsoft Office applications, as well as a rapidly growing number of applications such as AutoCAD and Visio, incorporate the VBA programming environment and are written to support both kinds of Automation interfaces. Additionally, many other software components, such as ActiveX controls and DLLs, expose their functionality to VBA programmers through Automation interfaces.
Using the objects, properties, and methods exposed through Automation interfaces, you can use VBA code running in modules associated with the currently open document, template, database, Microsoft FrontPage-based web, or add-in to automate that application. VBA and Automation make it possible to record simple macros to automate keystrokes and mouse actions (in applications that support macro recording), and also to create sophisticated integrated solutions, such as document management, accounting, and database applications.
To produce even more powerful integrated applications, you can use VBA code running in one application to create and work with objects from another installed application or component. For example, if you are developing a solution in Access and you want to use mathematical or other functions available only in Excel, you can use VBA to create an instance of Excel and use its features from code running in Access.
You can think of Automation as a nervous system that makes programmatic communication and feedback between applications and components possible, and as "glue" that lets you integrate features from Office applications and other software components into a custom solution.
VBA's support for Automation provides Office developers with incredible flexibility and power. By taking advantage of Automation, you can use the features exposed through the object models of the entire Office suite of applications (as well as any third-party applications and components that support Automation interfaces) as a set of business-application building blocks. By taking advantage of the prebuilt components exposed through Automation, you don't need to develop your own custom components and procedures every time you want to get something done. In addition to shortening the development time for your solution, using pre-built components also means that you can take advantage of the thousands of hours of design, development, and testing that went into producing them.
By using VBA and objects exposed through Automation, you can select the best set of features to use to perform the tasks you want to accomplish, you can provide the data users need to accomplish their jobs, and you can manage workflow to provide an effective and productive solution.