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:

From

"Once upon a time"

to

"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.