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.

Or

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: