VBA Array with Strings

In VBA, you can create an array with strings where each element of the array stores one string that you can further access or even combine into one string. Apart from this, you can also split the main string into multiple sub-strings (using a delimiter) and then store each of them into the array’s elements.

As I said above, there are two ways to use strings in an array, and in this tutorial, we will see how to write code for both.

VBA Array with Strings

Option Base 1
Sub vba_string_array()

Dim myArray() As Variant
myArray = Array("One", "Two", "Three")
   
Debug.Print myArray(1)
Debug.Print myArray(2)
Debug.Print myArray(3)
      
End Sub

1. First, declare an array without the element count that you want to store in there.
2. Next, you need to use VBA’s ARRAY function to define the value that you want to specify.
3. After that, specify all the strings using a comma into the function.
4. In the end, you can access all the strings using the element number.

In the same way, you can also get a string from the cells to store in the array.

VBA Split String and Store in an Array

If you want a string that has multiple substrings you can split it using the VBA’s SPLIT function which uses a delimiter.

Option Base 1
Sub vba_string_array()

Dim myArray() As String
myArray = Split("Today is a good day", " ")
For i = LBound(myArray) To UBound(myArray)
    Debug.Print myArray(i)
Next i
      
End Sub

In this code, you have a string which is a sentence that has five words. And when you use the split function it splits it into five different substrings, and then you have stored it in the elements of the array.

After that, you have the for loop which uses the upper and lower bound for the counter to loop and prints each element of the array into the immediate window.