Excel VBA String Array
In VBA, a String array is nothing but an array variable which can hold more than one string value with a single variable.
For example, look at the below VBA code.
Code:
Sub String_Array_Example() Dim CityList(1 To 5) As Variant CityList(1) = "Bangalore" CityList(2) = "Mumbai" CityList(3) = "Kolkata" CityList(4) = "Hyderabad" CityList(5) = "Orissa" MsgBox CityList(1) & ", " & CityList(2) & ", " & CityList(3) & ", " & CityList(4) & ", " & CityList(5) End Sub
In the above code, I have declared as array variable and assigned the length of an array as 1 to 5.
Dim CityList(1 To 5) As Variant
For this array variable, I have assigned 5 city names mentioning each array count in the parenthesis.
CityList(1) = "Bangalore" CityList(2) = "Mumbai" CityList(3) = "Kolkata" CityList(4) = "Hyderabad" CityList(5) = "Orissa"
Next, I have written a code to show these city names in the message box.
MsgBox CityList(1) & ", " & CityList(2) & ", " & CityList(3) & ", " & CityList(4) & ", " & CityList(5)
When I run this code, we will get a message box that shows all the city names in a single message box.
We all know this has saved so much time from our schedule by eliminating the task of declaring individual variables for each city. However, one more thing you need to learn is we can still reduce the code of line we write for string values. Let’s look at how we write code for VBA string arrays.
Examples of String Array in Excel VBA
Below are the examples of an excel VBA string array.
Example #1
As we have seen in the above code, we learned we could store more than one value in the variable based on the array size determined.

4.9 (1,353 ratings) 35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
Now what we need to do is do not decide the array length well in advance.
Code:
Sub String_Array_Example1() Dim CityList() As Variant End Sub
As you can see above inside the parenthesis, I have not written any lengths. Now for this variable, let’s insert values by using VBA ARRAY function.
Inside the Array pass the values on double quotes, each separated by a comma (,).
Code:
Sub String_Array_Example() Dim CityList() As Variant CityList = Array("Bangalore", "Mumbai", "Kolkata", "Hyderabad", "Orissa") End Sub
Now retain the old code to show the result of city names in the message box in VBA.
Code:
Sub String_Array_Example1() Dim CityList() As Variant CityList = Array("Bangalore", "Mumbai", "Kolkata", "Hyderabad", "Orissa") MsgBox CityList(0) & ", " & CityList(1) & ", " & CityList(2) & ", " & CityList(3) & ", " & CityList(4) End Sub
One change I have made in the above code is since we have not decided the lower limit and upper limit of an array variable, and we have used the ARRAY function array count will start from 0, not from 1.
So, that is the reason we have mentioned the values as CityList(0), ClityList(1), CityList(2), CityList(3), and CityList(4).
Now run the code through excel shortcut key F5 or manually. We get the same result as we get from the previous code.
Example #2
VBA String Array with LBOUND & UBOUND Functions
In case if you don’t want to show all the city lists in a single message box, then you need to include loops, define one more variable for loops.
Now to include FOR NEXT loop, we are not sure how many times we need to run the code. In this case, we can decide it like 5 times, but that is not the right way to approach the problem. So how about the idea of auto lower and higher level array length identifier???
When we open FOR NEXT loop, we usually decide the loop length as 1 to 5 or 1 to 10 depending upon the situation. Instead of entering the numbers manually, let’s use LBOUND and UBOUND function to decide on the lower value and upper value automatically.
For LBound and Ubound, I have supplied an array name, i.e., CityList. VBA LBound identifies the lower value of the array variable, and VBA UBound function identifies the upper value of the array variable.
Now show the value in the message box, instead of inserting the serial number, let the loop variable “k” takes the array value automatically.
Code:
Sub String_Array_Example1() Dim CityList() As Variant Dim k As Integer CityList = Array("Bangalore", "Mumbai", "Kolkata", "Hyderabad", "Orissa") For k = LBound(CityList) To UBound(CityList) MsgBox CityList(k) Next k End Sub
Now the message box will show each city name separately.
Example #3
VBA String Array with Split Function
Now assume you have city names like the below.
Bangalore;Mumbai;Kolkata;Hydrabad;Orissa
In this case, all the cities are combined together with the colon separating each city. In such cases, we need to use the SPLIT function to separate each city.
For Expression, supply the city list.
Code:
Sub String_Array_Example2() Dim CityList() As String Dim k As Integer CityList = Split("Bangalore;Mumbai;Kolkata;Hydrabad;Orissa", For k = LBound(CityList) To UBound(CityList) MsgBox CityList(k) Next k End Sub
The next argument is “Delimiter,” i.e., what is the one character that is separating each city from other cities. In this case, “Colon.”
Code:
Sub String_Array_Example2() Dim CityList() As String Dim k As Integer CityList = Split("Bangalore;Mumbai;Kolkata;Hydrabad;Orissa", ";") For k = LBound(CityList) To UBound(CityList) MsgBox CityList(k) Next k End Sub
Now SPLIT function split values also determine the highest array length.
Things to Remember
- LBOUND and UBOUND are functions to determine the array lengths.
- ARRAY function can hold many values for a declared variable.
- Once if you want to use the ARRAY function, do not decide the array length.
Recommended Articles
This has been a guide to VBA String Array. Here we discuss how to declare the VBA string Array variable, which can hold more than one string value, along with practical examples and a downloadable template. Below you can find some useful excel VBA articles –
- 35+ Courses
- 120+ Hours
- Full Lifetime Access
- Certificate of Completion