Excel VBA 24-Hour Trainer (2015)

Part III
Beyond the Macro Recorder: Writing Your Own Code

Lesson 12
Working with Arrays

This lesson introduces you to arrays in VBA. As you will see, arrays are a very useful way to programmatically group and store many items of related data. After you've collected your array of data items, you can access any of the items individually, or access the group as a whole. Arrays can help you accomplish various tasks in a logical and efficient manner, which is important to remember when you find yourself faced with some tasks for which arrays are the only alternative.

What is an Array?

An array is like a variable on steroids. In addition to being a variable, an array also serves as a holding container for a group of individual values, called elements, that are of the same data type. You can populate the array yourself by specifying the known elements in your macro, or you can let VBA populate the array during the course of the macro if you don't know how many elements the array will end up containing.

The concept of arrays can be challenging to grasp at first, so a real-world analogy might help. Suppose you are a fan of classic movies, and you keep a library at home of perhaps 100 movies. Among those 100 movies are 5 that are your favorite classics. You can declare a variable named myFavoriteMovies, and create a String array with this macro:

Sub FavoriteMovies()

Dim myFavoriteMovies(1 to 5) as String

myFavoriteMovies (1) = "Gone With The Wind"

myFavoriteMovies (2) = "Casablanca"

myFavoriteMovies (3) = "Citizen Kane"

myFavoriteMovies (4) = "Sunset Boulevard"

myFavoriteMovies (5) = "Modern Times"

MsgBox myFavoriteMovies(3)

End Sub

Elements in an array are variables, and you can refer to a specific element by its index number inside the array. Because the array name is myFavoriteMovies, and the message box is referring to the third element in that array, when you run this macro, the message box displays Citizen Kane.

You have created an array that is a collection of your favorite classic movies. You can loop through each element in that collection—that is, each movie title—by referring to its index number inside the myFavoriteMovies array. The following macro shows how to display each movie title element in a message box:

Sub FavoriteMoviesLoop()

Dim myFavoriteMovies(1 To 5) As String

Dim intCounter As Integer

myFavoriteMovies(1) = "Gone With The Wind"

myFavoriteMovies(2) = "Casablanca"

myFavoriteMovies(3) = "Citizen Kane"

myFavoriteMovies(4) = "Sunset Boulevard"

myFavoriteMovies(5) = "Modern Times"

For intCounter = 1 To 5

MsgBox myFavoriteMovies(intCounter), , "Favorite #" & intCounter

Next intCounter

End Sub

If you would like to populate a range of cells with the elements of your array, the following macro demonstrates how to do that, listing the movie titles in range A1:A5:

Sub FavoriteMoviesRange()

Dim myFavoriteMovies(1 To 5) As String

Dim intCounter As Integer

myFavoriteMovies(1) = "Gone With The Wind"

myFavoriteMovies(2) = "Casablanca"

myFavoriteMovies(3) = "Citizen Kane"

myFavoriteMovies(4) = "Sunset Boulevard"

myFavoriteMovies(5) = "Modern Times"

For intCounter = 1 To 5

Cells(intCounter, 1).Value = myFavoriteMovies(intCounter)

Next intCounter

End Sub

VBA regards the array itself as one variable, but inside the array is a group of two or more elements that you can work with separately. You can, and often will, refer to each element by its index number, which is its position in the array. This way, you can pick a particular element in the array to work with based on its index number, or you can loop through all the index numbers one after the other, in case your project calls for every element to be worked on.

What Arrays Can Do for You

Arrays are often used for representing data in lists or tables, where each item in the list is of the same data type. Some examples might be a list of your friends' names, all of which would be String data types, or a table of your city's average daily temperatures by month, all of which might be Double data types. Arrays offer you the versatility of storing and manipulating data items through one array variable, which is much more efficient than assigning variables to every element in the array.

Say you want to count how many Excel workbook filenames reside in a particular folder. You don't know how many total files are in that folder, or how many of those total files are Excel files. With an array doing the job, you don't need any worksheet cells to store the filenames. Instead, you can programmatically compile into memory the count of Excel files, and the individual filenames too, all of which you can retrieve later in your macro if need be.

The previous arrays of movie titles are an example of one-dimensional arrays. In the macro named FavoriteMoviesRange, the five movies were listed in range A1:A5. VBA regards this as a one-dimensional array because the array elements stand by themselves in a table that is five rows deep and one column wide.

Many arrays you deal with will have more than one dimension. Figure 12.1 expands on this list of classic movies by adding a second column that lists the year each movie was released. This table is composed of five rows and two columns. You can create a two-dimensional String array by associating the movie title elements with their respective year of release elements.

Figure 12.1

The first item of business is to declare a String type variable for the array. The size of the array is specified with the variable, to include the span of rows and columns that make up the array. For example, with five rows and two columns, a variable named Classics is declared with the statement Dim Classics(1 To 5, 1 To 2) As String. The following macro loops through rows 1 to 5 in column A and rows 1 to 5 in column B. Each value in the array is stored in memory with two Integer type variables for collecting row and column data. Based on Figure 12.1, the message box returns 1941 because Classics(3, 2) returns the string value of the element that occupies the location of the array's third row and second column:

Sub TwoDimensionalArray()

Dim Classics(1 To 5, 1 To 2) As String

Dim intRow As Integer, intColumn As Integer

For intRow = 1 To 5

For intColumn = 1 To 2

Classics(intRow, intColumn) = Cells(intRow, intColumn).Value

Next intColumn

Next intRow

MsgBox Classics(3, 2)

End Sub

Declaring Arrays

You declare an array the same way you typically declare variables. The variable declaration starts with the Dim statement, followed by the array name and the data type. The array name ends with a pair of parentheses to indicate that it's an array with the count of elements, if known, placed inside the parentheses.

For example, the following statement declares an array named myDays, which is populated with all seven days of the week. Notice the data type is String, because weekday names are text values, such as “Sunday,” Monday,” and so on:

Dim myDays(6) As String

You can also declare arrays using the Public, Private, and Static keywords, just as you can with other variables, with the same results in terms of scope and visibility.

To declare an array as Public, place a statement at the top of your module. With the Public declaration at the top of your module, you can share an array across procedures. For example, if you run either of the following two macros, the array elements of Hello and Goodbye will be displayed in a message box:

Public MyArray(1) As String

Sub PublicArrayExample()

'Fill the array MyArray with values.

MyArray(0) = "Hello"

MyArray(1) = "Goodbye"

'Run the TestPublicArrayExample macro to display MyArray.

Run "TestPublicArrayExample"

End Sub

Sub TestPublicArrayExample()

'Display the values contained in the array MyArray.

Dim i As Integer

For i = 0 To UBound(MyArray, 1)

MsgBox MyArray(i)

Next i

End Sub

NOTE You may have noticed the UBound statement in the preceding macro. You read more about upper and lower boundaries in the upcoming section named “Boundaries in Arrays.”

A Static array is an array that is sized in the declaration statement. For example, the following declaration statement declares an Integer array that has 11 rows and 11 columns:

Dim MyArray(10, 10) as Integer

The Option Base Statement

When learning arrays, it's common for some head-scratching and confusion to accompany the concept of zero-based numbering. In the declaration statement Dim myDays(6) As String, you might wonder why the array shows the number 6 in parentheses, when there are seven days in a week.

In zero-based numbering, the first element of any array is represented by the default number of 0. The second element is represented by the number 1, and so on. That is why an array of seven weekday elements is represented by the number 6 in the statement Dim myDays(6) As String.

VBA does provide a way for specifying that the first element of the array be number 1, which is more intuitive for most people. You can do this by placing the statement Option Base 1 at the top of the module.

NOTE Most advanced-level VBA programmers exclusively use the default zero-based numbering style. I recommend that you resist the temptation to go the Option Base 1 route in your learning progression. Sooner or later, you will inherit array code that will be zero-based, and you'll be glad you became accustomed to that popular style from the get-go.

Here's a visual look at zero-based numbering in action. Figure 12.2 shows five text elements that you might manually place into an array macro.

Figure 12.2

Note the element index numbers starting with the default of 0. In the following macro, the array named FamilyArray is populated in the order of the pictured elements. Further, a variable named FamilyMember is assigned the element 2 item, which is actually the third item in the list of names because the list starts at number 0. Therefore, when the MsgBox FamilyMember command is executed, Tom is displayed in the message box because Tom occupies the element 2 position in the array named FamilyArray:

Sub ArrayTest()

Dim FamilyArray() As Variant

Dim FamilyMember As String

FamilyArray = Array("Bill", "Bob", "Tom", "Mike", "Jim")

FamilyMember = FamilyArray(2)

MsgBox FamilyMember

End Sub

To test this concept a bit further, enter the statement Option Base 1 at the very top of the module. When you run the ArrayTest macro again, you see that FamilyArray(2) returns Bob, because the array elements were counted starting at base number 1.

NOTE It's a fair question to ask why VBA uses zero-based numbering in the first place. Most other programming languages use zero-based numbering for their arrays because of the way arrays are stored in memory. The topic is rather complicated, but in simple English, the subscript (the numbers in the parentheses following the array's variable name) refers to an offset position in memory from the array's starting position. Therefore, the first element has a starting position of 1, but the array's subscript is translated into the offset memory address of 0. The second element is offset at 1, and so on.

Boundaries in Arrays

Arrays have two boundaries: a lower boundary, which is the position of the first data element, and an upper boundary representing the count of elements in the array. VBA keeps track of both boundaries' values automatically, with the LBound and UBound functions.

NOTE When you declare an array, you can specify only the upper index boundary. In the example, you have Dim myDays(6) As String but it could have been written as Dim myDays(0 to 6) As String. The 0 to does not need to be present because the lower index boundary is always assumed to be 0 (or 1 if Option Base 1 has been stated at the top of the module). Under the default setting of Option Base 0, the number you include in the declaration (which was 6 in this example) is the upper index number of the array, not the actual number of elements.

Here is an example to demonstrate the LBound and UBound functions in practice. In this example, you fill an array with a number of cell addresses, and the macro enters the word Hello in that array of cell ranges:

Sub ArraySheets()

'Declare your variables

Dim sheetName As Variant, i As Integer, TargetCell as Variant

'Populate the array yourself with the known cell addresses.

TargetCell = Array("A1", "B5", "B7", "C1", "C12", "D13", "A12")

'Loop from the lower boundary (the first array element)

'to the upper boundary (last element) of your sheetName array.

For i = LBound(TargetCell) To UBound(TargetCell)

Range(TargetCell(i)).Value = "Hello"

'Continue looping through the array elements to completion.

Next i

'End the macro.

End Sub

Declaring Arrays with Fixed Elements

Early in this lesson you saw this array declaration:

Dim myDays(6) As String

The ultimate objective of that declaration was to build an array containing the seven days of the week and to transfer that list into range A1:A7, as shown in Figure 12.3.

Figure 12.3

The macro to do that could look like the following one named ArrayWeekdays. Characteristics of a fixed array include a set of elements that remain constant, such as days of the week, where there will always be seven and their names will never change. The WEEKDAY function returns an integer from 1 to 7 that represents a day of the week. For example, 1 represents Sunday, 2 represents Monday, and so on. If you enter the function =WEEKDAY(5) in a cell, and custom format the cell as DDDD, the cell displays Thursday.

The comments in the code explain what is happening, and why:

Sub ArrayWeekdays()

'Declare the array variable for seven elements (from 0 to 6).

Dim myDays(6) As String

'Declare an Integer type variable to handle the seven indexed elements.

Dim intDay As Integer

'Start to loop through each array element starting at the default 0 lower boundary.

For intDay = 0 To 6

'For each array element, define the myDays String variable

'with its corresponding day of the week.

'There is no such thing as "Weekday 0", because Excel's Weekday function

'is numbered from 1 to 7,so the "+ 1" notation adds 1 to the intDays Integer

'variable which started at the lower bound of 0.

myDays(intDay) = Format(Weekday(intDay + 1), "DDDD")

'Cells in range A1:A7 are populated in turn with the weekday.

Range("A" & intDay + 1).Value = myDays(intDay)

'The loop is continued through to conclusion.

Next intDay

'End of the macro.

End Sub

Declaring Dynamic Arrays with Redim and Preserve

Unlike an array with a known fixed set of elements, some arrays are built programmatically during the macro. These arrays are called dynamic. Earlier you read about populating an array with the count of Excel workbook files that exist in a folder. In that case you'd have a dynamic array because the file count is subject to change; you would not know ahead of time what the array's size will be. With a dynamic array, you can create an array that is as large or as small as you need to make it.

To attack that problem of an unknown count of elements, you can change the size of an array on the fly with a pair of keywords called ReDim and Preserve. The ReDim statement is short for redimension, a fancy term for resizing the array. When ReDim is used by itself to place an element in the array, it releases whatever data was in the array at the time, and simply adds the element to a new empty array.

The Preserve statement is necessary to keep (preserve) the data that was in the array, and have the incoming element be added to the existing data. In VBA terms, ReDim Preserve raises the array's upper boundary, while keeping the array elements you've accumulated.

The following macro named SelectedWorksheets demonstrates ReDim Preserve in action. The purpose of the array in this example is to collect the names of all worksheets that are concurrently selected, such as when you press the Ctrl key and select a few worksheet tabs.

The comments in the code explain what each line of code is doing, so you can get a feel for how to populate a dynamic array and display its elements (the worksheet names) in a message box:

Sub SelectedWorksheets()

'Declare the array variable for an unknown count of elements.

Dim WhatSelected() As Variant

'Declare a variable for the Worksheet data type.

Dim wks As Worksheet

'Declare an Integer variable to handle the unknown count of selected worksheets.

Dim intSheet As Integer

'Start to loop through each selected worksheet.

For Each wks In ActiveWindow.SelectedSheets

'An index array element is assigned to each selected worksheet.

intSheet = intSheet + 1

'This macro is building an array as each selected worksheet is encountered.

'The Redim statement adds the newest selected worksheet to the growing array.

'The Preserve statement keeps (preserves) the existing array data,

'allowing the array to be resized with the addition of the next element.

ReDim Preserve WhatSelected(intSheet)

'The corresponding worksheet's tab name is identified with each selected sheet,

'and placed in the "WhatSelected" array for later retrieval.

WhatSelected(intSheet) = wks.Name

'The loop is continued to completion.

Next wks

'Looping through each element in the "WhatSelected" array that was just built,

'a message box displays the name of each corresponding selected worksheet.

For intSheet = 1 To UBound(WhatSelected)

MsgBox WhatSelected(intSheet)

Next intSheet

'End of the macro.

End Sub

Try It

In this lesson you verify whether a certain string element is part of an array. You test if a certain string element is in an array. At the end of the macro, you show a message box to confirm that the string element either was or was not found to exist in the array.

Like the example earlier in this lesson, say you have this list of names:

· Bill

· Bob

· Tom

· Mike

· Jim

Now, say you want to test whether a certain string element is in that array, which in this example you enter into a worksheet cell. Enter a good-looking name like Tom into cell A1 of Sheet1. Put the list of names in an array, and test to see whether “Tom” is among the elements in that list.

Lesson Requirements

To get the sample workbook you can download Lesson 12 from the book's website at www.wrox.com/go/excelvba24hour.

Step-by-Step

1. Open Excel and add a new workbook.

2. Press Alt+F11 to get into the Visual Basic Editor.

3. From the VBE menu, select InsertModule.

4. In the new module, type the name of your macro:

Sub TestArray

5. Press the Enter key, which causes Excel to place a set of parentheses after the TestArray macro name and also creates the End Sub statement. Your macro so far looks like this:

6. Sub TestArray()

End Sub

6. For the first line of code, establish that Sheet2 is VeryHidden, as an example to demonstrate the result of an element being found, or not found, in an array. If the element is found, Sheet2 will be unhidden:

Worksheets("Sheet2").Visible = xlSheetVeryHidden

7. For the second line of code, declare a variable for the array of names you'll be creating, and name the variable myArray. For the next line of code, assign the variable name to the array. In this case, you know what the list of names contains so you can build the array yourself by simply entering the individual names inside the parentheses. The two lines of code look like this:

8. Dim myArray As Variant

myArray = Array("Bill", "Bob", "Tom", "Mike", "Jim")

8. The next two lines of code show the String type variable to represent the string element you are attempting to verify, and then code to assign the string to that variable. The String variable, named strVerify, refers to a name you would enter into cell A1 of Sheet1 to test the macro. For example:

9. Dim strVerify as String

strVerify = Worksheets("Sheet1").Range("A1").Value

9. You need to declare two more variables. One of these variables is an Integer type variable, which helps you loop through each of the five elements in the array. The other variable is a Boolean data type, which helps to characterize as True or False that the string in cell A1 of Sheet1 is among the elements in the array:

Dim i as Integer, blnVerify as Boolean

10.Enter Tom in cell A1 of Sheet1.

11.Now, to see whether “Tom” exists in the array, loop through each element and compare it to the String variable. If there is a match, exit the loop and alert the user by unhiding Sheet2. If the string variable is not found, let the user know that as well, and keep Sheet2 hidden:

12. For i = LBound(myArray) To UBound(myArray)

13. If strVerify = myArray(i) Then

14. blnVerify = True

15. MsgBox "Yes! " & myArray(i) & " is in the array!", , "Verified"

16. Worksheets("Sheet2").Visible = xlSheetVisible

17. Exit For

18. End If

19. Next i

20. If blnVerify = False Then _

MsgBox strVerify & " is not in the array.", , "No such animal."

12.Putting it all together, the macro looks like this:

13. Sub TestArray ()

14. 'Establish that Sheet2 is VeryHidden.

15. Worksheets("Sheet2").Visible = xlSheetVeryHidden

16. 'Declare and assign a Variant type variable for the array.

17. Dim myArray As Variant

18. myArray = Array("Bill", "Bob", "Tom", "Mike", "Jim")

19. 'Declare and assign a String type variable for the element being evaluated.

20. Dim strVerify as String

21. strVerify = Worksheets("Sheet1").Range("A1").Value

22. 'Declare the Integer and Boolean data type variables.

23. Dim i as Integer, blnVerify as Boolean

24. 'Loop through each element starting with the first one (LBound)

25. 'and continue as necessary through to the last element (UBound).

26. 'If "Tom" is found, exit the loop and alert the user.

27. 'If "Tom" is not found, alert the user of that as well.

28. For i = LBound(myArray) To UBound(myArray)

29. If strVerify = myArray(i) Then

30. blnVerify = True

31. MsgBox "Yes! " & myArray(i) & " is in the array!", , "Verified"

32. Worksheets("Sheet2").Visible = xlSheetVisible

33. Exit For

34. End If

35. Next i

36. If blnVerify = False Then _

37. MsgBox strVerify & " is not in the array.", , "No such animal."

38. 'End the macro.

End Sub

REFERENCE Please select the videos for Lesson 12 online at www.wrox.com/go/excelvba24hour. You will also be able to download the code and resources for this lesson from the website.