I'm sure this has been done before. But I couldn't find any code samples (really!), so I wrote my own.
Here's how I build a string (for message boxes and such) from the contents of an array.
Function BuildString(arr() As Variant, Optional delimiter As String = ",") _
As String
' loop through array and build text string consisting
' of all the array elements, delimited as specified
Dim j As Long
For j = LBound(arr) To UBound(arr)
BuildString = BuildString & delimiter & arr(j)
Next j
' remove leading delimiter and return string
BuildString = Right$(BuildString, Len(BuildString) - Len(delimiter))
End Function
Sample usage
Sub TestBuildString()
Dim stringArray() As Variant
stringArray = Array("Dog", "Car", "Door")
MsgBox BuildString(stringArray, "|")
' outputs Dog|Car|Door
End Sub
Now if someone could just point me to some existing code…?
I think you just reinvented the Join() wheel.
LOL can't remember every function! I knew it was something but couldn't remember what.
There is a small bug in your function. If the separator is not exactly one character, the removal of the leading delimiter doesn't do exactly what it should.
Thank you, I think I fixed it.
A little simpler than this (from your blog article code)…
' remove leading delimiter and return string
BuildString = Right$(BuildString, Len(BuildString) – Len(delimiter))
is this…
' remove leading delimiter and return string
BuildString = Mid$(BuildString, Len(delimiter) + 1)
Notice for the VB Mid function that if you omit the 3rd argument, it is automatically set to the length of the remaining text.