How to Create and Manage Multidimensional Arrays Using Visual Basics

In this article we will get more detailed into arrays, how they work, creating multidimensional arrays, and looping through them to work with their data. Arrays are beneficial when you need to store several values into one variable. Instead of creating 10 variables, you can create one and store those 10 values in each index.

Declaring Single Dimensional and Multidimensional Arrays

If you've ever worked with any other programming languages, you are probably familiar with single dimensional arrays. Single dimensional arrays are the most common, so they are the ones often seen in code examples. In some cases, you'll also need to know how to work with multidimensional arrays.

Single dimensional arrays carry one dimension of values. The following code shows you how to create a single dimensional array.

Dim mystring(0 to 2) As String

The data type is set to string and the variable can contain one dimension of variables assigned to indexes 0 to 2. In most of your VBA code, you'll use single dimensional arrays.

In cases where you need to perform analytics or two dimensions of data, you use a multidimensional array. The following code shows you how to create a multidimensional array.

Dim mystring(0 to 1, 0 to 3) As String

In the above code, a two dimensional array is created. The first dimension contains indexes 0 and 1. The next dimension contains indexes from 0 to 3. When you use a two dimensional array, think of the output with an x and y axis. The array fills out the values in a row and column format. The x axis moves from left to right, and the y axis moves from the top to the bottom. Using the two dimensional array above, the output would look like something similar to the following.

1 2 3

4 5 6

In the matrix, there are two rows and three columns, which match the number of indexes in the array.

Filling the array would look like the following.

mystring (0, 0) = 1

mystring (0, 1) = 2

mystring (0, 2) = 3

mystring (1, 0) = 4

mystring (1,1) = 5

mystring (1, 2) = 6

As you can see, multidimensional arrays can get very complicated. In most applications and throughout this article, we will stick to single dimension arrays.

Propagating Arrays and Retrieving Values

Once you create an array, you want to propagate it with values. In most cases, you propagate an array using a loop. For loops are the most commonly used. You can also statically assign them, which we did in the previous section. In this section, we'll show you how to create loops to propagate values in a single dimensional array.

Take a look at the following example.

Dim myarray (0 to 3) As Integer

For j = 0 to 3

myarray(j) = j

In the code above, we create an integer array. We then have a For loop that loops from 0 to 3 (the number of indexes in the array) and adds the value of j to the array's index. Notice that instead of a statically defined integer in the array variable, we use j to dynamically loop through the array and reference the index. The first loop references index 0, the second loop references the index 1, the third references index 2 and the final loop references index 3.

With For loops, you can dynamically assign values when you don't know what those values should be until the code runs.

In many cases, you want to fill an array with data from a spreadsheet. The following code shows you how to retrieve data from a sheet's first row of data.

Dim myarray (0 to 3) As Integer

For j = 0 to 3

myarray(j) = Cells(0, j) .Value

In the above code, the data that propagates the array is from the first row of cells. Since the array only loops 4 times, the first four cells add data to the array.

You can also use the For loop to retrieve values in the same way. With the array propagated, you can then retrieve the values and place them in your spreadsheet. You can also place the data in formulas or charts. The following code shows you how to take the data you added to the original array and add it to an alternative set of cells.

For j = 0 to 3

Cells(3, j) .Value = myarray(j)

In the code above, we take the originally created myarray variable and loop through each element to add the data to the third row in the spreadsheet. The logic is reversed in this loop since we want to propagate the data in a group of cells rather than retrieve their data.

You might also need to propagate and retrieve data from a multidimensional array. Since Excel is a matrix of data, it's common to propagate cells in a specific row or column. The following code shows you how to retrieve data from a dynamic range of cells.

Dim myarray (0 to 3, 0 to 2) As Integer

For j = 0 to 3

For k = 0 To 2

myarray(i, j) = Cells(j,k).Value

Next k

The above code has an embedded For loop. An embedded For loop loops through the first array three times since the second dimension contains three indexes. When the embedded loop finishes, the outer loop iterates and moves to the second index. This index is then iterated three times with the embedded loop. This continues until all values in the matrix are propagated.

Similar to a single dimensional array, you also need to retrieve values once you propagate a multidimensional array.

The following code is an example of using a multidimensional array to fill cells in a spreadsheet.

For j = 0 to 3

For k = 0 To 2

Cells(j,k).Value = myarray(i, j)

Next k

Just like the previous section, we retrieve data from the myarray variable and add it to a worksheet's cells. The cells used are determined by the j and k values, which are 0 to 3 and 0 to 2.

Using Dynamic Arrays

In all of the above examples, you had to define an array and propagate it based on the number of indexes you assigned to it. The ReDim command re-dimensions an array that you've already defined. You can even preserve the data within the array, so you don't lose any of the information you've already stored.

The following code is an example of re-dimensioning an array.

Dim myarray() As Integer

ReDim myarray (0 to 2) As Integer

myarray (0) = 0

myarray (1) = 1

myarray (2) = 2

ReDim Preserve myarray (0 to 3) As Integer

myarray (3) = 3

In this code, we re-dimensioned the array twice. The original declaration of the array declares an array with no indexes. We then re-dimension the array with 3 indexes. Since we haven't added any values to the array, we don't need to preserve any of its values. With the array re-dimensioned with 3 indexes, we can now add three values to it. We add the values 0, 1, and 2.

We later determine that we need to have 4 values in the array. Notice that we then use the ReDim command again, except this time we use the Preserve keyword. This statement ensures that the original data stored in the array is kept intact as the compiler adds another index to the array and re-allocates memory for it.

Interested in learning more? Why not take an online Visual Basic for Excel course?

After the re-allocation, you can now add your new value to the new index. In this example, we add the value 3 to the new index.

You'll use the ReDim statement when you create arrays and later need to re-dimension it based on new user input.

Passing Arrays in Functions

When you work with arrays, you will need to pass them to functions. The functions use the data to manipulate it and either return values or output the results to a spreadsheet or chart.

When you pass an array, you can either pass it by value or by reference. There is a distinct difference in the way you pass array variables. When you pass an array by value, you pass only the value. The value is then stored in a function's local variable. Once the function is finished, the local variable is destroyed and it no longer exists.

When you pass an array as a reference, you pass the actual memory location for the array. As you change the data, it changes the array values globally.

Let's take a look at the difference.

Sub MyArrayFunction()

Dim myarray (0 to 3) As Integer

myarray(0) = 1

ChangeArray (ByVal myarray)

MsgBox myarray(0)

End Sub

Sub ChangeArray (ByVal myarray As Integer)

myarray(0) = 2

End Sub

In this example, we've passed the array as a value. The end result is that the MsgBox function displays 1. The reason is because we only passed the value of the array and not the array itself. Since the value is destroyed in the ChangeArray local variable, the value remains the same in the main MyArrayFunction subroutine.

Now, let's see what happens when we pass by reference.

Sub MyArrayFunction()

Dim myarray (0 to 3) As Integer

myarray(0) = 1

ChangeArray (ByRef myarray)

MsgBox myarray(0)

End Sub

Sub ChangeArray (ByRef myarray As Integer)

myarray(0) = 2

End Sub

Notice that we changed the ByVal statement to ByRef. Now, the result is that the message box displays 2 to the user. This is because we passed the actual array and not just its value. This is significant when you want to code functions that change variables globally and not just by value locally.

In this article, we discussed arrays in more detail. Most programs have at least one array coded in them, so you will need to know arrays well to code extensively in Visual Basic.

Classes, Records, and Collections

Classes are the main objects in any OOP program. Classes are internal to some frameworks. For instance, if you want to code in Microsoft .NET, you work with the framework's internal library classes. You can also make your own. When you work with VBA, you'll want to focus on classes to make your applications scalable. Classes are reusable and can plug into other programs, so you can save time in future coding projects with your classes.

Class Modules

We've worked with Visual Basic modules, but these modules have been basic functions and subroutines. Class modules are much more scalable and common in larger VBA projects. You can create as many class modules that you need, but recall from chapter 6 that you should design your application properly. Classes represent components in your application.

Classes are also referred as custom objects, so you'll see these two terms used interchangeably. In most languages, you manually type your classes, but you can create an empty class using the Visual Basic Editor in Excel.

With the VBE open, click the Insert menu item and then click "Class Module." The VBE creates a file that you can see listed in the left project explorer panel. In the properties window, give the class a name. We'll use an Employee class as an example, so name the new class file Exployee.

Once the file is created, double-click it in the project explorer to open the code editor. The file itself is your class. If you're familiar with other languages, you know that you need a class declaration. You don't need one in VBA. The class module file itself is your class. All methods and properties for your class must be created in this file.

Classes are composed of properties and methods. Since we're using an Employee class as an example, you can imagine the properties and methods that you need for your class. Each application requires its own custom object properties and methods, but an Employee class has some basic features that are universal to every applications. An employee has a name, a salary, and an associated department. Remember that properties are the "nouns" or attributes that define a class. The following code shows you how to define the first Name property.

Private pName As String

Public Property Get Name() As String

Name = pName

End Property

Public Property Let Key(p As String)

pName = p

End Property

In the above code, we've created a writable and readable property. We create a private variable indicated with the "p" prefix. You don't have to use the "p" prefix, but it makes the process of identifying a private variable from a public on easier.

Properties are created with the Get and Let statements. The Get statement tells the compiler that outside code can retrieve the value of the Name property. The Let statement tells the compiler that outside code can assign a new value to the property. If we removed the Get property, you would only be able to assign a name to the property but never read it. If we removed the Let statement, you'd be able to read the value of the Name property but never assign it a value.

Just like any other variable, you must assign a name to the property variable and data type. Both the Let and the Get statement assign data types and a name to the properties. Since both property statements manipulate the same private variable, they must use the same data type.

Let's take a look at what the code would look like for all three Employee properties we mentioned: Name, Salary, and Department.

Private pName As String

Private pSalary As Long

Private pDepartment As String

Public Property Get Name() As String

Name = pName

End Property

Public Property Let Key(p As String)

pName = p

End Property

Public Property Get Salary() As Long

Salary = pSalary

End Property

Public Property Let Key(p As Long)

pSalary = p

End Property

Public Property Get Department() As String

Department = pDepartment

End Property

Public Property Let Key(p As String)

pDepartment = p

End Property

The above code defines three properties. Notice that each data type matches the assigned variable.

With class properties created, we can now create our Employee methods. Methods are the "verbs" that you use to create actions for your class. For an Employee class, you could have several methods. For instance, the Employee could have a salary increase, so you would need to increase the Salary returned from the class properties. The type of pay raise could be dependent on title or other circumstances. All of this logic would be placed in your Employee class method.

Let's add a simple method for the Employee class.

Private pName As String

Private pSalary As Long

Private pDepartment As String

Public Property Get Name() As String

Name = pName

End Property

Public Property Let Key(p As String)

pName = p

End Property

Public Property Get Salary() As Long

Salary = pSalary

End Property

Public Property Let Key(p As Long)

pSalary = p

End Property

Public Property Get Department() As String

Department = pDepartment

End Property

Public Property Let Key(p As String)

pDepartment = p

End Property

Sub PayRaise(amount as Long)

pSalary = amount

End Sub

In the code above, we added a PayRaise subroutine that changed the salary amount for the employee. It takes an amount as a parameter, so you can dynamically assign the new salary from the application.

With your class created, now we can use it in other modules. Before you can use your class, you must instantiate it. We discussed class instantiation in chapter 6, but let's take another look at how you can use this new class in other modules.

Dim myclass As Employee

myclass = New Employee()

With the class instantiated, you can now use its properties. In the above code, we first defined a variable for the employee and then used the New statement to instantiate the class. When you instantiate the class, you can use the new variable as the Employee class and implement its methods and properties.

With the class instantiated, let's assign values to the properties and change the employee's salary.

Dim myclass As Employee

myclass = New Employee()

myclass.Name = "John"

myclass.Salary = 50000

myclass.Department = "Sales"

myclass.PayRaise(60000)

MsgBox myclass.Salary

In the code above, we instantiated the class using the New statement and assign it to a variable named myclass. Notice that we assigned values to the class properties using the same syntax we discussed in chapter 6. You use the variable name with a dot and then the property name.

We then call the PayRaise method. Notice that we assign the Salary property to the value 50000. We then use the PayRaise method to change the value of Salary. We then use the MsgBox function to see the change in the employee's salary. Even though we initially set the salary to 50000, we changed it using the method. When we call the Get function in the Employee class, it retrieves the current value for the private variable, which is 60000 after the method runs.

Using Collections

We discussed arrays, which let you contain one value with each index. A collection lets you contain a class object within an index. For instance, suppose you want to add 10 new employees to the system. You can use a collection to hold a list of classes. Using arrays to hold class data is too complicated. However, using a collection is easy.

We have our Employee class created and populated it with data. Let's take a look at how we can use a collection to hold the class object.

Dim myclass As Employee

myclass = New Employee()

myclass.Name = "John"

myclass.Salary = 50000

myclass.Department = "Sales"

myclass.PayRaise(60000)

MsgBox myclass.Salary

Dim people As Collection

Set people = New Collection

people.Add Item:= myclass

In the code above, we instantiated a class variable again. We then create a collection variable. Notice that the collection variable uses the Add method with the Item and class variable assignment. This syntax is specific to the collection class variable. It's the syntax needed to add a class object to the collection. Instead of saving just the values of the class properties, we store the class variable itself. This code provides a much cleaner, efficient way to store values when they are associated to a class object.

With the collection variable filled with items, you will need to extract its data at some point. We only added 1 item, but you can add 10 or 100 variables to the collection. The best way to manage iterating through each collection item is using a loop. You can use the For Each loop to work through each item in the collection. Te following code shows you how to iterate through a collection.

Dim get_people As Employee

Set people = New Employee

For Each get_people In people

If get_people.Age = 38 Then

Debug.Print get_people.Name

Exit For

End If

We're using the same collection variable and created the get_people Employee variable to store each collection item in the people variable.

Collections and classes work hand-in-hand when you work with large applications that have several components. As you work more with VBA, you'll work with more classes and find that they are the best for scalability and large application design.