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
- First, declare an array without the element count that you want to store in there.
- Next, you need to use VBA’s ARRAY function to define the value that you want to specify.
- After that, specify all the strings using a comma into the function.
- 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.
More on VBA Arrays
VBA Add New Value to the Array | VBA Clear Array | VBA Loop Through an Array | VBA Multi-Dimensional Array | VBA Range to an Array | VBA Search for a Value in an Array | VBA Sort Array | VBA Array Length (Size) | VBA Dynamic Array | ISARRAY Function | ARRAY Function | VBA Arrays