Building a string from an array

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…?

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 5 Comment(s) on Building a string from an array:

  1. I think you just reinvented the Join() wheel.

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

  3. Rick Rothstein (MVP - Excel) writes:

    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.

This article is closed to any future comments.
Peltier Tech Charting Utilities for Excel