Building SQL in VBA? BAD programmer. BAD. me too.
'join strings using joiner. a can be list of string params, or a single collection, or a single array
Function Join(joiner As String, ParamArray a() As Variant) As String
'examples
'w = join(" and ","customer=12","color=red")
'w=join(" and ",array("length=3","width=2"))
'dim col as new collection: col.add("height=77")
'col.add("material=steel")
'w=join(" and ",col)
Dim s As String
Dim i As Integer
Dim c As Variant
If Not IsArray(a) Then
s = ""
ElseIf UBound(a) = 0 Then
If TypeOf a(0) Is Collection Or IsArray(a(0)) Then
For Each c In a(0)
If Len(c) > 0 Then
If Len(s) > 0 Then s = s & joiner
s = s & c
End If
Next
Else
s = ""
End If
Else
For i = LBound(a) To UBound(a)
If TypeOf a(i) Is Collection Then
For Each c In a(i)
If Len(c) > 0 Then
If Len(s) > 0 Then s = s & joiner
s = s & a(i)
End If
Next
ElseIf Len(a(i)) > 0 Then
If Len(s) > 0 Then s = s & joiner
s = s & a(i)
End If
Next i
End If
Join = s
End Function
Sub TestJoin()
Dim s As String
Dim t As String
Dim arr As New Collection
Dim a As Variant
t = "apples,pears,oranges"
a = Split(t, ",")
s = Join(",", Split(t, ","))
If s <> t Then
MsgBox "the sword has not been mended"
End If
s = Join(" ", "hello", "access", "world")
If s <> "hello access world" Then
MsgBox "I'm sorry Dave I can't do that"
End If
arr.Add "hello"
arr.Add "access"
arr.Add "world"
s = Join(" ", arr)
If s <> "hello access world" Then
MsgBox "i only wanted to say hello"
End If
End Sub
