JIYIK CN >

In any code execution in any programming language, the need to store information is sometimes unavoidable. The good news is that VBA allows for a variety of options when it comes to storing data, one of which is arrays.

In VBA, arrays are classified based on their length and flexibility of data type.

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

Declare and initialize a dynamic string array

The following code block will demonstrate how to create and initialize a string array.

Method 1 - Declare as Variantdata type

A dynamic array is declared by creating a variable in a variant data type. The array is then Array()initialized with a collection ( ).

Sub DynamicArrayDemo()

Dim stringArray As Variant

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

Debug.Print stringArray(3)

End Sub

DynamicArrayDemoOutput:

Monkey

Method 2 - Declare as a string and then use Split()the function

Declare an stringArrayarray of strings named without explicitly stating the bounds.

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

DynamicArrayDemoOutput:

Cheetah

Declare and initialize a static string array

The following code block demonstrates different ways to declare and initialize a static string array.

Method 1 - Declare LowerBoundand UpperBound:

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

grammar:

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

parameter:

The following example declares a stringArraystring array named with six elements, 0 through 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

StaticArrayDemoOutput:

Elephant

Method 2 - Explicitly change下限

Lower boundDeclare a string array with a common 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

StaticArrayDemoOutput:

Lion

Method 3 - Using Multidimensional Array Declaration and Initialization

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

grammar:

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

parameter:

In the following example, a multidimensional array is 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

MultiStaticArrayDemoOutput:

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