Introduction | Example | Tutorial | Applications

Introduction - VBA Array of Strings

This tutorial shows you how to declare and initialize a VBA array of strings using the Split function. This array of strings solution is compact and efficient, saving memory and reducing VBA execution time

Example - VBA Array of Strings

Sub ArrayOfStringsDemo()
Dim arrChoices() As String
arrChoices = Split("Yes,No,Maybe", ",")
End Sub

Make powerful macros with our free VBA Developer Kit

This is actually pretty neat. If you have trouble understanding or remembering it, our free VBA Developer Kit can help. It’s loaded with VBA shortcuts to help you make your own macros like this one - we’ll send a copy, along with our Big Book of Excel VBA Macros, to your email address below.

I'll take a free VBA Developer Kit

Tutorial - VBA Array of Strings

The example macro uses the VBA Split function to create an array with 3 elements, numbered from 0 to 2. It’s a compact, one-line solution to making arrays of strings with VBA. Let’s explain how it works.

The Split function takes a string (your first argument) and splits it into an array at the delimiter you give in the second argument. In the example macro, I split my string at each comma.

The beauty of this approach is you don’t have to know how big your array is before creating it! Notice, I didn’t define the size of my string array with my Dim statement. The Split function just knows how big it needs my new array to be.

The result is an array of strings with 3 elements, defined below:

arrChoices(0) = Yes
arrChoices(1) = No
arrChoices(2) = Maybe

In this tutorial, we showed you how to quickly declare an array of strings by taking advantage of how the VBA Split function behaves. That’s just one many tricks included in our comprehensive VBA Arrays Cheat Sheet which has over 20 pre-built macros and dozens of tips designed to make it easy for you to handle arrays.

Items to Consider - VBA Array of Strings

Delimiters

It’s important to recognize when your list of strings might actually contain a comma. If you find yourself in this situation, simply enter a new delimiter, like a semi-colon:

Sub ArrayOfStringsDemo2()
Dim arrChoices() As String
arrChoices = Split("Yes;No;Maybe", ";")
End Sub

Spaces

Notice how I don’t have spaces immediately before or after my delimiter - the comma, or semi-colon in the above examples. If you put a space, the space will be retained as part of your split string. In other words, you may have a leading or a trailing space before or after your string in the array.

Application Ideas - VBA Array of Strings

Arrays of strings are useful. Many people find themselves needing to create an array of months in Excel VBA. You can do this in a number of ways, but since we’re talking about the Split method, that’s what I’m going to show. It’s almost Christmas, so I’m giving this gift for you to use in whatever project you’re working on!

Bonus Macro - Array of Months

Sub ArrayOfMonths()
Dim arrMonths() As String
arrMonths = Split("January,February,March,April,May,June,July,August,September,October,November,December", ",")
End Sub

When you’re done with this tutorial, I hope you’ll read my tutorials on other string manipulation functions.

Please, share this article on Twitter and Facebook. Sharing on social media is how I’m able to reach and teach more people about the awesome power of VBA.

I want to thank all my readers who have already subscribed to my free wellsrPRO VBA Training Program and I encourage you to go ahead and subscribe using the form below if you haven’t done so. You’ll love the great VBA content I send your way!