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
Write macros like this in just 7 days
Over 10,000 people have finished our free Write Better Macros in 7 Days guided challenge. It's powerful, effective and comes with a VBA Developer's Kit full of shortcuts, tips and pre-written macros to make writing VBA easier.
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
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.
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!