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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
Public Function Join2D(ByVal vArray As Variant, Optional ByVal sWordDelim As String = " ", Optional ByVal sLineDelim As String = vbNewLine) As String Dim i As Long, j As Long Dim aReturn() As String Dim aLine() As String ReDim aReturn(LBound(vArray, 1) To UBound(vArray, 1)) ReDim aLine(LBound(vArray, 2) To UBound(vArray, 2)) For i = LBound(vArray, 1) To UBound(vArray, 1) For j = LBound(vArray, 2) To UBound(vArray, 2) 'Put the current line into a 1d array aLine(j) = vArray(i, j) Next j 'Join the current line into a 1d array aReturn(i) = Join(aLine, sWordDelim) Next i Join2D = Join(aReturn, sLineDelim) End Function |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
Sub TEST_Join2d() Dim a(1 To 2, 1 To 2) As String a(1, 1) = "The" a(1, 2) = "Quick" a(2, 1) = "Brown" a(2, 2) = "Fox" Debug.Print Join2D(a) Debug.Print Debug.Print Join2D(a, ",") Debug.Print Debug.Print Join2D(a, , "|") Debug.Print Debug.Print Join2D(a, ";", "||") End Sub |
For 4 alternatives:
http://www.snb-vba.eu/VBA_Arrays_en.html#L_6.15.2
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
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.
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).
Nigel: I just read your code and it seems to do the same thing as mine, other than skipping blank rows.
Jon von der Heyden has a routine at http://jonvonderheyden.net/excel/concatenate-a-range-of-values/ that lets you conditionally concatenate based on some criteria. Nice idea.
Dick, here is a slightly more compact version of your Join2D function (uses only one loop)…
How can I combine first row from first dimension with first row from second dimension? I mean in the case with “The quick brown fox” I would like to accomplish “The brown quick fox”?
@Maduser1