Dim fruits As Variant followed by fruits = Array("Apple", "Banana", "Cherry") creates an array of three strings in one line. For a fixed list of known size, declare a typed array and fill it: Dim regions(1 To 4) As String, then regions(1) = "East" and so on.
To break text into an array, use Split: parts = Split("East,West,North", ",") gives three elements. To turn an array back into one string, use Join: Join(parts, " | ") returns “East | West | North”.
Split always returns an array that starts at 0, so the first item is parts(0) and the last is parts(UBound(parts)). Array also starts at 0 unless the module has Option Base 1. Loop with For i = LBound(parts) To UBound(parts) so the code works either way.
Splitting on a single space breaks at every space, so double spaces create empty elements. Clean the text first with Application.Trim, which also collapses spaces in the middle.
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 in 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 count of elements that you want to store in it.
- Next, you need to use VBA’s ARRAY function to define the values that you want to specify.
- After that, specify all the strings in the function, separated by commas.
- In the end, you can access all the strings using the element number. As the code has Option Base 1, the first string is myArray(1).

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 have a string that has multiple substrings, you can split it using 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 them 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.
One thing to keep in mind: the array that SPLIT returns always starts from 0, even when you have Option Base 1 at the top of the module. So, in the above code, myArray(0) is “Today” and myArray(4) is “day”. That’s why the loop uses LBound and UBound instead of fixed numbers.
Split a String with a Comma
The delimiter can be any character or text, like a comma, a semicolon or a dash. When you split a list where each comma is followed by a space, the substrings get a space at the start, so use TRIM to remove it.
Sub vba_split_comma()
Dim myArray() As String
Dim i As Long
myArray = Split("Apple, Mango, Banana", ",")
For i = LBound(myArray) To UBound(myArray)
Debug.Print Trim(myArray(i))
Next i
End Sub
If you leave out the delimiter, SPLIT uses a space. And SPLIT also has an optional third argument (limit) to tell it how many substrings you want, like Split(“a-b-c-d”, “-“, 2), which gives you “a” and “b-c-d”.
Combine an Array of Strings into One String (JOIN)
As I said at the start, you can also combine all the strings in an array into one string. For this, you need to use the JOIN function, which is the opposite of SPLIT. You specify the array and the delimiter that you want to put between the strings.
Sub vba_join_array()
Dim myArray() As Variant
Dim myString As String
myArray = Array("One", "Two", "Three")
myString = Join(myArray, ", ")
Debug.Print myString
End Sub
This code prints “One, Two, Three” in the immediate window. If you leave out the delimiter, JOIN uses a space, and if you use an empty string (“”), it joins the strings without anything between them.
Count the Words in a String
As the array from SPLIT starts from 0, the count of its elements is UBound + 1. You can use this to count the words in a sentence:
Sub vba_count_words()
Dim myArray() As String
myArray = Split("Today is a good day", " ")
Debug.Print UBound(myArray) + 1
End Sub
This prints 5. Keep in mind that two spaces next to each other create an empty element, so clean the text with Application.WorksheetFunction.Trim first if it might have extra spaces.
Store the Strings from an Array in Cells
You can also write all the strings from an array into cells in one go. The code below splits a sentence and writes each word into its own cell in a row, starting from cell A1.
Sub vba_array_to_cells()
Dim myArray() As String
myArray = Split("Today is a good day", " ")
Range("A1").Resize(1, UBound(myArray) + 1).Value = myArray
End Sub
To write the words down a column instead, use Application.WorksheetFunction.Transpose(myArray) and resize the range to rows instead of columns.
Frequently Asked Questions
Should I declare a string array As String or As Variant?
With the ARRAY function, declare it As Variant, as ARRAY always returns a Variant array, and storing it in a String array gives a type mismatch error. With SPLIT, you can use As String, as SPLIT returns an array of strings.
Why does my Split array start from 0 when I have Option Base 1?
Because SPLIT always returns an array that starts from 0, whatever Option Base says. Use LBound and UBound to loop through it, so your code works either way.
How do I check if a string is in an array?
Loop through the array and compare each element with the string, or use the FILTER function. You can find the code for this in Search for a Value in an Array.
Written by
Puneet Gogia
Microsoft MVP — Excel · Founder, Excel Champs
Twelve years of teaching Excel, and a decade before that using it as a data analyst. Every tutorial here is written from a file I actually built.