VBA String Array

In VBA, a String Array is nothing but an array variable that can hold more than one string value with a single variable.

For example, look at the VBA code below.

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, we have declared an array variable and assigned the length of an array as 1 to 5.

Dim CityList(1 To 5) As Variant

Next, we have written a code to show these city names in the message box.

CityList(1) = "Bangalore" CityList(2) = "Mumbai" CityList(3) = "Kolkata" CityList(4) = "Hyderabad" CityList(5) = "Orissa"

Next, we 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 we 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 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 lines we write for string values. So, let's look at how we write code for VBA string arrays.

Below are 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.

We do not need to decide the array length well in advance.

Code:

Sub String_Array_Example1()

Dim CityList() As Variant

End Sub

As you can see above, we have not written any lengths in the parenthesis. So now, for this variable, let's insert values 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 we have made in the above code is that we have not decided on the lower limit and upper limit of an array variable. Therefore, the ARRAY function array count will start from 0, not 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. Again, we get the same result as the previous code.

Example #2

VBA String Array with LBOUND & UBOUND Functions

If you don't want to show all the city lists in a single message box, then you need to include loops and define one more variable for loops.

Now, to include FOR NEXT loop, we are unsure how many times we need to run the code. Of course, we can decide five times in this case, but that is not the right way to approach the problem. So, how about the idea of auto lower and higher level array length identifiers?

When we open FOR NEXT loop, we usually decide the loop length as 1 to 5 or 1 to 10, depending upon the situation. So, instead of manually entering the numbers, let's automatically use the LBOUND and UBOUND functions to decide on the lower and upper values.

For LBound and Ubound, we have supplied an array name, CityList. The VBA LBound identifies the array variable's lower value. The 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" take 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 one below.

Bangalore;Mumbai;Kolkata;Hydrabad;Orissa

In this case, all the cities combine with the colon separating each city. Therefore, we need to use the SPLIT function to separate each city in such cases.

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," which is the one character 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, the SPLIT function split values determine the highest array length.

• The LBOUND and UBOUND are functions to determine the array lengths.
• The ARRAY function can hold many values for a declared variable.
• Once we want to use the ARRAY function, do not decide the array length.