VBA String Array

What is Excel VBA String Array?

In Excel VBA, a String Array is a variable that can store multiple strings. An array is a collection of variables of the same type, and a String Array specifically holds strings. Each element in the array is a separate string.

View this simple example below to learn how to do a VBA String Array declaration.

VBA String Array - Definition Example - 1

In this example, the way the array is initialized is called the “Static Declaration.” There are multiple ways to declare a string in Excel VBA. The output is printed in a message box, as shown below. It is how you define a VBA String Array.

VBA String Array - Definition Example - 2

What is Excel VBA String Array?

VBA String Arrays in Excel are used to store and manage collections of strings.
Declare a String Array using syntax as Dim myArray() As String.
Initialize the array with values using the Array function or by assigning values in a loop.
Use built-in functions like Split for array creation and Join for converting arrays to strings.
Avoid storing non-string data types in a String Array. Use appropriate data structures for mixed data.
Use LBound and UBound for the lower and upper bounds (first element and last element) of the array

How to Initialize Excel VBA String Array?

Below, you can see how to initialize an array and perform a VBA String Add item to print all the values.

Open the Excel workbook and select the “Developer” tab on the Excel toolbar.

In Developer, select “Visual Basic.” It will open the Excel VBA Editor.

In the Editor toolbar, select “Insert,” then in the dropdown, select “Module.”
Define a sub-procedure to print a VBA String Array.
Define a Variant array datatype. It is not only used to hold multiple data types but also to declare multiple values in a single line instead of manually adding each element.
Perform the VBA String Array add item, and define them using the “Array” function.
Print the values in the next line using the vbCrLf constant, as shown.

The vbCrLf constant in VBA is used to represent a newline character sequence. Mainly, it is used to insert a carriage or line break return in strings when you want to create a multiline text output.

Here,
“vb” stands for Visual Basic.
“CrLf” stands for Carriage Return and Line Feed, which are control characters used in text files and communication protocols to indicate the end of a line and move the cursor to the beginning of the next line.

Code:

Sub StringArrayExample()
Dim myArray() As Variant
myArray = Array(“Apple”, “Banana”, “Orange”, “Grapes”)
MsgBox “Fruit 1: ” & myArray(0) & vbCrLf & _
“Fruit 2: ” & myArray(1) & vbCrLf & _
“Fruit 3: ” & myArray(2) & vbCrLf & _
“Fruit 4: ” & myArray(3)
End Sub
Click the “Run” icon on the VBA toolbar.

Also, you can run the subroutine by pressing “F5”. Either way, it will print the message box as shown below.

Let us look some interesting examples on VBA String Array declaration.

Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)

If you want to learn Excel and VBA professionally, then Excel VBA All in One Courses Bundle (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

Start Learning Now

Examples of String Array in Excel VBA

You can see different, interesting examples of how VBA String Array can be used in various ways.

Example #1

In this example, you can see how to print a VBA String Array using a for-loop instead of declaring every single element as seen in earlier examples through the VBA String Array length. Follow the steps below on how to do that.

Step 1: Start by creating a sub-procedure to loop through the length of the array.

Step 2: Define an array of the Variant VBA datatype.

Step 3: Provide values to the string array.

Step 4: Define an iterative variable that performs VBA String Array count by individually going through each element in the array.

Step 5: Define a FOR-loop running through the array and print each element using the message box function, as seen below.

Code:

Sub StringArrayLoopExample()
Dim people() As Variant
people = Array(“John”, “Corey”, “Adam”, “Manny”, “Jacket”)
Dim i As Integer
For i = LBound(people) To UBound(people)
MsgBox “Person ” & i + 1 & “: ” & people(i)
Next i
End Sub

Step 6: Press “F5” to run the VBA code. The output is displayed as shown below:

VBA String Array in Excel - Example 1 - Step 6e

Example #2

In this example, you have a list of employees and their salary. You are supposed to find the average/median salary in the list and also print the employee names earning above the median salary. It can be done using VBA String Array count.

Step 1: Define a subroutine to find the median salary.

Step 2: Define two arrays, an integer and a string data type, both of which can be defined using the Variant datatype to store the employee’s salary and names, respectively, in arrays.

Step 3: Provide values for the employee names and the salary in the arrays.

Step 4: Define three variables, namely the total salary (sum of the integer array) and the average salary to store it in once its value has been calculated. Along with that, declare an iterative variable to go through both arrays.

Step 5: Find the total salary, that is, the sum of all the salaries in the array.

You find the first and last elements of the array using the LBound and UBound functions, respectively.

Step 6: To avoid division by zero error when finding the average salary, you check if the array has any elements or not.

The snippet UBound(employeeSalaries) - LBound(employeeSalaries) + 1 returns the VBA Array length.

Step 7: In case the array is empty, assign the average salary as 0.

Step 8: Print the average salary in a Message Box function.

Step 9: Find the employees earning above the average salary by initializing a new variable and declaring a new line using the “vbCrLf” constant.

Step 10: Define a FOR-loop running through the salary array and check if any number in the array is greater than the average salary found.

If the condition is true, then the employee’s names, along with their salaries, are defined. To not define every single line in a Message box, it is simpler to use a string array for the employees, as seen in step 9.

Step 11: Print the variable.

Code:

Sub EmployeeSalaryExample()
Dim employeeNames() As Variant
Dim employeeSalaries() As Variant
employeeNames = Array(“John”, “Jane”, “Bob”, “Alice”)
employeeSalaries = Array(50000, 60000, 75000, 70000)
Dim totalSalary As Double
Dim averageSalary As Double
Dim i As Integer
For i = LBound(employeeSalaries) To UBound(employeeSalaries)
totalSalary = totalSalary + employeeSalaries(i)
Next i

‘Ensure array length is not zero to avoid divide by zero error and calculate average salary
If UBound(employeeSalaries) – LBound(employeeSalaries) + 1 > 0 Then
averageSalary = totalSalary / (UBound(employeeSalaries) – LBound(employeeSalaries) + 1)
Else
averageSalary = 0
End If
MsgBox “Average Salary: ” & averageSalary
Dim aboveAverageEmployees As String
aboveAverageEmployees = “Employees with Salaries Above Average:” & vbCrLf

‘Print employees whose salaries are above average salary
For i = LBound(employeeSalaries) To UBound(employeeSalaries)
If employeeSalaries(i) > averageSalary Then
aboveAverageEmployees = aboveAverageEmployees & employeeNames(i) & “: ” & employeeSalaries(i) & vbCrLf
End If
Next i
MsgBox aboveAverageEmployees
End Sub

Step 9: Click the green arrow button when you want to run the code. It will pop up a Message Box once it is run.

VBA String Array in Excel - Example 2 - Step 12a

VBA String Array in Excel - Example 2 - Step 12b

Example #3

In this example, a database for the library consisting of books, authors and their respective published years are stored. Given the name of the book, it will print the author’s name along with the publication year. Such an application can be created using Excel VBA String Array.

Step 1: Always start by naming the subroutine to create a search box for a book’s information.

Step 2: Define the book titles in a VBA String array, as a variant data type. Define the names of the respective book authors in another String array.

Step 3: Write the years of their publication in an integer array of variant data types.

Step 4: Define an InputBox to accept the name of the book the user is searching for.

Step 5: Define an integer variable and declare it as -1. This is done to prevent an “Out of Range” error if the book being searched for doesn’t exist in the database.

Step 6: Run a FOR-loop through the book names. Run a FOR-loop through the book names. The LBound and UBound functions, which return the first and last numbers, can be used to perform the VBA String Array count through the array.

Step 7: Define an If condition to check whether each element in the given book matches or not. In the loop, both the user input and the array values are converted to uppercase before checking. This is done to make the input of the book by the user hassle-free and will

make both the strings of the same case.

Once the element is found, the index is assigned to the index variable and the for-loop immediately exited.

Step 8: Initialize another If condition to check if the index exists or not.

Print the book title, author and publication year in separate lines using the Message box function.

Step 9: Define an ELSE condition in case a matching book does not exist to prevent any runtime error.

Code:

Sub BookInformationExample()
Dim bookTitles() As Variant
bookTitles = Array(“The Great Gatsby”, “To Kill a Mockingbird”, “1984”, “The Catcher in the Rye”)
Dim bookAuthors() As Variant
bookAuthors = Array(“F. Scott Fitzgerald”, “Harper Lee”, “George Orwell”, “J.D. Salinger”)
Dim publicationYears() As Variant
publicationYears = Array(1925, 1960, 1949, 1951)
Dim searchTitle As String
searchTitle = InputBox(“Enter the title of the book you want to search:”)
Dim foundIndex As Integer
foundIndex = -1 ‘ if not found
For i = LBound(bookTitles) To UBound(bookTitles)
If UCase(bookTitles(i)) = UCase(searchTitle) Then
foundIndex = i
Exit For
End If
Next i
If foundIndex <> -1 Then
MsgBox “Book Title: ” & bookTitles(foundIndex) & vbCrLf & _
“Author: ” & bookAuthors(foundIndex) & vbCrLf & _
“Publication Year: ” & publicationYears(foundIndex)
Else
MsgBox “Book not found.”
End If
End Sub

Step 10: Run the subroutine. An input box will pop up, where the user can input the name of the book, similar to a search bar.

Excel VBA String Array - Example 3 - Step 10a

Once the user provides an input, they can view the details of the book which will come in a message box.

Excel VBA String Array - Example 3 - Step 10b

Important Things to Note

Always initialize your String Array before use, either with the Array function or by assigning values in a loop.
Be mindful of memory usage, especially with large arrays. Release memory if needed.
If your requirements involve mixed data types, explore other array types or data structures in VBA.
String Arrays in VBA have a 0 once declared. If dynamic sizing is needed, consider alternative data structures.
If your data requirements go beyond strings, explore other VBA data structures suited for diverse data types.

Frequently Asked Questions (FAQs)

1. How to split string into array in VBA?

Using the “Split” function in Excel VBA, you can split a string into an array as seen below.

This will print the array in a message box.

2. Can I change the size of a String Array dynamically in VBA?

No, the size of a String Array in VBA is fixed once declared. Use dynamic data structures like Collection or ArrayList for a resizable alternative. So, you cannot perform VBA String Array add item dynamically.

3. Can a String Array hold non-string data types in VBA?

No, a String Array in VBA can only hold string data types. Use a different array type, like a Variant Array, for mixed data types. To declare multiple values in a VBA String Array length requires you to use a Variant variable.

4. Are there any built-in functions for manipulating String Arrays in VBA?

Yes, VBA provides built-in functions like Split for creating arrays from strings and Join for converting arrays to strings. Additionally, use standard array manipulation functions like UBound and LBound for array bounds.

VBA String Array

What is Excel VBA String Array?

Table of contents

Key Takeaways

How to Initialize Excel VBA String Array?