VBA: Component Glue for Applications

by Neil Charney

Did the Internet take you entirely by surprise? If so, you may want help predicting the "Next Big Thing." Here is the future in four words: Visual Basic, Applications Edition (VBA). It will soon be available in third-party applications that license VBA, such as Visio, Photoshop, and Autodesk, which means more components will be accessible to the millions of Visual Basic and Visual Basic for Applications developers. Add Visual Basic Scripting Edition (see "VBScript: It’s How Visual Basic Programmers Get Webbed," page XX) to the list and you realize that Visual Basic is everywhere. If you already develop in Visual Basic or Microsoft Office then you already know a lot about VBA (and VBScript).

VBA for the Visual Basic developer

Rather than working with forms and controls, as in Visual Basic, VBA works within the context of an application. Applications supporting VBA "expose" objects or functionality, such as a chart or pivot table in Microsoft Excel, a form in Microsoft Access, or a document in Microsoft Word. There are three basic scenarios in which VBA is used with applications: customizing a single application by setting methods and properties of its objects, integrating third-party components (ActiveX controls) into a custom solution, and integrating objects from multiple applications into a single, seamless solution.

Developing within the application

Probably the most common VBA scenario involves programming multiple? objects within a single application. For example, an accounting department might want a custom application to integrate multiple spreadsheets into a consolidated online report, with charts and pivot tables. Using VBA, you can access each object within Microsoft Excel and provide a friendly and familiar interface to walk the user through the task of creating the report. In the simple example below, VBA code is written within Microsoft Excel to control the Excel Chart object.

    Sub Create_Chart()
    Dim objXLchart As Chart
    Dim MyArray As Variant
    MyArray = Array(1, 2, 3)

    With Excel.Application
    .Workbooks.Add
    .Range("A1:C1").Value = MyArray
    .Range("A1:C1").Select
    'Create a Chart and make it visible
    Set objXLchart = .Charts.Add()
    .Visible = True
    End With
    End Sub

VBA was designed for working with objects. Note the use of the WITH statement to set the properties for the Microsoft Excel application. By using it, the code is easier to type, easier to read, and even executes faster.

Developing within the application with ActiveX

As with Visual Basic, VBA allows you to add pre-built software components or ActiveX controls (formerly called OLE controls) and relieves you of having to write them from scratch. For example, in Microsoft Access, you may wish to present the user with a calendar for selecting dates. Rather than creating one, which you could do with a lot of code, you can insert the Microsoft Access Calendar control, and work with it just as if it were another exposed object in the application. This example uses the Calendar custom control to provide a simple interface for selecting a date, which is then displayed to the user in a message box:

    ‘Present the chosen date in a message box
    Private Sub Cal1_Updated(Code As Integer)
    Dim ChosenDate As Date
    ‘name of the calander control is "Cal1"
    ChosenDate = Me.Controls("Cal1").Value
    MsgBox "You Chose " & ChosenDate
    End Sub

While it is a simple example, it shows you the ease with which you can integrate a custom control into your application. Both Microsoft Access and Visual Basic now support ActiveX controls. All applications in the next release of Microsoft Office will have ActiveX support, both on the application documents, and on the new forms that VBA will provide.

Developing across multiple applications

One of the most powerful features of VBA is its ability to integrate objects from multiple applications into a single solution. One approach to combining application functionality is through automation of embedded objects. For example, you might determine that the most efficient way to create a sales reporting system is a tool that combines a Microsoft Access form object with a Microsoft Excel chart object. However, the form and chart objects exist in the two respective applications. By using the Microsoft Access Form object as an OLE container, you can embed Microsoft Excel chart and worksheet objects to create a single solution—and the user may never realize that the objects are coming from different Office applications. You might also wish to include the Calendar control to make the interface even more user-friendly (see Figure 1).

Figure 1: The Calendar control

In this example, a Microsoft Access form contains an embedded Microsoft Excel worksheet and chart as well as the Calendar custom control. You could activate the embedded objects and then control them in place through automation. Automation also allows you to create a new "instance" of an application and then control it using the same VBA code you would use within the application itself. Confusing? Not really. Taking our first example where we created a Microsoft Excel chart, we could paste that code—with a few minor changes—into Microsoft Access and achieve the same results:

    Sub Create_Chart()
    'Change 1 - Add a line to create a variable for Excel
    Dim objXL As Excel.Application
    Dim objXLchart As Chart
    Dim MyArray As Variant
    MyArray = Array(1, 2, 3)
    'Change 2 - add a line to create an instance of Excel
    Set objXL = CreateObject("Excel.Application")
    With objXL
    .Workbooks.Add
    .Range("A1:C1")    .Value = MyArray
    .Range("A1:C1").SELECT
    'Create a Chart and make it visible
    Set objXLchart = .Charts.Add()
    .Visible = True
    End With
    End Sub
This is a powerful feature that allows you to work from within one central application while accessing the functionality of any other application that supports VBA. With third-party products incorporating VBA into their applications, the number of components that can be integrated into a Microsoft Office solution increases significantly. Keeping up with the latest on Visual Basic, Applications Edition ensures that you’ll be ready for the "Next Big Thing."

More information online

More information on Visual Basic for Applications is available on the Office Developers Forum at:\
http://www.microsoft.com/officedev/.

Neil Charney is a product manager in the Internet Platform and Tools Division and a strong supporter of Office developers everywhere.

VBA allows you to add pre-built software components or ActiveX controls and relieves you of having to write them from scratch.
One of the most powerful features of VBA is its ability to integrate objects from multiple applications into a single solution.