Office 2000 and Visual Basic for Applications

Office 2000 and Visual Basic for Applications

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.

Automation is the Component Object Model (COM) technology that makes Microsoft Office 2000 applications programmable, which makes creating an integrated Office solution possible. Automation (formerly called OLE Automation) includes the following features.

Automation exposes features in a hierarchy of programmable objects Applications, dynamic-link libraries (DLLs), and ActiveX controls that support the appropriate Automation interfaces expose their features as a set of programmable objects. Any application or service that supports these interfaces is called a COM component. The set of programmable objects that a COM component exposes is organized into a set of hierarchical relationships that is called an object model, or a programming model.

Automation allows applications to share features with other applications A COM component can be either an Automation server that shares its component objects with other applications or an Automation client that uses the component objects of other applications.

Visual Basic for Applications provides features that make it easy to use Automation When you use Visual Basic for Applications (VBA) code, you can establish a reference to the programming model of an Automation server by using the References dialog box (Tools menu). The reference allows your solution to allocate memory efficiently to work with the objects that the Automation server exposes. The reference also allows the Visual Basic Editor to assist you when you enter VBA statements by automatically listing relevant objects, properties, and methods from the referenced programming model. And you can use the Object Browser command (View menu) to examine how the objects that are exposed by the Automation server are related to each other, and to find more information about how to program the objects.

Automation servers and Automation clients

Office 2000 developers can take advantage of all Automation features when developing custom solutions. Office applications can function as COM components that expose most of their features to other applications that support Automation as programmable objects. Office applications serve as both Automation servers and Automation clients. And most Office applications incorporate the VBA programming environment that makes working with Automation easy.

Programmable applications

The Visual Basic for Applications programming language and the Visual Basic Editor can be incorporated into applications that support Automation to make the applications programmable. The following Office 2000 applications support the VBA programming language and the same version of the Visual Basic Editor:

• Microsoft Access 2000
• Microsoft Excel 2000
• Microsoft FrontPage 2000
• Microsoft Outlook® 2000
• Microsoft PowerPoint® 2000
• Microsoft Word 2000

In addition, many other applications incorporate the Visual Basic for Applications programming environment and are written to expose their functionality to VBA programmers through Automation interfaces. This means that developers can use Visual Basic for Applications to create solutions that integrate not only Office 2000 applications, but also other applications that support VBA and Automation.

Programming individual Office applications

Visual Basic for Applications and Automation allow you to program individual Office applications. For example, you can create procedures (macros) to automatically create and format documents in Word 2000. Developers can also use Automation to run other applications from within a client application. For example, a VBA solution developed in Access can run a hidden instance of Excel to perform mathematical and analytical operations on Access data.

Working with programmable services

Automation also allows developers to use Visual Basic for Applications code to work with programming models that do the following:

• Function as free-standing services.
• Are independent of individual applications.

For example, Office 2000 installs the Microsoft Data Access Components (MDAC), which consist of OLE DB components that provide low-level access to a variety of data sources and components that make up the ActiveX Data Objects (ADO) programming model. Because the ADO programming model supports Automation, you can use ADO from Visual Basic for Applications code running in any Office application to gain access to, and work with, any data source that is available through an OLE DB provider or an ODBC driver.

New programming features

The programming models of the Office 2000 applications expose new objects, methods, properties, and events. The Visual Basic for Applications language also has new features.

Expanded event model

The Office 2000 event model has been greatly expanded. Word, PowerPoint, and FrontPage now expose more than two dozen new events, primarily associated with window and document objects.

In addition, all Office applications now expose three command bar events: The CommandBarButton object has a click event, the CommandBarComboBox object has a change event, and the CommandBars collection object has an update event.

New VBA functions

Several new features make it easier to format data or to parse and manipulate strings. New Visual Basic for Applications features include the following:

• Use FormatCurrency, FormatDateTime, FormatNumber, and FormatPercent functions to format data.

• Use the Split, Join, and Filter functions to parse strings.

  The Split function parses a string into an array of substrings. The Join function is the opposite of the Split function; it creates a string from an array of substrings. The Filter function filters an array and returns an array containing the elements that match the specified criteria.

• Use the new Replace and InStrRev functions to manipulate strings.

• Use the Round function to round a number to a specified number of decimal places.

In this latest version of Visual Basic for Applications, you can also write functions that return arrays, and you can assign one array to another.

New VBA objects

Also new to Visual Basic for Applications in Office are the FileSystemObject and Dictionary objects. The Dictionary object is analogous to a VBA collection, except that the Dictionary object can hold objects of different data types. You can use the FileSystemObject object to work with the drives, directories, and files on your computer as if they were objects and collections of objects with methods and properties you can use to return information about your file system.

New custom object features

Visual Basic for Applications also allows you to create your own objects. New to VBA in Office 2000 is the ability to add custom events to objects that you create. In addition, you can now extend your custom objects by implementing interfaces.

See also

For more information about the Visual Basic for Applications programming language and applications that support it, see the Microsoft Visual Basic for Applications Web site at https://msdn.microsoft.com/vba/.

For more information about developing custom solutions by using Office applications and the Visual Basic for Applications programming language, see the Microsoft Office Developer Web site, at https://www.microsoft.com/officedev/, and the Microsoft Office 2000/Visual Basic Programmer’s Guide.

For more information about ADO and the data access technologies that Office 2000 supports, see Data Access Technologies.