7 Examples to Create VBA String Array

Create and access a string array in VBA – one dimensional

A string array in VBA can be created by using Dim keyword as follows:

Dim str_arr(7) As String

Its index starts at 0.

Dim str_arr(3 To 10) As String

Its index starts at 3

Accessing string array element

The following VBA program creates and accesses a string array and its elements. Then we displayed an element value in the message box:

Sub string_arr_ex()

Dim str_arr_days(7) As String

'String array elements

str_arr_days(0) = "Saturday"

str_arr_days(1) = "Sunday"

str_arr_days(2) = "Monday"

str_arr_days(3) = "Tuesday"

'Display Array element

MsgBox "Array element 2 = " & str_arr_days(1)

End Sub

Output:

An example declaring an array with “To”

Now we declared the string same array with (3 To 10) syntax.

Code:

Sub string_arr_ex()

Dim str_arr_days(3 To 10) As String

'String array elements

str_arr_days(3) = "Saturday"

str_arr_days(4) = "Sunday"

str_arr_days(5) = "Monday"

str_arr_days(6) = "Tuesday"

'Display Array element

MsgBox "Array element 5 = " & str_arr_days(5)

End Sub

Output:

Accessing all array elements by For..Each loop

The VBA For Each loop can be used to iterate through each array element. The example below creates a string array.

A For..each loop is used to iterate through each array element.

Then we collect all array values in a string variable and display the result in a message box.

VBA code:

Sub string_arr_ex()

Dim str_arr_colors(5) As String

Dim AllEle As String

'Creating string array elements

str_arr_colors(0) = "Black"

str_arr_colors(1) = "White"

str_arr_colors(2) = "Red"

str_arr_colors(3) = "Green"

str_arr_colors(4) = "Orange"

'Message box shows all array items with linebreak

For Each Item In str_arr_colors

AllEle = AllEle & vbNewLine & Item

Next Item

MsgBox AllEle

End Sub

Result:

A two-dimensional string array example

A 2-D string array can be created by using the Dim keyword as follows:

Dim str_arr_2d(1 To 5, 1 To 3) As String

The example below creates a two-dimensional array and accesses one element and displays in the message box.

Code:

Sub string_arr_ex()

Dim str_arr_2d(1 To 5, 1 To 3) As String

Dim AllEle As String

'Creating a 2-D string array

str_arr_2d(1, 1) = "Black"

str_arr_2d(1, 2) = "White"

str_arr_2d(1, 3) = "Red"

str_arr_2d(2, 1) = "Green"

str_arr_2d(2, 2) = "Orange"

str_arr_2d(2, 3) = "Orange"

MsgBox "Array element (2,2) = " & str_arr_2d(2, 2)

End Sub

Output:

Creating a two-dimensional string array based on Excel cells data

In this example, we will create two dimensional array from the range of cells in Excel. For that we have the following sample sheet:

VBA Code

Sub string_arr_ex()

'Creating a 2D String Array

Dim str_arr_products(2 To 9, 2 To 5) As String

Dim i As Integer, j As Integer

'Array elements from Excel sheet

For i = 2 To 8

For j = 2 To 5

str_arr_products(i, j) = Cells(i, j).Value

Next j

Next i

MsgBox "Row = 4 Col = 2 : " & str_arr_products(4, 2)

End Sub

Result:

How did it work?

A 2-D array of string type is created
Outer for loop is used to run from 2 to 8 indices (representing the first dimension of the array)
The inner loop executed from 2 to 5 (second dimension of the array)
In each iteration, the element is assigned the value from the cell
In the message box, we displayed (4, 2) array item, which is row number 4 and column 2

Get the size of above string array

So, how many elements do we have in our above array?

Either count them one by one or use the LBound and UBound functions of the array.

The program below uses these functions to get the size of our Excel-based array:

Sub string_arr_ex()

'Creating a 2D String Array

Dim str_arr_products(2 To 9, 2 To 5) As String

Dim i As Integer, j As Integer

'Array elements from Excel sheet

For i = 2 To 8

For j = 2 To 5

str_arr_products(i, j) = Cells(i, j).Value

Next j

Next i

'Getting the array length of string array

i = UBound(str_arr_products, 1) - LBound(str_arr_products, 1) + 1

j = UBound(str_arr_products, 2) - LBound(str_arr_products, 2) + 1

k = i * j

MsgBox "2-D String Array has " & k & " Elements."

End Sub

Output:

Display the whole 2-D string array in the message box

Sub string_arr_ex()

'Creating a 2D String Array

Dim str_arr_products(2 To 9, 2 To 5) As String

Dim i As Integer, j As Integer

Dim Str_Prods As String

'Array elements from Excel sheet

For i = 2 To 8

For j = 2 To 5

str_arr_products(i, j) = Cells(i, j).Value

Next j

Next i

'Displaying whole array in Message Box

For i = 2 To UBound(str_arr_products)

Str_Prods = Str_Prods & str_arr_products(i, 2) & " " & str_arr_products(i, 3) & " " & str_arr_products(i, 4) & " " & str_arr_products(i, 5) & vbNewLine

Next i

MsgBox Str_Prods

End Sub

Result: