O'Reilly Media | VB & VBA in a Nutshell: The Language

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

To applications developers, end users, corporate buyers, and software vendors, Visual Basic for Applications (VBA) is becoming an increasingly important language. But VBA is more than just another software language; VBA is a unique conceptual method of creating professional business-oriented solutions.

VBA is the same language whether you are using it to create a Visual Basic application or to automate some task within Word or Excel. When you fire up your copy of the retail version of Visual Basic, the vast majority of language elements you use actually come from VBAx.DLL, the VBA library. Just look in the Object Browser to see how dependent on VBA Visual Basic actually is. Consequently, this book concentrates on the core VBA language regardless of its context.

Visual Basic for Applications is a hosted language and part of the Visual Basic family of development tools. Although VBA can be thought of as sitting below the retail version of VB and above VBScript in the VB hierarchy, VBA is actually an essential element of the retail version of VB, providing the vast majority of language elements used in VB. When hosted in VB, VBA provides language support and an interface for forms, controls, objects, modules, and data-access technologies. When hosted in other applications such as Word or Excel, VBA, using a technology called automation , provides the means of interacting with and accessing the host application's object model, as well as the object models of other applications and components.

In order to customize complex applications such as Excel, Word, Access, and a growing number of other applications from Microsoft and other vendors, VBA allows the developer to provide solutions that take advantage of sophisticated components that have been tried and tested. VBA is a glue language: a language that interfaces with the various objects that make up an application via the host application's object model. VBA is the means by which applications can become extensible, and it's ActiveX (or OLE automation) that provides the interface between VBA and its host application. It's this support for OLE automation that makes VBA an outstanding tool for rapidly developing robust Windows applications.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Visual Basic for Applications is a hosted language and part of the Visual Basic family of development tools. Although VBA can be thought of as sitting below the retail version of VB and above VBScript in the VB hierarchy, VBA is actually an essential element of the retail version of VB, providing the vast majority of language elements used in VB. When hosted in VB, VBA provides language support and an interface for forms, controls, objects, modules, and data-access technologies. When hosted in other applications such as Word or Excel, VBA, using a technology called automation , provides the means of interacting with and accessing the host application's object model, as well as the object models of other applications and components.

In order to customize complex applications such as Excel, Word, Access, and a growing number of other applications from Microsoft and other vendors, VBA allows the developer to provide solutions that take advantage of sophisticated components that have been tried and tested. VBA is a glue language: a language that interfaces with the various objects that make up an application via the host application's object model. VBA is the means by which applications can become extensible, and it's ActiveX (or OLE automation) that provides the interface between VBA and its host application. It's this support for OLE automation that makes VBA an outstanding tool for rapidly developing robust Windows applications.

Until the launch of VBA 5.0 in early 1997, the language had no development environment; very much like VBScript today, VBA was simply a language interpreter. VBA 5.0 marked the start of an exciting new chapter for VBA; it now has its own integrated development and debugging environment running within the process space of the host application.

VBA itself becomes more object-oriented with each release, but the latest release (Version 6.0) adds relatively few functions and keywords to the VBA language. Instead, extra functionality has been incorporated into VB6 using new object models, and again it's the VBA language that allows you to integrate these object models into your application.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

The incredible popularity of Visual Basic shortly after its launch prompted Microsoft to wonder if a "cut down" version of the product could replace the many different macro languages lurking behind its range of business applications. Bill Gates talked for many years—since the days of DOS—of a universal batch language. This goal is now coming to fruition in the shape of VBA. However, as the following chronology shows, this goal wasn't achieved overnight:

1993—VBA launched with Microsoft Excel: VBA first saw the light of day as a replacement for the macro language in Microsoft Excel. Its power and sophistication in comparison to the original macro languages made it an instant success with those developers creating custom solutions with Excel.
1994—VBA included with Microsoft Project: Perhaps because Microsoft Project had to be customized in many situations to satisfy the wide and varied needs of project managers, Project was next on the list of applications to be VBA-enabled.
1995—Included with Microsoft Access, replacing Access Basic: Perhaps the biggest boost to VBA came when Access Basic (a subset of VBA written specifically for Access) was replaced with the full version of VBA. Many of today's VB programmers apprenticed on VBA in Access, cutting their teeth on custom applications using VBA and Access. Many Access developers have moved on to the full version of Visual Basic to create full three-tier client server applications.
1996—VBA becomes the language element of Visual Basic: 1996 saw the launch of Visual Basic 4.0, a massive leap forward and almost a totally different product from VB3. Written in C++, Version 4 was a ground-up rewrite of VB, whose previous versions were written in assembler. With VB4, VB became object-oriented; VB could be used to create class models and DLLs, as well as to easily reference external object libraries. Part of the componentization of Visual Basic was the use of a separate language library, VBA. Some intrinsic language elements remained in the VB and VB runtime libraries for backward compatibility, but most were transferred to the VBA library, and many were completely rewritten.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

VBA contains all the functions and statements necessary to create robust Windows applications, whether this is done using Visual Basic or a host application. The tasks you can perform with VBA include (but are not limited to):

Creating instances of OLE (ActiveX) objects within your code
Creating classes (reusable custom software objects)
Linking to ODBC databases like Access and SQL Server
Integrating with the messaging API (MAPI) to create Exchange/Mail applications
Integrating with Internet and intranet solutions
Creating custom dialog boxes and forms
Storing and retrieving data from the Windows registry
Detecting and handling errors
Incorporating ActiveX controls into the application interface
Passing data between VBA-enabled applications with a minimum of programming and fuss
Driving a second VBA-enabled application from within a first VBA-enabled application
Controlling the Office applications; in theory, 100% of the functionality of Office products is exposed as objects/properties/methods, which means that, with occasional exceptions, there isn't anything you can't do programmatically that you can do from the application's interface.
Automating anything that can be done from the keyboard, mouse, or menus

There is also one thing you can't do directly with the VBA language: you can't output to a printer. So how do you print from a VBA application? When hosted in an application, VBA can control the application's own printing functionality; when used within VB, VBA can control the VB Printer object.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

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 through , 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.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

In its simplest form, Visual Basic for Applications is a glue language. This means that as a VB or VBA developer, you concentrate on the interface of and interaction between the objects and controls within the application, gluing the various elements of the application together by writing procedures to perform programmatic tasks and by adding code to handle events. Visual Basic programs are primarily event-driven. Some event or other—such as the user clicking a button— triggers most of the procedures you will write.

From a developer's point of view, one of the most important characteristics of an event-driven application is that, for the most part, the various elements of the program are not interdependent. Sections of your program can be written in complete isolation from the rest. Procedures can be added, removed, or disabled without necessarily having an adverse effect on the whole application. This isn't to say that a Visual Basic application is unstructured; far from it. Before starting to write your VB application, you should have a clear plan of how the various elements of your application are going to interact.

Over the past few years, VB developers have been empowered with a rapidly expanding development environment that can now create custom controls and ActiveX DLLs and EXEs that run either as client-side servers or as remote servers. This movement towards a more object-based ethos has forced a change in the programming style of most VB developers. For the majority of professional VB developers, the days when you could sit in front of a blank form and begin programming without a written plan—altering the architecture of your application on the fly—are long gone.

In this chapter, you will see how to structure a VB program, from starting your program, through the various procedure types at your disposal, and then how you can eventually end your VB program.

Regardless of the type of application you're writing and the development tool (hosted VBA or the retail version of VB) you're using, there has to be a starting point or an entry point for your program. Here there is a major difference between VB and VBA: a VB application is launched as an application in its own right, whereas the VBA program has to be launched by the host application. But in either case, the starting point you choose is decided by the type of application you are writing, as well as by the facilities offered by your development environment for launching applications. In this section, we'll look at the methods available to you for starting your application.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Regardless of the type of application you're writing and the development tool (hosted VBA or the retail version of VB) you're using, there has to be a starting point or an entry point for your program. Here there is a major difference between VB and VBA: a VB application is launched as an application in its own right, whereas the VBA program has to be launched by the host application. But in either case, the starting point you choose is decided by the type of application you are writing, as well as by the facilities offered by your development environment for launching applications. In this section, we'll look at the methods available to you for starting your application.

Because VBA is now hosted in a wide range of different applications, each of which has its own ways of launching an application or routine, it's impossible to describe here how to start your program running in each. Instead, we'll focus on the two most popular applications for hosted VBA, Word and Excel.

In discussing the launching of VBA programs in Word and Excel, I mention using the application's user interface to launch the program using a keyboard combination or a toolbar button. This can also be done programmatically. A discussion of how to do so, however, is beyond the scope of this book.

A Word/VBA program can take a multitude of forms, ranging from a small routine that accomplishes some utility function at one extreme to a complete application that handles every detail of the user's interaction with Word. Of course, you want the method that invokes your program to be consistent with its general purpose. Fortunately, Word provides several ways to launch a VBA application.

Section 2.1.1.1: Storing your code

Whenever Word starts, it automatically loads the default global template file,

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Any VB program—whether a hosted VBA application or a VB executable—is a collection of modules containing code, graphical user interface objects, and classes. This book concentrates on the language elements of VBA as they relate to both hosted VBA and the retail version of VB. The VBA and VB user interfaces—whether Word, Excel, Project, or a VB form—all fire events that are handled by the code you create using the VBA language. Therefore the code modules within your program are of greatest concern to us here.

Visual Basic code can be split into three categories:

Code you write to handle events such as a button being clicked by the user; these procedures are called event handlers
Custom procedures, where you create the main functionality of your application
Property procedures, used in form and class modules

All Visual Basic language elements work equally well in all three types of procedure. For example, there are no restrictions placed on the type of code you can write within a particular type of procedure. It's left to you as the developer to decide what code goes where.

An event is always the starting point for your procedure. It can be a system-generated event, such as the Form Load event or a Timer control event, or it can be a user-generated event, such as the Click event on a command button.

To code an event handler for a control event, open the form's code window and select the control from the drop-down list of the available objects. Next, select the required event from the drop-down list of available events for that control. The Event handler definition is then automatically placed in the code window, and you can start coding the event handler.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

At some stage, most users want to exit from a program. OK, yours might be a really great program, but unfortunately the user may want to go off and do something else—like go home! You have to allow your application to both exit and tidy up before it ends. One advantage you have when building an application in a VBA-hosted environment is that you don't have to worry too much about finishing the program; the majority of the work is taken care of by the host application. You just have to ensure that any object references are cleaned up, and all database connections closed. You can place this type of code in the Close event. VB developers writing executables have to take care of unloading the application themselves, but in most cases this is no more onerous a task than in VBA; this section shows you how.

If you specified a form as the startup object, then you must unload this form to close the application. You can do this by including the following statement somewhere in the form, usually in the event handler of an Exit menu option or Exit command button:

Unload Me

If you specified a Sub Main procedure as the startup object, the program terminates when the Sub Main procedure is completed. For example, here's the Sub Main you saw earlier in this chapter:

Sub Main()
    Dim oForm as frmStartUp
    Set oForm = New frmStartUp
        oForm.Show vbModal
    Set oForm = Nothing
End Sub

Because the form is shown modally, the Sub Main procedure doesn't continue until the form is either hidden—using the statement Me.Hide—or unloaded. Once this happens, program execution is handed back to the Sub Main procedure, which destroys the form object it created by setting the reference to Nothing. When the End Sub statement is executed, the whole application terminates.

If you are writing an ActiveX DLL or EXE, things are slightly different: you shouldn't place any code in your application to terminate the application. The termination should be handled by the operating system. Basically, when all references to your ActiveX component are set to

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

The VBA language offers a full set of the usual data types, plus a smart data type called a Variant, which is the chameleon of the programming world, adapting itself seamlessly to hold any type of data. This chapter lists the data types available in VBA and discusses a complete range of issues related to data types, including variable scope and lifetime, the character of the variant, and performance issues that arise in using particular data types.

Visual Basic and Visual Basic for Applications support the following data types:

Boolean

Indicates the presence of logical data that can contain either of two values, True or False. The keywords True and False are constants that are predefined in VBA, so you can make use of them in your code when you want to assign a value to a Boolean variable, as the following code fragment shows:

var1 = True
var2 = False

Many of the properties of ActiveX controls have possible values of True or False. In addition, within programs, Boolean variables often serve as flags to control program flow, as the following example, which toggles (or reverses) the value of myBool within the If...Else...End If construct, shows:

If myBool = False Then
  myVar = 4
  myBool = True
Else
  myVar = 5
  myBool = False
End If

Storage required: Two bytes
Range: True or False
Default value

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Visual Basic and Visual Basic for Applications support the following data types:

Boolean

Indicates the presence of logical data that can contain either of two values, True or False. The keywords True and False are constants that are predefined in VBA, so you can make use of them in your code when you want to assign a value to a Boolean variable, as the following code fragment shows:

var1 = True
var2 = False

Many of the properties of ActiveX controls have possible values of True or False. In addition, within programs, Boolean variables often serve as flags to control program flow, as the following example, which toggles (or reverses) the value of myBool within the If...Else...End If construct, shows:

If myBool = False Then
  myVar = 4
  myBool = True
Else
  myVar = 5
  myBool = False
End If

Storage required: Two bytes
Range: True or False
Default value: False

Byte

The smallest numeric subtype available in VBA. Because only one byte holds a number ranging from to 255 (or 00 to FF in hexadecimal), there is no room for the sign, and so only positive numbers can be held in a Byte data type. Attempting to assign a negative number or a number greater than 255 to byte data results in a runtime error.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

VBA provides two sets of built-in conversion functions. The first set, which includes Int and Str, is from the early versions of VB and is simply left in for backwards compatibility. The functions of the second set all start with the letter "C" and are the more recent conversion functions. Microsoft recommends that you use this latter set of functions, since they are locale-aware; that is, they take account of international date, time, and number settings on the host system.

The syntax for each of the latter conversion functions is basically the same. For example:

CBool(variablename)

where variablename is either the name of a variable, a constant, or an expression (like x-y) that evaluates to a particular data type. Regardless of the particular function you use, the data type being converted is immaterial; what matters is the data type to which you want to convert a particular value.

The conversion functions supported by VBA are:

CBool: Converts variablename to a Boolean data type. variablename can contain any numeric data type or any string capable of being converted into a number. If variablename is or "0", CBool returns False; otherwise, it returns True (–1).
CByte: Converts variablename to a Byte data type. variablename can contain any numeric data or string data capable of conversion into a number that is greater than or equal to and less than or equal to 255. If variablename is out of range, VBA displays an Overflow error message. If variablename is a floating point number, it's rounded to the nearest integer before being converted to byte data.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

VBA contains a special data type, the Variant. Internally, the Variant is highly complex, but it's also extremely easy to use. The Variant is the default data type of VBA, so the following code casts myVar as a variant:

Dim myVar

The Variant data type allows you to use a variable with any of the intrinsic VBA data types, automatically working out what is the closest data type to the value you are assigning. When you consider the amount of processing required to determine what data type should be used for an abstract value, it's a testament to the VB development team at Microsoft that the Variant is as quick as it is. However, there is a slight performance hit when using both variant data and functions that return variant data, which we discuss later in this chapter.

Another drawback to using variant data is that your code becomes at best horrible to read, and at worst unreadable! To illustrate, consider two versions of the same function, the first written exclusively with variants, the second using strong typing:

Private Function GoodStuff(vAnything, vSomething, _
                           vSomethingElse)

If vAnything > 1 And vSomething > "" Then
   GoodStuff = vAnything * vSomethingElse
Else
   GoodStuff = vAnything + 10
End If

End Function

Private Function GoodStuff(iAnything As Integer, _
                           sSomething As String, _
                           iSomethingElse As Integer) _
                          As Integer
If iAnything > 1 And sSomething > "" Then
   GoodStuff = iAnything * iSomethingElse
Else
   GoodStuff = iAnything + 10
End If

End Function

I know which one I'd rather maintain!

So how do you use variant data? Well, at the simplest level, you can ignore the fact that there are such things as data types (or, to be more precise, when using variants, you can ignore data subtypes). But to be a proficient VB programmer, if you use variants at all, it's best to be aware that every item of variant data has a subtype (like Integer, Long, or String) that corresponds to one of the major data types. And Decimal data is something of an exception: it's only available as a subtype of the Variant data type.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

As was mentioned earlier, VBA supports a default data type, which means that, unlike many other programming languages, VBA allows the implicit declaration of variables. As soon as you use a variable or constant name within your code, VBA does all the necessary work of allocating memory space, etc., and the variable is considered to be declared.

However, it's good programming practice (and one that will save you endless hours of grief) to explicitly declare any variables and constants you want to use by using the Dim, Private, or Public statements. Their syntax is:

Dim VariableName As datatype
Private VariableName As datatype
Public VariableName As datatype

If you have a number of variables to declare, you can do this on a single line by separating them with commas, as in the following Dim statement:

Dim iRefNo As Integer, iAnyVar As Integer

By explicitly declaring variables in this manner, you can reduce the number of bugs in your code caused by spelling errors, perhaps the most common of programming errors. Once declared, a variable name is available to you in the IntelliSense statement completion drop-down list, which means that you should never have a misspelled variable again!

For full details of how to use the Dim, Private, and Public statements, see their entries in . There is further discussion later in this chapter about how the declaration of variables affects their scope and lifetime.

Using the Option Explicit statement is good programming practice. It forces us to declare all variables and constants. You can automatically have VB add this to new modules as they are created by checking the Require Variable Declaration option, which can be found on the Editor tab of the Options dialog. (Select the Options option from the Tools menu to open the dialog.)

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Before we look at the types of arrays at our disposal, let's quickly cover some of the terminology used when talking about arrays. Creating an array is called dimensioning the array (i.e., defining its size). The individual data items within the array are known as elements , and the number used to access an element is known as an index . The lowest and highest index numbers are known as bounds or boundaries. In VBA, there are four types of arrays: arrays can be either fixed or dynamic, and arrays can also be either one-dimensional or multidimensional.

Most of the time, we know how many values we need to store in an array in advance. We can therefore dimension it to the appropriate size, or number of elements, prior to accessing it by using a Dim statement like the following:

Dim myArray(5) As Integer

This line of code creates an array, named myArray, with six elements. Why six? All VBA arrays start with location 0, so this Dim statement creates an array whose locations range from myArray(0)to myArray(5).

If you want to populate an array with a series of values, use the Array function. The function allows you to quickly assign a range of comma-delimited values to an array. For instance:

myArray = Array(12,3,13,64,245,75)

To use the Array function, simply declare a variant variable, then assign the values of the array to the variable using the Array function. Any data type (even mixed data types) can be used with the Array function. Another point to remember about arrays built using the Array function is that they are always based at 0, regardless of the Option Base setting, which is used to define the lower boundary of an array.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

One major limitation of the multidimensional array is that all the dimensions within the array must be of the same data type. The user-defined type (UDT), which combines multiple data types into a single new data type, overcomes this limitation.

Since VB 4.0, UDTs have gone out of fashion somewhat, this fall from favor having resulted from the introduction of the Collection object, which on the surface operates like an infinitely flexible UDT. However, VB6 has given the humble UDT a new lease on life by allowing UDTs to be passed as property values and to be used in public function declarations. This is good news, as the UDT is far more efficient than a Collection object.

So what is a user-defined type? Simply put, it's a pseudo-data type constructed from other data types. One of its common applications is the replication of a data record in memory. For example, let's say you want to create a local array to hold the data of your customer records. Because each of the fields within the record is of a different data type, a multidimensional array can't be used. A UDT, on the other hand, is ideal in this situation. The following snippet defines a simple UDT:

Private Type custRecord
    custAccNo As Long
    custName As String
    RenewalDate As Date
End Type

Private custArray(10) As custRecord

The last line of code creates a local array of the UDT.

You can also use other UDTs within a UDT, as the following example demonstrates:

Private Type custOrders
    OrderNo As Long
    OrderDate As Long
End Type

Private Type custRecord
    custAccNo As Long
    custName As String
    RenewalDate As Date
    orders(10) As custOrders
End Type

Private custArray(10) As custRecord

Here, a user-defined type, custOrders, is defined to hold the OrderNo and OrderDate fields; then, within the custRecord UDT, an array of type custOrders is defined.

Here are two simple lines of code that access the data within these UDTs:

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Sometimes you need a variable to be seen by all the procedures within your project, while other variables should only be available within a particular procedure. The visibility of a variable is known as its scope. Closely related to a variable's scope is its lifetime, or the period of program execution when the variable is live and available for use. Precisely where you declare a variable or constant in a program determines its scope and its lifetime.

In a nutshell, variables declared in the declarations section of a module using the Private keyword can be accessed by all the procedures within the module. Variables declared in the declaration section of a code module using the Public keyword can be accessed by the whole project. Variables declared in the declaration section of a class module using the Public keyword can be accessed by the whole project once an object reference has been made to the class. And variables declared using the Dim statement within a subroutine or function can only be accessed in the procedure in which they've been declared.

A variable that is declared within an individual procedure (that is, within a subroutine or a function) can only be used within that procedure, and is therefore said to have procedure-level scope. You can therefore define different variables that use the same name in different procedures (like the simple x variable commonly used in the For...Next loop). You can even use the same variable names in a calling procedure and in a procedure that it calls, and they will be treated as two separate variables.

The lifetime of a procedure-level variable ends when the End Sub or End Function statement is executed. As soon as the procedure is complete, references to the variables defined within that procedure are erased from the computer's memory. This makes procedure-level variables ideal for temporary, localized storage of information.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Although Object variables are in many ways no different from other types of variables, the fact that they are references to other software components rather than simple values warrants special attention. While objects, classes, and binding are discussed in greater depth in , a short introduction to the subject is nevertheless worthwhile.

Object variables are declared in much the same way as other variables. There are three ways to declare an object variable:

Dim myObject As LibName.ClassName
Dim myObject As New LibName.ClassName
Dim myObject As Object

In each of the methods shown above, a Private or Public statement can replace the Dim statement, and the same scope rules apply as for other variables.

In the first declaration, the object variable is referenced to the class type library, but no instance of the class is assigned to the variable. At this stage, myObject is set to Nothing. To reference the class in this manner, you must have used the References dialog to add a reference to the class to your project. To assign a reference to a real instance of the class, you must use the Set statement prior to using the variable; for example:

Set myObject = LibName.ClassName

This produces an early bound reference to the object.

In the second declaration, a reference to a new instance of the class is assigned to the object variable, which is now ready to use immediately. Again, to reference the class in this manner, you must have first used the References dialog to add a reference to the class to your project. This second method also produces an early bound reference to the object; however, the object isn't actually created until the object variable is used.

In the third declaration, the object variable has been declared as a generic Object data type. This is useful when you don't know beforehand what type of object you will be creating. At this stage, the object variable also has a value of

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

There are numerous occasions when you need to call a custom function or subroutine from another function or subroutine, and a variable you are using in the calling procedure is needed in the called procedure. You therefore pass the variable as a parameter to the called procedure. Whether the called procedure is in the same module, the same project, or is a method within a class on a remote server, passing variables from one procedure to the other is always the same.

The called procedure, and not the calling procedure, determines how the variable is passed from the calling to the called procedure. As the user of a called procedure, you have no control over how Visual Basic treats the passed parameters. As the author of a called procedure, it's up to you to decide how best to bring in variables from calling procedures.

Visual Basic allows you to pass variables between procedures and components in two ways. Within the function or subroutine definition, you specify either ByRef or ByVal for each of the variables in the argument list.

This is the default method for passing variables between procedures in Visual Basic; that is, if you specify neither ByVal nor ByRef, VB treats the variable as though it had been specified as ByRef.

ByRef means that the variable is passed by reference. In other words, only a reference to the original variable is passed to the called procedure. The called procedure doesn't get its own copy of the variable; it simply references the original variable. This is very similar in concept to the pointers you find in C and C++. The result is that if you make a change to the variable in the called procedure, that change is reflected in the variable in the calling procedure, because they are actually the same variable.

The code fragment below demonstrates passing a variable by reference. It also demonstrates how to circumvent the problem that a function can only return one value. For example, if the

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

In addition to allowing you to define your own constants using the Const keyword, VBA includes a number of built-in or intrinsic constants whose values are predefined by VBA. Along with saving you from having to define these values as constants, the major advantage of using intrinsic constants is that they enhance the readability of your code. So, for instance, instead of having to write code like this:

If myObject.ForeColor = &hFFFF Then

you can write:

If myObject.ForeColor = vbYellow Then

Intrinsic constants are now available for most operations. The best place to find information about the available intrinsic constants is in the VB object browser, which you can open by selecting Object Browser from the View menu or by pressing F2. In many cases, though, a list of available constants for a particular operation will pop up as you are entering the code. also lists constants available in VB and VBA.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

To some degree, class modules can be seen as "replacements" for code modules—that is, class modules are repositories for shared variables as well as for shared code, just as code modules are. So why have class modules? What's wrong with normal code modules? Basically, there is nothing wrong with them. But code modules allow you to share procedures only within the project in which they reside. For example, you can call a public function from another code module in a project, but you can't call that function from another project. To do that, you have to add the code module to your project or, even worse, create a second copy of the code module.

Class modules have (without wanting to sound too evangelistic) revolutionized VB. The whole style of writing VB programs has changed since version 4.0 of VB was launched, bringing the VB/VBA language closer than ever to being a true object-oriented language. In fact, much of VB's current success in the corporate marketplace can be directly attributed to the ability to create ActiveX components, the cornerstone of which is the class module.

When you create a class module, you are creating a COM interface. Therefore, class modules allow you to describe your application to the outside world via a programmable interface that consists of properties, methods, and events in a way that allows you to retain control over the application. Using class modules, you can break an application into logical sections, each having its own class. This is the concept of encapsulation —everything having to do with a particular thing held within one wrapper—which is critical to object-oriented programming.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

An important element of any class is its properties. These are the equivalent of global or public variables in code modules. However, a property has the added advantages that you can both validate a value and execute other code every time a value is assigned to the property. In addition, properties declared within class modules can be accessed from outside the current project. Properties help eliminate the clutter of global variables that plagued almost every large-scale VB3 application I've seen, and that made both the development and maintenance of VB3 applications a nightmare.

Properties allow users and other programmers (including yourself) to safely access data. In many ways, properties are simply variables that hold a particular value or object. But with careful planning and a professional approach, you can turn these simple variables into powerful tools.

Take, for example, a class that is acting as a wrapper for a collection object. You may have a read-only property within your class called Count that returns the number of records held within the collection. Your Count property would simply pass on the Count property of the collection object. However, you could write code within your class's Count property procedure that checks if the value of the collection object's Count property is zero and, if it's zero, calls a procedure that populates the collection. In this way, the user of your class could populate the collection automatically by returning the Count property, as the following snippet demonstrates:

Public Property Get Count() As Long
    If mcolAnyColl.Count = 0 Then
        Call PopulateCollection()
    End If
    Count = mcolAnyColl.Count
End Property

A major use of property procedures is to validate data. Without class modules and properties, validation is typically performed at the form level; for example, the Change or LostFocus event handler of a TextBox control determines if the data entered in the text box is acceptable. It often happens, though, that this value is referenced on many different forms, so that each reference has to be accompanied by the data validation code. If a programmer gets the validation code wrong, or simply ignores or forgets the validation altogether, you risk accepting invalid data into your database. If the validation rules change, you have to visit each form that contains a reference to the data item and change the validation code.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Constants, as you know, are useful for improving the readability and maintainability of code by making it self-documenting. However, you can't define a public constant (using the Public Const statement) within a class module. How do you make some constants available both to yourself and possibly to other users of your class? The answer lies in the use of enumerated constants.

Enumerated constants allow you to create a set of constants that become intrinsic to your application or class, very much like the intrinsic constants, such as vbCrLf and vbRightButton, within VB itself. By using enumerated constants within your class, you can associate a constant name and its value with the class, in the process providing the user of the class with a set of meaningful constants that are instantly available from the IntelliSense drop-down list for statement completion.

To create a set of enumerated constants, you use the Enum statement, which defines the name of the set of constant values, the names of the individual constants within the set, and the individual values of these constants. You place the Enum statement in the declarations section of your class module. For example:

Public Enum empTypes
   empTypeOne = 1
   empTypeTwo = 2
   empTypeThree = 3
End Enum

The major drawback with enumerated constants is that their values can be numeric only. In other words, you can't declare an enumerated constant that represents a string.

Once you have created a reference to your class from the client application using the references dialog or automatically if the class module in which empTypes is defined is a part of your project, you have access to the enumerated constants via the IntelliSense drop-down list. For example, you could access the constants shown in the example above by typing emp, then pressing the Ctrl key and spacebar together; in the list of available items, you would see all three of the constants and the

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Unlike normal code modules, class modules support two events that are automatically defined when you add a class module to your project. These two standard events, the Initialize and Terminate events, are analogous to the class constructor and destructor in an object-oriented programming language like C++; they are fired automatically when a class is instantiated and destroyed, respectively.

An event handler is the code attached to a particular event. When the event is fired, the event handler is executed automatically. Like all event handlers, writing code to handle the Initialize and Terminate events is optional, but it's at the heart of sound VB programming. So let's look at some of the uses you can put these event handlers to and some of the rules relating to these two events.

Let's begin by examining precisely when the Initialize event is fired, then look at some possible applications for the Initialize event handler.

Section 4.3.1.1: When is the Initialize event fired?

The firing of the Initialize event depends on how the class object is instantiated. If you use the combined method of declaring a New instance of an object:

Dim oVar As New svrObject

the Initialize event is fired when the first reference is made to a member of the class, and not when the class is declared as New. For example:

Dim oVar As New svrObject ' Initialize event not called
oVar.AnyProp = sAnyVal    ' Initialize event fired _
                       immediately prior to the Property Let

However, if you use the Set statement to instantiate an object, the Initialize event is fired when the Set statement is executed. For example:

Dim oVar As svrObject
Set oVar = New svrObject  ' Initialize event fired here

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|

Table of Contents

Section 2.1.1.1: Storing your code

Section 4.3.1.1: When is the Initialize event fired?