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
Old 08-04-2017, 11:10 PM   #2
ashleedawg
"Here for a good time"
 
ashleedawg's Avatar
 
Join Date: Jun 2017
Location: Canada
Posts: 152
Thanks: 18
Thanked 37 Times in 34 Posts
ashleedawg is on a distinguished road
Re: VBA : Join Functions (Strings, Collections, Arrays, Ranges)

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!
__________________
_______________________________________
Back in the Fire
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
ashleedawg 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 05:04 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