VBA String Array

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 codeVBA CodeVBA code refers to a set of instructions written by the user in the Visual Basic Applications programming language on a Visual Basic Editor (VBE) to perform a specific task.read more 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 stringCode For VBA StringString functions in VBA do not replace the string; instead, this function creates a new string. There are numerous string functions in VBA, all of which are classified as string or text functions.read more 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 functionUsing VBA ARRAY FunctionArrays are used in VBA to define groups of objects. There are nine different array functions in VBA: ARRAY, ERASE, FILTER, ISARRAY, JOIN, LBOUND, REDIM, SPLIT, and UBOUND.read more.

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 VBAMessage Box In VBAVBA MsgBox function is an output function which displays the generalized message provided by the developer. This statement has no arguments and the personalized messages in this function are written under the double quotes while for the values the variable reference is provided.read more.

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 keyExcel Shortcut KeyAn Excel shortcut is a technique of performing a manual task in a quicker way.read more 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 LBoundVBA LBoundLBound in VBA or “Lower Bound” extracts the lowest number of an array. For example, if the array says “Dim ArrayCount (2 to 10) as String” then using LBound function we can find the least number of the array length i.e. 2.read more identifies the array variable’s lower value. The VBA UBound functionVBA UBound FunctionUBOUND, also known as Upper Bound, is a VBA function that is used in conjunction with its opposite function, LBOUND, also known as Lower Bound. This function is used to determine the length of an array in a code, and as the name suggests, UBOUND is used to define the array's upper limit.read more 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: –