OLE Automation and Visual Basic for Applications are the most significant new features to be added to Microsoft Windows and its applications subsequent to Microsoft's introduction of Windows on May 22, 1990. As mentioned in the preceding chapter,
Windows 3.1 marked the official release of OLE 1.0, but many of the features of OLE 1.0 had been embedded in versions of Microsoft Excel, Word for Windows, and PowerPoint before Windows 3.1 appeared in retail stores. Subsequent versions of these products
added OLE 1.0 functionality to Windows 3.0. Similarly, the supporting infrastructure for 16-bit OLE 2.0, in the form of OLE 2.0 DLLs, was grafted to Windows 3.1+ upon installing upgraded 16-bit versions of members of the Microsoft Office suite. Windows 95
and Windows NT 3.51+ include 32-bit OLE 2.x DLLs, but many of the OLE 2.x libraries originally supplied with these operating systems are upgraded when you install the 32-bit version of Visual Basic 4.0 or 32-bit Access 95.
OLE 2.x provides the framework for the future of truly object-oriented 32-bit Windows applications. Entire books have been (and undoubtedly will continue to be) devoted to OLE Automation and Visual Basic for Applications on Intel, RISC, and
Macintosh platforms. For the moment, you still need to run OLE full server applications that participate in an OLE Automation process on a single computer, but Visual Basic 4.0 lets you create remote OLE Automation servers that are accessible over a
network. (The preceding chapter discusses Microsoft's recently announced NetworkOLE for utilizing OLE Automation across networks; Chapter 23 "Creating and Managing Remote Automation Objects" describes how to use Visual Basic to implement an early
version of NetworkOLE.)
OLE Automation (often abbreviated as OA) and Visual Basic for Applications (always abbreviated as VBA) are close relatives. Both OA and VBA use the same collections of objects. OLE Automation is an interprocess communication (IPC) methodology that lets
one OA-compliant application manipulate members of the collection of objects that are exposed by another OA-compliant application. Exposing an application's objects means making members of the application's object collection available to be declared
as object variables in another application. Thus, one application can alter properties of and apply methods that are applicable to the member objects of another application.
Visual Basic for Applications is the long-awaited common macro language (CML) for Windows applications; it's slowly but surely making its way into all of Microsoft's productivity applications. VBA was originally derived from Visual Basic 3.0; Visual
Basic 4.0, in turn, contains new features derived from the first versions of VBA—such as named arguments, substitution of the Public keyword for the Global keyword, and others. VBA enables you to manipulate members of the collection of
OA objects exposed by Visual Basic 4.0 or member objects of another OA-compliant application. Ultimately, application-specific dialects of VBA will replace the existing macro languages of all Microsoft applications for 32-bit Windows and Apple's System 7+.
An OLE 2.x application does not need to support VBA to be a participant in an OA process. Microsoft Word 7 and Visio 4.0 are examples of OLE 2.x–compliant applications that support OA without using VBA as their application programming
language. OLE 2.x applications that don't yet support VBA are limited to acting as OA full servers. Microsoft Excel 7.0 and Project 4.1 are fully compliant OA clients and servers.
When referring to Microsoft Excel and Microsoft Word, this chapter uses the current version number for both products—that is, version 7.0. Neither Excel nor Word have changed their file formats or data structures from their previous versions, however. For the purposes of this chapter, the only difference between Excel 5.0 and Excel 7.0 is that Excel 5.0 is a Windows 3.1+ 16-bit application, while Excel 7.0 is a Windows 95/Windows NT 32-bit application. The same is true for Word for Windows versions 6.0 and 7.0. Essentially, the discussion of OLE Automation involving Excel 7.0 and Word 7.0 is applicable to Excel 5.0 and Word 6.0. If you are using Visual Basic 4.0 to develop both 16-bit and 32-bit applications, you can use these techniques equally well in either environment.
The upshot of OLE 2.x, and specifically OLE Automation and VBA, is that mainstream Windows applications become building blocks from which you can easily construct customized, multipurpose applications. In the past, programmers were forced to use
DDEExecute instructions to manipulate data in documents of DDE server applications. Now you can use Visual Basic 4.0, Excel VBA, or Project VBA to orchestrate objects, not just data. Word Windows 7.0's Word Basic is not an VBA dialect, but you can use Word
7.0 as an OLE Automation server with Visual Basic 4.0.
This chapter discusses the following major topics:
Microsoft designed OLE Automation to accomplish the following three basic objectives:
Applications that incorporate VBA or an equivalent application programming (macro) language can manipulate their own objects. If an OA-compliant application does not have its own macro language, you can use Visual Basic 4.0 as an external programming
tool to fill the gap. As an example, you can create an application that animates objects in Visio 2.0 while it plays MIDI music and WAVE file narration in the background.
Technically, OLE Automation objects are instances of a class that consists of member function pairs that you can use to get and set the properties of an OA object, and member functions that you use to apply a set of methods to the object. Figure 15.1
is a diagram that shows, in greatly simplified form, how a Visual Basic 4.0 external programming tool (the OA client) interacts with Excel 7.0 and Visio 2.0 (OA servers).
Many OA-compliant applications include a built-in object browser tool—essentially identical to the Object Browser in Visual Basic 4.0—that displays a list of the object collections and member objects that the application exposes. When you
choose a collection or member object, list boxes display the methods and properties applicable to the collection or member object. Figure 15.2 shows Excel 7.0's Object Browser, which you open by selecting the View | Object Browser menu
command after you open a VBA module. Notice that Excel's Object Browser is identical to the Visual Basic Object Browser, except that the labels of the drop-down list and the two list boxes are slightly different—the labels in Excel's Object Browser
reflect the terminology of the Excel environment.
Just like with the Visual Basic 4.0 Object Browser, when you choose a property or method in Excel's Methods/Properties list, the syntax for the object's method or property appears adjacent to the Help (?) button. Clicking the Help button displays the
help window for the property or method. (The Help button is only enabled if there is online help available for the selected method or property.) Figure 15.3 shows the help window for the Workbooks.Open method. (The Excel Workbooks object is a collection
object—it contains all of the workbooks opened in a particular instance of the Excel application, and is analogous to the Workspaces collection of workspace objects in the Jet database engine.) You can see from Figure 15.3 that the syntax of the
Workbooks object's Open method is quite similar to that of Visual Basic 4.0's OpenDatabase method. All OA applications that support storing and retrieving data from files support an Open method.
Figure 15.1. Interaction of participants in an OLE Automation process.
Open is not printed in boldface type in this book because the Open method is not a member of Visual Basic 4.0's collection of reserved words and keywords. Open is a keyword of VBA.
Figure 15.3. Excel 7.0's help window for the Open method that applies to the Workbooks collection.
The following sections introduce you to the terminology of OLE Automation.
Readers who are interested in delving deeper into the intricacies of OLE Automation are advised to acquire the OLE 2.x SDK or join the Microsoft Developers Network (MSDN), if only to obtain the OLE 2.x documentation that is included with the SDK or on the MSDN CD-ROM.
OLE Automation is built on Microsoft's Component Object Model (COM) for OLE 2.x. Applications that support OA expose programmable objects. A programmable object is an application-specific object, such as an Excel Worksheet object,
whose properties can be manipulated by code contained in another OA-compliant application. The application that manipulates the programmable object is called the OA client, and the application that supplies the object or provides the framework to
create the object is called the OA server. This terminology is consistent with DDE, but conflicts with OLE 2.0's use of "destination" to specify the OLE 2.0 client, and "source" to identify the OLE 2.0 server application. (The
preferred terminology, introduced with OLE 2.1, is "container" to specify the OLE client, and "server" to specify the OLE server.)
External programming tools dispatch an action to the OA application. You dispatch actions through the IDispatch interface of OLE 2.x applications. Following are typical actions you dispatch from Visual Basic 4.0 OA client applications to
OA servers:
Complex OA applications, such as Excel 7.0, may have a total of several hundred collections, member objects, properties, and methods that are exposed to OA client applications. Developers of OA applications use the IClassFactory interface to define
objects of application-specific classes. Collections are handled by OLE 2.x's IEnumVARIANT interface. (Values passed between OA applications always are of the Variant data type.) The identities of the exposed objects, as well as their
properties and methods, are contained in the application's object type library. As an example, the object library for Excel 7.0 is Xl5en32.olb (about 224KB), which is located in the \MSOffice\Excel folder.
Each OA-compliant application has its own object hierarchy. The name of the application, such as Excel, Word, or Visio, specifies the base class of the programmable objects. Most, but not all, OA-compliant servers use the name Application to specify
the top member of the application-specific object class structure. Thus, Excel.Application or Visio.Application specifies a programmable object class that encompasses all of the programmable objects that are exposed by Excel 7.0 or Visio 2.0. Word for
Windows 7.0 uses Word.Basic to specify the highest member of the class hierarchy. The Excel.Application, Visio.Application, and Word.Basic programmable object classes correspond to the Database object class that is the topmost member of the class of
Visual Basic 4.0 data access objects.
Documents correspond to the next lower level of an object class. Excel 7.0's document hierarchy consists of Workbook documents (members of the Workbooks collection), which contain Worksheet (members of the Worksheets collection) and Module objects
(members of the Modules collection) that are stored in a single .xls file. Like the tables contained in Database objects, you open a Workbook object from a file to make the programmable objects, such as a member of the Worksheets collection, that
are contained in the Workbook object accessible to your client application. Worksheet objects have a vast collection of properties and methods. Some methods or properties of Worksheet objects, such as Cells, return or reference additional objects that have
their own set of properties and methods. The Cells method, for example, returns a reference to a Range object, which contains properties governing font styles, worksheet formulas, and so on.
Programmable OA objects at (or near) the top of the object hierarchy, such as Excel.Application, Workbook, Worksheet and so on, that you can create directly in your Visual Basic code are called externally creatable objects. Programmable OA objects lower in the object hierarchy that can only be accessed through properties or methods of objects higher in the hierarchy (the Range object, for example, which can only be accessed through a Worksheet object) are called dependent objects. Typically, you must create a new instance of an externally creatable object before you can access instances of its dependent objects. That is, you must create an instance of a Worksheet object before you can access the dependent Range object. (A Range object refers to one or more cells in a worksheet; typically, you access Range objects through the Cells method of the Worksheet object.)
Excel also has two types of programmable document objects that you can specify directly: Excel.Sheet and Excel.Chart. Visio 2.0 has a Documents collection that includes each Document (drawing or template) object that is open in Visio. (Word 7.0 does
not have a programmable document object. Word 7.0 exposes the Word.Basic object discussed later in this chapter.) Examples of the use of the collections and member objects discussed in this section appear in the section titled "Visual Basic 4.0 Syntax
for OLE Automation Instructions," later in this chapter.
Although you can obtain the names of objects (and their properties and methods) exposed by OA-compliant OLE 2.x servers by consulting the documentation and/or online help that accompanies the server application, or by using the server application's Object Browser (such as Excel's Object Browser, shown in Figure 15.2), you can view the available exposed OA objects directly in Visual Basic 4.0's Object Browser if the server application provides an object library.
To add the externally creatable objects to Visual Basic's Object Browser, you must first create a reference to the server application's object library. Use the Tools | References command to display the References dialog, which contains a list of all object libraries registered with the system. Select the reference that you want to add in the References dialog. If the object library you want isn't listed, use the Browse button to search for type libraries (files ending with .olb or .tlb). Some OA server applications store their object library in .exe or .dll files—if you cannot add a reference to an .exe or .dll file with the Browse command in the References dialog, then that file does not contain an object library.
After you've added the reference to the object library, the objects exposed by the OA server appear in Visual Basic 4.0's Object Browser. Figure 15.4 shows the Excel 5.0 object library (which is the same object library exposed by Excel 7.0) in the Visual Basic 4.0 Object Browser. These Excel OA objects were added to the Visual Basic 4.0 Object Browser by creating a reference to Xl5en32.olb. Compare Figure 15.4 with Figure 15.2; notice that the objects, methods, and properties listed are the same in both Object Browser dialogs. Clicking the Help button in Browser for the Excel Workbooks.Open method shows Excel's online help for this method—the same help window as shown in Figure 15.3.
Figure 15.4. After adding a reference to Excel's Object Library (Xl5en32.olb), Visual Basic 4.0's Object Browser dialog shows the available programmable objects in Excel.
There are three ways to gain programmatic control over OLE Automation objects. First, you can create a reference to the OA server (and hence its exposed objects) by using the Tools | References command. By creating a reference, you cause
Visual Basic to create an early binding to the OA server's exposed objects, and make it possible to refer to external objects directly, just as if the object names are part of Visual Basic. With this approach, you simply use the OA server's objects in your
Visual Basic code—there is no need to use special functions to create instances of the OA server, nor do you need to declare variables for the OA objects (except as a shorthand convenience when using lengthy or complex object references).
Second, you can use the CreateObject or GetObject functions in Visual Basic to create a late-binding instance of the OA object, assign that instance to an object variable, and then manipulate the OA object through the object variable. This second
approach follows the pattern established in Visual Basic 3.0 for the Data Access Object. The primary difference between most programmable objects and the Data Access Object is that you can easily create a new or empty programmable object. (Creating new
database tables with Visual Basic 4.0 code requires considerable programming effort.)
Any OLE object that Visual Basic loads at the same time your application loads (whether your application project is a compiled .exe or you are executing it in Visual Basic's run mode) is said to have early binding. The .ocx controls contained in your application's forms are examples of early-binding OLE objects. If Visual Basic cannot load or locate the early-binding components of your application, your application will not load. Any OLE object that Visual Basic loads after your application has already started running is said to have late binding.
The third technique is to use insertable objects in your forms, and to program the OA server through the insertable object's methods and properties. An insertable object is any OLE Automation object that provides a user interface as part of the
object. For example, an Excel worksheet is an insertable object. The worksheet object has scrollbars, a menu, selection tabs, and so on that comprise a user interface for the worksheet. Similarly, a Word 7.0 document is also an insertable object—it
provides a menu and toolbars as part of its user interface. Because the OLE Automation object has a user interface, you can insert it into your Visual Basic forms as a control on the form. You can then use OA programming techniques to manipulate the
insertable object with your Visual Basic code. Insertable objects, like OA objects for which you have created a reference, are early-binding elements of your application.
Whichever technique you use to access the OA object, you can set or get the values of properties of the object and apply methods to the object using the same techniques that apply to the Data Access Object. The sections that follow describe how to
declare and assign values to programmable object variables, and how you manipulate the programmable OA objects with Visual Basic 4.0 code using each of the three techniques just described: how to use early-binding OA objects for which you have established
a reference in your project, how (and why) to use late-binding OA objects, and how to program insertable OA objects contained in your Visual Basic forms.
The Object data type of Visual Basic 4.0 is the generic data type for variables that point to application-specific programmable objects exposed by OA-compliant applications. You use object-type variables to store short-hand references to objects
that you manipulate in your Visual Basic code (as shown in Listing 15.2, later in this chapter). If you choose to create late-binding instances of OA objects with the CreateObject or GetObject functions (also described later in this chapter), you'll need
to use an object variable to store a reference to the object instances you create.
You declare Object type variables the same as any other variable type:
Dim objObjectVar As Object
You can also declare object variables with specific object types. If you intend to use an object variable to store a reference to a specific OA object type—such as an Excel workbook, an Excel worksheet, or a Word for Windows document—you
write object variable declarations like the following:
Dim objObjectVar As Workbook 'an Excel workbook object Dim objObjectVar As Worksheet 'an Excel worksheet object Dim objObjectVar As Document 'a Word for Windows document object
Declaring object variables with a specific object type instead of the generic Object type has a couple of advantages. First, using the specific OA object name (as listed in either Visual Basic's or the OA server's Object Browser) permits Visual
Basic to manipulate the object variables more quickly, resulting in a performance improvement for your code. Second, using the specific object type causes Visual Basic to produce a type-mismatch error, should you accidentally write a statement assigning
the wrong object type to the variable—such as assigning an object reference to an Excel Workbook object to a variable declared with the Worksheet object type. Enabling Visual Basic to detect type-mismatch errors in this way helps you detect subtle
programming errors in your code.
When declaring object variables with a specific object type, you can determine the specific object names by reviewing the available objects in the Object Browser, or by checking the contents of the HKEY_ CLASSES_ROOT hive in your Registry database.
Using OA objects in your Visual Basic code involves a certain amount of operating overhead in both your application and in the computer system running your application. To use OA objects, the OA server application must be started—obviously, the OA
server requires a certain amount of system memory and other resources in order to execute. The internal connections between your Visual Basic application and the OA server also add a certain amount of memory and resource overhead to your application.
Typically, Visual Basic releases any resources it has allocated to the OA object as soon as the OA object is no longer needed. Depending on how, specifically, you created the OA server's executing instance (by directly using an early-binding referenced
OA object, or creating an instance with late-binding) Visual Basic may or may not automatically shut down the OA server application.
In any case, Visual Basic will not release an OA object as long as there is any variable with active scope that refers to that object. To follow good programming practice and to help ensure that Visual Basic is able to automatically release OA
objects and their associated resources, you should assign the special Nothing value to an object variable as soon as you are done using it. Assigning the Nothing value dissociates the object variable from any object it may have referenced and
ensures that Visual Basic will be able to release any memory or other resources allocated to the OA object. (As described later in this chapter, you should also specifically close or quit any OA server applications that you use with late-binding—that
is, any OA object instance that you create with an OLE control or by using the CreateObject or GetObject functions.) To dissociate an object variable from the object it references, use the special Nothing value in a Set assignment statement:
Set objVar = Nothing
If objVar refers to an OA object, and if there are no other variables with active scope that refer to the same object as objVar, then Visual Basic releases all resources allocated to the OA object and (if the OA server was implicitly
activated by your application) ends the executing instance of the OA server.
To use OLE Automation objects provided by an early-binding reference, you must first create a reference in your project to an external OLE object or type library. After creating the OA object reference, you can directly utilize the programmable OA
objects exposed by the object or type library. By creating a reference to an OA object or type library, you cause Visual Basic to bind the OA objects (throughout the object library) to your application when your application loads—whether your
application is loading as an .exe file or in Visual Basic itself. Most OLE Automation server applications provide an object library (.olb) or an object type library (.tlb) file. A few OA servers store their object libraries in an executable file (.exe or
.dll). To create a reference to an object library, follow this procedure:
If you're not certain whether an application exposes its objects (that is, contains an object library), try selecting its .exe file or its .dll file(s) with the Browse button of the References dialog. If the .exe or .dll file contains an object library, Visual Basic will add it to the Available References list. If the attempt to add the reference fails, then that .exe or .dll file does not contain an object or type library.
After establishing the reference to the object or type library, the OA server's objects are listed in Visual Basic's Object Browser dialog, and the objects and methods are directly available in your code, without any requirement that you declare object
variables or use special functions to make the OA object accessible. You may access any referenced object through its programmatic ID (ProgID). (Programmatic IDs and ProgID were discussed in the preceding chapter.) Typically, you access instances of a
referenced OA object through the OA server's top-level object (usually the application itself).
You learned in the preceding chapter that OLE server applications have their ProgID names entered in the Windows 95 Registry. The name you use in your code to refer to a referenced OA object is the same as the OA server's ProgID name in the Registry.
For example, to refer to an object or method in Excel, you use the following syntax:
Excel.Application.objectIdentifier
Excel.Application.methodIdentifier
For convenience, you might wish to create a shorthand reference to an OA object by assigning a lengthy object reference to an Object type variable (as demonstrated in Listing 15.2).
Listings 15.1 and 15.2 both provide examples of procedures that use OA objects from a referenced object library (Excel, in this case). Listing 15.1 shows how to use referenced Excel objects to open an Excel workbook and execute Excel VBA code contained
in that workbook (called Excel VBA in this book).
Notice that the procedure in Listing 15.1 does not declare any variables. Listing 15.1 illustrates how easily you can use Visual Basic and referenced object libraries to create externally programmed tools for an OLE 2.x application. In the
InsObj.xls workbook, the XLHello procedure merely displays a message dialog, but it could just as easily have been a complete application programmed in Excel Basic. Listing 15.1 begins by using the Open method of the Excel Workbooks collection to open the
InsObj.xls workbook. Next, Excel's Run method is called to execute the specified macro procedure. Finally, the Excel workbook's Close method is executed to close the workbook (which is accessed through the Workbooks collection), and the Excel application's
Quit method is then invoked to release any system resources allocated to the executing instance of Excel that Visual Basic automatically created when the first use of the Excel.Application object occurred.
The procedure in Listing 15.1 is contained in the Xl5oa1.bas module of the Xl5oa1.vbp sample application, which is located in your \Ddg_VB4\32_bit\Chaptr15 folder. Listing 15.1 (and other examples in this chapter) relies on the existence of the InsObj.xls workbook file, also located in your \Ddg_VB4\32_bit\Chaptr15 folder. If InsObj.xls is in a different directory, change the sample code accordingly.
Listing 15.1. Code to implicitly create and then manipulate a referenced Excel.Application object.
Sub Main()
'Purpose: Demonstrate programmable OLE Automation objects using early
' object binding.
'NOTE: This code only works if the project has a reference to Excel.
'open a workbook
Excel.Application.Workbooks.Open "c:\ddg_vb4\32_bit\chaptr15\insobj.xls"
'execute code in workbook (or perform other workbook/worksheet tasks)
Excel.Application.Run "INSOBJ.XLS!XLHello"
'close the workbook
Excel.Application.Workbooks("insobj.xls").Close
'quit Excel
Excel.Application.Quit
End Sub
Listing 15.2 shows the same type of implicit creation of an OA server object, but this time performs operations with some lower-level objects within the Excel application, accessing individual cells of individual worksheets in a workbook to print a few
lines of each worksheet in the Debug Window. Because object references all the way down to the cell level of a worksheet become quite lengthy, the procedure in Listing 15.2 uses a couple of object variables—objXLBook and objXLSheet—to create
shorthand references to the OA objects being manipulated.
The procedure in Listing 15.2 is contained in the Xl5oa2.bas module of the Xl5oa2.vbp sample application supplied on the accompanying CD-ROM. Xl5oa2.vbp is located in your \Ddg_VB4\32_bit\Chaptr15 folder. The procedure in Listing 15.2 (and others in this chapter) relies on the existence of the Stocks5.xls file in the default \Ddg_VB4\32_bit\Chaptr04 folder. If Stocks5.xls is located in a different directory, change the code accordingly.
Listing 15.2. Code to manipulate lower-level objects in the Excel.Application object.
Sub Main()
'Purpose: Demonstrate programmable OLE objects
'NOTE: This code only works if the project has a reference to Excel
Dim intRow As Integer 'Row counter
Dim intCol As Integer 'Column counter
Dim objXLBook As Workbook 'An Excel Workbook object
Dim objXLSheet As Worksheet 'An Excel Worksheet object
'Open an existing .XLS file and add it to the Workbooks collection
Set objXLBook = Excel.Workbooks.Open( _
"c:\ddg_vb4\32_bit\chaptr04\stocks5.xls")
On Error Resume Next 'Prevent error on Null value
'Loop through each of the Worksheet objects
For Each objXLSheet In objXLBook.Worksheets
Debug.Print objXLSheet.Name
'Print the cell values for a few rows to the Debug object
For intRow = 1 To 5
For intCol = 1 To 6
If Err Then
Debug.Print "Null",
Err = 0
Else
Debug.Print objXLSheet.Cells(intRow, intCol).Value,
End If
Next intCol
'Add a blank line to the Debug Window display
Debug.Print
Next intRow
'Add two blank lines separating sheet listings
Debug.Print
Debug.Print
Next objXLSheet
'Turn on error processing
On Error GoTo 0
'Release the memory and close the application
Excel.Application.Quit
Set objXLSheet = Nothing
Set objXLBook = Nothing
End Sub
In Listing 15.2, notice that the object variables used were declared with a specific object type—Workbook and Worksheet—in order to make the code more efficient. Notice also that the objXLBook and objXLSheet variables are set to the special
value Nothing to help ensure that Visual Basic will be able to release any memory or other resource allocated to the workbook and worksheet objects.
Because the Excel.Application object (and all the objects it contains) in Listings 15.1 and 15.2 is created through a reference to the OA server's object library, any reference to the Excel application implicitly creates an instance of Excel. Because of the reference, the Excel application was bound to the Visual Basic application early—when the Visual Basic project was loaded—so there is no need to perform any explicit action to create an instance of Excel. (Note that early binding of an OA object does not mean that the OA server is loaded right away—the OA server is loaded when it is needed.)
As mentioned earlier in this chapter, an insertable object is an OLE Automation object that provides a user-interface for interacting with the object. Excel worksheets and Word for Windows documents are examples of insertable objects. Insertable
objects are another form of early-binding OA object.
You use insertable objects as controls on your Visual Basic forms. To use an insertable object as a control, you must first add the insertable object to the Visual Basic Toolbox. After you add the insertable object to the Toolbox, you may place it on
forms and use it much as any other Visual Basic control. To add an insertable object control to the Visual Basic Toolbox, follow this procedure:
Figure 15.6. Use the Custom Controls dialog to add insertable controls, such as the Excel Worksheet insertable object control, to the Visual Basic Toolbox.
Insertable objects are always a form of an embedded OLE object. Insertable objects store their data in the form's .frx resources file. Your .frx file and application distributables will increase in size proportionately to the amount of data stored in an insertable OLE object.
After you add an insertable object to the Visual Basic Toolbox, the insertable object type and its available properties and methods appear in Visual Basic's Object Browser dialog. For example, after adding the Excel Worksheet insertable object shown in
Figure 15.6, the "ExcelCtl - Excel Type Library" entry appears in the Libraries/Projects drop-down list of the Visual Basic Object Browser.
The number of properties, events, and methods provided by Visual Basic for an insertable control is quite limited. An insertable object control has only the most generic properties for height, width, name, help context, and so on. The only available
events for insertable object controls are losing/getting focus, DragDrop, and DragOver. Available methods are similarly limited to setting the focus, displaying the What's This help, and so on. The most important insertable object property is the Object
property—the Object property returns a object reference to the OA server that provides the insertable object. It is through the Object property that you gain programmatic control over the insertable object control.
The primary uses of an insertable object control are to permit the user to interactively work with some stored default data, and to provide access to the programmable OA objects that belong to the insertable object's server application.
Listing 15.3 shows two procedures. Each procedure in Listing 15.3 executes as a result of a menu choice and carries out actions involving an insertable object control—an Excel worksheet. The mnuObjectExecuteXLVBA_Click procedure uses the
insertable object's server application to open an Excel workbook and execute Excel Basic code stored in that workbook (similar to Listing 15.1). The mnuObjectLoadXL_Click procedure uses the CreateObject function to create an OA instance of Excel
(completely separate from any instance of Excel represented by the insertable object control). CreateObject is described in the next section of this chapter.
The instance of Excel created by CreateObject is used to open a workbook and to copy data from that workbook to the Windows Clipboard; as soon as the copying task is done, that instance of Excel is closed. Next, the insertable object is used to provide
access to its own instance of the Excel OA server. The insertable object uses its own OA server, accessed through the Object property, to copy the Clipboard contents into itself, adds some formulas to the insertable object's worksheet data, and then forces
calculation of the new formulas in the insertable object's worksheet.
The procedure in Listing 15.3 is contained in the InsObj2.frm of the InsObj.vbp sample application supplied on the accompanying CD-ROM. InsObj.vbp is located in your \Ddg_VB4\32_bit\Chaptr15 folder. The procedures in Listing 15.3 rely on the existence of both the InsObj.xls file (located in the \Ddg_VB4\32_bit\Chaptr15 folder) and the Stocks5.xls file (located in the \Ddg_VB4\32_bit\Chaptr04 folder). If either InsObj.xls or Stocks5.xls is located in a different directory, change the code accordingly.
Listing 15.3. Code to manipulate an insertable object's data and the object's server application.
Private Sub mnuObjectExecuteXLVBA_Click()
'Purpose: Demonstrate programmable access to other objects by accessing
' an insertable object's parent object.
With frmInsObj.insobjXLWorksheet.object
'Open another workbook by using the insertable object.
.Parent.Parent.Workbooks.Open "c:\ddg_vb4\32_bit\chaptr15\insobj.xls"
'Execute a VBA macro in that workbook
.Parent.Parent.Run "INSOBJ.XLS!XLHello"
'make the insertable object the current workbook/sheet
.Activate
'close the extra workbook
.Parent.Parent.Workbooks("insobj.xls").Close
End With
End Sub
Private Sub mnuObjectLoadXL_Click()
'Purpose: Demonstrate programming insertable objects (an Excel
' worksheet, in this case). Also demonstrates the use of the
' CreateObject function to create a late-binding instance
' of the Excel application.
Dim objXLApp As Excel.Application
Dim objXLBook As Workbook
'create a new instance of Excel
Set objXLApp = CreateObject("Excel.Application.5")
'open the Stocks5.xls workbook with Excel
Set objXLBook = objXLApp.Workbooks.Open( _
"c:\ddg_vb4\32_bit\chaptr04\stocks5.xls")
'select a range of cells on the "Volume" worksheet
With objXLBook.Worksheets("Volume")
.Select 'select the worksheet
.Range("A1:D6").Select 'select some cells on the worksheet
End With
'clear the clipboard
Clipboard.Clear
'copy the contents of the sheet to the clipboard
objXLApp.Selection.Copy
'close the workbook, quit Excel, and reclaim memory
objXLBook.Close
objXLApp.Quit
Set objXLApp = Nothing
Set objXLBook = Nothing
'Manipulate worksheet data through the worksheet control on the form.
'The Object method of the insertable object control returns a reference
'to the OA server's programmable object.
With frmInsObj.insobjXLWorksheet.object
'paste clipboard contents into the insertable object
.Paste
'enter text labels and formulas in cells F2, G2, F3, and G3 of the
'insertable object
.Cells(2, 5).Value = "Total:"
.Cells(2, 6).Formula = "=Sum(C2:C6)"
.Cells(3, 5).Value = "Average:"
.Cells(3, 6).Formula = "=Average(C2:C6)"
'force calculation of new formulas in the insertable object
.Calculate
End With
frmInsObj.insobjXLWorksheet.SetFocus
End Sub
Figure 15.7 shows the insertable object after executing the code in mnuObjectLoadXL_Click.
Figure 15.7. An Excel worksheet insertable object control.
If you receive an OLE server cannot create object message when you run the preceding example, a timeout may have occurred due to the time required to load Excel 7.0 when several add-ins also are loaded. Open Excel's Add-In Manager and temporarily disable all of the add-ins. Then try running the preceding example again.
Some of the code statements in Listing 15.3 require a little explanation. In general, when using VBA code to program an insertable object, you must access the OA object's methods and properties through the insertable object's Object property, which
returns a reference to the instance of the OA object that creates the insertable control. To access the OA object's methods and properties through the Object property, use the following syntax:
insobjObjectName.Object.MethodOrProperty
As an example, the Worksheet object's Activate method makes that worksheet the currently active worksheet. The mnuObjectExecuteXLVBA_Click procedure in Listing 15.3 uses the following statement to activate the inserted worksheet object. (Activating the
worksheet makes it current as far as the OA server—Excel—is concerned, it does not activate the worksheet for user interaction in the same way that double-clicking the insertable object does.)
insobjXLWorksheet.Object.Activate
You're probably accustomed to accessing objects by creating references that point downward through the object hierarchy to the object you want. For example, you ordinarily select a particular field in a table by first referring to an open database,
then using the database reference to access a particular table in the database, and then using the table reference to access the desired field. You can also access objects by working backwards through the object hierarchy. In fact, you'll almost
always have to work backwards through the object hierarchy when you program insertable objects.
Almost every OA object has a Parent property that returns an object reference to the current object's owner or creator (that is, its parent object). For example, the Parent property of an Excel worksheet returns an object reference to the workbook that
contains that worksheet. You use the Parent property to create object references that point backwards through the object hierarchy. As an example, the mnuObjectExecuteXLVBA_Click procedure in Listing 15.3 contains the following statement:
insobjXLWorksheet.Object.Parent.Parent.Run "INSOBJ.XLS!XLHello"
In this statement, insobjXLWorksheet is an Excel worksheet placed on a form as an insertable object. The Object property returns a reference to the actual OA worksheet object, a Worksheet object. The first Parent property returns a reference to a
workbook object—a theoretical workbook that contains the worksheet insertable object. The expression insobjXLWorksheet.Object.Parent therefore returns a reference to a Workbook type OA object. The second Parent property (which belongs to
the Workbook object) returns a reference to the Excel application. The expression insobjXLWorksheet.Object.Parent.Parent therefore returns a reference to an Excel.Application type OA object. The Run method being executed belongs
to the Excel application.
You also can gain access to OLE Automation objects without adding references or insertable objects to your project. You can use the CreateObject function to create a new instance of an application-specific class, or you can open an object whose data is
stored in an existing document file with the GetObject function. Both of these functions can be considered methods of the generic Visual Basic 4.0 programmable object, just as OpenDatabase is treated in this book as a method of the abstract Data Access
Object of Visual Basic 4.0.
To create a new object instance, you use the following pair of statements (refer to the mnuObjectLoadXL_Click procedure in Listing 15.3 for an example of the CreateObject function):
Dim objObjectVar As Object Set objObjectVar = CreateObject(strApplicationName.strObjectType)
Thus, to create an object variable, objXLApp, that points to Excel.Application, you write the following two lines of code:
Dim objXLApp As Object
Set objXLApp = CreateObject("Excel.Application")
These two lines create a new object in much the same manner as adding a new OLE 2.x container control with the default Create New option button selected in the OLE 2.x Insert Object dialog, as described in the preceding chapter. The
mnuObjectLoadXL_Click procedure in Listing 15.3 is an example of a procedure that uses CreateObject to create an Excel application object and to open an existing .xls file.
When you create and manipulate a programmable object (whether implicitly or explicitly), you launch an instance of the OLE 2.x server application. The OLE 2.x server application is invisible, and the server name does not appear in the Windows Task Manager's list. Invisible execution of OLE 2.x servers is accomplished by adding a parameter to the command line that executes the server. The value of the LocalServer entry in the Registry database for the CLSID corresponding to Excel.Application (Microsoft Excel Application) is C:\MSOffice\Excel\Excel /automation. The /automation parameter causes Excel to execute without opening a window.
You can open an OA-compliant application with a specified document by using the GetObject function with the following syntax:
Dim objObjectVar As Object
Set objObjectVar = GetObject("d:\path\filename.ext"[,strObjectClass])
The preceding code fragment is related to the creation of a new OLE 2.x container control when you select the Create from File option button of the Insert Object dialog and then type d:\path\filename.ext in the File text box. The
syntax also parallels that for the OpenDatabase function (a method of the Data Access Object).
If you omit the optional strObjectClass argument, the server specified in the file extension association entry in the HKEY_ CLASSES_ROOT hive of the Registry database determines the server that is used. If you've installed Excel 7.0, files with an .xls extension specify the Excel.Sheet object class. You can override the default association with the strObjectClass argument. If you want to create a programmable object from an OA-compliant application that is currently running, substitute a comma for the path and filename argument and specify the object class.
To open an existing Excel 7.0 Worksheet object, use the following two lines:
Dim objXLSheet As Object
Set objXLSheet = GetObject("c:\vb\ddg\chaptr04\stocks5.xls")
Listing 15.4 uses the GetObject method to return the values of cells contained in the first few rows of a Worksheet object. You obtain the same result if you omit the Excel.Sheet argument of the GetObject method, because the default object for an .xls
file is a Worksheet object. The active Worksheet object is one that was opened last in the Excel workbook. Xl5oa3.bas contains the code shown in Listing 15.4.
Listing 15.4. Code that uses the GetObject method to open a Worksheet object.
Sub Main()
'Purpose: Test the GetObject() function
Dim intRow As Integer 'Row counter
Dim intCol As Integer 'Column counter
Dim objXLSheet As Object 'A worksheet object
Set objXLSheet = GetObject("c:\ddg_vb4\32_bit\chaptr04\stocks5.xls", _
"Excel.Sheet.5")
'Print the active worksheet name
Debug.Print objXLSheet.Name
'Print the cell values for a few rows to the Debug object
For intRow = 1 To 5
For intCol = 1 To 6
'Formula property shows dates in Microsoft Date/Time numeric format
Debug.Print objXLSheet.Cells(intRow, intCol).Formula,
Next intCol
Debug.Print
Next intRow
Debug.Print
Set objXLSheet = Nothing
End Sub
In most cases, using a reference to an object library is the best technique for manipulating programmable objects. If you want late binding for some reason, or you need to explicitly manage the creation of OA object instances, using the CreateObject
method to open the application and then opening a file is the second best method of manipulating programmable objects. The code example in Listing 15.3, for example, opens the .xls file as a Workbook object (the sole member of the Workbooks collection).
Thus, you can open and manipulate any programmable object contained in the Workbook object. When you use the GetObject method, most applications limit you to manipulating a single object. The GetObject example in Listing 15.4, for example, creates only a
Worksheet object. If you use "Excel.Application" as the value of the strObjectClass argument with the GetObject method, you receive an error message that reads OLE Automation server cannot create object.
You've already seen how to use an insertable object's Object property to access the programmable OA object behind the insertable object's control. Similarly, when you add an OLE container control to a form and select an object type supplied by an
OA-compliant application, the Object property of the OLE container control points to the programmable object. When you add an OLE control whose content is created from a file, the OLE control applies the GetObject(strFileName) method for you. The
first few statements in Figure 15.8 illustrate the use of Visual Basic 4.0's Debug Window to get or set the properties of the selected Worksheet object, STOCKS.
Figure 15.8. Using the Debug Window to experiment with the Object property.
You must activate the OLE object in an OLE container control before you refer to the object with Visual Basic code that you enter in the Debug Window or execute in a procedure. You activate the OLE object by double-clicking the OLE control or by executing the statement oleObject.DoVerb vbOLEShow.
If you add a new (empty) Excel 7.0 OLE container object to a form, Visual Basic applies the CreateObject("Excel.Application") method to the control. When you open a new Excel 7.0 object in an OLE container control, the default Workbook
object, Book1, contains one empty Worksheet object, Sheet1. The Application object class of the OLE container Object gives you access to all of the properties and methods of the programmable objects of the application.
The statements in the lower two-thirds of the Debug Window shown in Figure 15.8 illustrate how to use the oleObject.Object.Application reference to set the value of an object variable that you can use with code similar to that in
Listing 15.3 or Listing 15.4. Using the Debug Window to experiment with programmable objects is quicker and easier than writing and testing OA code that is executed in subprocedures.
You need to declare the object variables with Dim objName As Object statements in the Declarations section of the form on which the OLE container control is located or with Public objName As Object statements in a module before you type the Set objName = . . . statements in the Debug Window.
When you open a new Workbook object in an OLE container with the Open method, the new Workbook object is added to the Workbooks collection. The original Workbook object with the single empty Worksheet object is Workbooks(1) or
Workbooks("Book1") and the new Workbook object opened from the file is Workbooks(2). You refer to the three Worksheet objects in Workbooks(2) either as Worksheets(1). . .Worksheets(3) or by name, as in Worksheets("Stocks3"). References
to programmable objects and their member objects follow the same conventions as the naming rules applicable to the Jet Data Access Object.
When you explicitly open an OA server application with Visual Basic code, the server application remains open until you close it by applying the objApplication.Quit method (or the equivalent method if the application does not support the Quit
method). If you do not apply the Quit method or its equivalent when you are finished manipulating a programmable object, you ultimately run out of memory when opening other OA server applications. Exiting your application does not apply the equivalent of
the Quit method. Closing (but not hiding) a form that contains an OLE control closes the OA server application in most cases.
Programmable Word.Basic objects you create in Visual Basic 4.0 are an exception to the preceding rule. Unlike other programmable objects, Word.Basic objects close when the subprocedure that uses the Word.Basic object completes execution. Visual Basic terminates most implicitly created instances of OA object servers when the procedure that started the OA server ends and there are no object variables in the currently active scope that refer to any objects that belong to that particular OA server.
Object variables can consume a significant amount of memory. You do not regain the memory consumed by object variables that you declare with local scope when the subprocedure in which the variables are declared finishes executing. To recover the memory
used by object variables, you set the value of the variables to the Nothing reserved word when you have finished using them.
You cannot apply the Close method to programmable object variables (unless the object has its own Close method); Close is restricted to use with Visual Basic 4.0 data access objects. If you attempt to apply the Close method to object variables, you
receive an error message from the Visual Basic interpreter that reads Method is not applicable to this object.
The purpose of the simple code examples given in the preceding sections is to illustrate the techniques you use to access OA objects directly and through object variables using both referenced and specifically created objects. As noted earlier in this
chapter, each OA application has its own set of programmable objects, and each object has a specific set of properties and applicable methods. The programmable objects, properties, and methods exposed by Microsoft applications that use Visual Basic for
Applications as their macro language are, as a whole, the same objects, properties, and methods that are manipulated by the macro language.
OLE Automation server applications that do not incorporate VBA have sets of programmable objects, properties, and methods that reflect the nature of the application. The base object class of both Microsoft Word for Windows 7.0 and Microsoft Access 95
is the Application object. Unlike Excel, Access, and Microsoft Project, Word 7.0 is an OA server only.
The section that follows describes the changes you need to make to VBA code that you import into Visual Basic 4.0 from Excel 7.0, Access 95, and other VBA-supporting applications. A subsequent section describes changes you need to make in Word's Word
Basic macros you import into your Visual Basic 4.0 code.
To date, the applications that include VBA as their internal macro language are Microsoft Excel 5.0 and higher, Microsoft Access 95, and Microsoft Project 4.0 and higher. Microsoft applications that incorporate VBA expose the same objects, properties,
and methods to OA client applications that are accessible to the application's dialect of VBA. Excel 7.0 is used for most of the examples in this chapter because Excel is likely to be the most common OA server used with Visual Basic 4.0 database
applications.
Space limitations preclude a full discussion of Visual Basic 4.0 programming methods for programmable objects exposed by the OA server applications that were available when this book was published. Fortunately, you can use the VBA documentation that
accompanies the OA server application and the server's online help system to guide you in writing Visual Basic 4.0 OA client code. The syntax for variable declaration and assignment, conditional execution statements, loops, and most other program
flow-control instructions is identical in all flavors of VBA. You can copy VBA code from an OLE server application's module, paste the code into a Visual Basic 4.0 subprocedure, and execute the code under Visual Basic 4.0 with little or no modification.
To execute VBA code imported from other applications in Visual Basic 4.0, you're likely to need to modify the imported code as follows:
Versions 6.0 and 7.0 of Microsoft Word are OLE Automation clients only. The Word Basic macro language of Word, which is not a dialect of VBA, cannot open programmable objects of other OA server applications. Thus, Word Basic retains its status as a
macro language, as opposed to an application programming language such as VBA. As noted in the introduction to this chapter, you can use Word 7.0 as an OA server to your Visual Basic 4.0 client applications. It is possible, for example, to create a Visual
Basic 4.0 application that generates complete Word 7.0 documents from names, addresses, boiler plates, and graphic images that are stored in one or more Jet databases.
The Microsoft Word Developer's Kit is a separate product that you purchase from Microsoft. The Word Developer's Kit documents all of the instructions of Word Basic and provides examples of Word Basic 6.0 and 7.0 macro code.
When you use Word 7.0 as an OA server application, almost all of the instructions of the Word Basic macro language are available to your Visual Basic OA client application. Therefore, a Visual Basic 4.0 application can employ the
CreateObject("Word.Basic") method to launch Word 7.0 as an application and gain access to the Basic object of Word 7.0. Basic is the only object that Word 7.0 exposes (apart from the Document insertable object); the Basic object does not have
conventional, object-oriented properties and methods. Thus, you cannot use the GetObject("docname.doc") method to open an existing Word 7.0 document from a file as a programmable object. Instead, you execute Word Basic instructions as if these
instructions were methods of the Basic object you create in your Visual Basic 4.0 application. This book uses the term pseudo-method to describe macro language instructions that are exposed by OLE Automation.
Word 7.0 does not have an object library. Therefore, the only way you can execute Word Basic code from Visual Basic is by using CreateObject, through the Object property of an OLE container control, or through the Object property of an inserted object document control.
To open an existing Word document using OA, you use the following three statements:
Dim objWWApp As Object
Set objWWApp = CreateObject("Word.Basic")
objWWApp.FileOpen "d:\path\filename.doc"
Once you've opened the file, you can apply any Word Basic instruction as a pseudo-method of the objWWApp object. Word Basic includes many instructions that append the string object type identifier symbol ($) to indicate that the instruction returns a
string. You need to enclose references to Word Basic pseudo-methods that include the $ symbol within square brackets ([]) in your code. The use of square brackets in Visual Basic 4.0 to normalize nonconforming OA syntax is the same as the use of square
brackets to enclose table, field, and QueryDef names of Jet database objects that incorporate spaces or other impermissible characters.
To return the value of a bookmark in a Word 7.0 document pointed to by objWWApp to a String variable, for example, you use the following syntax:
Dim strBookmark As String
strBookmark = objWWApp.[GetBookmark$]("BookmarkName")
When you embed a Word 7.0 document in a Visual Basic 4.0 OLE container control, you use the following statements to gain access to the OLE container control's Basic object:
Dim objWWApp As Object Set objWWApp = oleControl.Object.Application.Word Basic
In this case, the Word Basic object descriptor does not include a period between Word and Basic because Word Basic is the (only) object exposed by the Application object class of the OLE container control's Object "property." As is the case
with any object embedded in an OLE container control, you need to activate the object before you refer to the Object "property." When you activate a Word 7.0 document in an OLE container, Word's toolbars appear together with the document, as
shown in Figure 15.9.
When you embed an object in a Visual Basic OLE container control, the data for the object is stored in the .frx file for the form on which the control appears. As noted in the preceding chapter, in-place activation is available only with embedded (not linked) OLE objects. Embedding Word documents with embedded bitmapped graphics in OLE container controls can result in very large .frx files. Unless you have a specific need to edit Word 7.0 documents embedded in a Visual Basic 4.0 application, you are likely to be better served by using the CreateObject method to operate on large Word 7.0 documents and to manipulate large Excel 7.0 worksheets.
Most Word Basic developers had expected that Word 6.0 would incorporate VBA, yet even Word 7.0 does not incorporate VBA. Changing from the template-oriented Word Basic macro language (the progenitor of the Visual Basic and VBA programming languages) to
object-oriented VBA contained in modules constitutes a major change in the development environment for Word applications. Major alterations of this scope often are called paradigm shifts. Publishers of high-volume, mainstream Windows mega apps take
a very conservative approach to instituting paradigm shifts. This conservatism is understandable when hundreds of millions of dollars of revenue hinge on the success of the application in a highly competitive market.
Microsoft can afford to take an incremental approach that includes the in-place activation features of OLE 2.x and added OA features that preserve backward compatibility with existing Word Basic macros, while opening up the capability to write
the equivalent of Word Basic macros in Visual Basic 4.0. This approach lets Microsoft reserve full VBA compliance for the next battle in the word processing features war.
Developers of Word Basic applications should seriously consider using Visual Basic 4.0 and OA for interprocess applications that involve Word 6.0 or 7.0. This is especially advisable for any Word application that requires access to databases, a subject that is discussed later in this chapter. Just as you can import VBA code to Visual Basic 4.0, you can export most Visual Basic 4.0 code to other application's VBA modules. The principal limitation of Excel and Project VBA is the lack of the rich complement of control objects provided by Visual Basic's Toolbox and OLE Controls. Excel and Project VBA currently limit you to displaying dialogs with only a few standard Windows controls. To the maximum extent possible, use controls in Visual Basic 4.0 applications that also are available in Word and Excel dialogs. Design your VBA code for Word applications so that event handlers call subprocedures contained in modules and explicitly address all Visual Basic 4.0 objects. This design method minimizes the amount of restructuring of your Visual Basic 4.0 code when you import into Visual Basic modules.
Word for Windows 7.0 Word Basic, while becoming increasingly object-aware, is still not yet a member of the VBA language family. As a result, there are some syntax changes you'll need to make in any Word Basic code that you intend to import into a
Visual Basic 4.0 application.
VBA enables you to use named arguments of procedures, methods, and functions without regard to the position of the argument in the instruction's or function's argument list. Word Basic employs a form of named argument syntax with its functions
and statements. The syntax for named arguments in Word Basic is not the same as the syntax for named arguments in VBA. If you import Word Basic code into your Visual Basic 4.0 programs, you'll need to rewrite some or all Word Basic function and procedure
calls to use the named argument syntax supported by Visual Basic 4.0.
As an example, a call to Word Basic's EditFind instruction that uses several of the available named arguments is as follows:
EditFind .Find = "Developer", .WholeWord = 0, .MatchCase = 1, .Direction = 0
Visual Basic 4.0 does allow you to use named arguments, but requires a slightly different syntax for the named arguments. The Visual Basic 4.0 equivalent to the preceding Word Basic EditFind statement is this:
objWWApp.EditFind Find:="Developer", WholeWord:=0, MatchCase:=1, Direction:=0
In the preceding Visual Basic 4.0 statement, objWWApp is an object variable that points to a Word.Basic OA object created with CreateObject or GetObject. Notice the absence of the dot in front of each argument name, and the addition of the colon (:) to
each argument's value assignment; thus, .Find = "Developer" becomes Find:="Developer".
In Word Basic, as in VBA, you can accept the default value for a named argument by omitting the argument from the argument list, as in the following Word Basic example:
EditFind .Find = "Developer's", .MatchCase = 1
The equivalent Visual Basic 4.0 statement is this:
EditFind Find:="Developer's", MatchCase:=1
Word Basic uses a space followed by a backslash character ( \) as a line-continuation character. A line-continuation character indicates that a single logical line of code has been divided over more than one physical line, and tells the
Basic interpreter to join the lines together to form a single code statement. The VBA line-continuation character is the space and underscore ( _) characters. As a result, you must change any Word Basic statements that use the line-continuation character.
The Word Basic EditFind statement from the last example, reformatted for readability using line-continuation characters, appears as follows:
EditFind .Find = "Developer", \
.WholeWord = 0, \
.MatchCase = 1, \
.Direction = 0
The equivalent Visual Basic 4 statement is this:
objWWApp.EditFind Find:="Developer", _
WholeWord:=0, _
MatchCase:=1, _
Direction:=0
Word Basic doesn't have a special string concatenation operator. Word Basic uses the plus sign (+) to indicate both arithmetic addition and string concatenation. Visual Basic 4.0 does recognize the plus sign as a string concatenation operator, but will
try to perform arithmetic addition before it performs string concatenation—depending on the specific values stored in variable expressions you may end up with various runtime errors. Although you aren't required to, it is recommended that you replace
the plus sign in Word Basic string concatenation operators with Visual Basic's preferred string concatenation operator: the ampersand (&).
Microsoft Excel 5.0 and 7.0 have adopted the Excel flavor of VBA as the preferred method of creating new macros to automate Excel applications. Although Excel 5.0 and 7.0 are backwardly compatible with conventional Excel function-style macros, any new
macros you record will generate Excel Basic code in a module, unless you explicitly choose to create Excel 4.0 function macros. The process of creating Excel VBA macros is virtually identical to the method used by Word. You can use Excel VBA statements to
execute existing macros and user-defined functions (UDFs) that were created with earlier versions of Excel. Figure 15.9 shows Excel 7.0's standard, no-frills Record Macro dialog.
Figure 15.9. Excel 7.0's standard Record Macro dialog.
After you type the name of the macro you want to record, type its description, and choose the options you want, click the OK button to have Excel automatically create a new module with the default name Module1. Figure 15.10 illustrates the expanded
version of the Record Macro dialog, which enables you to specify whether to create an Excel VBA procedure or an Excel 4.0 function macro. Figure 15.11 shows the Object Basic code that is written when you select the entire worksheet, choose a new data
format (#,##0.00), select a new display font (9-point Arial), and then set the column width to fit the newly formatted data. The floating toolbar, labeled Visual Basic, provides shortcuts for the menu selections applicable to editing and debugging Excel
VBA modules.
Figure 15.11. A simple recorded Excel VBA macro.
Excel VBA's Debug Window differs substantially from the Debug Window of Visual Basic 4.0. You choose between the Immediate window and the Watch window by clicking the Immediate or Watch folder tabs. Figure 15.12 shows the Immediate window of Excel 7.0
modules. The split window enables you to easily paste expressions from the module code in the lower pane onto the upper pane, where you edit and execute the expressions.
Figure 15.12. Excel 7.0's Debug Window.
To add watched variables, you click the Watch folder tab and then click the right mouse button to display the Watch floating menu. Choose Add Watch from the floating menu to display the Add Watch dialog. You can add a watch breakpoint to all
procedures, or to any procedure in a specified module, by making the appropriate selection from the Context combo boxes. You enter the watch expression in the Expression text box and then click OK to create the watch point. Watch Type options determine how
your expression affects execution of the Object Basic code. Figure 15.13 shows the Add Watch dialog.
Figure 15.13. Excel 7.0's Add Watch dialog.
Excel 7.0 calls the workbook on which you are working currently the current project. (Workbooks are Excel 7.0's projects.) You can use Excel 7.0's References dialog to create links to other projects—and to OLE object libraries—from the
current project. To display Excel 7.0's References dialog, as shown in Figure 15.14, choose the Tools | References menu command in Excel 7.0. You can add a reference to another project by clicking the Browse button to display the Browse
dialog and then choosing the file that incorporates references you want to include in your application. You can link references in other Excel 7.0 workbooks (.xls files) or Excel add-ins (.xla files), as well as in object libraries (.olb and .tlb files).
Figure 15.14. The References dialog for an Excel 7.0 workbook.
A newly linked project is called a directly referenced project, and the current project is called the directly referencing project. In a directly referenced project, public symbols are visible to the directly referencing project, but in a
directly referencing project they are not visible. Public symbols consist of the names of VBA functions and subprocedures, as well as public (global) variables and constants.
Excel 7.0's Object Browser dialog, which appears when you choose the View | Object Browser menu command (available only when a module is active), enables you to browse Excel 7.0 objects, the content of your workbook's modules, VBA
reserved words and key words, and the functions of other directly referenced projects and libraries. The Object Browser in Excel—and other Object Basic applications—looks and behaves almost identically to the Visual Basic 4.0 Object Browser.
If you intend to create an application that involves the interaction of two applications that are OA-compliant and which incorporate VBA, you make a choice of the primary programming environment for your application from the following two options:
If your application involves access to one or more databases, writing a Visual Basic 4.0 OA client application is the obvious choice. Using Visual Basic 4.0's Data Access Object to open and manipulate database objects is more straightforward than
employing the somewhat cumbersome database extensions of Excel 7.0 and Word 7.0. Although you can create a reference to the DAO object library in Excel and then use Excel VBA to manipulate the database the same way you would in Visual Basic 4.0, Excel 7.0
lacks the sophisticated bound data controls available in Visual Basic 4.0. Without the bound data controls of Visual Basic 4.0, displaying data in a dialog becomes very code-intensive.
Word 7.0 developers currently are limited to using DDEExecute instructions to manipulate objects of other applications that are not components of a Word 7.0 document. As mentioned earlier in the chapter, Word Basic programmers who need to manipulate
programmable objects in other OA-compliant applications can gain control more easily over the other applications' objects by using Visual Basic 4.0 OA client applications.
Excel developers are likely to favor VBA code because the Excel worksheet is a familiar development environment, and creating references to Excel objects is simpler in Excel VBA. When you program Excel objects in Visual Basic 4.0, however, you are not
limited to using Excel 7.0 modal dialogs to interact with the user of your application. Therefore, your Excel application, executed from a Visual Basic 4.0 application, can take full advantage of the variety of window modes and control objects offered by
Visual Basic 4.0.
This chapter has given you an overview of the capabilities of Visual Basic 4.0's implementation of OLE Automation client features and insight into the use of Excel VBA, an application-specific dialect of Visual Basic for Applications. This book is
devoted to Visual Basic 4.0, so only the principal similarities and differences between generic VBA and Visual Basic 4.0 were discussed. The examples of Excel VBA presented in this chapter demonstrate that interchanging VBA code between Visual Basic 4.0
and Excel's VBA dialect is a relatively simple process and that few, if any, modifications to the imported code are necessary.
Access 2.0's Access Basic is a specialized dialect of Object Basic designed specifically for writing code for database applications. Access 95 has made the transition to 32-bit VBA, with a few objects that aren't supported by other VBA flavors. Chapter
17, "Translating Access Basic and VBA Code to Visual Basic 4.0," shows you how to import Access VBA code into Visual Basic 4.0 database applications and how to make the necessary alterations to the Access VBA code for compatibility with Visual
Basic 4.0 syntax. The next chapter shows you how to use Visual Basic 4.0 to create your own OLE Automation server applications.
