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

Pre-order our VBA Strings Cheat Sheet

Our team is putting the finishing touches on the wellsrPRO VBA Strings Cheat Sheet, a brand-new reference guide containing everything you could possibly want to know about controlling VBA Strings.

Save 25% when you pre-order our VBA Strings Cheat Sheet before its launch on 6/28.

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.


Becoming a VBA expert isn't hard
Over 5000 members are improving their VBA skills for free with our email tutorials. Why don't you join them? Our experts share time-saving VBA tips and we'll give you access to our huge macro library - it's sure to speed up your macro development.

I want to get your free VBA material

Comments

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 if you haven’t done so. You’ll love the great VBA content I send your way!