VBA String Array

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.

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 –

• VBA String Comparison
• Find Array Size in VBA
• SubString in Excel VBA
• Variant Data Type in VBA