Introduction to VBA: Part 2

In the article Introduction to VBA: Part 1, you learned many of the basics of the VBA language. We began by discussing why the VBA language is important to you as an Access developer. You then learned all about the development environment and how to create event procedures. You also learned how to create and work with user defined procedures. In this article we take your use of the VBA language to the next level. You first learn how to work with variables. You then learn how to add comments and line continuation characters to your code. Finally, you will explore how the various control structures available in the VBA language help you to effectively branch through your code.

Working with Variables

You must consider many issues when creating VBA variables. The way that you declare a variable determines its scope, its lifetime, and more. The following topics will help you better understand declaring variables in VBA.

Declaring Variables

There are several ways to declare variables in VBA. For example, you could simply declare x=10. With this method of variable declaration, you really aren’t declaring your variables at all; you’re essentially declaring them as you use them. This method is quite dangerous. It lends itself to typos and other problems. If you follow the practice recommended previously, of always using the Option Explicit statement, Access will not allow you to declare variables in this manner.

You could also type Dim intCounter; the Dim statement declares the variable. The only problem with this method is that you haven’t declared the type of the variable to the compiler, so it’s declared as a variant variable.

Another common mistake is declaring multiple variables on the same line, as in this example:

Dim intCounter, intAge, intWeight As Integer

Select all Open in new window

In this line, only the last variable is explicitly declared as an integer variable. The other variables are implicitly declared as variants. If you’re going to declare multiple variables on one line, make sure each variable is specifically declared, as in the following example:

Dim intCounter As Integer, intAge As Integer, intWeight As Integer

Select all Open in new window

The most efficient and bug-proof way to declare your variables is to strong-type them to the compiler and declare only one variable per line of code, as in this example:

Dim intCounter As Integer
                      Dim strName As String

Select all Open in new window

As you can see, strong-typing declares the name of the variable as well as the type of data it can contain. This type of declaration enables the compiler to catch errors, such as storing a string in an integer variable, before your program runs. If implemented properly, this method can also reduce the resources needed to run your programs by selecting the smallest practical data type for each variable.

Note

You should try to avoid using variants whenever possible. Besides requiring a significant amount of storage space, variants are also slow because they must be resolved by the compiler at runtime. However, certain situations warrant using a variant. One example is when you want the variable to contain different types of data at different times. Another case occurs when you want to be able to differentiate between an empty variable (one that hasn’t been initialized) and a variable that has a zero or a zero-length string. Also, variant variables are the only type of variable that can hold the special value of Null.

VBA Data Types

VBA offers several data types for variables. Available data types include Byte, Boolean, Integer, Long      Integer,      Single, Double, Currency, Date, Object Reference, String, Variant, and User-Defined Data Type.

Scope and Lifetime of Variables: Exposing Your Variables as Little as Possible

You have read about the different types of variables available in VBA. Like procedures, variables also have a scope. A variable can be declared as local, private (Module), or public in scope. You should try to use local variables in your code because they’re shielded from being accidentally modified by other routines.

Variables also have an attribute referred to as their lifetime. The lifetime of a variable reflects the time during which the variable actually exists and, therefore, the time during which its value is retained. In the following sections, we take a closer look at how to set the scope and lifetime of variables.

Local Variables

Local variables are available only in the procedure where they are declared. Consider this example:

Private Sub cmdOkay_Click
                        Dim strAnimal As String
                        strAnimal = "Dog"
                        Call ChangeAnimal
                        Debug.Print strAnimal ‘Still Dog
                      End Sub
                      
                      Private Sub ChangeAnimal
                        strAnimal = "Cat"
                      End Sub

Select all Open in new window

This code can behave in one of three ways. If Option Explicit is in effect, meaning that all variables must be declared before they’re used, this code generates a compiler error. If the Option Explicit statement isn’t used, strAnimal is changed to Cat only within the context of the subroutine ChangeAnimal. If the Dim strAnimal As String statement is moved to the General Declarations section of the module, the variable’s value is changed to “Cat”.

Note

Notice the Debug.Print statement in the cmdOkay_Click event routine shown previously. The expression that follows the Debug.Print statement is printed in the Immediate window. The Immediate window is a tool that helps you to troubleshoot your applications. You can invoke the Immediate window from almost anywhere within your application. The easiest way to activate the Immediate window is with the Ctrl+G keystroke combination. You are placed in the VBE within the Immediate window. You can then view the expressions that were printed to the Immediate window.

Static Variables: A Special Type of Local Variable

The following examples illustrate the difference between local and static variables. Local variables are reinitialized each time the code is called. You can run the following procedure by opening the form named frmScopeAndLifeTime and clicking the Local Age button. Notice that each time you run the procedure, the numeral 1 is displayed in the txtNewAge text box.

Private Sub cmdLocalAge_Click()
                        Dim intAge As Integer
                        intAge = intAge + 1
                        Me.txtNewAge.Value = intAge
                      End Sub

Select all Open in new window

Each time this code runs, the Dim statement reinitializes intAge to zero. This is quite different from the following code, which illustrates the use of a static variable:

Private Sub cmdStaticAge_Click()
                        Static sintAge As Integer
                        sintAge = sintAge + 1
                        Me.txtNewAge.Value = sintAge
                      End Sub

Select all Open in new window

Each time this code executes, the variable called sintAge is incremented, and its value is retained. You can test this by opening on the the form named frmScopeAndLifeTime and clicking the Static Age button.

Private Variables

So far, this discussion has been limited to variables that have scope within a single procedure. Private (module-level) variables can be seen by any routine in the module they were declared in, but not from other modules. Thus, they are private to the module. You declare private variables by placing a Private statement, such as the following, in the General Declarations section of a form, report, or Access module:

[General Declarations]
                      Option Explicit
                      Private mintAge As Integer

Select all Open in new window

You can change the value of a variable declared as private from any subroutine or function within that module. For example, the following subroutine increments the value of the private variable mintAge by 1. You can run this code by opening the form named frmScopeAndLifeTime and clicking the Module Age button.

Private Sub cmdModuleAge_Click()
                        mintAge = mintAge + 1
                        Me.txtNewAge.Value = mintAge
                      End Sub

Select all Open in new window

Notice the naming convention of using the letter m to prefix the name of the variable, which denotes the variable as a private module-level variable. You should use private declarations only for variables that need to be seen by multiple procedures in the same module. Aim for making most of your variables local to make your code modular and more bulletproof.

Public Variables

You can access public variables from any VBA code in your application. They’re usually limited to things such as login IDs, environment settings, and other variables that must be seen by your entire application. You can place declarations of public variables in the General Declarations section of a module. The declaration of a public variable looks like this:

Option Explicit
                      Public gintAge As Integer

Select all Open in new window

Notice the prefix g (a relic of the old Global variables), the proper prefix for a public variable declared in a Standard module. This standard is used because public variables declared in a Standard module are visible not only to the module they were declared in, but also to other modules. The following code, placed in the Click event of the cmdPublic command button, increments the public variable gintAge by 1. You can run this code by opening the form frmScopeAndLifeTime and clicking the Public Age button.

Private Sub cmdPublicAge_Click()
                        gintAge = gintAge + 1
                        Me.txtNewAge.Value = gintAge
                      End Sub

Select all Open in new window

Adding Comments to Your Code

You add comments, which have been color-coded since the release of Access 97 (prior to Access 97 they were the same color as the programming code), to modules by using an apostrophe (‘). You can also use the keyword Rem, but the apostrophe is generally preferred. You can place the apostrophe at the beginning of the line of code or anywhere within it. Anything following the apostrophe is considered a comment. Figure 1 shows code containing comments.


Tip

Many people ask if it is possible to comment several lines of code at once. Although not easily discoverable, the process is quite simple. Within the VBE, right-click any toolbar or menu bar and display the Edit toolbar. Click the Comment Block tool on the Edit toolbar. To uncomment the block of code, click the Uncomment Block tool.

Using the Line Continuation Character

Access Basic code, used in Access 2.0, didn’t have a line continuation character. Therefore, you had to scroll a lot, as well as pull out a bag of tricks to simulate continuing a line of code. With VBA, Access 97 and higher solve this problem: The line continuation character is an underscore. Figure 2 illustrates the use of this character.

Using the VBA Control Structures

VBA gives the developer several different constructs for looping and decision processing. The most commonly used ones are covered in the following sections and are found in the form called frmControlStructures.

If…Then…Else

The If…Then…Else construct evaluates whether a condition is True. In the following example, anything between If and Else will occur if the statement evaluates to True, and any code between Else and End If will be executed if the statement evaluates to False. The Else is optional.

Private Sub cmdIfThenElse_Click()
                       If IsNull(Me.txtName.Value) or IsNull(Me.txtAge.Value) Then
                          MsgBox "Name or Age is Blank"
                        Else
                          MsgBox "Your Name Is " & Me.txtName.Value _
                              & " And Your Age Is " & Me.txtAge.Value
                        End If
                      End Sub

Select all Open in new window

This code tests whether the text box called txtName or the text box txtAge contains a Null. A different message is displayed depending on whether one of the text boxes contains a Null value.

One-line If statements are also permitted; they look like this:

If IsNull(Me.txtvalue.Value) Then MsgBox "You must enter a value"

Select all Open in new window

However, this format for an If statement isn’t recommended because it reduces readability.

Another useful form of an If statement is ElseIf, which enables you to evaluate an unlimited number of conditions in one If statement. The following code gives you an example:

Sub MultipleIfs(intNumber As Integer)
                         If intNumber = 1 Then
                            MsgBox "You entered a one"
                         ElseIf intNumber = 2 Then
                            MsgBox "You entered a two"
                         ElseIf intNumber >= 3 And intNumber <= 10 Then
                            MsgBox "You entered a number between 3 and 10"
                         Else
                            MsgBox "You entered some other number"
                         End If
                      End Sub

Select all Open in new window

The conditions in an If statement are evaluated in the order in which they appear. For this reason, it’s best to place the most common conditions first. After a condition is met, execution continues immediately after End If. If no conditions are met, and there’s no Else statement, execution will also continue immediately after End If.

Note

If multiple conditions exist, using a Select Case statement, described later in this article, is almost always preferable to using an If statement. Case statements generally make your code easier to read and maintain.

The Immediate If (IIf)

An Immediate If (IIf) is a variation of an If statement. It’s actually a built-in function that returns one of two values, depending on whether the condition being tested is true or false. Here’s an example:

Function EvalSales(curSales As Currency) As String
                         EvalSales = IIf(curSales >= 100000, "Great Job", "Keep Plugging")
                      End Function

Select all Open in new window

This function evaluates the curSales parameter to see whether its value is greater than or equal to $100,000. If it is, the function returns the string "Great Job"; otherwise, the function returns the string "Keep Plugging".

Caution

Both the true and false portions of the IIf are evaluated, so if there’s a problem with either part of the expression (for example, a divide-by-zero condition), an error occurs.
The IIf function is most often used in a calculated control on a form or report, or to create a new field in a query. Probably the most common example is an IIf expression that determines whether the value of a control is IsNull. If it is, you can have the expression return a zero or an empty string; otherwise, you can have the expression return the value in the control. The following expression, for example, evaluates the value of a control on a form:

=IIf(IsNull(Forms!frmOrders.txtFreight.Value),0,_
                          Forms!frmOrders.txtFreight.Value)

Select all Open in new window

This expression displays either a zero or the value for freight in the control called txtFreight.

Note

Although the IIf function can be used to handle Nulls, the built-in NZ function is a more efficient solution to this problem and avoids the inherent pitfalls of IIf.

Caution

The IIf function is rather slow. It is best to avoid using it whenever possible by replacing it with a properly formed If…Then…Else block.

The Conditional If: Conditional Compilation

Conditional compilation enables you to selectively execute blocks of code. This feature is useful in several situations:

Conditional compilation is done by using the #If…Then…#Else directive, as shown here and found under the Conditional Compilation command button on the frmControlStructures form:

Sub cmdConditionalCompilation_Click()
                         #If Language = "Spanish" Then
                            MsgBox "Hola, Que Tal?"
                         #Else
                            MsgBox "Hello, How Are You?"
                         #End If
                      End Sub

Select all Open in new window

Note

The difference between conditional compilation and standard If..Then..Else logic is that conditional compilation is performed at compile time. Only the appropriate line(s) of code is placed in the compiled code. This improves performance if similar logic is needed throughout the application.

You can declare the compiler constant, in this case, Language, in one of two places: in a module’s General Declarations section or in the Project Properties dialog box. A compiler constant declared in the General Declarations section of a module looks like this:

#Const Language = "Spanish"

The disadvantage of this constant is that you can’t declare it as public. It isn’t possible to create public compiler constants by using the #Const directive. This means that any compiler constants declared in a module’s Declarations section can be used only within that module. The major advantage of declaring this type of compiler constant is that it can contain a string. For example, the compiler constant Language, defined in the preceding paragraph, is given the value "Spanish".

Public compiler constants can be declared by modifying the Project Properties. Because they are public in scope, compiler constants declared in the Project Properties can be referred to from anywhere in your application. The major limitation on compiler directives set up in Project Properties is that they can contain only integers. For example, you would have to enter Language = 1.

To define compiler constants using the Project Properties dialog box, right-click within the Project window and select projectx Properties, where projectx is the name of the project you are working with. You can now enter the values you need into the text box labeled Conditional Compilation Arguments. You can enter several arguments by separating them with a colon, such as Language = 1 : Version = 2.

With the compiler directive Language=1, the code would look like this:

Sub ConditionalIf()
                         #If Language = 1 Then
                            MsgBox "Hola, Que Tal?"
                         #Else
                            MsgBox "Hello, How Are You?"
                         #End If
                      End Sub

Select all Open in new window

Note

For this code to execute properly, you must remove the constant declaration from the previous example.

Notice that ConditionalIf now evaluates the constant Language against the integer of 1.
It’s important to understand that using conditional constants isn’t the same as using regular constants or variables with the standard If…Then…Else construct. Regular constants or variables are evaluated at runtime, which requires processing time each occasion the application is run. Conditional constants and conditional If…Then…Else statements control which sections of code are actually compiled. All resolution is completed at compile time; this eliminates the need for unnecessary processing at runtime.

Select Case

Rather than using multiple If…Then…Else statements, using a Select Case statement is often much clearer, as shown here. This Select Case statement is found under the Select Case command button of the frmControlStructures form.

Private Sub cmdCase_Click()
                          Dim intAge As Integer
                          intAge = Nz(Me.txtAge.Value, 0)    Select Case intAge
                            Case 0
                              MsgBox "You Must Enter a Number"
                            Case 1 To 18
                              MsgBox "You Are Just a Kid"
                            Case 19, 20, 21
                              MsgBox "You are Almost an Adult"
                            Case 22 to 40
                              MsgBox "Good Deal"
                            Case Is > 40
                              MsgBox "Getting Up There!"
                            Case Else
                              MsgBox "You Entered an Invalid Number"
                          End Select
                      End Sub

Select all Open in new window

This subroutine first uses the Nz function to convert a Null or empty value in the txtAge control to 0; otherwise, the value in txtAge is stored in intAge. The Select Case statement then evaluates intAge. If the value is 0, the code displays a message box with You Must Enter a Number. If the value is between 1 and 18 inclusive, the code displays a message box saying You Are Just a Kid. If the user enters 19, 20, or 21, the code displays the message You are Almost an Adult. If the user enters a value between 22 and 40 inclusive, the code displays the message Good Deal. If the user enters a value greater than 40, the code displays the message Getting Up There!; otherwise, the user gets a message indicating that she entered an invalid number.

Looping

Several looping structures are available in VBA; most are discussed in this section. Take a look at the following example of a looping structure (found under the Do While…Loop command button of the frmControlStructures form):

Sub cmdDoWhileLoop_Click()
                      
                         Do While Nz(Me.txtAge.Value)< 35
                      
                            Me.txtAge.Value = Nz(Me.txtAge.Value) + 1
                          Loop
                      End Sub

Select all Open in new window

In this structure, if the value in the txtAge text box is greater than or equal to 35, the code in the loop is not executed. If you want the code to execute unconditionally at least one time, you need to use the following construct (found under the Do…Loop While command button of the frmControlStructures form):

Sub cmdDoLoopWhile_Click()
                      
                          Do
                      
                              Me.txtAge = Nz(Me.txtAge.Value) + 1
                          Loop While Nz(Me.txtAge.Value) < 35
                      End Sub

Select all Open in new window

This code will execute one time, even if the value in the txtAge text box is set to 35. The Do While…Loop in the previous example evaluates before the code is executed, so it doesn’t ensure code execution. The Do…Loop While is evaluated at the end of the loop, and therefore guarantees execution.

Alternatives to the Do While…Loop and the Do…Loop While are Do Until…Loop and Do…Loop Until. Do Until…Loop (found under the Do Until…Loop command button of the frmControlStructures form) works like this:

Sub cmdDoUntil_Click()
                      
                         Do Until Nz(Me.txtAge.Value) = 35
                      
                            Me.txtAge.Value = Nz(Me.txtAge.Value) + 1
                          Loop
                      End Sub

Select all Open in new window

This loop continues to execute until the value in the txtAge text box becomes equal to 35. The Do…Loop Until construct (found under the Do…Loop Until command button of the frmControlStructures form) is another variation:

Sub cmdLoopUntil_Click()
                      
                         Do
                      
                            Me.txtAge.Value = Nz(Me.txtAge.Value) + 1
                         Loop Until Nz(Me.txtAge.Value) = 35
                      End Sub

Select all Open in new window

As with the Do…Loop While construct, the Do…Loop Until construct doesn’t evaluate the condition until the end of the loop, so the code in the loop is guaranteed to execute at least once.

Tip

Due to the associated performance degradation, it is not a good idea to reference a control over and over again in a loop. Notice that, in the looping examples, the txtAge control is referenced each time through the loop. This was done to keep the examples simple. To eliminate the performance problem associated with this technique, use the code that follows (found under the cmdEfficient command button on the frmControlStructures form):

Private Sub cmdEfficient_Click()
                          Dim intCounter As Integer
                          intCounter = Nz(Me.txtAge.Value)
                          Do While intCounter < 35
                             intCounter = intCounter + 1
                          Loop
                          Me.txtAge.Value = intCounter
                      End Sub

Select all Open in new window

Caution

With any of the looping constructs, it’s easy to unintentionally cause a loop to execute endlessly, as is shown in this example and can also be illustrated with the code samples shown previously:

Sub EndlessLoop()
                         Dim intCounter As Integer
                         intCounter = 5
                         Do
                            Debug.Print intCounter
                            intCounter = intCounter + 1
                         Loop Until intCounter = 5
                      End Sub

Select all Open in new window

This code snippet sets intCounter equal to 5. The code in the loop increments intCounter, and then tests to see whether intCounter equals 5. If it doesn’t, the code in the loop executes another time. Because intCounter will never become equal to 5 (it starts at 6 within the Do loop), the loop executes endlessly. You need to use Ctrl+Break to exit the loop; however, Ctrl+Break doesn’t work in Access’s runtime version.

For…Next

The For...Next construct is used when you have an exact number of iterations you want to perform. It looks like this and is found under the For…Next command button of the frmControlStructures form:

Sub cmdForNext_Click()
                          Dim intCounter As Integer
                          For intCounter = 1 To 5
                              Me.txtAge.Value = Nz(Me.txtAge.Value) + 1
                          Next intCounter
                      End Sub

Select all Open in new window

Note that intCounter is self-incrementing. The start value and the stop value can both be variables. A For…Next construct can also be given a step value, as shown in the following (the counter is incremented by the value of Step each time the loop is processed):

Sub ForNextStep()
                      ' Note that this code is not in database Chap78ex.mdb
                          Dim intCounter As Integer
                          For intCounter = 1 To 5 Step 2
                              Me.txtAge.Value = Nz(Me.txtAge.Value) + 1
                          Next intCounter
                      End Sub

Select all Open in new window

With…End With

The With…End With statement executes a series of statements on a single object or user-defined type. Here’s an example (found under the With…End With command button of the frmControlStructures form:

Private Sub cmdWithEndWith_Click()
                         With Me.txtAge
                            .BackColor = 16777088
                            .ForeColor = 16711680
                            .Value = "40Hello World"
                            .FontName = "Arial"
                         End With
                      End Sub

Select all Open in new window

This code performs four operations on the txtAge text box, found on the form it’s run on. The code modifies the BackColor, ForeColor, Value, and FontName properties of the txtAge text box.

Tip

The With…End With statement offers two main benefits. The first is simply less typing: You don’t need to repeat the object name for each action you want to perform on the object. The more important benefit involves performance. Because the object is referred to once rather than multiple times, this code runs much more efficiently. The benefits are even more pronounced when the With…End With construct is found in a loop.

For Each…Next

The For Each…Next statement executes a group of statements on each member of an array or collection. The following example (found under the For Each…Next command button of the frmControlStructures form) illustrates the use of this powerful construct:

Private Sub cmdForEachNext_Click()
                      
                          Dim ctl As Control
                          For Each ctl In Controls
                              ctl.FontSize = 8
                          Next ctl
                      End Sub

Select all Open in new window

This code loops through each control on the form, modifying the FontSize property of each control.

As in the following example, the With…End With construct is often used along with the For
Each…Next construct:

Private Sub cmdForEachWith_Click()
                          Dim ctl As Control
                          For Each ctl In Controls
                              With ctl
                                  .ForeColor = 16711680
                                  .FontName = "Arial"
                                  .FontSize = 14
                              End With
                          Next ctl
                      End Sub

Select all Open in new window

This code loops through each control on a form; the ForeColor, FontName, and FontSize properties of each control on the form are modified.

Caution

Before you put all this good information to use, remember that no error handling has been implemented in the code yet. If one of the controls on the form in the example doesn’t have a ForeColor, FontName, or FontSize property, the code would cause an error.

Passing Parameters and Returning Values

Both subroutines and functions can receive arguments (parameters), but  subroutines can return values only when you use the ByRef keyword. The following subroutine (found under the Pass Parameters command button of the frmParametersAndReturnValues form) receives two parameters:, txtFirst and txtLast. It then displays a message box with the first character of each of the parameters that was passed.

Private Sub cmdPassParameters_Click()
                        Call Initials(Nz(Me.txtFirstName.Value), Nz(Me.txtLastName.Value))
                      End Sub
                      
                      Sub Initials(strFirst As String, strLast As String)
                      ' This procedure can be found by selecting General in
                      ' the Object drop-down list in the VBE window
                        MsgBox "Your Initials Are: " & Left$(strFirst, 1) _
                         & Left$(strLast, 1)
                      End Sub

Select all Open in new window

Notice that the values in the controls txtFirstName and txtLastName from the current form (represented by the Me keyword) are passed to the subroutine called Initials. The parameters are received as strFirst and strLast. The code displays the first left character of each parameter in the message box.

The preceding code simply passes values and then operates on those values. This next example (found under the Return Values command button of the frmParametersAndReturnValues form) uses a function to return a value:

Private Sub cmdReturnValues_Click()
                          Dim strInitials As String
                          strInitials = ReturnInit(Nz(Me.txtFirstName.Value), _
                                Nz(Me.txtLastName.Value))
                          MsgBox "Your initials are: " & strInitials
                      End Sub
                      
                      Function ReturnInit(strFName As String, strLName As String) As String
                      ' This procedure can be found by selecting General in
                      ' the Object drop-down list in the VBE window
                          ReturnInit = Left$(strFName, 1) & Left(strLName, 1)
                      End Function

Select all Open in new window

Notice that this example calls the function ReturnInit, sending values contained in the two text boxes as parameters. The function sets ReturnInit (the name of the function) equal to the first two characters of the strings. This returns the value to the calling routine (cmdReturnValues _Click) and sets strInitials equal to the return value.

Note

Notice that the function ReturnInit is set to receive two string parameters. You know this because of the As String keywords that follow each parameter. The function is also set to return a string. You know this because the keyword As String follows the list of the parameters, outside the parentheses. If you don’t explicitly state that the function should return a particular type of data, it returns a variant.

Executing Procedures from the Module Window

You can easily test procedures from the Module window in Access 2010. Simply click anywhere inside the procedure you want to execute, and then press the F5 key or click the Run Sub/UserForm button on the toolbar. The procedure you’re in will execute as though you had called it from code or from the Immediate pane of the Debug window.

Summary

After reading this article, you should be much more comfortable with the VBA language and what it has to offer. You learned how to work with variables and control structures. You also learned how to add comments and line continuation characters to your code. Finally, you saw how simple it is to execute your user-defined procedures from the Module window.

Note: This article was excerpted from Alison Balter's Mastering Access 2007 Development with permission by SAMS Publishing.
Chap8ex.accdb