Quick Jump
Introduction | Example | Tutorial | Applications | Comments

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

Get wellsrPRO for Free

Get these macros delivered right to your spreadsheet for free

Access tutorials and import my macros without ever leaving Excel

Show me - It's free

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

Remember, unless explicitly told to do otherwise, VBA array elements start at 0.

Items to Consider - VBA Array of Strings


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


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 social media and follow me on Google+ and Twitter for even more great VBA content. 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 email list and I encourage you to go ahead and subscribe if you haven’t done so. You’ll love the great VBA content I send your way!

Get wellsrPRO for Free

Get these macros delivered right to your spreadsheet for free

Access tutorials and import my macros without ever leaving Excel

Show me - It's free

About Ryan Wells

Ryan Wells

Ryan Wells is a Nuclear Engineer and professional VBA Developer. He is the lead developer of several VBA applications, including PDF VBA - a leading Excel Add-in for exporting Excel Objects, like charts and tables, to PDFs. Discover more of his popular Excel Add-ins, including Mouse To Macro and CF Shapes, at his dedicated Excel Add-ins page.