Chapter 25

Understanding VBA and Other Microsoft Basic Languages

On May 20, 1991, Microsoft announced Visual Basic at Windows World '91 in Atlanta, and the programming world hasn’t been the same since. Windows programmers finally had a very powerful programming language that was simple to use and very intuitive. Visual Basic for Applications takes the success of Visual Basic one step further, and in this chapter you learn how VBA helps make your job as a programmer much easier.

In this chapter, you learn more about the following:

• The origin of Visual Basic for Applications and how it relates to Visual Basic (VB)
• How to use similar code in all the Microsoft Basic languages for Windows
• What type libraries are and why they are useful
• How to access database files directly with Visual Basic using data access objects (DAO)
• Determining when to use to use Access rather than Visual Basic
• The Word Insertable Object Control
• Using OLE Automation and the OLE container control with Word

Introducing Visual Basic for Applications (VBA)

Visual Basic for Applications (VBA) is Microsoft's attempt to create a common macro language for its products. VBA is based on the highly successful Visual Basic (VB) product. This second generation version of Visual Basic is as easy to use as VB and includes many new enhancements to the language. Some of its major enhancements include the following:

• Close integration with OLE 2.0 (including enhanced OLE Automation support)
• Additions to the language (line continuation character, optional arguments, and so on)
• A stronger emphasis on object oriented concepts (encapsulation, objects, classes, and so on)
• Consistency across different implementations (a common VBA type library for basic features)

Most of the improvements help to make VBA a more robust language for its most important feature, OLE Automation.

For a more exhaustive discussion on VBA, read Using Visual Basic for Applications, Excel Edition, by Jeff Webb. This book contains useful information for using VBA with the most powerful OLE 2.0 server, Microsoft Excel.

Understanding the Differences between VB and VBA

Although VB and VBA are very similar, they differ in some important ways. Perhaps the most important difference is the definition of the two languages. Table 25.1 provides these definitions.

Table 25.1 Definitions of VB and VBA

All this stuff is rather confusing, but there's a simple way to differentiate between the two: VB is a separate programming product that enables you to make executables, and VBA is a new macro language for Microsoft's applications. As of VB 4.0, both products can share elements (type libraries) through the Object Browser. Currently only Excel 5.0 and Project 4.0 have VBA, but eventually all Microsoft languages will adopt VBA as a common macro language.

Using Visual Basic for Applications

You are probably wondering whether you can simply look at a code sample and automatically distinguish VB code from VBA code. You cannot; instead, you must look at the editor in which the code was written. For example, the following procedure could appear in either VB or VBA:

By looking at this code in its editor, you can tell which language was used. Figure 25.1 shows an example of VBA in Microsoft Excel, and figure 25.2 shows an example of Visual Basic 4.0. Viewing the editor makes a difference because all VBA editors conform to the same basic rules.

Fig. 25.1

VBA code in Microsoft Excel 5.0.

Fig. 25.2

The same code in Visual Basic 4.0.

Type Libraries and the Object Browser

How can one language meet all the needs of three separate products? That’s where type libraries enter the picture. A type (or object) library is a file that each variation of Visual Basic provides to add product-specific features (for example, accessing a range of cells in Excel). In fact, all products with VBA share a common VBA type library. What's more, any variation of VBA can use type libraries from other applications to perform OLE Automation.

Type libraries are managed by a program, the Object Browser, that is built in to VBA. Object Browser (see fig. 25.3) provides the user with a list of the available libraries and their elements.

Fig. 25.3

The Object Browser in Visual Basic 4.0 provides easy access to type libraries.

Visual Basic 4.0's version of the Object Browser (VBA 2.0) is slightly enhanced from VBA 1.0's, but their purposes are identical.

Other Basic Languages from Microsoft

New programmers often complain that they are confused by all the "Basics" found in Microsoft products. This section clears up the confusion for you.

Currently, Microsoft has four versions of BASIC floating around in Windows: Visual Basic, Visual Basic for Applications, WordBasic, and Access Basic. You have already seen the two Visual Basic forms, so this section introduces the other two forms.

WordBasic

You can trace the origin of Visual Basic back to the macro language in Microsoft Word for Windows 1.0. This macro language, WordBasic, included many of the elements found in Visual Basic today. In fact, WordBasic is still the macro language used in Word for Windows 7.0.

This chapter focuses only on Microsoft BASIC development in Windows. Other forms of Microsoft BASIC (for example, QuickBasic) are not discussed.

You can run the Greetings sample in WordBasic, with some slight modifications. As you can see in figure 25.4, Visual Basic inherited its MsgBox function from WordBasic.

Fig. 25.4

Some features of WordBasic are identical to their Visual Basic counterparts.

For the greatest compatibility between Word and VB code, consider using Visual Basic 1.0, or using OLE Automation with the WordBasic type library from VB4.

Usually Visual Basic code is not interchangeable with WordBasic. For a detailed description of WordBasic's syntax, consult the Microsoft WordBasic Help file (WRDBASIC.HLP) in the directory in which you installed Microsoft Word for Windows.

At this point, you might be jumping for joy at the idea that WordBasic and Visual Basic are similar. However, here’s the bad news: Although Visual Basic derives partially from WordBasic, the languages have several differences. In fact, almost every line of code ported between the two products requires some modification. However, all is not lost.

After the tremendous success of Visual Basic, Microsoft realized that it needed a way to enable Visual Basic programmers to customize or control other applications (for example, Word) from VB. Although Word has always exposed WordBasic through Dynamic Data Exchange (DDE), this method was too cumbersome and links were easily broken. Microsoft's solution was to make WordBasic an OLE Automation object. This means that almost any method performed in WordBasic can be executed directly in Visual Basic. Listing 25.1 demonstrates this point.

Listing 25.1 Using WordBasic from Visual Basic

For a complete listing of the WordBasic's OLE Automation syntax, use the WordBasic Type Library with the Object Browser.

For more information about using WordBasic from within Visual Basic, see “Integration with Microsoft Word” later in this chapter.

Access Basic

In late 1992, over a year after the release of Visual Basic, Microsoft released yet another version of BASIC. This version, Access Basic, is the macro language that enables you to customize Microsoft Access. Although there are some subtle differences, Access Basic is about 90 percent compatible with Visual Basic.

Microsoft Access 7.0 includes VBA 2.0, so your Access code must be 100-percent compatible with Visual Basic 4.0. Use the information in this chapter if you want to integrate Access 2.0 with Visual Basic 4.0.

If you declare your constants and remove the line continuation character from your VB code, you can successfully run the Greetings example in Access. Figure 25.5 shows the modified Greetings example in Microsoft Access 2.0.

Fig. 25.5

Code that conforms to the Visual Basic 3.0 syntax is virtually interchangeable with Access Basic.

For the best compatibility between Visual Basic 4.0 (VB4) and Access, you should develop your code in VB4, using the DAO 2.5/3.0 Compatibility layer, and port it to the next version of Access when it becomes available.

Visual Basic 4.0 includes major enhancements to its language that make its code much less compatible with Access Basic 2.0. Although you can create functions that work with either product, you must be more careful when doing so. Be sure to avoid VBA enhancements (for example, line continuation characters, optional arguments, and so on) when writing code for both products, unless you intend to use Access 7.0.

If you want to distribute an application that you have created in Access Basic, Microsoft provides the Access Development Kit (ADK). This product includes additional documentation about Access Basic, and includes everything that you need to distribute your Access application legally to users who do not own Access.

When to Use Macros Rather Than VB

When should use any one of these versions of BASIC? Although there is no one answer, you can use table 25.2 as a guide to help decide which is best for you.

Table 25.2 Choosing the Right Language

If you're developing a custom application and the customer doesn't have Excel or Project, consider buying them a copy. Even the smallest custom projects can cost several thousand dollars, so including one of these products (which you can typically purchase for less than $150) can save you weeks or months of development time.

Integration with Other Microsoft Applications

To harness the full power of Visual Basic, you should take advantage of the existing Microsoft applications installed on your user's computer. This approach saves you from the burden of reinventing the wheel, and enables you to take advantage to the robust features that these products offer.

Communication with Access

At the heart of most custom applications is a database, and Microsoft Access has quickly become one of the most popular programs for database management. This section describes how you can tap the power of Access for use in your Visual Basic applications.

Programmers often ask how their program can control Access. However, this question is rather silly due to the nature of Access. If you own a copy of Visual Basic 3.0 or greater, you already have most of the power of Access. The only part of Access over which you don't have control is its user interface. However, that part of Access isn't very important for custom applications because you create your own user interface with Visual Basic. To help clarify, the next few sections describe some of the components of Access and how you can access them.

Jet Database Layer 2.5

The real power behind Microsoft Access (MSACCESS.EXE) is its database engine, Jet. Everything else in Access is basically a user interface for the Jet. As a Visual Basic programmer, this concept is very important to you because you also have control of the Jet Database Layer (also referred to as data access objects, or DAO). Consider the following scenarios:

• Suppose that your application needs the titles of all books in a database file created in Microsoft Access. Because Visual Basic has access to the Jet Database Layer, you can directly retrieve this information using Visual Basic code.
• Suppose that your application needs to create, modify, and compact database files created in Access. Again, Visual Basic has access to the Jet Database Layer, so you can perform these actions directly using Visual Basic code.
• Suppose that your application needs to perform actions on SQL Server or some other foreign database format. Because the Jet Database Layer supports many database formats (through ODBC), you can directly access these files using Visual Basic code.

These scenarios show some of the most common uses of Access, but your copy of VB.EXE already has much of the power of MSACCESS.EXE to perform these tasks. In most cases, if you really need the Access interface, you shouldn't use Visual Basic.

Communicating with Access through DDE

If you absolutely must communicate with Access directly, you can perform a wealth of operations by using DDE. To connect to an application through DDE, your application must know an application (or service) name, a LinkTopic, and a LinkItem. Access always uses the MSAccess application name. Table 25.3 lists the available topics and items.

Table 25.3 DDE Methods for Microsoft Access 2.0

In addition to the previously mentioned DDE methods, Access also can receive the following commands through LinkExecute:

• The name of any macro in an open database
• [DoCmd [<same as Access Basic>]]
• [OpenDatabase <database filename>]
• [CloseDatabase]

Using DDE with Microsoft Access 2.0

To demonstrate the power of DDE with Access, this section presents a small application (shown in fig. 25.6) that performs many of the DDE methods previously described.

Fig. 25.6

An example of a DDE tester for Microsoft Access 2.0.

This program is very helpful for testing DDE commands to ensure that they work with Access, before you write a single line of implementation code. Listing 25.2 shows this handy utility’s code, which demonstrates how to implement many of the DDE methods in VB.

Listing 25.2 FRMODE.FRM, an Access 2.0 DDE Test Application

This sample demonstrates how easily you can communicate with Access 2.0 through DDE. After this book is published, you might find yourself purchasing a copy of Access 7.0. Although the preceding technique should work with Access 7.0, you should consider using OLE Automation instead. For more information on using OLE Automation, see the next section and Chapter 26, “Integration with Microsoft Excel 5.0.”

Integration with Microsoft Word 6.0

Microsoft Word for Windows 6.0 includes a vast amount of functionality that would take a tremendous amount of time to rewrite in Visual Basic. Fortunately, with the release of OLE 2.0 Automation, you can tap into Word's power with relative ease. This section explains how you can make Word work for you.

DDE, the Origin of Communication with Word

Traditionally, if you wanted to tap the power of Microsoft Word, you either wrote a custom application in WordBasic or you used DDE. DDE links are easily broken, so most developers were reluctant to use the power of Word. This problem forced many programmers to rewrite Word's features in Visual Basic, or they simply purchased custom controls with similar functionality.

Today, programmers no longer need to use DDE with Word because Visual Basic 4.0 can access Word with OLE Automation. For this reason, this book doesn’t discuss this dated technology in detail.

You should avoid using DDE, because its future is uncertain. Currently, almost every application that uses DDE is being rewritten to support OLE Automation instead. Therefore, you should use DDE only as a last resort.

OLE Automation with Microsoft Word 6.0

In late 1993, Microsoft released Word for Windows 6.0 with the entire WordBasic engine exposed as an OLE Automation object. Therefore, Visual Basic programmers can now depend on stable connections with Word, and the barriers of the past had been removed. The Speller program shown in figure 25.7 demonstrates the power of this new feature. Listing 25.3 shows the program’s code.

Fig. 25.7

Speller demonstrates the power of OLE Automation with Word.

Listing 25.3 OLE Automation with Word Can Save You Hours of Work

Unfortunately, not all WordBasic features are available from Visual Basic. For a complete listing of the unsupported features, see page 167 of Programming Integrated Solutions with Microsoft Office (Microsoft Press, 1993).

OLE Automation requires that your application, the server application, and the OLE 2.0 dynamic link libraries (DLL) be loaded. Consequently, OLE Automation applications consume a great deal of random-access memory (RAM) and system resources. You should prepare your program for low-memory situations, which could prevent the program from running under 16-bit Windows. Under 32-bit Windows, low-memory situations rarely occur, but you should still trap for them.

The SpellCheck function provides an easy way to spell check text in your application with only a few lines of code, but this is only the beginning. You can find a listing of all the methods that WordBasic supports in the “Microsoft Word Object” chapter of the ODK's Programming Integrated Solutions book.

To use WordBasic's named arguments in OLE Automation, you must use Visual Basic 4.0 or VBA. Earlier versions of Visual Basic do not support named arguments.

Tips for OLE Automation with Word

When you use OLE Automation with Word 6.0, you should remember the following tips:

• Creating an OLE Automation object variable with Word automatically launches Word and makes it visible to your user. This might distract or confuse the user, so be prepared to handle this problem. However, this behavior does not exist in Word 7.0.
• If creating your object variable (for example, Word in the Speller example) starts Word, Word closes when that variable loses scope. You then lose any unsaved changes because OLE Automation does not cause Word to mark documents as dirty.
• When you use CreateObject with it, Word 6.0, unlike other applications, does not launch a new instance. Therefore, you can use CreateObject without determining whether Word is already running.
• If you start Word with OLE Automation, no documents are open on startup. Therefore, your application must always use .FileNew or .FileOpen before performing any actions.
• If multiple instances of Word are running, Word uses the most recently run instance that is visible.

Using Word with Custom Controls

By now you should be quite familiar with the reasons that custom controls can be helpful, but with OLE's in-place activation, such controls are an absolute necessity. The tremendous amount of work has gone into making in-place activation usable by Visual Basic programmers, so you certainly want take advantage of it. In VB 4.0, you can support two types of in-place activation for Word:

• Using Word directly as an OLE custom control
• Using the OLE container control

Each of these methods has advantages and disadvantages. The next few sections demonstrate the power of each tool so that you can decide which is right for you.

Word as an OLE Custom Control

One of the most exciting new features of Visual Basic 4.0 is the capability to use OLE objects as controls on your forms. This capability enables you to use any OLE server as a control in your application. Because Word is such a powerful OLE server, you will certainly want to use this capability.

To experience the simplicity of this powerful feature, just follow these 10 steps:

1. If it is not already running, start Visual Basic and create a new project.
2. From choose Tools, Custom Controls.
3. In the list of available controls in the Custom Controls dialog box (shown in fig. 25.8), click on the Microsoft Word 6.0 Document check box.

Fig. 25.8

Using the Custom Controls dialog box, you can add any OLE object.

4. Click OK and Visual Basic adds the Word control to your Toolbox. Your Toolbox should now look similar to that shown in figure 25.9

Fig. 25.9

The Toolbox with a Word 6.0 document custom control.

5. Click the Word control, then click the upper-left corner of Form1. Drag your mouse to the lower-right corner and release the mouse button.
6. Click the bottom border of your form and increase the height of the form by about half an inch.
7. Add a new command button to the form and resize it as it is shown in figure 25.10.

Fig. 25.10

This application demonstrates the power of in-place activating OLE custom controls.

8. With Form1 still active, choose Tools, Menu Editor.
9. From the Menu Editor, add an invisible top-level menu item called "x" (or whatever else you want). Figure 25.11 shows the properties for this menu item.

Fig. 25.11

The Menu Editor with an invisible menu entry.

10. Run the application and double-click on the Word object. Your application should look similar to that shown in figure 25.10.

After you activate an OLE object in place, you can deactivate (or close) it only after it loses the focus. The only purpose of the codeless command button in figure 25.10 is to provide an easy way to change the focus.

The form in which you place the control must have either a visible or invisible menu. If it does not, the activated object cannot display its menu in your application.

In addition to the functionality shown earlier, the Word document control supports the following properties and events:

• DragDrop event

• DragIcon property

• DragMode property

• DragOver event

• GotFocus event

• Height property

• HelpContextID property

• Index property

• Left property

• LostFocus event

• Name property

• TabIndex property

• TabStop property

• Tag propery

• Top property

• Visible property

• WhatsThisHelpID property

• Width property

As you can see, the Word document control (and any other OLE controls) support a wealth of events and properties. In some cases, this method possesses all the functionality that you need.

If you plan to use only Word in your application, the Word document control is a great choice. However, if your users need to insert objects at run time, consider using the OLE container control.

Your application fails if the destination system does not have Microsoft Word 6.0, or if Word is not registered in the system registry properly.

OLE Container Control with Microsoft Word 6.0

One of the major enhancements for Visual Basic 3.0 was its support for OLE 2.0. Unfortunately, because no major OLE 2.0 applications existed at that time, Visual Basic 3.0’s OLE container control shipped with several limitations.

Now that people are starting to use OLE 2.0, Microsoft has released a more advanced version of this control. Some of the benefits of this updated control are the following:

• Many new events, methods, and properties, which provide an enormous amount of flexibility and control over the object
• The capability to print the contents of an embedded OLE object at design time
• Data-binding with OLE fields in your Access database

The container control gives you the power to create the program shown in figure 25.10, and you also have more control of your inserted object. Figure 25.12 and listing 25.4 shows a sample application that adds a full-featured word processor to your application with very little code (except for comments).

Fig. 25.12

OLECONT.VBP demonstrates in-place activation.

The first file in the OLECONT.VBP is PUBLIC.BAS. It keeps track of all the Public (or Global) constants, variables, and functions for this project. Because this application uses a multiple document interface, you have to store your variables that keep track of the open forms in a module that all the project’s forms can access. This module also contains a public function, UpdateStatus, that gives any procedure in the project the capability to update the status bar.

Listing 25.4 OLECONT.VBP, an OLE Container Control with Word Embedded

The second file in this project is the MDI parent form, mdiOLE. This form is responsible for controlling the status bar, toolbar, and its menu.

The application stores the toolbar buttons in a image control array, imgTools. You set the picture’s property and the control’s position in the array at design time. The odd controls in the array contain the up picture, and the even (odd number + 1) controls contain the down picture. The imgHold image control is a temporary location to store the toolbar picture when the user clicks a toolbar button.

When an odd-numbered image control receives a Mouse_Down event, the application stores its image in a holding image control, imgHold. Next, the application sets the imgTools(Index) picture property to the picture of the next control in the array (index + 1), which should be its down picture. Finally, when the control receives a Mouse_Up event, the application restores the control’s up picture by setting imgTools(Index) to the picture currently stored in imgHold. The following code shows how to accomplish this:

Because this toolbar has only one tool, you place its code in the Mouse_Down event. If this toolbar had more than one button, you would place the action code for the imgTools control array in a large Select statement in the Click event.

Every time that the user moves the mouse over a toolbar button, you should update the status bar to reflect the action that the tool performs. Listing 25.5 demonstrates how to do this.

Listing 25.5 Updating the Status Bar

As listing 25.6 demonstrates, the MDIForm_Load procedure maximizes the window and tiles all open child windows, and the MouseMove procedures set the caption of lblStatus equal to "Ready" whenever the user moves the mouse over the MDI form.

Listing 25.6 Preparing OLECONT.VBP for Use

To make this code application-independent, you create separate procedures for Highlight and HighlightBar. You can then use these procedures in this project, but also copy and paste them into another project.

Listing 25.7 adds a three-dimensional appearance to the status bar and the toolbar.

Listing 25.7 Adding a Three-Dimensional Appearance to the Status Bar and Toolbar

Listing 25.8 shows two functions that demonstate how to use a series of line methods to create a three-dimensional effect around controls.

Listing 25.8 Adding a Three-Dimensional Appearance to Controls

Listing 25.9 shows FRMWORD.FRM, an MCI child form with an OLE container control.

As this application demonstrates, the power of the OLE container control can yield some amazing results. In addition, you can spare yourself from from hundreds of hours of coding by using this powerful feature.

From Here...

This chapter demonstrated how powerful Visual Basic can be when you integrate Office applications, so now it is time to explore more ways to use Microsoft Office products in your programs. The next chapter looks in detail at the most powerful Office product, Microsoft Excel, and demonstrates how easily you can use a VBA-aware application such as Excel with Visual Basic 4.0 to create world-class applications.

To learn more about related topics, see the following sections:

• Browse your copy of the Microsoft Developers Network Starter Kit CD. This compact disk is similar to the contains much great information on OLE and more. It also includes excellent tips and workarounds for Visual Basic in the KnowledgeBase articles.
• The Guide to Data Access Objects in the Professional Features Book 1 is a source of information about data access. This document also includes many helpful example programs.
• The Access Software Development Kit (available separately from Microsoft) is essential if you intend to write any serious applications with Access Basic.
• The WordBasic Developer's Kit (available from Microsoft Press) is a great source for understanding WordBasic. If you need to know what a particular WordBasic command does, this book is a must.