WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Free Tutorials
  • Certification Courses
  • Excel VBA All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Learn VBA » VBA String Array

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.

VBA String Array Example 1-1

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.

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

Popular Course in this category
Sale
All in One Excel VBA Bundle (35 Courses with Projects)
4.9 (1,353 ratings)
35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
View Course

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

VBA String Array Example 1-1

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.

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

VBA String Array Example 1.gif

 

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.

VBA String Array Example 1-6

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.

VBA String Array Example 1-7

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

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

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,” 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

split Example 2-2.png

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
0 Shares
Share
Tweet
Share
All in One Excel VBA Bundle (35 Courses with Projects)
  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

WallStreetMojo

Download VBA String Array Excel Template

New Year Offer - All in One Financial Analyst Bundle (250+ Courses, 40+ Projects) View More