VBA : Join Functions (Strings, Collections, Arrays, Ranges) (1 Viewer)

Status
Not open for further replies.

Stormin

Nawly Ragistarad Usar
Local time
Today, 07:27
Joined
Dec 30, 2016
Messages
74
I have just finished rebuilding my join functions to implement recursive behaviour (where the function calls itself), an idea taken from my recent build of a function that joins Excel ranges together. These functions mimic the behaviour of the Join() function which joins an array of strings together with a delimiter (e.g. a comma for CSV).

To see the Excel JoinRng() function it is here: https://www.excelforum.com/tips-and...ctions-strings-collections-arrays-ranges.html

Even though these aren't Access-specific, hopefully they can make a few jobs easier inside VBA.


Simple one first...

JoinStr
Code:
Public Function JoinStr(Delimiter As String, ParamArray Strings()) As String
    
' Author        :   Stormin
' Date Created  :   July 2017
' Purpose       :   Joins a series of strings together with a delimiter
' Notes         :   Skips empty strings
    
    Dim itm As Variant
    
    For Each itm In Strings
        If VarType(itm) = vbString Then
            If Len(itm) > 0 Then JoinStr = JoinStr & Delimiter & itm
        End If
    Next itm
    
    JoinStr = Mid(JoinStr, Len(Delimiter) + 1)
    
End Function
Hopefully you can see how this behaves. It takes each item you input, checks it is a string, checks it's not empty, then adds it to the end of output with the delimiter. At the end it removes the delimiter at the beginning of the output string. So...
Code:
JoinStr("\","C:","TEMP","myfiles")
would finally output "C:\TEMP\myfiles". The first pass would give "\C:" because JoinStr = "" initially... "" & "" & "C:" which is why we remove the first delimiter at the end.



JoinCol and JoinArr work on the same principles, except they check the input for more properties than just being a string. If the input is not a string, and if IncludeSubLevels = True, then they check for arrays and collections which are then in turn checked for strings, or collections, or arrays, and so on and so on.

JoinCol
Code:
Public Function JoinCol( _
    Delimiter As String, _
    ByVal CollectionToJoin As Collection, _
    Optional IncludeSubLevels As Boolean = False) _
As String
    
' Author        :   Stormin
' Date Created  :   July 2017
' Purpose       :   Joins a collection of strings together with a delimiter
' Notes         :   - Skips empty strings
'                   - Recursive operations possible with JoinArr and JoinCol
    
    Dim itm As Variant
    Dim t   As String
    
    For Each itm In CollectionToJoin
        Select Case VarType(itm)
            Case vbString
                If Len(itm) > 0 Then JoinCol = JoinCol & Delimiter & itm
            Case Is >= vbArray
                If IncludeSubLevels Then
                    t = JoinArr(Delimiter, itm, IncludeSubLevels)
                    If Len(t) > 0 Then JoinCol = JoinCol & Delimiter & t
                End If
            Case Else
                Select Case TypeName(itm)
                    Case "Collection"
                        If IncludeSubLevels Then
                            t = JoinCol(Delimiter, itm, IncludeSubLevels)
                            If Len(t) > 0 Then JoinCol = JoinCol & Delimiter & t
                        End If
                End Select
        End Select
    Next itm
    
    JoinCol = Mid(JoinCol, Len(Delimiter) + 1)
    
End Function

JoinArr
Code:
Public Function JoinArr( _
    Delimiter As String, _
    ByVal ArrayToJoin As Variant, _
    Optional IncludeSubLevels As Boolean = False) _
As String

' Author        :   Stormin
' Date Created  :   July 2017
' Purpose       :   Joins an array of strings together with a delimiter
' Notes         :   - Skips empty strings
'                   - Recursive operations possible with JoinArr and JoinCol

    Dim i As Integer
    Dim t As String
    
    For i = LBound(ArrayToJoin) To UBound(ArrayToJoin)
        Select Case VarType(ArrayToJoin(i))
            Case vbString
                If Len(ArrayToJoin(i)) > 0 Then JoinArr = JoinArr & Delimiter & ArrayToJoin(i)
            Case Is >= vbArray
                If IncludeSubLevels Then
                    t = JoinArr(Delimiter, ArrayToJoin(i), IncludeSubLevels)
                    If Len(t) > 0 Then JoinArr = JoinArr & Delimiter & t
                End If
            Case Else
                Select Case TypeName(ArrayToJoin(i))
                    Case "Collection"
                        If IncludeSubLevels Then
                            t = JoinCol(Delimiter, ArrayToJoin(i), IncludeSubLevels)
                            If Len(t) > 0 Then JoinArr = JoinArr & Delimiter & t
                        End If
                End Select
        End Select
    Next i
    
    JoinArr = Mid(JoinArr, Len(Delimiter) + 1)

End Function

To test this you can step through the following sub:
Code:
Sub test()

    'test the JoinArr and JoinCol functions
    
    Dim c As New Collection
    Dim d As New Collection
    Dim e(1) As String
    Dim f(1)
    
    Dim x, y, z
    
    x = Array("x1", "x2", "x3")
    ReDim y(1)
    y(0) = "y"
    y(1) = x
    ReDim z(1)
    z(0) = "z"
    z(1) = y
    
    c.Add "test1"
    c.Add "test2"
    e(0) = "test3"
    e(1) = "test4"
    f(0) = 5
    f(1) = 6
    
    d.Add "START"
    d.Add c
    d.Add e
    d.Add f
    d.Add z
    d.Add "FINISH"
    
    Debug.Print JoinCol("__", d, True)
    
End Sub

As always I have left the author information in because I'm too lazy to remove it, and the notes are helpful.
Feel free to remove / edit / play with any part of my code or comments to your heart's content!
 

ashleedawg

"Here for a good time"
Local time
Yesterday, 23:27
Joined
Jun 22, 2017
Messages
154
I love-to-hate figuring out recursive procedures! ...but are you aware of the JOIN function (the opposite of SPLIT)? It does the same thing as both your joinArr and joinStr functions:

Code:
    Join(arr, "\")
with any mix of data types, and can skip blanks with:

Code:
    Replace(Join(arr, "\"), "\\", "\")
and along the same lines with a collection:

Code:
    Function joinCol(dlim, coll)
        Dim itm
        For Each itm In coll
            If itm <> "" Then joinCol = joinCol & itm & dlim
        Next
    End Function
Granted, these don't account for sub-arraysa-of-sub-collections like your code (do you come across those very often?) and I'll admit I thought I knew a simpler way to deal with that, but gave up after crashing Access repeatedly with 'Stack Overflow' errors... Don't really see that one very often! :rolleyes:
 
Status
Not open for further replies.

Users who are viewing this thread

Top Bottom