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