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
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...
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
JoinArr
To test this you can step through the following 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!
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
Code:
JoinStr("\","C:","TEMP","myfiles")
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!