If you need to store and manipulate a list of your text values in VBA code, string arrays can be very useful. A string array can store multiple elements of the same data type. It can be one-dimensional or multidimensional. In this example, we will demonstrate four examples of using Excel VBA with Array of Strings.
Introduction to VBA Array
An array is a collection of variables of the same type, stored under the same variable name. Arrays can be one-dimensional or multidimensional. Arrays can hold different types of data such as integers, strings, booleans, objects, and variants, etc.
How to Declare Different Types of String Array in VBA
i. Declare Static String Array
If you want an array that can store string values, you can declare a static string array. You can define the size of the array while declaring it. The below image shows how to declare a string array. If the size of the array is 3, it can store 4 elements that will have index numbers 0,1,2, and 3.
You can also define the start and end positions of an array by using “To”.
ii. Declare Variant String Array
When you want to store string values in an array but don’t want to define the size of the array, you can declare a variant-type array.
iii. Declare String Array Using Split Function
Arrays can be declared using the Split function. The Split function splits a string into individual elements using the delimiter “,“.
iv. Declare Multidimensional Array
A multidimensional array can hold values in more than one dimension. The following figure shows how to declare a 3×2 multidimensional array.
Read More: How to Declare Array in Excel VBA
How to Launch VBA Editor in Excel
To access the Microsoft Visual Basic window, go to the Developer tab and click on Visual Basic. You can also open it by pressing Alt+F11 on your keyboard.
Go to the Insert tab and click on Module to launch code Module.
Excel VBA Array of Strings: 4 Examples
In this article, we will show you four examples of using an array of strings in Excel VBA. We will use the following dataset for this purpose.
1. Use VBA Array Function with String
In this example, we will use the VBA Array function with string. We will declare an array that will have strings as elements. Then we will store them in a column. The procedure is described in the following section.
- First of all, open the VBA code Module and write the following code.
Sub String_Array()
Dim Movies() As Variant
Dim numRows As Long
Dim i As Long
' Define the movies array
Movies = Array("The Shining", "Rashomon", "The Departed", _
"Jaws", "Inception", "Pulp Fiction")
numRows = UBound(Movies) + 1
' Insert the movies into column E starting from row 5
For i = 1 To numRows
Range("E" & i + 4).Value = Movies(i - 1)
Next i
End Sub
Code Breakdown
Movies = Array("The Shining", "Rashomon", "The Departed", _
"Jaws", "Inception", "Pulp Fiction")
numRows = UBound(Movies) + 1- The code defines an array that contains six elements.
- Then it calculates the number of rows using the UBound.
For i = 1 To numRows
Range("E" & i + 4).Value = Movies(i - 1)- This line uses the For loop to find the number of rows and stores the array elements in cells E5 to E10.
- Press F5 or click on the Run button to run the code.
Read More: Excel VBA Array of Arrays
2. Split Strings and Store Them in VBA Array [Video2]
In this example, we will split strings and store them in different cells. We will take the column of full names (cells B5 to B10) and split them into first and last names. Then the first and last names will be stored in two different columns.
- First, write the following code in the code Module.
Sub SplitNames()
Dim Names() As Variant
Dim firstNames() As Variant
Dim lastNames() As Variant
Dim numRows As Long
Dim i As Long
' Get the names from column B
Names = Range("B5:B10").Value
numRows = UBound(Names, 1)
' Split the names into first and last names
ReDim firstNames(1 To numRows)
ReDim lastNames(1 To numRows)
For i = 1 To numRows
nameParts = Split(Names(i, 1), " ")
firstNames(i) = nameParts(0)
lastNames(i) = nameParts(UBound(nameParts))
Next i
' Write the first and last names into columns C and D
Range("C5:C10").Value = WorksheetFunction.Transpose(firstNames)
Range("D5:D10").Value = WorksheetFunction.Transpose(lastNames)
End Sub
Code Breakdown
Names = Range("B5:B10").Value
numRows = UBound(Names, 1)- This line gets the full names from cells B5 to B10 and stores them in an array named Names.
ReDim firstNames(1 To numRows)
ReDim lastNames(1 To numRows)
For i = 1 To numRows
nameParts = Split(Names(i, 1), " ")
firstNames(i) = nameParts(0)
lastNames(i) = nameParts(UBound(nameParts))
Next i
- This part splits the elements of the Names array and stores them into two different arrays named firstNames and lastNames.
- Run the code to get your desired results.
3. Create a Dynamic String Array
Sometimes while working with an array we might not know how many elements will be stored in the array. Therefore, we can not declare the array size initially. We will need a dynamic array so that we can increase the size of the array each time we find a new element to store in that array. To resize an array, we will use the ReDim Preserve function.
- First, go to the VBA code Module and insert the following code there.
Sub Dynamic_Array()
Dim Names() As String
Dim i As Integer
Dim j As Integer
Dim allNames As String
'Store the names with more than 20 movies in an array
j = 0
For i = 5 To 10
If Range("E" & i).Value > 20 Then
' Resize the Names array to include the current name
ReDim Preserve Names(j)
Names(j) = Range("B" & i).Value
j = j + 1
End If
Next i
'Concatenate all the names into a single string
allNames = Join(Names, ", ")
'Display all the names in a message box
MsgBox "Director names with more than 20 movies are: " & allNames
End Sub
Code Breakdown
For i = 5 To 10
If Range("E" & i).Value > 20 Then
ReDim Preserve Names(j)
Names(j) = Range("B" & i).Value
j = j + 1
End If
Next i- This code uses For loop to find numbers greater than 20.
- Each time it finds a number greater than 20, it resizes the Names array and adds the corresponding name to that array.
allNames = Join(Names, “, “)
- This line uses the Join function to join all the elements of the Names array separated by a comma.
4. Use LBound and UBound Functions with VBA String Array
The LBound and UBound functions are used to determine the lower and upper bounds of an array. We will use these functions with a string array in this example to find the lower and upper bound of the movieNames array. Then we will store the elements of this array in the worksheet. The process is described below.
- Open the Microsoft Visual Basic window and insert a Module.
- Next, write the following code in the Module.
Sub Array_LBOUND_UBOUND()
Dim movieNames() As String
Dim i As Integer
Dim lowerBound As Integer
Dim upperBound As Integer
Dim allMovies As String
'Store the movie names in an array
For i = 5 To 10
ReDim Preserve movieNames(i - 5)
movieNames(i - 5) = Range("E" & i).Value
Next i
'Determine the lower and upper bounds of the array
lowerBound = LBound(movieNames)
upperBound = UBound(movieNames)
'Concatenate all the movie names into a single string
allMovies = Join(movieNames, ", ")
'Display the movie names in a message box
MsgBox "The movie names are: " & allMovies
End Sub
Code Breakdown
For i = 5 To 10
ReDim Preserve movieNames(i - 5)
movieNames(i - 5) = Range("E" & i).Value
Next i- This part stores the movie names in an array.
lowerBound = LBound(movieNames)
upperBound = UBound(movieNames)- These lines determine the lower and upper bounds of the array.
Run the code and you will find your desired output.
Handling Variant Type Array in Excel VBA
A variant type array can hold different types of data. We can declare this type of array using the keyword “Variant”. You will see an example where we will store numbers in a variant-type array and then sum all the elements of that array. The following code is used for this purpose.
Sub Variant_Array()
Dim Movies() As Variant
Dim TotalMovies As Long
Dim i As Long
' Assign values from cells E5 to E10 to the Movies array
Movies = Range("E5:E10").Value
' Loop through the elements of the array
For i = LBound(Movies) To UBound(Movies)
'sum up the values
TotalMovies = TotalMovies + Movies(i, 1)
Next i
' Display the total number of movies in a message box
MsgBox "Total number of movies: " & TotalMovies
End Sub
Code Breakdown
Movies = Range("E5:E10").Value- This line assigns values from cells E5 to E10 to the Movies.
For i = LBound(Movies) To UBound(Movies)
TotalMovies = TotalMovies + Movies(i, 1)
Next i- This part loops through the elements of the array and adds all the elements of the array.
How to Convert into Excel VBA Array of Strings from Range
We can assign elements from a range to an array easily. In this example, we will show you how to create an array of strings from a range. The VBA code with an explanation is given below.
Sub Array_Range()
Dim movieArray() As Variant
Dim rowCount As Integer
movieArray = Range("E5:E10")
Dim concatenatedMovies As String
' Loop through the rows
For rowCount = 1 To UBound(movieArray)
concatenatedMovies = concatenatedMovies & _
movieArray(rowCount, 1) & vbNewLine
' Concatenate the element and a line break
Next rowCount
MsgBox "Movies in the array: " & vbNewLine & _
concatenatedMovies
End Sub
Code Breakdown
movieArray = Range("E5:E10")- This line gets the data from range E5:E10 and adds them to movieArray.
For rowCount = 1 To UBound(movieArray)
concatenatedMovies = concatenatedMovies & _
movieArray(rowCount, 1) & vbNewLine
Next rowCount- This part concatenates the value of the element in the first column of the current row of the movieArray array to the concatenatedMovies The “&” operator is used to concatenate the strings, and the vbNewLine constant is appended to create a line break after each concatenated element.
Read More: How to Convert Range to Array in Excel VBA
How to Use Multidimensional Array in Excel VBA
Multidimensional arrays can have more than one dimension. You need to define the dimensions while declaring an array. In this example, we will create a two-dimensional array that will store the first and last names of the movie directors. The VBA code is explained in the following section.
Sub Multi_Array()
' Define the array to store the names
Dim nameArray(1 To 6, 1 To 2) As String
Dim numRows As Long
Dim numCols As Long
Dim concatenatedNames As String
Dim rng As Range
Set rng = Range("C5:D10")
Dim cell As Range
Dim i As Long, j As Long
i = 1
j = 1
For Each cell In rng
nameArray(i, j) = CStr(cell.Value)
j = j + 1
If j > 2 Then
j = 1
i = i + 1
End If
Next cell
' Determine the number of rows and columns in the array
numRows = UBound(nameArray, 1)
numCols = UBound(nameArray, 2)
' Loop through the rows and columns to concatenate the names
For i = 1 To numRows
For j = 1 To numCols
concatenatedNames = concatenatedNames & nameArray(i, j)
If j < numCols Then
concatenatedNames = concatenatedNames & " "
End If
Next j
concatenatedNames = concatenatedNames & vbNewLine
Next i
MsgBox concatenatedNames
End Sub
Code Breakdown
Dim nameArray(1 To 6, 1 To 2) As String- This line declares a 6×2 multidimensional array named nameArray to store string values.
For Each cell In rng
nameArray(i, j) = CStr(cell.Value)
j = j + 1
If j > 2 Then
j = 1
i = i + 1
End If
Next cell- This part iterates through each cell in the rng range object and assigns the value of the current cell to the corresponding element in the array.
If j > 2 Then- This line checks whether j has exceeded the maximum number of columns in the two-dimensional array.
Read More: Excel VBA Multidimensional Arrays
Things to Remember
- The index of an array begins at zero. If we declare the array size to be 3, it can hold 4 elements.
- It is not recommended to fix the size of the array to avoid errors.
- The LBound and UBound functions are used to determine the size of the array.
Frequently Asked Questions
1. How do I create an array of values in Excel VBA?
You can create an array of values similar to the array of strings. You need to define the data type as per your need or you can just define the data as a variant that can hold all types of data.
2. How do you create an array of variables in VBA?
Arrays allow you to store multiple values in one variable. You can create an array of variables by using Dim, Static, Public, or Private keywords. You can fix the array size in the parenthesis if you want.
3. How do you create an array list in VBA?
You can create an array list by using the ArrayList class from the “System.Collections” namespace of the .NET Framework. To create an array list, you need to make sure that the box next to Microsoft.NET Framework is checked. You can find this option in the References dialogue box from the Tools menu in the Visual Basic Editor.
Download Practice Workbook
Download this practice workbook to exercise while reading this article.
Conclusion
Thanks for reading this article. Hopefully, you found the article useful. In this article, we demonstrated four examples of using Excel VBA with Array of Strings. We used the VBA array function with strings to store string values. We used the Split function to split a string and store each part in an array. Moreover, we created a dynamic array without defining the size. The LBound and UBound functions are used when the size of the array is unknown. We have covered both one-dimensional and multidimensional arrays in this article. If you have any queries regarding this article, feel free to let us know in the comment section below.
