Go Back   Access World Forums > Microsoft Access Reference > Code Repository

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 07-27-2017, 08:04 AM   #1
Stormin
Nawly Ragistarad Usar
 
Join Date: Dec 2016
Location: UK
Posts: 50
Thanks: 8
Thanked 6 Times in 4 Posts
Stormin is on a distinguished road
VBA : Join Functions (Strings, Collections, Arrays, Ranges)

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

Stormin is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Optimization Question: Arrays, collections, tempTables: which is faster? PaulSchrum Modules & VBA 16 08-04-2011 06:09 AM
Arrays, Collections, Movies pono1 Sample Databases 0 02-19-2009 05:10 PM
Strings within strings using expression builder psgardiner Queries 1 06-07-2007 03:21 AM
Printing files from a button but need to join filename strings Sam Summers Modules & VBA 1 05-18-2006 09:12 PM
Using Collections gblack Modules & VBA 1 08-30-2005 10:07 PM




All times are GMT -8. The time now is 02:12 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World