1.4. Object Models: The Power of Programming with VBA

VBA is a single language, although when comparing code taken from a VBA program written for Word with one written for Access or Visual Basic, you could be forgiven for thinking you are reading code from two very different languages. This is because VBA interfaces with an application's object model, and much of the time the code you write references objects that are unique to the host application. To demonstrate this, in the VBA code fragments shown in Examples Example 1.1 through Example 1.4, generic VBA code is shown in a normal typeface, object code that is unique to the application is shown in bold, and variables are shown in italics.

Example 1.1. A Code Snippet from an Excel VBA Program

For Each c In Worksheets("Sheet1").Range("C4:C17").Cells 
   If c.Value  = iCond  Then
      tempTot  = tempTot 
						 + c.Offset(0, 1).Value 
   End If
Next c

Example 1.2. A Code Snippet from a Word VBA Program

Set myRange = ActiveDocument.Range( _
						Start:=ActiveDocument.Paragraphs(2).Range.Start, _
						              End:=ActiveDocument.Paragraphs(2).Range.End)
						myRange.Select
						myRange.Bold = True

Example 1.3. A Code Snippet from an Access VBA Program

Form_Form1.RecordSource = "SELECT Products.ProductCode, " _ 
						  & " Products.BinLocation, Descriptions.Description" _ 
						  & " FROM Products INNER JOIN Descriptions " _ 
						  & " ON Products.ProductCode = Descriptions.ProductCode" _ 
						  & " WHERE (((Descriptions.Language)="  
						  & iLangCode & "));" 
						Text0.ControlSource = "ProductCode" 
						Text2.ControlSource = "Descriptions.Description" 
						Text4.ControlSource = "BinLocation" 

Example 1.4. A Code Snippet from a Visual Basic Program

Dim oADOComm      As ADODB.Command 
Dim oADORecs      As ADODB.Recordset 
Dim sSQL          As String
    
Set oADOComm  = New ADODB.Command 
						oADOComm.ActiveConnection  = "LiveDSN" 
						   sSQL = "SELECT * FROM employees" 
						oADOComm.CommandType  = adCmdText 
						oADOComm.CommandText  = sSQL 
        
   Set oADORecs  = oADOComm.Execute 
      If Not oADORecs.EOF  And Not oADORecs.BOF  Then
         Do While Not oADORecs.EOF 
						cboEmployeeNames.AddItem  _
                             oADORecs.Fields ("Name") .Value 
						oADORecs.MoveNext 
         Loop
      End If
   Set oADORecs  = Nothing
Set oADOComm  = Nothing

As you can see from these examples, the referenced object model plays an integral role in the creation of VBA-based programs. The object model describes the application and the features you can control. You then use VBA to access and change properties of the object model, handle events fired by objects in the model, and call the methods of the objects.

Once you're familiar with one object model, you will find great reductions in the time it takes to learn another object model. For example, about 50% of the Office 97 object models are shared. In short, if you can create applications using one object model, you can move to another host and develop custom applications there too, with a minimum amount of time spent learning the new object model.

Through the object model, the software vendor allows you to control the application, to set and retrieve properties, and to invoke methods. It's up to the software vendor to decide how much or how little of the application you have access to via its object model, and in the case of the Microsoft Office applications, 100% of their functionality is presumably exposed via the various object models. In addition to the object model, each host application has its own set of predefined (intrinsic) constants to speed development and make code easier to read and maintain.

So although they are conceptually distinct from the VBA language itself, object models are central to programming in VBA both within a host application, and—although to a lesser extent—in Visual Basic. The extensive reliance on individual object models, though, doesn't diminish the significance of VBA, even if it is only the "glue" that holds together a program's use of an object model (or, in the case of Visual Basic, a program's use of ActiveX controls).

As we've already seen throughout this chapter, the VBA language is the single most important development language in the business world and will continue to be so for some time to come. Becoming proficient in the VBA language is therefore important to many millions of professional developers across the world, because no other language presents the developer with so many opportunities—not just a promise but a real demand for skills right now. However, VBA is by its very nature a large language in terms of the number of functions, statements, and constructs that must be mastered. Once the language is mastered, the rest of the jigsaw falls into place, and you can easily and quickly move from one VBA-enabled development environment to another.