Excel VBA Array of Strings (4 Examples)

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.

Download Practice Workbook

Download this practice workbook to exercise while reading this article.

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: Excel VBA to Declare Multidimensional Array of Unknown Size

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

Movies = Array("The Shining", "Rashomon", "The Departed", _
    "Jaws", "Inception", "Pulp Fiction")
    numRows = UBound(Movies) + 1

For i = 1 To numRows
        Range("E" & i + 4).Value = Movies(i - 1)

Read More: How to Declare Array in Excel VBA (3 Easy Ways)

Similar Readings

• Excel VBA to Populate Array with Cell Values (4 Suitable Examples)
• Excel VBA: Determine Number of Elements in Array (4 Examples)
• How to Create an Array in Excel VBA (4 Ideal Methods)
• Excel VBA to Read CSV File into Array (4 Ideal Examples)
• Excel VBA: Remove Duplicates from an Array (2 Examples)

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

Names = Range("B5:B10").Value
    numRows = UBound(Names, 1)

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

Read More: How to Split a String into an Array in VBA (3 Effective Ways)

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

 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

Read More: How to ReDim Preserve 2D Array in Excel VBA (2 Easy Ways)

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

For i = 5 To 10
        ReDim Preserve movieNames(i - 5)
        movieNames(i - 5) = Range("E" & i).Value
    Next i

 lowerBound = LBound(movieNames)
    upperBound = UBound(movieNames)

Read More: How to Use UBound on Multidimensional Array with VBA in Excel

Similar Readings

• VBA to Get Unique Values from Column into Array in Excel (3 Criteria)
• Excel VBA to Create Data Validation List from Array
• Calculate the Average of an Array with VBA (Macro, UDF, and UserForm)
• How to Sort Array with Excel VBA (Both Ascending and Descending Order)
• Excel VBA to Transpose Array (3 Suitable Examples)

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

   Movies = Range("E5:E10").Value

 For i = LBound(Movies) To UBound(Movies)
        TotalMovies = TotalMovies + Movies(i, 1)
    Next i

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

movieArray = Range("E5:E10")

  For rowCount = 1 To UBound(movieArray)
        concatenatedMovies = concatenatedMovies & _
        movieArray(rowCount, 1) & vbNewLine
    Next rowCount

Read More: How to Convert Range to Array in Excel VBA (3 Ways)

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

 Dim nameArray(1 To 6, 1 To 2) As String

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

  If j > 2 Then

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

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.

Related Articles

• Excel VBA Array of Arrays (3 Examples)
• How to Redim 2D Array with VBA in Excel (3 Examples)
• Excel VBA Multidimensional Array for Assigning Values (6 Ways)
• How to Find Lookup Value in Array in Excel VBA (2 Easy Ways)
• VBA Read Text File into Array (2 Suitable Methods)
• How to Use Arrays Instead of Ranges in Excel VBA
• Excel VBA 2 Dimensional Array Initialization