External Components

By Ken Getz

One of the more powerful aspects of writing applications in VBA is that you can easily reference and use external components. That is, you have a world of tools available to you, and all you must do to take advantage of them is set a reference to the component within your application, figure out the object model for the component, and write the code.

Think about it. If you’re writing applications in VBA, you’re using external components all the time — even if you’re not aware of it. For example, when you write code that uses ADO or DAO, you’re taking advantage of an external component written by a team of programmers. When you add an ActiveX control to a form, you’re using an external component containing code you don’t have to write yourself.

This magic is available because of the plumbing provided by COM (Component Object Model), which is part of Windows. COM is a huge topic, and writing COM components the hard way (in C++) would require at least a week-long intensive course. Writing these components in Visual Basic (VB), on the other hand, is easy. This three-part series describes how you can take advantage of this functionality. In this article, we’ll discuss basic concepts and issues, create an ActiveX component in VB, and use it from within another VBA host. (Note: This series is an introduction to the issues involved in creating ActiveX components in VB that can be called from VBA applications. The VB online help provides detailed coverage, and there are many books on this topic. Work through the examples provided here, then dig in deeper using other resources.)

All the functionality provided in external COM objects is exposed as methods, properties, and events of class modules; that is, the original programmer created a class module for each object to be exposed, and added normal property procedures (or something like them, depending on the language) to provide properties. Just as you might instantiate an internal class module, say a class named SystemInfo, with code like this:

Dim si As SystemInfo
Set si = New SystemInfo
' Now you can use properties and methods of si, 
' the SystemInfo object.
...
' Release the reference to the object when you're done.
Set si = Nothing

you do the same thing with external objects. For example, to use the Connection object provided by ADO, you might write code like this:

Dim cnn As ADODB.Connection
Set cnn = New ADODB.Connection
' Now you can use properties and methods of cnn.
...
' Release the reference to the object when you're done.
Set cnn = Nothing

It’s essentially the same as using a built-in component. Before you can use an external COM component (often referred to as an ActiveX component), you need to tell your VBA project about it. In this example, you’ll take a simple look at the FileSystemObject provided by the Microsoft Scripting Runtime library. In this case, the external component’s name is ADODB, and the specific object you’re interested in is the Connection object. You can think of this as a class module named Connection that some developer created so you wouldn’t have to.

When you write VBA code, you don’t have to worry about VBA “understanding” references to built-in objects, such as Form and Application. When you write code that uses external objects, however, VBA does not, by default, know anything about the application you’re trying to control. Any object, property, or method references aren’t validated until you try to run your code.

You can, however, tell VBA about those applications using what’s known as a type library. You can think of a type library as a small database that contains all the objects, properties, methods, and constants that another application implements. Most ActiveX components ship with a type library file, which usually has a .TLB or .OLB (for object library) file extension. Some components, such as those you create with VB, have their type library built into the executable or DLL file. You “tell” VBA about the server application by setting a reference to the server’s type library. If you want to investigate the objects provided by the type library, the Object Browser is a great place to look.

Suppose you need to use the Drives collection of the FileSystemObject object provided by the Microsoft Scripting Runtime library. This object allows you to retrieve a collection of Drive objects, each of which provides information about a drive attached to your computer. To tell your project that you’d like to use the FileSystemObject object, you must set a reference to the Microsoft Scripting Runtime library:

With your project open, select the Tools | References menu item. Scroll the list box until you find Microsoft Scripting Runtime, and click its check box to select it (see FIGURE 1).

FIGURE 1: Adding a reference to the Microsoft Scripting Runtime library.

Click the OK button to close the dialog box.

Open the Object Browser (2) and select Scripting from the list of libraries.

Find the Drive object from Classes list, and peruse its members. There are several other interesting objects, but you’ll be focusing on the Drive and Drives objects (see FIGURE 2).

FIGURE 2: Browsing the Microsoft Scripting Runtime library’s Drive object.

Because VBA won’t create an instance of the FileSystemObject itself, you can’t use it without writing a bit of code to load an instance of the object into memory. In the sample XLS file, you’ll find a sample form named frmFileSystem that does this work for you. (The sample XLS file is available for download; see the end of this article for details.) In its Declarations area, it includes an object variable, declared like this:

Private fso As FileSystemObject

This declaration doesn’t create a FileSystemObject object; it simply declares a variable that could refer to such an object. To instantiate an object, you must use the New keyword. This keyword tells VBA to create a new instance of the object in memory, and return a reference to that object. This code, from frmSystemObject’s Load event procedure, creates the object for you:

Set fso = New FileSystemObject

Once you’ve got the FileSystemObject available in memory, you can use its objects, properties, and methods. The following code from frmFileSystem’s Load event procedure fills a list with all the available drives:

Dim drv As Drive
For Each drv In fso.Drives
  lstDrives.AddItem drv.DriveLetter
Next drv

You’re better off listing the library and object name in all declarations, i.e. Scripting.FileSystemObject and Scripting.Drive are preferable to FileSystemObject and Drive. Doing this allows VBA to know exactly where to look for the object. Without that library name, VBA can’t tell where the object might be provided, and must search all available libraries. Supplying the library name speeds up the code, and makes it explicit which FileSystemObject object you’re referring to, if more than one library supplies a FileSystemObject object. The official term for supplying the library name is disambiguation of the reference.)

Once you click on any specific drive in the list of drives, code in the list box’s Click event fills all the labels on the form with information about the drive, using the properties of the Drive object. FIGURE 3 shows the form in use.

FIGURE 3: Sample form demonstrating the FileSystemObject object.

You can create COM (or ActiveX) components with, obviously, any tool that supports the task, including Microsoft Visual C++, Borland Delphi, Microsoft FoxPro, Microsoft Visual J++, and VB. Because you’re already developing applications in VBA, you have 98 percent of the tools you need to create your own components. That is, you won’t have to learn language skills to create COM components. In brief, if you know how to create and use VBA class modules, you know how to create COM components with VB. It’s that simple.

Why create an external component? You might want to create an external, compiled component if, among other reasons:

you have code you’d like to reuse in several VBA applications, but don’t want to have multiple copies of the code in the various projects.

you want to protect some of your code, but allow users to modify the code for the bulk of your application.

you want to centralize processing of data, business rules, or other shared tasks, so applications written in various environments can share the same code.

If you’re a VBA developer, the best tool to use to create these components is, of course, VB. If other VBA hosts allowed you to create compiled ActiveX DLLs or EXEs, you wouldn’t generally need VB — but they don’t. Note: You can create compiled COM and VBE add-ins if you have Microsoft Office 2000 Developer Edition (the subject of Thomas Rizzo’s article, beginning on page XX of this issue), but generally, this technology is too limited to support creating any of the various types of ActiveX components you might need, so it won’t replace the need to own and use VB.

To create any ActiveX code component, your development must rely on class modules; the only way to expose any functionality from a code component is as a method, property, or event of an object created from the component. The only way to create objects in VB is, of course, to create a class module. Although your ActiveX component project can contain other items, it’s the class modules that will be available outside the project.

For any object you’d like to make publicly available, you’ll need to create a class module. For each property, method, and event for that object, you’ll need to create the appropriate code in the class module. If you look carefully at the Object Browser, you’ll notice the left-hand list box is labeled Classes. This is because each object exposed by an ActiveX component is a class module of encapsulated functionality.

When you write an application in VB, you add some file requirements to your application distribution. Not only must you distribute the ActiveX component you’ve created, but you must also distribute the VB run-time DLL: Msvbvm60.DLL for VB6, or Msvbvm50.DLL for VB5. This 1.5MB DLL must be available on every user’s machine, or your component cannot work. Only one copy per machine is required, however.

To get this DLL to each user, your best bet is to use some sort of tool that creates distribution packages. VB ships with its own Package and Deployment wizard (called the Setup wizard in VB5) that you can use to package your component for distribution. There are third-party tools you can use as well. In any case, the component, the VB support files, and any other files you need (text files, database files, and so on) must be copied to users’ machines.

Note: No matter what you’ve heard, no matter what you’d like to believe, compiling to a native executable doesn’t change the distribution requirements of a VB application; that is, it doesn’t create one mammoth file that you can distribute to your users. The only difference compiling to native code makes is that processor-intensive code will run faster (generally about as fast as code written in C), and your executable will be a bit larger. You still must distribute the VB run-time DLLs.

Let’s say you need to find the current user from several applications. Sure, you could include the required code in each application, but wouldn’t it be better to call one, common component instead? That way, the code lives in a single location, and you only worry about the details of obtaining the user name once, when you create the component. You’ll have achieved programmer’s nirvana: code written once, and used anywhere.

To find the user name, you must use a simple API function: GetUserName. To expose this information from an ActiveX component, it must be a property or method of an object. To get started, follow these steps:

Start VB5 or VB6, and from the initial dialog box, select ActiveX DLL (you could also create an ActiveX EXE, and a later section will discuss the differences between the two; selecting DLL makes it easier to debug the component from within VB, if you want to do that). FIGURE 4 shows the initial dialog box in VB6. (If you’re already in VB, you can select the File | New Project menu item instead, to get started.)

FIGURE 4: Creating an ActiveX DLL at the VB6 New Project dialog box.

When you create an ActiveX component (DLL or EXE) project, VB automatically creates a class module for you. The project and class names are important to you as a component developer, so set them both now.

To set the project name, select the project (Project1) in the Project window, find the Properties window (press 4 to give the Properties window the focus), and set the Name property to VBDemo.

To set the class name, select the class (Class1) in the Project window, find the Properties window, and set the Name property for the class to Info.

In the Properties window, make sure the Instancing property is set to 5 - MultiUse (more on this later).

Double-click on the Info class in the Project window (to load the class module for editing), and enter the code shown in FIGURE 5. If you don’t understand the code, don’t worry; you might want to dig into using the Windows API to gather system information like this, but it’s not crucial for understanding ActiveX components. The important issue is that UserName is a property of the Info class within a project named VBDemo.

Private Declare Function GetUserName Lib "Advapi32.dll" _
  Alias "GetUserNameA" (ByVal lpBuffer As String, _
                        nSize As Long) As Long

Public Property Get UserName() As String
  Dim lngLen As Long
  Dim strBuffer As String
  Const conUNLEN = 256
    
  strBuffer = Space(conUNLEN + 1)
  lngLen = conUNLEN
  If GetUserName(strBuffer, lngLen) <> 0 Then
    UserName = Left$(strBuffer, lngLen - 1)
  Else
    UserName = "
  End If
End Property

FIGURE 5: Describing a Windows API function to VB.

Select File | Save Project and save the project file (VBDemo.vbp), and class module (Info.cls). It’s best if you create a new folder in which to place these source files.

Select File | Make VBDemo.dll to create the executable file. This not only compiles the code and creates the DLL file on disk, it also registers the component so you can use it in other applications.

Just to prove that everything’s working as it should, shut down VB. That way, when you use the VBDemo component in the next step, you’ll know for sure that you’re getting the functionality you see from a compiled DLL.

Start up a copy of Microsoft Excel (or any other VBA host with which you’re familiar). Open a new module.

Choose the Tools | References menu item, scroll down the list until you find VBDemo, and select it.

Back in your module, add the following code:

Sub Test()
  Dim vbi As VBDemo.Info
  Set vbi = New VBDemo.Info
  Debug.Print vbi.UserName
  Set vbi = Nothing
End Sub

From the Immediate window, run the Test procedure. You should be greeted with your user name.

You’ve done it! You’ve created an ActiveX component in VB, and used it from within another VBA host.

In this article, we covered some basic concepts, such as external objects as class modules, the role of type libraries, using the FileSystemObject, and distribution. Finally, we created an ActiveX component to be used in a VBA host.

We’ve only scratched the surface of external components. Next month, we’ll delve deeper into the subject, with discussions on debugging, compatibility, raising and sinking events, and in-process and out-of-process components.

Portions of this article are reproduced from Visual Basic 6.0 courseware written for Application Developers Training Company (http://www.appdev.com) by Ken Getz. The author would like to thank Brian Randell, Paul Sheriff, and Michael Kaplan for their contributions to the examples used in this article.

Ken Getz, a senior consultant with MCW Technologies, splits his time between programming, writing, and training. Ken is co-author of several books for developers, including Access 97 Developer’s Handbook [SYBEX, 1997] and VBA Developer’s Handbook [SYBEX, 1997]. He also co-wrote the training materials and travels around the United States teaching Access 97 and VB5 for Application Developers Training Company. Ken is currently at work on Access 2000 Developer’s Handbook and Visual Basic Language Developer’s Handbook, both from SYBEX. In addition, Ken is a Contributing Editor for Microsoft Office & Visual Basic for Applications Developer magazine.