Programming Fundamentals Using VBA

In Chapter 5 you learned about the VBA environment by looking at its structure, concepts, and editor. In this chapter, we are going to study the fundamentals of all programming while, at the same time, examine the specific syntax of the VBA language. We are going to review concepts from Chapter 5 and study them in greater detail, beginning with a review of the places where the code is stored and the types of blocks it is stored in. While discussing that, we will visit the subject of variables in greater detail. It is difficult to talk about variables without discussing a special type of variable called an array.

We are also going to look at some of the built-in tools that VBA gives you to make your job easier. We will conclude our discussion by looking at the various types of programming structures and how they are used in a VBA environment.

Programming Fundamentals

As stated in Chapter 5, VBA code is written and stored within modules. Recall that there are two basic types of modules: those associated with forms or reports, and a general module for holding procedures that is applicable to the whole project.

There is one other type, called a class module, which contains code associated with an object. We have discussed modules already in the previous chapters and will be referring to them throughout the rest of the book.

Before you can write VBA code, you need to be able to create the module. Since form or report modules are created through their respective forms and reports, we will focus on creating standard modules here.

There are a couple of different ways of creating a standard module. The easiest way is to use the Modules category of objects right in the Database window, as shown in Figure 6-1. All you need to do is click on New and you are taken to the VBA Editor with a module created, as shown in Figure 6-2.

VBA assigned your new module a temporary name of Module1. You will probably want to give it a more descriptive name. This can be done from the Properties window of the VBA Editor or directly in the Database window.

As an example, in the VBA Editor, highlight the name Module1 in the Properties window, as shown in Figure 6-3, and rename it. You can call it myFirstModule. Once you have changed it in the Properties window, the name is changed in the Project window as well as in the Database window in Access (under the Modules category).

Figure 6-1.  The Modules category selected in the Database window  

 
Figure 6-2.  The VBA Editor with a module open

In addition, you should click on the Save icon in the toolbar. You will be prompted to confirm the name, as shown here:

Now you can confirm that the name has been changed in the Project window (Figure 6-4) and the Database window Figure 6-5).

 
Figure 6-3.  The Name property

Figure 6-4.  Project window after renaming the module 

Figure 6-5.  Database window after renaming the module

NOTE

---

You can also change the name in the Database window by right-clicking it and selecting the Rename menu command.

---

You will recall from Chapter 5 that most VBA code is contained in blocks called procedures. Further, these procedures are divided into two categories: sub procedures (sometimes called subs) or function procedures. A sub just performs a task but does not return a value, while a function does return a value.

Most of the time, you will see the following two lines of code at the top of the module:

Option Compare Database
Option Explicit

These are called the general declarations of the module. Any code put into this section will affect all the procedures in the module.

The Option Compare line has three possible settings for sorting strings of characters within the code:

• Option Compare Database This uses the same sort order as the database itself and is the most common setting.
  
• Option Compare Binary This sorts a string of characters based upon the binary values of the characters. Thus, uppercase letters would be recognized before lowercase.
  
• Option Compare Text This makes the sort case sensitive with regard to the local language.

Throughout this text, we will keep the Option Compare Database setting, which is the default.

The Option Explicit line is, in my opinion, an important one. As you shall see momentarily, that line can keep you from making a lot of coding mistakes by forcing you to formally declare all variables before using them.

If this option does not come on automatically when you create the module, force it to do so by selecting Tools | Options and selecting Require Variable Declaration on the Editor tab, as shown in Figure 6-6.

Think of a procedure as a miniprogram to do one specialized job. For instance, a procedure may be used to add two numbers together. Every time those numbers need to be added, you can just call this prewritten procedure. Without the procedure, you might need to write redundant code every time you needed to do that particular job.

Within the procedure, you declare variables, have loops and If statements, and even call other procedures. Let’s build a simple procedure that adds two numbers together. We will discuss each step along the way.

 
Figure 6-6.  The Require Variable Declaration option

A procedure is first declared with the following syntax. Of course, the name is anything that you choose it to be.

Sub addNumbers()
End Sub

Notice that the VBA Editor draws a horizontal line between the general declaration area and the new procedure. This is to help delineate the procedures. It has no bearing on how the procedure works.

When you declare a procedure, it is important that you begin it with the word Sub and complete the declaration with the parenthesis. I say it is important because it is worthwhile to use proper syntax from the beginning. If you don’t put the parenthesis in, the VBA Editor will put it in for you as soon as you press ENTER.

Normally you would place any arguments the procedure would be expecting to receive within the parenthesis. Even if no arguments are expected, you need to have the empty parentheses.

As soon as you press ENTER, the VBA Editor adds the End Sub line to close the procedure. All of your code will go between the opening and closing statements.

As we move through this book, I will relate what I feel are good programming habits, or practices. While these are certainly not rules, they are accepted by many programmers and are industry standards.

The first such practice is to name your procedures with a name that reflects its job, for instance, addNumbers. Any other programmers looking at it would easily discern what it does. In addition, even though VBA is not case sensitive, it will sometimes need to interact with programs that are. A common naming convention, as mentioned in Chapter 4, is to use lowercase letters, with the exception of midword capitalization, no spaces, and to begin with a letter rather than a number. In the case of beginning with a letter rather than a number, this is more than just a naming convention; for many programs, including VBA, it is a rule.

The second practice is to indent the code in the procedure. That way, it is easy to spot where the procedure begins and ends. I usually just press the SPACEBAR three times. The VBA Editor will preserve the indenting for subsequent lines.

The third practice is to carefully comment your code. You can easily do that by using the single quote before a line. If you do that, VBA will ignore that line as a comment.

The following example shows the indent and comment:

Sub addNumbers()
   'Declare the variables
End Sub

Notice the color the VBA Editor uses to indicate the comment. Understanding the color coding in the VBA Editor can help you track what is going on with the code. As an example, code colored red indicates a syntax error.

You can see what the various colors denote by selecting Tools | Options. Once you are in the Options dialogue box, the Editor Format tab will show you what the various colors mean and, in addition, allow you to change the scheme.

Within a procedure, you will find two basic components: variables and methods. Quite simply, a variable is a piece of information stored somewhere in the computer’s memory. It could be a number, a letter, or an entire sentence. The location where it is stored in memory is known by the variable name. As an example, let’s say that you have a line of code like this:

number = 23

From that point on, every time the code refers to the variable name number, 23 will be put in its place. Of course, later on, you could assign a different value to the variable name of number. This example is a simplification.

In order for the variable to function properly, you should also declare what type of information is going to be kept by the variable. (The reasons for this will become obvious as we progress.) Table 6-1 lists variable types and how they are used.

To declare a variable, you use the keyword Dim. As an example:

Dim number As Integer

This declares that the variable name number will hold only data of type Integer. You will notice that we have not assigned a value yet (and in fact the editor will not allow us to do so). We only declared the name and type. At some future point in the code, we would have a line like this:

number = 32

NOTE

---

Remember that VBA is not case sensitive. The variable names number, Number, and numBer would all be recognized as the same name.

---

Table 6-1  Types of Variables

There are a couple of things you should be aware of when assigning values to a variable. First of all, a variable of type String must have its value enclosed in quotation marks. For instance, this would be a proper assignment:

Dim lastName as String
lastName = “Smith”

The second thing you must be aware of is that variables of type Date must have their values enclosed in # signs. A proper assignment would be as follows:

Dim thisDate as Date
thisDate = #10/08/03#

Chapter 5 discussed naming conventions in conjunction with the objects of your database. The same rules apply to variables. Remember, these are not requirements, but conventions adopted by most programmers. The prefixes associated with variables are listed in Table 6-2.

It is a good idea when naming objects of any kind to use descriptive names. This will have the benefit of making the code self-documenting. Using the date example, the proper way of declaring the variable would be as follows:

Dim datThisDate as Date

Getting back to the original procedure, addNumbers, let’s go ahead and add three variables as shown here:

Sub addNumbers()
   'Declare the variables
  Dim intNumber1 As Integer
  Dim intNumber2 As Integer
  Dim intSum As Integer
End Sub

You should be able to see where this is going. There will be two numbers entered and stored in variables intNumber1 and intNumber2. They will be added and stored in intSum.

Table 6-2  Variable Prefixes

Variant

There is one other variable type that hasn’t been discussed yet: the Variant. Depending on which programmer you talk to, it is either a powerful programming tool or an excuse for sloppy programming.

A Variant allows VBA to make its own decision as to what type of data it is holding. It is the default variable type and is used automatically if you leave the “as type” clause off the variable’s declaration. It uses the prefix of var.

As an example, let’s say we declare a variable as follows:

Dim varMyData

Because we left out the “as type” parameter, this defaults to the type Variant and will be the same as if you typed:

Dim varMyData as Variant

Let’s say you assign it as follows:

varMyData = “This is a Variant”

VBA will convert varMyData into a String type. If, later on, you assign the following:

varMyData = 12

VBA will now convert varMyData into type Integer.

As we progress, you will see situations where a variant could end up being a type other than what was wanted or, even worse, could result in an error. Many programmers also argue that too many variants take up too much memory “overhead” and slow the code down. So before you make the decision to work with variants, you want to carefully weigh the pros and cons.

As a beginner, it is best to approach variants with great caution and stay with one of the standard type declarations.

Many times you may want to declare a value that will not change. This type is called a Constant and is declared using the keyword Const in place of the normal declaration using Dim.

As an example:

Const conNumber1 As Integer
Const conDate As Date = #03/02/04#

Notice that you preface the variable’s name with con instead of the normal type. Also, when declaring a constant, you must assign it an initial value, or you will get a syntax error message when you leave the line.

You now know how to save data with the use of variables. But how do you get the information into a variable? Or read the information that is stored in them? In many ways, that will be what we spend the rest of this book examining. However, let’s begin by looking at a couple of simple techniques for testing our code.

One of the simplest techniques for getting information from the user into a variable is to use a built-in function called InputBox. This will give you a simple dialog box with a prompt.

To code this in our small example, you would enter the shaded lines shown here:

Sub addNumbers()
   'Declare the variables
  Dim intNumber1 As Integer
  Dim intNumber2 As Integer
  Dim intSum As Integer
  'Create InputBoxes to enter numbers
  intNumber1 = InputBox("Enter the first number")
  intNumber2 = InputBox("Enter the second number")
End Sub

Notice that for clarity’s sake, I also added a comment to indicate what I am doing.

The entry intNumber1 = InputBox is called an assignment statement, and it assigns the value the user enters to the variable. In this case two assignments have been created: one for intNumber1 and the other for intNumber2.

You can execute the code by selecting the Run button:

That brings up a dialog box like the one shown here for the first entry (a second one will appear for the second entry).

If you followed the preceding steps, the code will run and the variables will be set. However, there is no means of outputting the results. There are two ways to create an output for now. The first way is to add the code, shown with shading, here:

Sub addNumbers()
   'Declare the variables
   Dim intNumber1 As Integer
   Dim intNumber2 As Integer
   Dim intSum As Integer
   'Create InputBoxes to enter numbers
   intNumber1 = InputBox("Enter the first number") 
   intNumber2 = InputBox("Enter the second number")
   'Add numbers
   intSum=intNumber1 + intNumber 2
' Create an output
   Debug.Print "The numbers entered were " & intNumber1 & " and " & _
intNumber2
End Sub

If you run the code now, you will once again get the two dialog boxes. Notice that the output will appear in the Immediate window, located at the bottom of the editor environment:

This window is used as a temporary testing place for our code and will come in handy initially.

There are several issues worth discussing here. First of all, the line:

Debug.Print "The numbers entered were " & intNumber1 & " and " & _
intNumber2

This is called a concatenation because it brings various components together. The literal text is enclosed in quotation marks, and the variables are not. The two types are separated by the ampersand.

Also, notice that the line is broken with a space and then an underscore. When you break up a statement onto multiple lines, VBA requires this. Throughout this book, we may need to do that for typographical reasons. However, you can keep the code on one line.

Another way of formatting an output is to use a technique you first saw in Chapter 4. You could use a message box by replacing the output line with the following line of code:

MsgBox "The numbers entered were " & intNumber1 & " and " & intNumber2

This will produce output similar to the following:

You now have your first VBA program running.

Control Structures

Computer code only runs in a sequence. It first executes a statement and, if there is no error, moves on to the next statement. We call this a sequence structure. However, what happens if you want the next step in the sequence not to be the next line of code? You may need to transfer control of the program to a different block of code if a certain situation occurs. Or you may need a block of code to repeat until a situation occurs (or for a predefined number of times).

We call the mechanisms to accomplish this control structures. These structures can be divided into two very broad categories: decision structures and repetition structures. As you will see shortly, a decision structure allows the program to make decisions. The most common of them is the If…Then structure. However, VBA provides other possibilities: If…Then…Else, ElseIf, Select Case, and IIF.

A repetition structure goes through a block of code either a predefined number of times, or until something occurs to cause the program to break out of the loop.

VBA provides two broad repetition structures: For…Next and Do…Loop. Within those two structures, there are a number of variations.

Let’s consider the pseudocode for a morning routine. You may have one point where you write the following:

  If it is raining,
  Then I will take an umbrella.
  Else I will just go to my car.

The words in boldface are the VBA keywords necessary to make a decision.

You start a decision-making structure by doing a comparison of two entities. Is entity A equal to entity B? Is entity A greater than entity B? Is entity B true? These are called conditional expressions. The symbols used for them are listed in the following table.

Let’s take a look at a simple decision-making structure in a subroutine:

Sub ifTest()
    Dim intNum As Integer
    Dim strMessage As String
    intNum = 12
    If intNum > 10 Then
        strMessage = "The number is " & intNum
    End If
    Debug.Print strMessage
End Sub

There are a couple of things to notice in the code. The line that contains the conditional expression begins with If and ends with Then. Also, like other structures in VBA, the conditional If structure must end with an End statement, in this case End If.

If you run the preceding example in the Immediate window, you should see the results shown here:

There are a couple of problems here. The code works fine. But what happens if the number is not greater than 10? More importantly, since the number is “hard-coded” to the variable, it will always be greater than 10. Let’s take a closer look at these problems.

Within an If structure, you can have an alternative path by using an Else statement. Let’s modify the code we just used slightly:

Sub ifTest()
    Dim intNum As Integer
    Dim strMessage As String
    intNum = 9
    If intNum > 10 Then
        strMessage = "The number is greater than 10"
    Else
        strMessage = "The number is less than 10"
    End If
    Debug.Print strMessage
End Sub

Notice that in the preceding code, we assigned new values to the variable intNum and then added an Else statement. Since the Else statement is part of the If statement, it does not need a separate End.

When you run it now, since intNum is less than 10, the Else statement will be triggered, giving you the results shown here:

Of course, as we did earlier, you could have used the input box to enter the number and the message box to output it.

In some cases, you may want to test multiple conditions. Is intNum < 1 OR intNum >= 10? The keywords AND, OR, and NOT are called logical operators and will help you to evaluate multiple conditions.

In the case of an AND logical operator, both conditional expressions must be true in order for the If statement to be true.

The following table shows the possible logical outcomes using an AND logical operator.

With an OR connector, only one of the statements needs to be true for the If statement to be true. The following table shows the logical outcomes of an OR logical operator.

Finally, the NOT logical operator returns the opposite of what you expect. If the conditional statement is true (A is NOT equal to B), Not (A is NOT equal to B) returns false. However, if A does equal B, the statement is false and the result is true. (Don’t worry if you need to take a few moments to figure that out.)

We are going to get fancy with the ifTest subroutine we have been using. We are going to use multiple procedures using multiple If/Else statements. We also want to restrict the user to a range of possible entries between 1 and 15.

Change your code so that it looks as follows:

Option Compare Database
Option Explicit
Private intNum As Integer
––––––––––––––––––––––––––––-
Sub ifTest()
    Dim strMessage As String
    intNum = InputBox("Enter a number between 1 and 15", _ 
             "Testing the If structure")
    If intNum >= 1 And intNum <= 15 Then
        iftest2
    Else
        MsgBox "Sorry, the number must be between 1 and 15"
    End If
End Sub
–––––––––––––––––––––––––––––––––-
Sub iftest2()
    If intNum > 10 Then
        MsgBox intNum & " is greater than 10"
    Else
        MsgBox intNum & " is less than 10"
    End If
End Sub

Notice that you have to redefine intNum as a global variable. We have not discussed this yet. However, recall from the beginning of the chapter that anything placed in the general declarations area will affect the whole module.

Subroutine ifTest tests intNum to see if it is between 1 and 15. If it is, it calls ifTest2, which contains the code we used earlier. However, if it does not, it returns a message to the user. The call to another procedure is known as a procedure call.

Go ahead and run the code a few times. If the number is between 1 and 15, you should get back the less-than or greater-than message, depending on the value you choose. If the number is not between 1 and 15, you should get the following message:

You can combine several If structures using ElseIf. As an example:

Sub ifTest()
    Dim intNum as Integer
    intNum = 12
    If intNum = 1 Then
          Debug.Print “This is the lowest number”
    ElseIf intNum = 15 Then
          Debug.Print “This is the highest number”
    Else
          Debug.Print “The number is between 1 and 15”
    End If
End Sub

You can use as many ElseIf statements as necessary to perform as many conditional tests as necessary.

If you find yourself using a lot of ElseIf structures, you may want to consider the Select Case structure. It will result in easier-to-read code. Using the ElseIf example in the previous section, you would use Select Case as follows:

Sub ifTest()
      Dim intNum as Integer
      intNum = 2
      Select Case intNum
      Case 1
            Debug.Print “This is the lowest number”
      Case 15
            Debug.Print “This is the highest number”
      Case Else
            Debug.Print “The number is between 1 and 15” 
      End Select
End Sub

Of course you could add a case to match any situation. VBA will keep going through the structure until it finds a match with the value of intNum and then carry out the instructions. If it can’t make a match, it defaults to Case Else. The Case Else statement is optional, but I strongly recommend that you always use it to have all your bases covered.

If you have multiple cases in which you want to carry out the same set of instructions, you can use the syntax:

Case 1, 2, 3…

Or you could use

Case 1 To 4

IIF

There is one other structure that we will take a quick look at: IIF. This is referred to as the Immediate If. This is handy if you want to assign the final value to a variable because its syntax is self-contained. The correct syntax is

IIF(conditional test, value for True, value for False)

So, working code might look something like this:

strMessage = IIF(intNum > 10, “Number is greater than 10”, _
“Number is less than 10”)

The performance of the IIF structure is somewhat slow and rarely used by programmers in a larger programming project.

Now let’s turn our attention to the second type of control structure—looping.

You use loops when you need to have a block of code repeated a certain number of times or until an event of some sort happens. The number of times the code repeats can be controlled by a counter. This is called counter-controlled repetition. The second type is called sentinel-controlled repetition. We will look at both types.

The For…Next loop is an example of a counter-controlled repetition. Using either actual numbers, or variables, you can set the following components for the loop:

• Counter This is the heart of the loop. It tracks the number of times the loop has repeated.
  
• Start This is the starting number for the counter. It is rare, if ever, to set this to anything else but 1. (Occasionally you may use a different start number for mathematical calculations.)
  
• End This number marks the end of the loop, where you want the counter to stop.
  
• Step You can specify a number for the counter to increment with each loop. This part is optional.

Here is an example of the syntax for a For…Next loop:

Dim intCounter As Integer
For intCounter = 1 To 25
      …….
Next

To repeat, either the start or end numbers can be variables. Notice that the final line of the loop, unlike previous structures we have worked with, is not End, but Next. This instructs the counter to advance to the next number.

We also could have used the following syntax to declare the loop:

For intCounter = 1 To 25 Step 5

This forces the counter, in this case intCounter, to increment by five on every loop. As a result, the For loop will run five times.

It is not unusual to include If…Then structures within a loop. Let’s try the one that follows:

Sub forTest()
Dim intCounter As Integer
For intCounter = 1 To 10
    If (intCounter Mod 2) = 0 Then
       Debug.Print intCounter & " is an even number"
    Else
       Debug.Print intCounter & " is an odd number"
    End If
Next
End Sub

All right! I can hear my editor yelling at me that I snuck a couple of extra things in here. Let’s take a closer look at a few things in this code.

Here we see a For…Next loop that is instructed to make 10 passes. Within that, I wanted to test to see if a particular iteration was odd or even. I used an If…Then…Else structure to do that. Think about this: we have an If…Then…Else structure within a For…Next structure. When you have one structure within another, it is called a nested structure, which is very common in programming.

However, I asked the conditional statement to perform a calculation—specifically, division. I used the keyword Mod, which is short for modulus. Mod returns the remainder of the division (what is to the right of the decimal place). If you divide an even number by 2, the remainder is 0. So, if the counter is divided by 2 and there is a 0 remainder, it triggers the If condition. If the remainder is other than 0, the Else condition is triggered.

The Next just increments the counter.

If you run this subroutine in the Immediate window, you should see the result shown here:

Let’s now turn our attention to the other type of loop.

You use the Do loop as the sentinel-controlled loop. In other words, it will continue until a specific condition occurs (something equals something, or is greater than something, for example).

There are two variations of the Do loop—the Do While and the Do Until. We will look at each one.

Do While The Do While loop tests to see if a condition is true. If it is true, the statements in the loop execute. For instance, let’s look at the following subroutine:

Sub doTest()
    Dim intCounter As Integer
    Dim intTest As Integer
    intTest = 1
    intCounter = 1
    Do While intTest = 1
        Debug.Print "This is loop number " & intCounter
        If intCounter >= 5 Then
            intTest = 0
        End If
        intCounter = intCounter + 1
    Loop
End Sub

You will notice that the Do While loop is not as self-contained as the For…Next loop discussed earlier. In this case, you have to set up two variables, one for the counter and one for the conditional test. In this particular subroutine, I wanted the loop to end after the fifth pass.

Unlike the For loop from before, we are not running this based on a counter but, instead, until intTest changes to a value other than 1. I then nested an If…Then structure within the loop and tested the counter value. As soon as that value is equal to 5, the If structure will change the intTest value to 0 and will prevent the loop from running again.

Since it is testing for a condition rather than the number of iterations, you would not normally use a counter in a Do loop. In this case, I forced one with a second variable and incremented it as the last line of the loop. There are a variety of ways to do this.

Notice that this structure ends with the word Loop. Remember, we are not required to work with a counter here.

If you try running the Do While code, your Immediate window gives you these results:

However, here is a question: what happens if, for some reason, intTest never gets a value of 1? Will this Do loop ever run? (All right! Two questions!) The answer is no, it won’t. However, what happens if you need the loop to run at least once?

Sub ifTest()
    Dim strMessage As String
    intNum = InputBox("Enter a number between 1 and 15", _
                      "Testing the If structure")
    If intNum >= 1 And intNum <= 15 Then
        iftest2
    Else
       MsgBox "Sorry, the number must be between 1 and 15"
    End If
End Sub
–––––––––––––––––––––––––––-
Sub iftest2()
    If intNum > 10 Then
        MsgBox intNum & " is greater than 10"
    Else
        MsgBox intNum & " is less than 10"
    End If
End Sub

Notice, in the ifTest subroutine, there is a test to see if the user has entered a number between 1 and 15. However, if the user enters a number out of bounds, the program just stops, and you need to start it up again. We want to keep prompting the user until he or she enters a number in the correct range.

Change ifTest to look as follows:

Sub ifTest()
    Dim strMessage As String
    Dim intTest As Integer
    intTest = 1
    Do
        intNum = InputBox("Enter a number between 1 and 15",_
                          "Testing the If structure")
        If intNum >= 1 And intNum <= 15 Then
            intTest = 0
            iftest2
        Else
         MsgBox "Sorry, the number must be between 1 and 15"
        End If
    Loop While intTest = 1
End Sub

Notice that we are not using a counter of any sort here. Instead, what we are doing is testing for intTest. However, what is interesting here is where the test occurs. Unlike the previous example, we are testing at the end of the loop rather than the beginning. This has the effect of forcing the loop to run at least once, which can be very handy in situations such as this.

Do Until This is a subtle variation of what we just looked at. In a Do While loop, you run the loop while a condition is true. However, in a Do Until loop, you run the loop until a condition becomes true.

Using the first example of the Do While loop above, with a few changes, you can make it into a Do Until loop:

Sub doTest()
    Dim intCounter As Integer
    Dim intTest As Integer
    intTest = 1
    intCounter = 1
    Do Until intTest <> 1
        Debug.Print "This is loop number " & intCounter
        If intCounter >= 5 Then
            intTest = 0
        End If
        intCounter = intCounter + 1
    Loop
End Sub

Notice that here we are saying to run the loop until intTest does not equal 1. From that point on, everything else in this example is identical. You should see the same result as with the Do While loop.

Like the Do While loop, you can place the Until condition at the end of the structure after the word Loop. This forces the loop to run at least once.

We will be revisiting loop structures frequently throughout the book. Like variables, they are an integral part of most programming situations today.

Arrays

In many ways, the discussion of arrays is tied closely to variables. An array is a variable that contains multiple values. The number of values that the variable will hold must be decided and declared in advance. You are also going to learn how to reference values inside the array.

As I was writing this, my technical editor asked me, “What about dynamic arrays?” Well, as you will learn, the concept of a dynamic array is a slight falsehood. You still need to declare, in advance, the number of values it will hold. The only difference is that you declare them before the array is used, during the runtime of the program (as opposed to when you are writing the code).

You are also going to learn how to allocate memory properly so that your array does not take up too much room.

Each value in the array is called an element. Since an array variable has multiple elements in it, you need a way to pick them out or reference them individually.

You can do that by using a number called an index. Most of the time, the first element of an array is index number 0.

If you could look behind the scenes of an array of names, using variable name strName, it might look something like this:

strName          (0) “John Smith”
                 (1) “Jane Doe”
                 (2) “Rosemary Brown”
                 (3) “Anita LaScala”
                 (4) “Bob Gray”

Notice that even though the index numbers only go up to 4, this is a five-element array. Again, the first element usually begins at 0. (Note that as we progress through this chapter, we will see some exceptions.)

If you wanted to select Anita LaScala’s name out of the array for printing, you would use

Print strName(3)

Anita is in index position 3. However, just to confuse things a bit, it is the fourth element of the array. This is the source of many a problem in programming and, a little later on in the chapter, we will examine a way of possibly correcting for that.

VBA gives us two flavors of arrays:

• Static array The number of elements in the array, called the length of the array, is decided in advance and remains fixed.
  
• Dynamic array The length of the array is variable and not decided in advance.

A static array has a predetermined length and does not change. Since this is the simplest of the arrays, we will start here.

Declaring a static array is similar to declaring a variable, with one small exception:

Dim intMyScores(10) As Integer

You have to be careful how you view this. You are probably thinking that we just declared an array of 10 elements. However, what we really did was declare an array of 11 elements, with the first element being 0, and the last element being index number 10 (sometimes called the upper bound). See the difference?

The lower bound, or lowest index number, of this array is 0.

You need to do this in order to properly allocate the memory necessary to hold the array.

If you wanted to, you could declare multiple arrays in a procedure as follows:

Dim strName(6) As String, intMyScores(10) As Integer

By default, the first index value is 0, strings are initialized as empty, and integers are initialized at 0.

Let’s set up an example. In this procedure, you are going to create two For…Next loops. The first one will allow you to populate the array, and the second will print the contents of the array back to you. Here is the code:

Sub arrayTest()
 Dim i As Integer
 Dim intMyScores(10) As Integer
 For i = 0 To 10
    intMyScores(i) = InputBox("Enter number " & i, "Static Array Test")
 Next
 For i = 0 To 10
    Debug.Print "For array element " & i & " the number is " & _
 intMyScores(i)
Next
End Sub

Programmers like to use the lowercase i as the variable representing the index of the array. It is just a programming convention. Here we are asking it to serve double duty: it is the counter variable of the For…Next loop, and it is also the representation of the array’s index. Notice that you always refer to an array variable by the variable’s name followed by the element number, or index, in parentheses. In the example, we are using the loop variable of i to help us populate our array.

As a nice little extra, notice that I have a concatenation for the prompt in the input box. This will help you keep track of what element you are entering.

Your input box should look something like this:

After you enter the elements, the second For loop takes over and should give you the printout in the Immediate window:

With a static array, you declare the size of the array right in the code. In other words, it is done during design time.

There is one little problem with the preceding code example. You could somehow end up declaring an incorrect lower or upper bound. This could result in a runtime error. VBA helps you out a bit with two built-in functions: LBound(array name)and UBound(array name). This returns the bounds of the array.

You could change the syntax of the For loop in the previous code as follows:

For i = LBound(intMyScores) To UBound(intMyScores) 
    intMScores(i) = InputBox("Enter number " & i, "Static Array Test")
 Next

Many programmers consider the concept of a dynamic array in VBA a slight programming fiction. Essentially, it is still a static array, but you do not declare the size until the program is running. So the only issue is when the size is being declared.

You start off by declaring an empty array. For example:

Dim intMyScores() As Integer

Then you use a keyword, ReDim, to redeclare the size of the array while the program is running and it is known what the size will be.

Let’s redo our previous example to demonstrate a dynamic array:

Sub arrayTest()
 Dim i As Integer
 Dim intMyScores() As Integer
 Dim intArraySize As Integer
 intArraySize = InputBox("How many scores are you entering?", "Array Size")
 ReDim intMyScores(intArraySize)
 For i = 1 To intArraySize
    intMyScores(i) = InputBox("Enter number " & i, "Static Array Test")
 Next
 For i = 0 To intArraySize
    Debug.Print "For array element " & i & " the number is " & _
      intMyScores(i)
Next
End Sub

You will notice that we first declare intMyScores as an empty array. Then, as shown, we use the ReDim keyword to redefine it as a static array, with the upper bound being controlled by intArraySize, which is entered by the user.

From there on, we just use intArraySize to control the loops.

If you work through this example, you will see a contradiction. If you enter 5 as the number of scores that you want to input, you end up inputting 6 because the index starts at 0. This is a frequent mistake of beginning programmers.

With just a little bit of recoding, you can reassign the first element as 1 instead of 0. This would help keep things a bit more coordinated. Take a look at the following code:

Sub arrayTest()
 Dim i As Integer
 Dim intMyScores() As Integer
 Dim intArraySize As Integer
 intArraySize = InputBox("How many scores are you entering?", "Array Size")
 ReDim intMyScores(1 To intArraySize)
 For i = 1 To intArraySize
    intMyScores(i) = InputBox("Enter number " & i, "Static Array Test")
 Next
 For i = 1 To intArraySize
    Debug.Print "For array element " & i & " the number is " & intMyScores(i)
    Next
End Sub

If you run this in the Immediate window, you should see the result shown here:

Just as intArray is the upper bound of our array, the 1 is now the lower bound of the array. We then have the two For…Next loops starting at 1. (Remember, there is no element 0 now.)

There is another technique for starting the array at index position 1. In the general declarations section, you could type either Option Base 0 or Option Base 1. This will set the default lower bound of the arrays in the module. The only two options are 0 and 1.

What happens if you make a programming error and end up trying to access more elements in the array than you declared using either Dim or ReDim?

VBA will not catch this until the program is actually running, not during the coding. This sort of error is called a runtime error. If that happens, you will get a message like this:

If you click Debug, it will show you where the program thinks the offending line of code is.

Be very careful! The line it brings you to is the point at which the code “crashed.” However, many times the crash happens due to things going bad earlier in the program. For instance, you may have set a variable to a wrong size or reference; or perhaps Dim or ReDim declared a wrong value. Finding the problem may require a bit of detective work on your part.

What happens if you declare an upper bound of 10, but only put four elements in the array?

Remember, the 10 declares the position of the last element of your array. However, unlike trying to use more elements, there is no rule that states you have to use all of the positions. Only using four elements would not cause a problem of any sort. But there is a potential problem in the background.

As a beginner, this is not a huge concern, but a major issue in software design is resource control. A computer has a finite memory capacity. When you declare that your array will have 10 elements, VBA puts a reserve on memory. This means that the rest of the memory is sitting there unused. This is a tremendous waste of resources.

Your first impulse may be to use ReDim to change the size declaration. This causes, however, another rather serious problem. When we used ReDim in the earlier example, the array still had no elements. If you use ReDim with a populated array, it will wipe it out and start anew. Odds are that is not the most desirable solution.

VBA helps us out by combining ReDim with a second keyword as follows:

ReDim Preserve intMyScores(4)

The Preserve keyword reallocates the memory and retains the elements intact.

You will sometimes have situations in which you want to keep the array declared, but erase the elements within it. You can easily do that with the keyword Erase, as shown here:

Erase intMyScores

This clears the contents but keeps the declaration.

Depending on the type of array, different things may happen. If it is a numeric array, the elements are set to 0. However, the elements of a string array are set to “ ”. This is an empty string. If it is a Boolean array, each element is set to False.

How do you know if a variable is an array? VBA provides a handy little function to test a variable. Let’s take a look at the following code.

Sub arrayTest()
   Dim intScores1 As Integer
   Dim intScores2(4) As Integer
   Debug.Print "Is intScores1 an array: " & IsArray(intScores1)
   Debug.Print "Is intScores2 an array: " & IsArray(intScores2)
End Sub

This code returns the results shown here:

You can see that IsArray is a Boolean function. It returns a value of either True or False. In the first case above, the result is false because we did not declare it as an array. In the second case, it is true because we did.

Summary

We have covered a lot of territory here. We looked at variables, as well as the various types of structures used in VBA. Yet, even with all of this, our study of the basic VBA structures is far from complete. We will be adding to our discussion as we progress through this book.

In the remaining chapters of this section, we will study the VBA Editor and these structures in even greater detail.