Visual Basic/Arrays

Introduction

Arrays are extremely useful in Visual Basic, and are present in many other programming languages. Arrays are used to group similar data together, to make it easier to search and sort through this data. The best way to look at an array is to show you one. Say you want to make a phone book for your computer. Rather than make a new variable with a new name whenever you want to add a name, you can make an array. Arrays consist of the variable name and a subscript, which is put in parentheses for computer use. So, if you want to make a phone book of say, 100 people, rather than make a bunch of variables named 'person1, person2, person3....person100', you may use an array. All you have to do is dimension the array (see above). So now those 100 variables turn into person(1 to 100) in a dim statement. Now you may be asking, what do I do now? Each object stored in an array is called an element. What you have done is created an array which can hold 100 elements. If you want to print the 32nd person in your phone book, you simply would type:

 Debug.Print Person(32)

Use of Arrays

Arrays have far more uses other than just making phone books. But before we get into specific uses, let's look at some of the basic techniques used when working with arrays. The first thing you may want to do is learn how to easily fill an array. Filling an array with random numbers is a popular technique for starting off programs, and testing other techniques such as sorting. To fill an array, a simple For Loop may be used.

  Option Explicit
  Dim lngIndex as Long
  Dim strArray(0 to 9) as String ' Create an array with 10 values
  Dim intCounter as Integer
  intCounter = 1
  For lngIndex = LBound(strArray) to UBound(strArray)
     intCounter = intCounter + 1
     strArray(lngIndex) = intCounter
  Next lngIndex

The above shows an example of read-write iteration over an array. For Each loop can be used for read-only iteration over an array:

  Dim MyArray(9)
  For i = 0 To 9
    MyArray(i) = i
  Next
  For Each Item In MyArray
    Debug.Print Item
  Next

Indices

Per default, array indices start at 0, unless "Option Base 1" declaration is used. Without the declaration used, an array declared as "Dim MyArray(5)" has 6 elements: 0, 1, 2, 3, 4, 5.

The index range of an array has to be a continuous sequence of integers, including negative numbers. Thus, the following is possible:

  Dim MyArray1(-5 to 5)
  Dim MyArray2(-10 to -5)
  For i = LBound(MyArray1) to UBound(MyArray1)
     MyArray1(i) = i + 5
     Debug.Print i, MyArray1(i)
  Next
  For i = LBound(MyArray2) to UBound(MyArray2)
     MyArray2(i) = i + 5
     Debug.Print i, MyArray2(i)
  Next

Size

The size of an array can be obtained using LBound and UBound as follows:

  Dim MyArray1(-5 to 5)
  Dim MyArray2(10-1)
  Size1 = UBound(MyArray1) -  LBound(MyArray1) + 1
  Size2 = UBound(MyArray2) + 1 'For a same array with indexing starting at zero
  Debug.Print Size1, Size2

Keywords: length, item count, element count.

Dynamic Arrays

An array with the number of elements specified upon its declaration, as in Dim Names(0 to 9), is a static one: the number of its elements cannot be changed in runtime. By contrast, an array declared without the number of elements, as in Dim Names(), is a dynamic array, and its number of elements can be changed using ReDim. To preserve the element content of the array when using ReDim, Preserve keyword has to be used after ReDim.

Say you have a phone book program running and it has an array of your friends' names, like this:

  Option Explicit
  Dim StrNames(0 to 2) As String
  StrNames(0) = "Alec"
  StrNames(1) = "Jack"
  StrNames(2) = "Pie"

But say you want to add more friends to your phone book, when a button is clicked, for example. What do you do? You can define a dynamic array instead:

  Dim Names() As String

This array initially has no elements. You can add more elements, by using the ReDim keyword:

  ReDim Names(0 to 1) As String

This would create two new elements. You can assign names to these elements in the same way as before:

  Names(0) = "Alec"
  Names(1) = "Jack"

If you want to add more entries then use, for example:

  ReDim Names(0 to 2) As String

But you'll end up losing all the old records! If you want to keep them, you must also use the Preserve keyword:

  ReDim Preserve Names(0 To 3) As String
  Names(1) = "Eugene H / Orage" 'Whoa this person's name is too long to remember

To iterate AKA loop over the elements of a dynamic array, you have to use LBound and UBound functions or For Each loop, as described at #Use of Arrays.

To add an element to an initialized dynamic array, you can proceed as follows, using UBound function:

  ReDim Preserve Names(0 To UBound(Names) + 1) As String
  Names(UBound(Names)) = "my new friend"

However, UBound does not work with an un-initialized dynamic array, so the following would cause a run-time error:

  Dim MyArray() As String
  Debug.Print UBound(MyArray)

One way to avoid this run-time error is to keep a flag:

  Dim ArrayIsEmpty As Boolean
  Dim MyArray() As String
  
  ArrayIsEmpty = True
  
  If Not ArrayIsEmpty Then
      ReDim Preserve MyArray(0 To UBound(MyArray) + 1) As String
  Else
      ReDim Preserve Names(0 To 0) As String
      ArrayIsEmpty = False
  End If
  Names(UBound(Names)) = "my new friend"

Another way to avoid the run-time error is to catch the error created by the use of "UBound" on an uninitialized array:

  Dim MyArray() As String
 
  NumberOfElements = 0
  On Error Resume Next
  NumberOfElements = UBound(MyArray) + 1
  On Error GoTo 0
  ReDim Preserve MyArray(0 To NumberOfElements) As String
  MyArray(UBound(MyArray)) = "my new friend"

Finally, you can use the following hack to check if a dynamic array has been initialized:

  Dim MyArray() As String
 
  If (Not MyArray) = -1 Then    
    NumberOfElements = 0 'The array is uninitialised
  Else
    NumberOfElements = UBound(MyArray) + 1
  End If
  ReDim Preserve MyArray(0 To NumberOfElements) As String
  MyArray(UBound(MyArray)) = "my new friend"

Variant Arrays

Variant arrays are dynamic arrays declared using the Variant type, and initialized using "= Array()". Their advantage is that, after they are initialized using "= Array()", LBound and UBound functions work with them even when they have no elements, as follows:

  Dim VariantArray As Variant
  
  VariantArray = Array()

  Debug.Print LBound(VariantArray) 'Prints 0
  Debug.Print UBound(VariantArray) 'Prints -1

As a consequence, adding an element is straightforward, with no need to check for UBound failure:

  ReDim Preserve VariantArray(0 To UBound(VariantArray) + 1) As Variant
  VariantArray(UBound(VariantArray)) = "Jennifer"

Variant arrays can be initialized to a set of values:

  Dim VariantArray As Variant  
  VariantArray = Array(1, 2.3, "Hey")

For very large arrays, the overhead of using variant arrays, which have the element type of Variant rather than String or other narrower type, may well be restrictive, in which case conventional dynamic arrays should be used. What seems also much faster than variant arrays for adding items one at a time are Collections.

Multi-Dimensional Arrays

Arrays can be defined to have any number of dimensions (or indices), by listing the sizes of each dimension:

  Dim FunkyArray(2,3,1,4) As String

An element can be referenced like this:

  FunkyArray(1,2,0,3) = 24

Dynamic arrays can also be re-dimensioned to have any number of dimensions:

  Dim VeryFunkyArray() as String
  
  ReDim VeryFunkyArray(2,2,2) As String

The LBound and UBound functions can be used to find the bounds of a particular dimension:

  Debug.Print UBound(FunkyArray, 4)

Would give 4.

Using UBound without the second parameter gives the first dimension

  Debug.Print UBound(FunkyArray)
  ' Displays 2
  Debug.Print UBound(VeryFunkyArray)
  ' Displays 2

Erasing Arrays

Any type of array can be re-set to empty by using:

  Erase SomeArray

Mixing Arrays

The real power of arrays comes when defining arrays of arrays. What does this mean? Declare an array:

  Dim VariantArray() As Variant
  
  VariantArray = Array()
  
  ReDim VariantArray(1) As Variant
  
  VariantArray(0) = Array(1,2,3)
  VariantArray(1) = Array(4,5,6)

What do we have here? Essentially two arrays inside another. They can be referenced like this:

  Debug.Print VariantArray(0)(2)

Would show 3.

You can nest arrays like this to any depth and in any order and they can be of any size. A note of caution must be taken when using the ReDim statement, however. You cannot specifically re-dimension a particular dimension of an array; instead you need to temporarily copy to a variant.

  Dim vtemp As Variant
  
  vtemp = VariantArray(0)
   
  ReDim vtemp(1+UBound(vtemp)) As Variant
  vtemp(UBound(vtemp)) = 7
  
  VariantArray(0) = vtemp

Use of Matrices

See also the section #Multi-Dimensional Arrays.

Matrices are not as commonly used as arrays, but are an important element of programming. Rather than just one dimension, a matrix may have 2 or more. So, to make a matrix, the Dim statement would be:

  Dim Matrix(0 To 9, 0 To 9) as Integer

This will create a matrix that is 10 by 10 and comprised of integers. In reality, a matrix is much like an array of arrays. The first thing you'll want to do is know how to create a loop for reading and filling a matrix. But before that, even, you should know how matrices are structured. An example matrix would be:

     1 2 3 4 5 6 7 8 9 
    
  1  1 1 1 1 1 1 1 1 1 
  2  1 1 1 1 1 1 1 1 1 
  3  1 1 1 1 1 1 1 1 1 
  4  1 1 1 1 1 1 1 1 1

This is a 4 by 9 matrix. When talking about matrices, rows are always stated before columns. You will also notice column numbers travel from left to right, while row numbers travel from top to bottom. This is very important. And of course, matrices need not consist of merely 1s. So, now you want to fill a matrix? Its very similar to an array.

  For Row = 0 To 3
      For Column = 0 To 8
          Matrix(Row, Column)=1
      Next
  Next

This will of course fill the matrix with nothing but ones, but random numbers may be used (see references). As you may notice, this involves nested loops. The loop fills left to right, top to bottom (the same directions we use when reading).

Sorting

For sorting variant arrays, see http://stackoverflow.com/questions/152319/vba-array-sort-function.

Previous: Strings

Contents

Next: Files