Joining Two Dimensional Arrays

The Join function takes an array and smushes it together into a String. I love the Join function. The only thing I don’t like about it is when I forget that it doesn’t work on 2d arrays. Join only works with 1-dimensional arrays. The last time my memory failed me, I decided to write my own. And here it is.

It’s pretty simple. It loops through the first dimension (the row dimension) and joins each line with sLineDelim. Inside that loop, it joins each element in the second dimension with sWordDelim. What this function doesn’t do is automatically insert itself into only the projects I want. That requires me to remember that I wrote it and where I put it. In reality, I’ll probably reinvent the wheel the next time I need it.

Here’s my extensive testing procedure.

7 thoughts on “Joining Two Dimensional Arrays

  1. A simpleway could be the following one:

    Private Function Join2D(ByVal vArray As Variant) As String
    Dim vItem As Variant
    Dim sReturn As String
    For Each vItem In vArray
    Join2D = Join2D & vItem
    Next
    End Function

  2. I wrote something similar recently right here at my post String concatenation is like the weather…
    =JoinText(Array,[Delimiter],[FieldDelimiter],[EndDelimiter],[SkipBlanks],[Transpose])

    The default delimiter is a comma. The Field Delimiter is a separate Delimiter that gets added if your input array is 2D, and the default is also a comma. EndDelimiter puts an extra Delimiter of your choice on the end if you want one. Aesthetics only, really. SkipBlanks does just that. Transpose ditto.

  3. Jeff’s post String concatenation is like the weather… includes this little gem:

    “…back in 2012 Nigel Heffernan at Excellerando.Blogspot.com did: he put up some nifty code for joining and splitting two dimensional arrays

    Your code’s a good teaching sample, and it works well for small arrays. But the native string-handling in VBA is not good, and larger arrays are going to be problematic without some very careful coding to minimise explicit concatenation (and all the other VBA operations that allocate memory).

  4. Nigel: I just read your code and it seems to do the same thing as mine, other than skipping blank rows.

  5. Dick, here is a slightly more compact version of your Join2D function (uses only one loop)…

Leave a Reply

Your email address will not be published. Required fields are marked *