VBA Excel Array Concepts | debugvba.com

VBA Excel array

Objective

In this VBA excel automation, we shall see some basic concepts related to array. We shall see how to declare an array, how to reset the array dimensions and some other operations.

An array is a variable which can contain multiple items of same data type.

Types of Array

We have 2 types of arrays in VBA –

  1. Fixed Size Array
  2. Dynamic Array

Fixed Size Array ( Static Array)

In fixed size array, we mention the size of the array when we declare the array. We can not change the size of the fixed size array during run time. To declare a fixed size array, we use Dim statement with array name. 

Code( Static Array)

Sub SampleFixedSizeArray()

'Declare the fixed size or static array
Dim SampleArray(10) As Integer

'Print the lower and upper bound of fixed size array
Debug.Print "Lower bound of array = " & LBound(SampleArray)
Debug.Print "Upper bound of array = " & UBound(SampleArray)

End Sub

 

We cannot resize the static array, if we try to use ReDim statement with static array, it will throw an error as shown below.

 

VBA excel static array

 

Example 

In this statement shown below , we have an array named as “SampleArray”, it can contain 11 values of integer type. Subscript of array can start at 0 or 1. It depends on how we have declared Option Base, if we have explicitly set Option Base 1, then array subscript will start at 1. By default, Option Base is 0, so array subscript will start at zero.

Dim SampleArray (10) as Integer

 

Dynamic Array

In dynamic array we do not initiate the size of the array when we declare the array, rather size is calculated or found during run time.To declare array dynamically, we shall write a statement like Dim SampleArray () as Integer.In this case the size of array will not be declared initially, rather it will be declared with the help of ReDim statement. The parenthesis around the array name will be kept blank initially. Dynamic array is used when we do not know the size of the array, we only get to know about the array size during run time.

For example, we want to store multiple cell values in an array, and the source data files keeps on changing, due to this, number of cells to be stored also changes, so to handle such cases we shall use ReDim statement.

Code [ Dynamic Array ]

Sub SampleDynamicArray()

'Declare the dynamic array
Dim SampleArray() As Integer

'Set the size of an array
ReDim SampleArray(5)

'Print the lower and upper bound of dynamic array
Debug.Print "Lower bound of array = " & LBound(SampleArray)
Debug.Print "Upper bound of array = " & UBound(SampleArray)

End Sub

 

Preserve Modifier

If we have already declared the size of a dynamic array and still we change the size of dynamic array using ReDim statement, then in that case all the data stored in array will be lost. So, to keep the data which already exists in the dynamic array, we use Preserve modifier with ReDim statement.We can see that in the code below.

Code

Sub SampleRedimStatment()

'Declare the dynamic array
Dim SampleArray() As String

'Change the array size
ReDim SampleArray(2)

SampleArray(0) = "NYC"
SampleArray(1) = "LA"
SampleArray(2) = "SF"

'Change the array size
ReDim Preserve SampleArray(5)

'Print the lower and upper bound of fixed size array
Debug.Print "Lower bound of this array = " & LBound(SampleArray)
Debug.Print "Upper bound of this array = " & UBound(SampleArray)

'Loop to access array elements
For i = LBound(SampleArray) To UBound(SampleArray)
Debug.Print "Array value for subscript " & i; " is " & SampleArray(i)

Next i
End Sub

 

We can see in the code above that we had an array “SampleArray” which has size as 3 which contains the name of 3 cities “NY”,”LA”,” SF”. If we do not use Preserve modifier and run the code above to increase the size using ReDim statement, all the existing data will be lost, we can see the expected output in the image below.

 

VBA excel preserve array

 

To avoid such situation we use Preserve modifier, we can see in the image below if we use Preserve modifier then all the existing values will be preserved.

 

VBA excel preserve array

 

Constraint with ReDim

We can use ReDim with the maximum dimension, which means if we have 2-dimension array, we can only change the size of 2nd dimension, if we have 3-dimension array then we can only resize the 3rd dimension.In the code below we can see that we had a 3-dimensional array and when we try to change the 2nd dimension, it will throw an error . We can increase or decrease the size of an array dimension.

 

Sub RedimMultiDimensionArray()

Dim SampleArray() As Integer

'We had a sample 3 dimension array
ReDim SampleArray(10, 12, 14)

'We can increase the last dimension
ReDim Preserve SampleArray(10, 12, 20)


'We can also decrease the last dimension
ReDim Preserve SampleArray(10, 12, 8)


'We can not change 1st or 2nd dimension

'ReDim Preserve SampleArray(10, 15, 14) -Will Throw Error
'ReDim Preserve SampleArray(11, 12, 14) -Will Throw Error


End Sub

 

Erase or Delete Array Elements

Erase statement will remove all the array elements and release the space allocated to array elements.

 

Sub EraseElement()
Dim SampleArray() As String

ReDim SampleArray(2)

SampleArray(0) = "NY"
SampleArray(1) = "LA"
SampleArray(2) = "SF"

Erase SampleArray

End Sub

 

Reference

https://docs.microsoft.com/en-us/office/vba/language/concepts/getting-started/using-arrays
https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/redim-statement
https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/erase-statement


Post you may like

Collection Object in VBA Excel