# VBA Arrays

An **array** is a structure that can store a multidimensional list of data. A variable can store a single element.

An **array** can store multiple elements referenced by index numbers

## 0. Preliminary

- A one dimension array is like a list (vector) of data, while a two dimensional array is like a table of data or a matrix
- VBA arrays can have up to 60 dimensions. Generally, three dimension is the maximum required in the business environment. The dimension can be conceptualized as 1. rows, 2. columns, and 3. depth (equivalent to multi worksheets in Excel)
- The number of elements in each dimension is only limited by computer memory. For example, you could have a 10,000 by 100,000 array
- Arrays are very useful for data processing as each individual item can be accessed via its index number(s)
- Details of the elements of an array can be viewed in the
**Locals Window**(in**Step**or**Break**mode) or other techniques such as**Debug.Print**

## 1. An array can either be static or dynamic

An array is declared in a similar way to a variable, and follows the same naming conventions. Note: you cannot use the name Array, because **Array** in a VBA keyword (a function).

### 1.1 Static array

A **static array** is given a fixed number of elements at the point of declaration. In other words the analyst needs to know the array dimension before the code is written.

**Code 1a:**

`DemoStatic1`

commas separate the dimensions. The values are the upper index value of the dimension.Base 0 by default

Sub DemoStatic1() ' Static array - includes the dimension n, in the brackets (n) Dim Price1D(5) As Double ' one dimension with elements 0 to 5 Dim Price2D(3, 1) As Variant ' two dimensions with elements 0 to 3, and 0 to 1 ' Assign some values Price1D(0) = 10 Price1D(4) = 50 ' Populate selected elements Price2D(0, 0) = "Coy1" Price2D(0, 1) = 123.45 Price2D(3, 0) = "Coy4" Price2D(3, 1) = 456.78 End Sub

A **static array** includes the dimension, and the upper index number elements in the dimension in declaration (Dim) statement. A one dimensional array (row vector), example **Price1D** in code 1 has no commas in the brackets; a two dimensional array, example **Price2D** in code 1 has one comma separating the dimension, a three dimensional arrays has two commas, .... A two dimensional array is similar to a worksheet viewed in R1C1 reference style. Cell/element coordinates are denoted by the row, column pair (r, c).

When viewed in Break mode, the code 1 arrays are shown in the Locals Window (figure 1).

**Price1D** has only one dimension and is equivalent to a row vector. The first element, index 0 has value 10. The fifth element, index 4 has value 50.

### Option Base statement

By default, the lower index number of the array dimension is zero.

- If you define an array with upper index 5 (see code 1, line 8), the indices will be 0,1,2,3,4,5 not 1,2,3,4,5
- You can change the starting point from zero to one (set the lower index to 1) by including the following statement in the Declaration area at the top of the module
**Option Base 1**(see code 1b line 1), or- Explicitly set the lower bound in the declaration with the
**To**statement (see code 1c, lines 8 and 9)

**Option Base**is module specific- Array dimension index numbers can be any consecutive sequence on the integer number line
- Generally, the base is usually 0 or 1, but could be negative (see
__Dim shpArray(-2 to 24)__), or even match a value like the year__Dim TaxR(2010 To 2016, 1 To 5, 1 To 3)__

**Code 1b:**

`DemoStatic1`

commas separate the dimensions. The values in the brackets are the upper index value of the dimension.Explicit Option Base 1 declaration

Option Base 1 Sub DemoStatic1() ' Static array - includes the dimension n, in the brackets (n) Dim Price1D(5) As Double ' one dimension with elements 1 to 5 Dim Price2D(3, 1) As Variant ' two dimensions with elements 1 to 3, and 1 to 1 ' Price2D is now equivalent to a column vector with 3 rows and 1 column ' The assignment statement must reference valid index numbers ' < more code > '

**Code 1c:**

`DemoStatic1`

commas separate the dimensions. The values are the upper index value of the dimension.Explicit lower bound declaration (Dim) with the

**To**keyword

Sub DemoStatic1() ' Static array - includes the dimension n, in the brackets (n) Dim Price1D(1 To 5) As Double ' one dimension with elements 1 to 5 Dim Price2D(1 To 3, 1 To 1) As Variant ' two dimensions with elements 1 to 3, and 1 to 1 ' Price2D is now equivalent to a column vector with 3 rows and 1 column ' The assignment statement must reference valid index numbers ' < more code > '

Other example of static arrays are in the next code module.

**Static arrays :: sample code**

Option Base 1 Sub DemoStaticArr1() ' Static array :: one dimension with upper index number Dim first_array(5) As Integer ' declare an array index 1 to 5 Dim a As Integer, x As Integer a = 4 For x = 1 To 5 first_array(x) = x ' assign the index number as the value of each element Next x MsgBox "The value of first_array(" & a & ") is " & first_array(a) ' return the value from the ath element End Sub ' The array and the output is shown in figure 2

Sub DemoStaticArr2() ' Static array :: multidimensional with lower and upper bound Dim second_array(1 To 12, 1 To 10) As Integer ' 2D array :: equivalent to 12 rows and 10 columns Dim a As Integer, b As Integer Dim x As Integer, y As Integer ' loop counters a = 5: b = 7 For x = 1 To 12 For y = 1 To 10 second_array(x, y) = x * y ' assign the product of the row and column numbers as the value of each element Next y Next x MsgBox "The value of second_array(" & a & "," & b & ") is " & second_array(a, b) ' return the value from the a,b element End Sub ' The array and the output is shown in figure 3

Sub DemoStaticArr3() ' Static array :: one dimension with lower and upper bound ' Assign an array of strings Dim third_array(0 To 5) As String ' the lower band zero overrides the option base 1 declaration Dim x As Integer: x = 3 third_array(0) = "Collingwood" third_array(1) = "Essendon" third_array(2) = "Fremantle" third_array(3) = "Geelong" third_array(4) = "Sydney Swans" third_array(5) = "Port Adelaide" MsgBox "The value of third_array(" & x & ") is " & third_array(x) ' return the value from the xth element) End Sub ' The array and the output is shown in figure 4

### 1.2 Dynamic array

It often happens that the required size of the array is unknown at the time that the code is written. The solution is to declare a dynamic array whose dimension can be varied within the procedure. A dynamic array normally involves a two stage process.

**Declare the array**without dimension to reserve the name - use the**Dim**statement and an array with empty brackets (code 2a line 108)**Redimension the array**to the required size using the**Redim**statement

A **dynamic array** can have its dimension changed whilst the code is running (within certain limits) - see code 2a.

### Redim statement

According to the statement syntax, the **ReDim** statement is used to size or resize a dynamic array already declared with a **Dim** statement

- SYNTAX: ReDim [Preserve] arrayname(subscripts)

where Preserve is optional, and subscripts represents the dimension of the array. See code 2a line 114

**Code 2a:**

`DemoDynamic1`

Step 1: The **Dim**statement reserves the name. Step 2: The

**Redim**statement creates the elements allowing data assignment

Sub DemoDynamic1() ' Dynamic array - no dimension at the point of declaration. The brackets are empty () Dim Price() As Double ' this statement reserves the name Price in computer memory ' Suppose that the analyst determines that the array needs to have 7 elements ' This is performed with the ReDim statement Redim Price(1 to 7) ' the reserved name Price is assigned a dimension of 7 elements. Equivalent to a row vector ' the array data type cannot be changed with ReDim so Price is of the type Double ' Populate selected elements Price(1) = 10# Price(3) = 33.33 Price(5) = 55# Price(7) = 70.7 End Sub

An alternate undocumented technique for the creation and dimension of a dynamic array is described by Getz and Gilbert (2006, p.379). This involves use of the **ReDim** statement to declare (create) the array. See code 2b.

### Preserve keyword

The **ReDim** statement erases all existing data in the array. All elements are reset to their default values, such as 0 for numeric type like Integer, and Double, and "" for String type array elements. To retain (preserve) the existing values when the array is resized, include the **Preserve** keyword. See code 2b line 146.

**Code 2b:**

`DemoDynamic2`

**ReDim**one stage - combined declaration and size operation

Sub DemoDynamic2() ReDim Price(1 to 7) As Double ' declare and dimension a dynamic array in the procedure declaration ' the ReDim declaration / dimension statement is still valid within an Option Explicit declaration module ' Populate selected elements Price(1) = 10# Price(3) = 33.33 Price(5) = 55# Price(7) = 70.7 1: Stop ' see figure 5 ' numeric colon is a label and is only used for reference purposes in the associated figures Redim Preserve Price(1 to 9) Price(9) = 90.9 2: Stop ' see figure 6 End Sub

### LBound and UBound functions

Description and Syntax:

VBA function | Description / argument |
---|---|

LBound(arrayname [,dimension]) | Returns a long for the smallest index value of the array dimensiondimension (optional): the array dimension - default 1 if omitted |

UBound(arrayname [,dimension]) | Returns a long for the largest index value of the array dimensiondimension (optional): the array dimension - default 1 if omitted |

The **LBound** and **UBound** functions are demonstrated in **DemoDynamic7** and figure 9 below.

**Dynamic arrays :: sample code**

Option Base 1 Sub DemoDynamicArr4() ' Dynamic array :: 2 dimension with upper index numbers as variables Dim fourth_array() As Integer ' declare a dynamic array Dim x As Integer, y As Integer Dim a As Integer, b As Integer: a = 4: b = 3 ReDim fourth_array(a, b) As Integer ' Size :: 1st dimension 1 to a, 2nd dimension 1 to b (Option base 1) For x = 1 To a For y = 1 To b fourth_array(x, y) = x * y Next y Next x MsgBox "The value of fourth_array(" & a & "," & b & ") is " & fourth_array(a, b) ' return the value from the a,b element End Sub

Sub DemoDynamicArr5() ' Dynamic array :: 1. Declare a dynamic array ' 2. Apply a size with the ReDim statement ' 3. Populate the array ' 4. Resize both dimension of the populated array ' 5. Return a value Dim fifth_array() As Integer ' declare a dynamic array Dim x As Integer, y As Integer Dim a As Integer, b As Integer: a = 4: b = 3 ReDim fifth_array(a, b) As Integer For x = 1 To a For y = 1 To b fifth_array(x, y) = x * y Next y Next x 1: Stop ReDim fifth_array(a + 2, b + 2) As Integer 2: Stop MsgBox "The value of fifth_array(" & a & "," & b & ") is " & fifth_array(a, b) ' return the value from the a,b element End Sub ' The array at two Break Points, and the output is shown in figure 7

Sub DemoDynamicArr6() ' Dynamic array :: a populated array is resized ' use the Preserve keyword to retain the values Dim sixth_array() As Integer Dim x As Integer, y As Integer Dim a As Integer, b As Integer a = 4 b = 3 ReDim sixth_array(1 To a, 1 To b) As Integer For x = 1 To a For y = 1 To b sixth_array(x, y) = x * y Next y Next x 3: Stop ReDim Preserve sixth_array(1 To a, 1 To b + 2) As Integer 4: Stop MsgBox "The value of sixth_array(" & a & "," & b & ") is " & sixth_array(a, b) End Sub The array at two Break Points, and the output is shown in figure 8

Sub DemoDynamicArr7() ' Dynamic array :: demonstrates the LBound and UBound functions Dim seventh_array() As Integer Dim x As Integer, y As Integer Dim a As Integer, b As Integer Dim LB1 As Long, UB1 As Long, LB2 As Long, UB2 As Long Dim Prompt As String a = 6: b = 2016 ReDim seventh_array(1 To a, 2012 To b) As Integer For x = 1 To a For y = 2012 To b seventh_array(x, y) = x * y Next y Next x ReDim Preserve seventh_array(1 To a, 2012 To b + 3) LB1 = LBound(seventh_array): UB1 = UBound(seventh_array) LB2 = LBound(seventh_array, 2): UB2 = UBound(seventh_array, 2) Prompt = "Dimension 1 - lower bound: " & LB1 & vbNewLine Prompt = Prompt & "Dimension 1 - upper bound: " & UB1 & vbNewLine Prompt = Prompt & "Dimension 2 - lower bound: " & LB2 & vbNewLine Prompt = Prompt & "Dimension 2 - upper bound: " & UB2 MsgBox Prompt, , "seventh_array :: dimension index numbers" End Sub ' The array Type and MsgBox is shown in figure 9

### Copy array to dynamic array **without** ReDim

A dynamic array does not require a **ReDim** statement if you assign an initialized array, of the same data type, to it. See the examples in code 3.

**Code 3:**

`DemoDynamicArrAssignment`

Sub DemoDynamicArrAssignment() Dim ArrS1(1 To 5, 1 To 3) As String ' static array Dim ArrS2(1 To 3, 1 To 6) As String ' static array Dim ArrD() As String ' dynamic array Dim i As Integer, j As Integer ' loop counters ' write static array1 For i = 1 To UBound(ArrS1) For j = 1 To UBound(ArrS1, 2) ArrS1(i, j) = "(" & i & "," & j & ")" Next j Next i ' write static array2 For i = 1 To UBound(ArrS2) For j = 1 To UBound(ArrS2, 2) ArrS2(i, j) = "(" & i & "," & j & ")" Next j Next i ' assign array to undimensioned dynamic array ArrD() = ArrS1() ' 1. ArrD() is 5 x 3 ' add 4 columns to the dynamic array ReDim Preserve ArrD(LBound(ArrD) To UBound(ArrD), LBound(ArrD, 2) To UBound(ArrD, 2) + 4) ' 2. ArrD() is 5 x 7 ' assign array to dimensioned dynamic array ArrD() = ArrS2() ' 3. ArrD() is 3 x 6 End Sub

**About Code 3**

**Lines 161 and 162:**Declare two*static*arrays, named ArrS1 and ArrS2, each of data type String**Lines 163:**Declare a*dynamic*array named ArrD of data type String**Lines 169**and**176:**Populate each element of the static array with its respective index coordinates in the form (1,1) (1,2),...(i,j)**Lines 181:**Assign the**ArrS1**static array with dimension 5 x 3 to the uninitialized dynamic array**ArrD**- figure 10**Lines 184:**Redimension**ArrD**by adding 4 columns. The existing upper index number of the second dimension is returned by`UBound(ArrD, 2)`

. The result is shown in the upper panel of figure 11**Lines 187:**Assign the**ArrS2**static array with dimension 3 x 6 to the initialized dynamic array**ArrD**with existing dimension 5 x 7.**ArrD**is effectively re dimensioned to 3 x 6 - see figure 11

## References

Getz K, and M Gilbert, (2006), 'VBA Developer's Handbook'', Wiley

## Related material

Black Scholes on the HP10bII+ financial calculator

**Development platform:**Excel 2016 (64 bit) Office 365 ProPlus and VBA 7.1**Revised:**Saturday 25th of February 2023 - 10:12 AM, [Australian Eastern Time (AET)]