• Home   /  
  • Archive by category "1"

Vba Excel Array Assignment

Arrays in Visual Basic

An array is a set of values, which are termed elements, that are logically related to each other. For example, an array may consist of the number of students in each grade in a grammar school; each element of the array is the number of students in a single grade. Similarly, an array may consist of a student's grades for a class; each element of the array is a single grade.

It is possible individual variables to store each of our data items. For example, if our application analyzes student grades, we can use a separate variable for each student's grade, such as , , etc. This approach has three major limitations:

  • We have to know at design time exactly how many grades we have to handle.
  • Handling large numbers of grades quickly becomes unwieldy. This in turn makes an application much more likely to have serious bugs.
  • It is difficult to maintain. Each new grade that we add requires that the application be modified, recompiled, and redeployed.

By using an array, you can refer to these related values by the same name, and use a number that’s called an index or subscript to identify an individual element based on its position in the array. The indexes of an array range from 0 to one less than the total number of elements in the array. When you use Visual Basic syntax to define the size of an array, you specify its highest index, not the total number of elements in the array. You can work with the array as a unit, and the ability to iterate its elements frees you from needing to know exactly how many elements it contains at design time.

Some quick examples before explanation:

In this article

Array elements in a simple array

Let's create an array named to store the number of students in each grade in a grammar school. The indexes of the elements range from 0 through 6. Using this array is simpler than declaring seven variables.

The following illustration shows the array. For each element of the array:

  • The index of the element represents the grade (index 0 represents kindergarten).

  • The value that’s contained in the element represents the number of students in that grade.


Elements of the "students" array

The following example contains the Visual Basic code that creates and uses the array:

The example does three things:

  • It declares a array with seven elements. The number in the array declaration indicates the last index in the array; it is one less than the number of elements in the array.
  • It assigns values to each element in the array. Array elements are accessed by using the array name and including the index of the individual element in parentheses.
  • It lists each value of the array. The example uses a statement to access each element of the array by its index number.

The array in the preceding example is a one-dimensional array because it uses one index. An array that uses more than one index or subscript is called multidimensional. For more information, see the rest of this article and Array Dimensions in Visual Basic.

Creating an Array

You can define the size of an array in several ways:

  • You can specify the size when the array is declared:

    • You can use a clause to supply the size of an array when it’s created:

If you have an existing array, you can redefine its size by using the statement. You can specify that the statement keep the values that are in the array, or you can specify that it create an empty array. The following example shows different uses of the statement to modify the size of an existing array.

For more information, see the ReDim Statement.

Storing Values in an Array

You can access each location in an array by using an index of type . You can store and retrieve values in an array by referencing each array location by using its index enclosed in parentheses. Indexes for multidimensional arrays are separated by commas (,). You need one index for each array dimension.

The following example shows some statements that store and retrieve values in arrays.

Populating an array with array literals

By using an array literal, you can populate an array with an initial set of values at the same time that you create it. An array literal consists of a list of comma-separated values that are enclosed in braces ().

When you create an array by using an array literal, you can either supply the array type or use type inference to determine the array type. The following example shows both options.

When you use type inference, the type of the array is determined by the dominant type in the list of literal values. The dominant type is the type to which all other types in the array can widen. If this unique type can’t be determined, the dominant type is the unique type to which all other types in the array can narrow. If neither of these unique types can be determined, the dominant type is . For example, if the list of values that’s supplied to the array literal contains values of type , , and , the resulting array is of type . Because and widen only to , is the dominant type. For more information, see Widening and Narrowing Conversions.

Note

You can use type inference only for arrays that are defined as local variables in a type member. If an explicit type definition is absent, arrays defined with array literals at the class level are of type . For more information, see Local type inference.

Note that the previous example defines as an array of type even though all the array literals are of type . You can create this array because the values in the array literal can widen to values.

You can also create and populate a multidimensional array by using nested array literals. Nested array literals must have a number of dimensions that’s consistent with the resulting array. The following example creates a two-dimensional array of integers by using nested array literals.

When using nested array literals to create and populate an array, an error occurs if the number of elements in the nested array literals don't match. An error also occurs if you explicitly declare the array variable to have a different number of dimensions than the array literals.

Just as you can for one-dimensional arrays, you can rely on type inference when creating a multidimensional array with nested array literals. The inferred type is the dominant type for all the values in all the array literals for all nesting level. The following example creates a two-dimensional array of type from values that are of type and .

For additional examples, see How to: Initialize an Array Variable in Visual Basic.

Iterating through an array

When you iterate through an array, you access each element in the array from the lowest index to the highest or from the highest to the lowest. Typically, use use either the For...Next Statement or the For Each...Next Statement to iterate through the elements of an array. When you don't know the upper bounds of the array, you can call the Array.GetUpperBound method to get the highest value of the index. Although lowest index value is almost always 0, you can call the Array.GetLowerBound method to get the lowest value of the index.

The following example iterates through a one-dimensional array by using the statement.

The following example iterates through a multidimensional array by using a statement. The GetUpperBound method has a parameter that specifies the dimension. returns the highest index of the first dimension, and returns the highest index of the second dimension.

The following example uses a For Each...Next Statementto iterate through a one-dimensional array and a two-dimensional array.

Array Size

The size of an array is the product of the lengths of all its dimensions. It represents the total number of elements currently contained in the array. For example, the following example declares a 2-dimensional array with four elements in each dimension. As the output from the example shows, the array's size is 16 (or (3 + 1) * (3 + 1).

Note

This discussion of array size does not apply to jagged arrays. For information on jagged arrays and determining the size of a jagged array, see the Jagged arrays section.

You can find the size of an array by using the Array.Length property. You can find the length of each dimension of a multidimensional array by using the Array.GetLength method.

You can resize an array variable by assigning a new array object to it or by using the Statement statement. The following example uses the statement to change a 100-element array to a 51-element array.

There are several things to keep in mind when dealing with the size of an array.

Dimension LengthThe index of each dimension is 0-based, which means it ranges from 0 to its upper bound. Therefore, the length of a given dimension is one greater than the declared upper bound of that dimension.
Length LimitsThe length of every dimension of an array is limited to the maximum value of the data type, which is Int32.MaxValue or (2 ^ 31) - 1. However, the total size of an array is also limited by the memory available on your system. If you attempt to initialize an array that exceeds the amount of available memory, the runtime throws an OutOfMemoryException.
Size and Element SizeAn array's size is independent of the data type of its elements. The size always represents the total number of elements, not the number of bytes that they consume in memory.
Memory ConsumptionIt is not safe to make any assumptions regarding how an array is stored in memory. Storage varies on platforms of different data widths, so the same array can consume more memory on a 64-bit system than on a 32-bit system. Depending on system configuration when you initialize an array, the common language runtime (CLR) can assign storage either to pack elements as close together as possible, or to align them all on natural hardware boundaries. Also, an array requires a storage overhead for its control information, and this overhead increases with each added dimension.

The array type

Every array has a data type, which differs from the data type of its elements. There is no single data type for all arrays. Instead, the data type of an array is determined by the number of dimensions, or rank, of the array, and the data type of the elements in the array. Two array variables are of the same data type only when they have the same rank and their elements have the same data type. The lengths of the dimensions of an array do not influence the array data type.

Every array inherits from the System.Array class, and you can declare a variable to be of type , but you cannot create an array of type . For example, although the following code declares the variable to be of type and calls the Array.CreateInstance method to instantiate the array, the array's type proves to be Object[].

Also, the ReDim Statement cannot operate on a variable declared as type . For these reasons, and for type safety, it is advisable to declare every array as a specific type.

You can find out the data type of either an array or its elements in several ways.

  • You can call the GetType method on the variable to get a Type object that represents the run-time type of the variable. The Type object holds extensive information in its properties and methods.

  • You can pass the variable to the TypeName function to get a with the name of run-time type.

The following example calls the both the method and the function to determine the type of an array. The array type is . Note that the Type.BaseType property also indicates that the base type of the byte array is the Array class.

Arrays as return values and parameters

To return an array from a procedure, specify the array data type and the number of dimensions as the return type of the Function Statement. Within the function, declare a local array variable with same data type and number of dimensions. In the Return Statement, include the local array variable without parentheses.

To specify an array as a parameter to a or procedure, define the parameter as an array with a specified data type and number of dimensions. In the call to the procedure, pass an array variable with the same data type and number of dimensions.

In the following example, the function returns an , a one-dimensional array of type . The procedure accepts an argument.

In the following example, the function returns an , a two-dimensional array of type . The procedure accepts an argument.

Jagged Arrays

Sometimes the data structure in your application is two-dimensional but not rectangular. For example, you might use an array to store data about the high temperature of each day of the month. The first dimension of the array represents the month, but the second dimension represents the number of days, and the number of days in a month is not uniform. A jagged array, which is also called an array of arrays, is designed for such scenarios. A jagged array is an array whose elements are also arrays. A jagged array and each element in a jagged array can have one or more dimensions.

The following example uses an array of months, each element of which is an array of days. The example uses a jagged array because different months have different numbers of days. The example shows how to create a jagged array, assign values to it, and retrieve and display its values.

The previous example assigns values to the jagged array on an element-by-element basis by using a loop. You can also assign values to the elements of a jagged array by using nested array literals. However, the attempt to use nested array literals (for example, ) generates compiler error BC30568. To correct the error, enclose the inner array literals in parentheses. The parentheses force the array literal expression to be evaluated, and the resulting values are used with the outer array literal, as the following example shows.

A jagged array is a one-dimensional array whose elements contain arrays. Therefore, the Array.Length property and the method return the number of elements in the one-dimensional array, and throws an IndexOutOfRangeException because a jagged array is not multidimensional. You determine the number of elements in each subarray by retrieving the value of each subarray's Array.Length property. The following example illustrates how to determine the number of elements in a jagged array.

Zero-length arrays

Visual Basic differentiates between a uninitialized array (an array whose value is ) and a zero-length array or empty array (an array that has no elements.) An uninitialized array is one that has not been dimensioned or had any values assigned to it. For example:

A zero-length array is declared with a dimension of -1. For example:

You might need to create a zero-length array under the following circumstances:

  • Without risking a NullReferenceException exception, your code must access members of the Array class, such as Length or Rank, or call a Visual Basic function such as UBound.

  • You want to keep the your code simple by not having to check for as a special case.

  • Your code interacts with an application programming interface (API) that either requires you to pass a zero-length array to one or more procedures or returns a zero-length array from one or more procedures.

Splitting an array

In some cases, you may need to split a single array into multiple arrays. This involves identifying the point or points at which the array is to be split, and then spitting the array into two or more separate arrays.

Note

This section does not discuss splitting a single string into a string array based on some delimiter. For information on splitting a string, see the String.Split method.

The most common criteria for splitting an array are:

  • The number of elements in the array. For example, you might want to split an array of more than a specified number of elements into a number of approximately equal parts. For this purpose, you can use the value returned by either the Array.Length or Array.GetLength method.

  • The value of an element, which serves as a delimiter that indicates where the array should be split. You can search for a specific value by calling the Array.FindIndex and Array.FindLastIndex methods.

Once you've determined the index or indexes at which the array should be split, you can then create the individual arrays by calling the Array.Copy method.

The following example splits an array into two arrays of approximately equal size. (If the total number of array elements is odd, the first array has one more element than the second.)

The following example splits a string array into two arrays based on the presence of an element whose value is "zzz", which serves as the array delimiter. The new arrays do not include the element that contains the delimiter.

Joining arrays

You can also combine a number of arrays into a single larger array. To do this, you also use the Array.Copy method.

Note

This section does not discuss joining a string array into a single string. For information on joining a string array, see the String.Join method.

Before copying the elements of each array into the new array, you must first ensure that you have initialized the array so that it is large enough to accompodate the new array. You can do this in one of two ways:

  • Use the statement to dynamically expand the array before adding new elements to it. This is the easiest technique, but it can result in performance degradation and excessive memory consumption when you are copying large arrays.
  • Calculate the total number of elements needed for the new large array, then add the elements of each source array to it.

The following example uses the second approach to add four arrays with ten elements each to a single array.

Since in this case the source arrays are all small, we can also dynamically expand the array as we add the elements of each new array to it. The following example does that.

Collections as an alternative to arrays

Arrays are most useful for creating and working with a fixed number of strongly typed objects. Collections provide a more flexible way to work with groups of objects. Unlike arrays, which require that you explicitly change the size of an array with the Statement, collections grow and shrink dynamically as the needs of an application change.

When you use to redimension an array, Visual Basic creates a new array and releases the previous one. This takes execution time. Therefore, if the number of items you are working with changes frequently, or you cannot predict the maximum number of items you need, you'll usually obtain better performance by using a collection.

For some collections, you can assign a key to any object that you put into the collection so that you can quickly retrieve the object by using the key.

If your collection contains elements of only one data type, you can use one of the classes in the System.Collections.Generic namespace. A generic collection enforces type safety so that no other data type can be added to it.

For more information about collections, see Collections.

Related Topics

See Also

System.Array
Dim Statement
ReDim Statement

“A list is only as strong as its weakest link” – Donald Knuth.

The following table provides a quick reference to using arrays in VBA. The remainder of the post provides the most complete guide you will find on the VBA arrays.

 

Contents

A Quick Guide to VBA Arrays

TaskStatic ArrayDynamic Array
DeclareDim arr(0 To 5) As LongDim arr() As Long
Dim arr As Variant
Set SizeSee Declare aboveReDim arr(0 To 5)As Variant
Increase size (keep existing data)Dynamic OnlyReDimPreserve arr(0 To 6)
Set valuesarr(1) = 22arr(1) = 22
Receive valuestotal = arr(1)total = arr(1)
First positionLBound(arr)LBound(arr)
Last positionUbound(arr)Ubound(arr)
Read all items(1D)For i = LBound(arr) To UBound(arr)
Next i
Or
For i = LBound(arr,1) To UBound(arr,1)
Next i
For i = LBound(arr) To UBound(arr)
Next i
Or
For i = LBound(arr,1) To UBound(arr,1)
Next i
Read all items(2D)For i = LBound(arr,1) To UBound(arr,1)
  For j = LBound(arr,2) To UBound(arr,2)
  Next j
Next i
For i = LBound(arr,1) To UBound(arr,1)
  For j = LBound(arr,2) To UBound(arr,2)
  Next j
Next i
Read all itemsDim item As Variant
For Each item In arr
Next item
Dim item As Variant
For Each item In arr
Next item
Pass to SubSub MySub(ByRef arr() As String)Sub MySub(ByRef arr() As String)
Return from FunctionFunction GetArray() As Long()
    Dim arr(0 To 5) As Long
    GetArray = arr
End Function
Function GetArray() As Long()
    Dim arr() As Long
    GetArray = arr
End Function
Receive from FunctionDynamic onlyDim arr() As Long
Arr = GetArray()
Erase arrayErase arr
*Resets all values to default
Erase arr
*Deletes array
String to arrayDynamic onlyDim arr As Variant
arr = Split("James:Earl:Jones",":")
Array to stringDim sName As String
sName = Join(arr, ":")
Dim sName As String
sName = Join(arr, ":")
Fill with valuesDynamic onlyDim arr As Variant
arr = Array("John", "Hazel", "Fred")
Range to ArrayDynamic onlyDim arr As Variant
arr = Range("A1:D2")
Array to RangeSame as dynamic Dim arr As Variant
Range("A5:D6") = arr

 
 

Introduction

This post provides an in-depth look at arrays in the Excel VBA programming language. It covers the important points such as

  • Why you need arrays
  • When should you use them
  • The two types of arrays
  • Using more than one dimension
  • Declaring arrays
  • Adding values
  • Viewing all the items
  • A super efficient way to read a Range to an array

 
 

In the first section we will look at is what are arrays and why you need them. You may not understand some of the code in the first section. This is fine. I will be breaking it all down into simple terms in the following sections of the post.

 
 

Arrays Webinar

If you are a member of the website, click on the image below to access the webinar.

(Note: Website members have access to the full webinar archive.)

 
 

Quick Notes

Sometimes Collections are a better option than arrays. You can read about collections here.

Arrays and Loops go hand in hand. The most common loops you use with arrays are the For Loop and the For Each Loop(read-only).

 
 

What are Arrays and Why do You Need Them?

 
 
A VBA array is a type of variable. It is used to store lists of data of the same type. An example would be storing a list of countries or a list of weekly totals.

In VBA a normal variable can store only one value at a time. 

 
 
The following example shows a variable being used to store the marks of a student.

' Can only store 1 value at a timeDim Student1 AsInteger Student1 = 55

 
 
If we wish to store the marks of another student then we need to create a second variable.

In the following example we have the marks of five students

Student Marks

 
 
We are going to read these marks and write them to the Immediate Window.

Note: The function Debug.Print writes values to the Immediate  Window. To view this window select View->Immediate Window from the menu( Shortcut is Ctrl + G)

 
 

 
 
As you can see in the following example we are writing the same code five times – once for each student

PublicSub StudentMarks()With ThisWorkbook.Worksheets("Sheet1")' Declare variable for each studentDim Student1 AsIntegerDim Student2 AsIntegerDim Student3 AsIntegerDim Student4 AsIntegerDim Student5 AsInteger' Read student marks from cell Student1 = .Range("C2").Offset(1) Student2 = .Range("C2").Offset(2) Student3 = .Range("C2").Offset(3) Student4 = .Range("C2").Offset(4) Student5 = .Range("C2").Offset(5)' Print student marksDebug.Print"Students Marks"Debug.Print Student1Debug.Print Student2Debug.Print Student3Debug.Print Student4Debug.Print Student5EndWithEndSub

 
 
The following is the output from the example

Output

 
 
The problem with using one variable per student is that you need to add code for each student. Therefore if you had a thousand students in the above example you would need three thousand lines of code!

Luckily we have arrays to make our life easier. Arrays allow us to store a list of data items in one structure.

 
 
The following code shows the above student example using an array

PublicSub StudentMarksArr()With ThisWorkbook.Worksheets("Sheet1")' Declare an array to hold marks for 5 studentsDim Students(1 To 5) AsInteger' Read student marks from cells C3:C7 into arrayDim i AsIntegerFor i = 1 To 5 Students(i) = .Range("C2").Offset(i)Next i' Print student marks from the arrayDebug.Print"Students Marks"For i = LBound(Students) To UBound(Students)Debug.Print Students(i)Next iEndWithEndSub

 
 
The advantage of this code is that it will work for any number of students. If we have to change this code to deal with 1000 students we only need to change the (1 To 5) to (1 To 1000) in the declaration. In the prior example we would need to add approximately five thousand lines of code.

 
 
Let’s have a quick comparison of variables and arrays. First we compare the declaration

' VariableDim Student AsIntegerDim Country AsString' ArrayDim Students(1 To 3) AsIntegerDim Countries(1 To 3) AsString

 
 
Next we compare assigning a value

' assign value to variable Student1 = .Cells(1, 1) ' assign value to first item in array Students(1) = .Cells(1, 1)

 
 
Lastly we look at writing the values

' Print variable valueDebug.Print Student1' Print value of first student in arrayDebug.Print Students(1)

 
 
As you can see, using variables and arrays is quite similar.

The fact that arrays use an index(also called a subscript) to access each item is important. It means we can easily access all the items in an array using a For Loop.

Now that you have some background on why arrays are useful lets go through them step by step.

 
 

Types of VBA Arrays

There are two types of arrays in VBA

  1. Static – an array of fixed size.
  2. Dynamic – an array where the size is set at run time.

 
 
The difference between these arrays mainly in how they are created. Accessing values in both array types is exactly the same. In the following sections we will cover both types.

 
 

Declaring an Array

A static array is declared as follows

PublicSub DecArrayStatic()' Create array with locations 0,1,2,3Dim arrMarks1(0 To 3) AsLong' Defaults as 0 to 3 i.e. locations 0,1,2,3Dim arrMarks2(3) AsLong' Create array with locations 1,2,3,4,5Dim arrMarks1(1 To 5) AsLong' Create array with locations 2,3,4 ' This is rarely usedDim arrMarks3(2 To 4) AsLongEndSub

 
 

An Array of 0 to 3

 
 
As you can see the size is specified when you declare a static array. The problem with this is that you can never be sure in advance the size you need. Each time you run the Macro you may have different size requirements.

If you do not use all the array locations then the resources are being wasted. If you need more locations you can used ReDim but this is essentially creating a new static array.

The dynamic array does not have such problems. You do not specify the size when you declare it. Therefore you can then grow and shrink as required

PublicSub DecArrayDynamic()' Declare dynamic arrayDim arrMarks() AsLong' Set the size of the array when you are readyReDim arrMarks(0 To 5)EndSub

 
 
The dynamic array is not allocated until you use the ReDim statement. The advantage is you can wait until you know the number of items before setting the array size. With a static array you have to give the size up front.

To give an example. Imagine you were reading worksheets of student marks. With a dynamic array you can count the students on the worksheet and set an array to that size. With a static array you must set the size to the largest possible number of students.

 
 

Need Help Using Arrays? Click here to get your FREE Cheat Sheet

 
 

Assigning Values to an Array

To assign values to an array you use the number of the location. You assign value for both array types the same way.

PublicSub AssignValue()' Declare array with locations 0,1,2,3Dim arrMarks(0 To 3) AsLong' Set the value of position 0 arrMarks(0) = 5' Set the value of position 3 arrMarks(3) = 46' This is an error as there is no location 4 arrMarks(4) = 99EndSub

 
 

The array with values assigned

 
 
The number of the location is called the subscript or index. The last line in the example will give a “Subscript out of Range” error as there is no location 4 in the array example.

 
 

Using the Array and Split function

You can use the Array function to populate an array with a list of items. You must declare the array as a type Variant. The following code shows you how to use this function.

Dim arr1 AsVariant arr1 = Array("Orange", "Peach","Pear")Dim arr2 AsVariant arr2 = Array(5, 6, 7, 8, 12)

 
 

Contents of arr1 after using the Array function

 
 
The array created by the Array Function will start at index zero unless you use Option Base 1 at the top of your module. Then it will start at index one. In programming it is generally considered poor practice to have your actual data in the code. However sometimes it is useful when you need to test some code quickly. The Split function is used to split a string into an array based on a delimiter. A delimiter is a character such as a comma or space that separates the items.

 
 
The following code will split the string into an array of three elements.

Dim s AsString s = "Red,Yellow,Green,Blue"Dim arr() AsString arr = Split(s, ",")

 
 

The array after using Split


 
 

The Split function is normally used when you read from a comma separated file or another source that provides a list of items separated by the same character.

 
 

Using Loops With Arrays

Using a For Loop allows quick access to all items in an array. This is where the power of using arrays becomes apparent. We can read arrays with ten values or ten thousand values using the same few lines of code. There are two functions in VBA called LBound and UBound. These functions return the smallest and largest subscript in an array. In an array arrMarks(0 to 3) the LBound will return 0 and UBound will return 3.

 
 
The following example assigns random numbers to an array using a loop. It then prints out these numbers using a second loop.

PublicSub ArrayLoops()' Declare arrayDim arrMarks(0 To 5) AsLong' Fill the array with random numbersDim i AsLongFor i = LBound(arrMarks) To UBound(arrMarks) arrMarks(i) = 5 * RndNext i' Print out the values in the arrayDebug.Print"Location", "Value"For i = LBound(arrMarks) To UBound(arrMarks)Debug.Print i, arrMarks(i)Next iEndSub

 
 
The functions LBound and UBound are very useful. Using them means our loops will work correctly with any array size. The real benefit is that if the size of the array changes we do not have to change the code for printing the values. A loop will work for an array of any size as long as you use these functions.

 
 

Using the For Each Loop

You can use the For Each loop with arrays. The important thing to keep in mind is that it is Read-Only. This means that you cannot change the value in the array.

 
 
In the following code the value of mark changes but it does not change the value in the array.

ForEach mark In arrMarks' Will not change the array value mark = 5 * RndNext mark

 
 
The For Each is loop is fine to use for reading an array. It is neater to write especially for a Two-Dimensional array as we will see.

Dim mark AsVariantForEach mark In arrMarksDebug.Print markNext mark

 
 

Using Erase

The Erase function can be used on arrays but performs differently depending on the array type.

For a static Array the Erase function resets all the values to the default. If the array is of integers then all the values are set to zero. If the array is of strings then all the strings are set to “” and so on.

For a Dynamic Array the Erase function DeAllocates memory. That is, it deletes the array. If you want to use it again you must use ReDim to Allocate memory.

 
 
Lets have a look an example for the static array. This example is the same as the ArrayLoops example in the last section with one difference – we use Erase after setting the values. When the value are printed out they will all be zero.

PublicSub EraseStatic() ' Declare arrayDim arrMarks(0 To 3) AsLong' Fill the array with random numbersDim i AsLongFor i = LBound(arrMarks) To UBound(arrMarks) arrMarks(i) = 5 * Rnd Next i ' ALL VALUES SET TO ZEROErase arrMarks ' Print out the values - there are all now zeroDebug.Print"Location", "Value"For i = LBound(arrMarks) To UBound(arrMarks) Debug.Print i, arrMarks(i) Next i EndSub

 
 
We will now try the same example with a dynamic. After we use Erase all the locations in the array have been deleted. We need to use ReDim if we wish to use the array again.

 
 
If we try to access members of this array we will get a “Subscript out of Range” error.

PublicSub EraseDynamic() ' Declare arrayDim arrMarks() AsLongReDim arrMarks(0 To 3) ' Fill the array with random numbersDim i AsLongFor i = LBound(arrMarks) To UBound(arrMarks) arrMarks(i) = 5 * Rnd Next i ' arrMarks is now deallocated. No locations exist.Erase arrMarks EndSub

 
 

ReDim with Preserve

If we use ReDim on an existing array, then the array and it’s contents will be deleted.

In the following example, the second ReDim statement will create a completely new array. The original array and it’s contents will be deleted.

Sub UsingRedim() Dim arr() AsString' Set array to be slots 0 to 2ReDim arr(0 To 2) arr(0) = "Apple"' Array with apple is now deletedReDim arr(0 To 3) EndSub

 
 
If we want to extend the size of an array without losing the contents, we can use the Preserve keyword.

When we use Redim Preserve the new array must be bigger and start at the same dimension e.g.

We cannot Preserve from (0 to 2) to (1 to 3) or (2 to 10) as they are different starting dimensions.
We cannot Preserve from (0 to 2) to (0 to 1) or (0) as they are smaller than original array.

 
 
In the following code we create an array using ReDim and then fill the array with types of fruit.

We then use Preserve to extend the size of the array so we don’t lose the original contents.

Sub UsingRedimPreserve() Dim arr() AsString' Set array to be slots 0 to 1ReDim arr(0 To 2) arr(0) = "Apple" arr(1) = "Orange" arr(2) = "Pear"' Resize and keep original contentsReDimPreserve arr(0 To 5) EndSub

 
 
You can see from the screenshots below, that the original contents of the array have been “Preserved”.

Before ReDim Preserve


 
 

After ReDim Preserve

 
 
Word of Caution: In most cases you shouldn’t need to resize an array like we have done in this section. If you are resizing an array multiple times then you many want to considering using a Collection.

 
 

Using Preserve with 2 Dimensional Arrays

Preserve only works with the upper bound of an array.

For example, if you have a 2 dimensional array you can only preserve the second dimension as this example shows:

Sub Preserve2D() Dim arr() AsLong' Set the starting sizeReDim arr(1 To 2, 1 To 5) ' Resize the upper boundReDimPreserve arr(1 To 2, 1 To 10) EndSub

 
 
If we try to use Preserve on a lower bound we will get the “Subscript out of range” error.

In the following code we use Preserve on the lower bound. Running this code will give the “Subscript out of range” error:

Sub Preserve2DError() Dim arr() AsLong' Set the starting sizeReDim arr(1 To 2, 1 To 5) ' "Subscript out of Range" errorReDimPreserve arr(1 To 5, 1 To 5) EndSub

 
 
When we read from a range to an array, it automatically creates a two dimensional array, even if we have only one column.

The same Preserve rules apply. We can only use Preserve on the upper bound as this example shows:

Sub Preserve2DRange() Dim arr AsVariant' Assign a range to an array arr = Sheet1.Range("A1:A5").Value ' Preserve will work on the upper bound onlyReDimPreserve arr(1 To 5, 1 To 7) EndSub

 
 

 
 

 Sorting an Array

There is no function in VBA for sorting an array. We can sort the worksheet cells but this could be slow if there is a lot of data.

The QuickSort function below can be used to sort an array.

Sub QuickSort(arr As Variant, first As Long, last As Long) Dim vCentreVal As Variant, vTemp AsVariantDim lTempLow AsLongDim lTempHi AsLong lTempLow = first lTempHi = last vCentreVal = arr((first + last) \ 2) DoWhile lTempLow <= lTempHi DoWhile arr(lTempLow) < vCentreVal And lTempLow < last lTempLow = lTempLow + 1 LoopDoWhile vCentreVal < arr(lTempHi) And lTempHi > first lTempHi = lTempHi - 1 LoopIf lTempLow <= lTempHi Then' Swap values vTemp = arr(lTempLow) arr(lTempLow) = arr(lTempHi) arr(lTempHi) = vTemp ' Move to next positions lTempLow = lTempLow + 1 lTempHi = lTempHi - 1 EndIfLoopIf first < lTempHi Then QuickSort arr, first, lTempHi If lTempLow < last Then QuickSort arr, lTempLow, last EndSub

 

You can use this function like this

Sub TestSort() ' Create temp arrayDim arr() AsVariant arr = Array("Banana", "Melon", "Peach", "Plum", "Apple") ' Sort array QuickSort arr, LBound(arr), UBound(arr) ' Print arr to Immediate Window(Ctrl + G)Dim i AsLongFor i = LBound(arr) To UBound(arr) Debug.Print arr(i) Next i EndSub

 
 

 Passing an Array to a Sub or Function

Sometimes you will need to pass an array to a procedure. You declare the parameter using parenthesis similar to how you declare a dynamic array.

Passing to the procedure using ByRef means you are passing a reference of the array. So if you change the array in the procedure it will be changed when you return.
 
 
Note: When you use an array as a parameter it cannot use ByVal, it must use ByRef. You can pass the array using ByVal making the parameter a variant.

' Passes array to a FunctionPublicSub PassToProc() Dim arr(0 To 5) AsString' Pass the array to function UseArray arr EndSubPublicFunction UseArray(ByRef arr() As String) ' Use arrayDebug.Print UBound(arr) EndFunction

 
 

Returning an Array from a Function

It is important to keep the following in mind. If you want to change an existing array in a procedure then you should pass it as a parameter using ByRef(see last section). You do not need to return the array from the procedure.

The main reason for returning an array is when you use the procedure to create a new one. In this case you assign the return array to an array in the caller. This array cannot be already allocated. In other words you must use a dynamic array that has not been allocated.

 
 
The following examples show this

PublicSub TestArray()' Declare dynamic array - not allocatedDim arr() AsString' Return new array arr = GetArrayEndSubPublicFunction GetArray() As String()' Create and allocate new arrayDim arr(0 To 5) AsString' Return array GetArray = arrEndFunction

 
 

Two Dimensional Arrays

The arrays we have been looking at so far have been one dimensional arrays. This means the arrays are one list of items.

A two dimensional array is essentially a list of lists. If you think of a single spreadsheet column as a single dimension then more than one column is two dimensional. In fact a spreadsheet is the equivalent of a 2 dimensional array. It has two dimensions – rows and columns.

 
 
The following image shows two groups of data. The first is a one dimensional layout and the second is two dimensional.

 
 

 
 
To access an item in the first set of data(1 dimensional) all you need to do is give the row e.g. 1,2, 3 or 4.

For the second set of data(2 dimensional) you need to give the row AND the column. So you can think of 1 dimensional being rows only and 2 dimensional as being rows and columns.

Note: It is possible to have more dimensions in an array. It is rarely required. If you are solving a problem using a 3+ dimensional array then there probably is a better way to do it.

 
 
You declare a 2 dimensional array as follows

Dim ArrayMarks(0 To 2,0 To 3) AsLong

 
 
The following example creates a random value for each item in the array and the prints the values to the Immediate Window.

PublicSub TwoDimArray()' Declare a two dimensional arrayDim arrMarks(0 To 3, 0 To 2) AsString' Fill the array with text made up of i and j valuesDim i As Long, j AsLongFor i = LBound(arrMarks) To UBound(arrMarks)For j = LBound(arrMarks, 2) To UBound(arrMarks, 2) arrMarks(i, j) = CStr(i) & ":" & CStr(j)Next jNext i' Print the values in the array to the Immediate WindowDebug.Print"i", "j", "Value"For i = LBound(arrMarks) To UBound(arrMarks)For j = LBound(arrMarks, 2) To UBound(arrMarks, 2)Debug.Print i, j, arrMarks(i, j)Next jNext iEndSub

 
 
You can see that we use a second For loop inside the first loop to access all the items.

 
 

The output of the example looks like this:

 
 
How this Macro works is as follows

  • Enters the i loop
  • i is set to 0
  • Entersj loop
  • j is set to 0
  • j is set to 1
  • j is set to 2
  • Exit j loop
  • i is set to 1
  • j is set to 0
  • j is set to 1
  • j is set to 2
  • And so on until i=3and j=2

 
 
You may notice that LBound and UBound have a second argument of 2. This specifies that it is the upper or lower bound of the second dimension. That is the start and end location for j. The default value 1 which is why we do not need to specify it for the i loop.

 
 

Using the For Each Loop

Using a For Each is neater to use when reading from an array.

 
 
Let’s take the code from above that writes out the two-dimensional array

' Using For loop needs two loopsDebug.Print"i", "j", "Value"For i = LBound(arrMarks) To UBound(arrMarks)For j = LBound(arrMarks, 2) To UBound(arrMarks, 2)Debug.Print i, j, arrMarks(i, j)Next jNext i

 
 
Now let’s rewrite it using a For each loop. You can see we only need one loop and so it is much easier to write

' Using For Each requires only one loopDebug.Print"Value"Dim mark AsVariantForEach mark In arrMarksDebug.Print markNext mark

 
 
Using the For Each loop gives us the array in one order only – from LBound to UBound. Most of the time this is all you need.

 
 

Reading from a Range of Cells to an Array

If you have read my previous post on Cells and Ranges then you will know that VBA has an extremely efficient way of reading from a Range of Cells to an Array and vice versa

PublicSub ReadToArray()' Declare dynamic arrayDim StudentMarks AsVariant' Read values into array from first row StudentMarks = Range("A1:Z1").Value' Write the values back to the third row Range("A3:Z3").Value = StudentMarksEndSub

 
 
The dynamic array created in this example will be a two dimensional array. As you can see we can read from an entire range of cells to an array in just one line.

 
 
The next example will read the sample student data below from C3:E6 of Sheet1 and print them to the Immediate Window.

PublicSub ReadAndDisplay()' Get RangeDim rg As RangeSet rg = ThisWorkbook.Worksheets("Sheet1").Range("C3:E6")' Create dynamic arrayDim StudentMarks AsVariant' Read values into array from sheet1 StudentMarks = rg.Value' Print the array valuesDebug.Print"i", "j", "Value"Dim i As Long, j AsLongFor i = LBound(StudentMarks) To UBound(StudentMarks)For j = LBound(StudentMarks, 2) To UBound(StudentMarks, 2)Debug.Print i, j, StudentMarks(i, j)Next jNext iEndSub

 
 

Sample Student data

 
 

Output from sample data

 
 
As  you can see the first dimension(accessed using i) of the array is a row and the second is a column. To demonstrate this take a look at the value 44 in E4 of the sample data. This value is in row 2 column 3 of our data. You can see that 44 is stored in the array at StudentMarks(2,3).

 
 

How To Make Your Macros Run at Super Speed

If your macros are running very slow then you may find this section very helpful. Especially if you are dealing with large amounts of data. The following is a well kept secret in VBA

 
 
Updating values in arrays is exponentially faster than updating values in cells.

 
 

In the last section, you saw how we can easily read from a group of cells to an array and vice versa. If we are updating a lot of values then we can do the following

1. Copy the data from the cells to an array.
2. Change the data in the array.
3. Copy the updated data from the array back to the cells.

 
 
For example, the following code would be much faster than the code below it

PublicSub ReadToArray()' Read values into array from first rowDim StudentMarks AsVariant StudentMarks = Range("A1:Z20000").ValueDim i AsLongFor i = LBound(StudentMarks) To UBound(StudentMarks)' Update marks here StudentMarks(i, 1) = StudentMarks(i, 1) * 2 '...Next i' Write the new values back to the worksheet Range("A1:Z20000").Value = StudentMarksEndSub

 
 

Sub UsingCellsToUpdate()Dim c AsVariantForEach c In Range("A1:Z20000") c.Value = ' Update values hereNext cEndSub

 
 
Assigning from one set of cells to another is also much faster than using Copy and Paste

' Assigning - this is faster Range("A1:A10").Value = Range("B1:B10").Value' Copy Paste - this is slower Range("B1:B1").Copy Destination:=Range("A1:A10")

 
 
The following comments are from two readers who used arrays to speed up their macros

“A couple of my projects have gone from almost impossible and long to run into almost too easy and a reduction in time to run from 10:1.” – Dane

“One report I did took nearly 3 hours to run when accessing the cells directly — 5 minutes with arrays” – Jim

 
 

Conclusion

The following is a summary of the main points of this post

  1. Arrays are an efficient way of storing a list of items of the same type.
  2. You can access an array item directly using the number of the location which is known as the subscript or index.
  3. The common error “Subscript out of Range” is caused by accessing a location that does not exist.
  4. There are two types of arrays: Static and Dynamic.
  5. Static is used when the size of the array is always the same.
  6. Dynamic arrays allow you to determine the size of an array at run time.
  7. LBound and UBound provide a safe way of find the smallest and largest subscripts of the array.
  8. The basic array is one dimensional. You can also have multi dimensional arrays.
  9. You can only pass an array to a procedure using ByRef. You do this like this: ByRef arr() as long.
  10. You can return an array from a function but the array, it is assigned to, must not be currently allocated.
  11. A worksheet with it’s rows and columns is essentially a two dimensional array.
  12. You can read directly from a worksheet range into a two dimensional array in just one line of code.
  13. You can also write from a two dimensional array to a range in just one line of code.

 
 

What’s Next?

Free VBA Tutorial If you are new to VBA or you want to sharpen your existing VBA skills then why not try out the The Ultimate VBA Tutorial.

Related Training: Get full access to the Excel VBA training webinars and all the tutorials.

(NOTE: Planning to build or manage a VBA Application? Learn how to build 10 Excel VBA applications from scratch.)

 
 

Arrays Cheat Sheet

 
 
Need Help Using Arrays? Click here to get your FREE Cheat Sheet


 
 

ArrayArraysDynamic ArrayExcel MacrosExcel VBAStatic Array

One thought on “Vba Excel Array Assignment

Leave a comment

L'indirizzo email non verrà pubblicato. I campi obbligatori sono contrassegnati *