FLASH SALE! - "CHATGPT AND ARTIFICIAL INTELLIGENCE FOR MICROSOFT EXCEL AT 60% OFF" Enroll Now

VBA String Array

Updated on June 12, 2024
Article byJeevan A Y
Edited byAshish Kumar Srivastav
Reviewed byDheeraj Vaidya, CFA, FRM

Excel 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.

VBA String Array Example 1-1

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.

–>> If you want to learn Excel VBA professionally, then our VBA Basic Course (16+ hours) is the perfect solution. In our Basic Excel VBA course you learn the skill of automating tasks using Variables, Data Types, Ranges, and Cells in VBA. Master Control Structures, including Conditional Statements and Loops, and discover techniques for manipulating data through sorting, filtering, and formatting. By the end of the course, you will be able to apply your acquired skills to real projects, culminating in an Excel VBA project which will solidify your ability to create efficient, automated solutions.

Examples of String Array in Excel VBA

Below are examples of an Excel VBA string array.

You can download this VBA String Array Excel Template here – VBA String Array Excel Template

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
VBA String Array Example 1-1

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.

VBA String Array Example 1-2.png

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
VBA String Array Example 1-3.png

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.

VBA String Array Example 1.gif

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.

VBA String Array Example 1-6

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.

VBA String Array Example 1-7

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
LBound & UBound Example 1-8

Now, the message box will show each city name separately.

VBA String Array Example 1.gif

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.

Split Example 2

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
Split Example 2-1

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
split Example 2-2.png

Now, the SPLIT function split values determine the highest array length.

Things to Remember

  • 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.

Recommended Articles

This article is 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: –