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
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!
About 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.Follow