How to Declare and Initialize String Array in VBA

1. Declare and Initialize Dynamic String Array
2. Declare and Initialize Static String Array

In any code execution in any programming language, the need for storing information is sometimes inevitable. The good thing is VBA allows several options when storing data, and one of these is the arrays.

In VBA, arrays are categorized based on their length and datatype flexibility.

1. Dynamic Arrays - arrays that are variable in length and datatype
2. Static Arrays - arrays that are fixed in length and datatype.

Moreover, arrays in VBA can also be categorized based on their dimensions.

1. Single-Dimension array - arrays that have one single-line array only.
2. Multidimensional array - arrays that have multiple single-line arrays.

Declare and Initialize Dynamic String Array

The code blocks below will demonstrate how to create and initialize a string array.

Method 1 - Declare as Variant datatype

Declare a dynamic array by creating a variable in a variant datatype. Then the array will be initialized by a collection (Array()).

Sub DynamicArrayDemo()

Dim stringArray As Variant

stringArray = Array("Lion", "Tiger", "Cheetah", "Monkey", "Elephant", "Zebra")

Debug.Print stringArray(3)

End Sub

DynamicArrayDemo Output:

Monkey

Method 2 - Declare as String, then use the Split() function

Declare a string array named stringArray without explicitly declaring the boundaries.

Sub DynamicArrayDemo()

Dim stringArray() As String
Dim str As String

str = "Lion,Tiger,Cheetah,Monkey,Elephant,Zebra"

stringArray = Split("Lion,Tiger,Cheetah,Monkey,Elephant,Zebra", ",")

Debug.Print stringArray(2)
End Sub

DynamicArrayDemo Output:

Cheetah

Declare and Initialize Static String Array

The code blocks below demonstrate different methods to declare and initialize a static string array.

Method 1 - Declaring LowerBound and UpperBound:

Declare a static string array by explicitly declaring its first and last elements.

Syntax:

Dim stringArray([LowerBound] To [UpperBound]) As String

Parameters:

The example below will declare a string array named stringArray with six elements from element 0 to 5.

Sub StaticArrayDemo()

Dim stringArray(0 To 5) As String

stringArray(0) = "Lion"
stringArray(1) = "Tiger"
stringArray(2) = "Cheetah"
stringArray(3) = "Monkey"
stringArray(4) = "Elephant"
stringArray(5) = "Zebra"

Debug.Print stringArray(4)

End Sub

StaticArrayDemo Output:

Elephant

Method 2 - Explicitly Change the Lower bound

Declare a string array by a generic Lower bound value.

Option Base 1

Sub StaticArrayDemo()
Dim stringArray(6) As String

stringArray(1) = "Lion"
stringArray(2) = "Tiger"
stringArray(3) = "Cheetah"
stringArray(4) = "Monkey"
stringArray(5) = "Elephant"
stringArray(6) = "Zebra"

Debug.Print stringArray(1)

End Sub

StaticArrayDemo Output:

Lion

Method 3 - Declare and Initialize using Multidimensional array

In VBA, you can declare arrays up to 60 dimensions.

Syntax:

Dim stingArray( [LowerBound1] to [UpperBound1],[LowerBound2] to [UpperBound2], . . .  ) as String

Parameters:

In the example below, a multidimensional array was declared where the first dimension is 1 to 5; then the other is 1 to 5.

Sub MultiStaticArrayDemo()

Dim stringArray(1 To 5, 1 To 5) As String
Dim i, j As Integer

For i = 1 To 5
    For j = 1 To 5
        stringArray(i, j) = "The value of (" & i & "," & j & ") is " & i * j
        Debug.Print stringArray(i, j)
    Next j
Next i

End Sub

MultiStaticArrayDemo Output:

The value of (1,1) is 1
The value of (1,2) is 2
The value of (1,3) is 3
The value of (1,4) is 4
The value of (1,5) is 5
The value of (2,1) is 2
The value of (2,2) is 4
The value of (2,3) is 6
The value of (2,4) is 8
The value of (2,5) is 10
The value of (3,1) is 3
The value of (3,2) is 6
The value of (3,3) is 9
The value of (3,4) is 12
The value of (3,5) is 15
The value of (4,1) is 4
The value of (4,2) is 8
The value of (4,3) is 12
The value of (4,4) is 16
The value of (4,5) is 20
The value of (5,1) is 5
The value of (5,2) is 10
The value of (5,3) is 15
The value of (5,4) is 20
The value of (5,5) is 25