Building strings for arrays

I don't know about you, but I hate building strings for arrays. That's where you have to wrap each string in double quotes and delimit with a comma, like this:


"Once upon a time"



Invariably, I make a mistake in typing (stupid fingers!) and have to correct it. So I came up with a way to generate it automatically. Enter the QuoteString function!

The QuoteString function

Function QuoteString(str As String, delimiter As String) As String()

Dim tempstring() As String
Dim newString As String

  newString = Replace(str, " ", delimiter)

  ' split the string into an array, using delimiter
  tempstring = Split(newString, delimiter)

  QuoteString = tempstring

End Function

Now all you have to do is use one set of quotes, and specify the delimiter, and the function returns your string as an array. Note that it splits the string based on the spaces found between each string.

Sample usage:

Sub TestQuoteString()

' declare String array
Dim arr() As String
' declare Variant array
Dim arr2() As Variant
Dim i As Long

arr = QuoteString("Once upon a time", ",")

For i = LBound(arr) To UBound(arr)
  Debug.Print arr(i)
Next i

arr2 = Array("Once", "upon", "a", "time")

For i = LBound(arr2) To UBound(arr2)
  Debug.Print arr2(i)
Next i

End Sub

You might ask what the difference is between these two looping sections, and whether the second isn't better. After all, you can just pass the string directly (using the Array function) without the intermediate step of converting it to a string.

First of all, the second array is declared as Variant, which I only use as a last resort. But it's required if you want to use the Array function.

Second, the QuoteString function allows us to pass in an unaltered string so it's harder to screw up (which is the point of this post). Once again I like my way better.

About JP

I'm just an average guy who writes VBA code for a living. This is my personal blog. Excel and Outlook are my thing, with a sprinkle of Access and Word here and there. Follow this space to learn more about VBA. Keep Reading »

Share This Article:

Share and bookmark this articledelicious buttonfacebook buttonlinkedin buttonstumbleupon buttontwitter button

comment bubble 2 Comment(s) on Building strings for arrays:

  1. Rick Rothstein (MVP - Excel) writes:

    I think I must be missing something in your blog article. If you have this text string…

    TextString = "Once upon a time"

    then why not just create and populate your array like this…

    Arr = Split("Once upon a time")

    where Arr can be declared as a dynamic String array (probably preferred) or a simple Variant? The nice thing about the Split function is you can specify an optional second argument (its default value is the space character) and create arrays from text strings with various delimiters. For example…

    Arr = Split("one,two,three,four", ",")

    • You're right, I can just use Split directly.

      Dim arr() As String
      arr = Split("Once upon a time")

This article is closed to any future comments.
Random Data Generator