Working with ActiveX

After you add the controls, you know all about setting properties, responding to events, and triggering methods. Some ActiveX controls let you go a step farther, however. You can actually use an embedded control inside your application and borrow that control's functionality for your own application's use.

Overview

In Hour 22, "Adding OLE to a Program," you learned some about OLE but were told that ActiveX replaced OLE. You also learned that OLE, in its truest form, exists only in Visual Basic's Standard Edition because other than a few drop-in controls, ActiveX has little support in Visual Basic 5. Although ActiveX is a replacement for OLE, programmers still use OLE terminology when describing such topics as Automation.

When your application uses Automation, your application processes another application's data and manipulates that data with the help of the other application. Users will have no idea that the other application has started, helped, and then gone away. For example, you can open Excel, load a worksheet, manipulate the worksheet data with Excel-based commands, close Excel, and embed the resulting worksheet in your application's Form window without users ever knowing that you borrowed Excel's capabilities.

This topic section is comprised primarily of a long example because you'll best grasp Automation when you see it in action. You're somewhat limited to using Automation only for ActiveX applications registered in your system Registry. Generally, if you use an application that's ActiveX-aware, that application registered its Automation availability in your system Registry when you installed the application.

Don't expect to master Automation in a single topic section. Automation requires extensive knowledge of the other application's object hierarchy, and such a hierarchy can be complex. The following example demonstrates Automation with an ActiveX document more than it teaches Automation due to Automation's requirement that you be well versed in the borrowed application's internals. This course can't get into the specifics of other applications. Fortunately, most Automation concepts overlap applications, so the example's concepts you see here carry over to other applications in many ways.

Example

You'll use Visual Basic's Object data type to create a variable that references the Automation application. First, you must define an application object like this:

Dim obExcelApp As Object

You must connect the application object variable to the application. If the application isn't running now, you must start the application in the background with the CreateObject() function. CreateObject() not only starts the application, but also connects your object variable to the application like this:

Set obExcelApp = CreateObject("Excel.Application")

You'll substitute the application's name in place of the CreateObject() function's argument.

Be sure to use Set and not a simple assignment. A variable can't hold an outside application, so you use Set to create a reference variable to the outside object.

A problem can occur if the application is already running. As a multitasking, multiuser operating system, Excel can have more than one copy of itself running at the same time on the same computer. Therefore, you can use the GetObject() function in place of CreateObject() if the application is running like this:

Set obExcelApp = GetObject(, "Excel.Application")

Notice the comma at the beginning of the argument list. You omit the first argument in most cases. You can check for failure with the following If:

If obExcelApp Is Nothing Then

  ' You must use CreateObject()...

Automation application object variables are an exception to the rule of local variables. The application is truly outside your application, so you could safely use a global object variable so that your procedures don't have to pass the application variable around.

When you open another application and use Automation, your application must intimately understand the other application's interface. In a way, your application is the user of the other application. Therefore, when you open the Excel application, you interact with Excel by using the normal row and column notation, except that you have to use some object property notation specific to Excel.

Now you must declare a worksheet object so that the application can generate data:

Dim obWorkSheet As Object  ' Worksheet object

The following code adds data to some worksheet cells:

' Enter values in cells

obWorkSheet.Cells(1, 1).Value = "Sales"

obWorkSheet.Cells(1, 2).Value = "Month"

obWorkSheet.Cells(2, 1).Value = 21913.44

obWorkSheet.Cells(2, 2).Value = "April"