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”
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 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.
Examples of String Array in Excel VBA
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.
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 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.
Inside the array, pass the values on double quotes, each separated by a comma (,).
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 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.
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.
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 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.
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.
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.”
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.
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: –
- VBA String ComparisonFind Array Size in VBASubString in Excel VBAVariant Data Type in VBA
