Concatenate strings and remove excess comma

Question

I'm trying to concatenate multiple strings and separate them by comma, and then subsequently to remove excess, leading and trailing commata.

For example, with an input of TEST("", "b", "c", "", ""), I would like to get b, c

However, my regex ,$| ,+|^, does not really take repeated commas into account:

Function TEST(a, b, c, d, e)
    res = a & ", " & b & ", " & c & ", " & d & ", " & e

    Debug.Print (res)
    Dim regex As Object, str, result As String
    Set regex = CreateObject("VBScript.RegExp")

    With regex
      .Pattern = ",$| ,+|^,"
    End With

    Dim ReplacePattern As String
    ReplacePattern = ""

    res = regex.Replace(res, ReplacePattern)

    TEST = res
End Function

How can I do this?

Answer 1

Most elegant is @ScottCraner's suggestion of TEXTJOIN (will remove this part of answer, if he wishes to post this as his own)

Private Function nonEmptyFields(ParamArray strings() As Variant) As String
    nonEmptyFields = WorksheetFunction.TextJoin(",", True, Array(strings))
End Function

Note: This will only work for Office 365+, but you can always create your own version of TEXTJOIN

---

Another option would be to loop over the ParamArray of strings and add them together, depending on their content (whether they are populated or empty)

Private Function nonEmptyFields(ParamArray strings() As Variant) As String

    Dim result As String
    Dim i As Byte

    For i = LBound(strings) To UBound(strings)
        If Len(strings(i)) <> 0 Then
            If result = vbNullString Then
                result = strings(i)
            Else
                result = result & "," & strings(i)
            End If
        End If
    Next i

    nonEmptyFields = result

End Function

Both would yield desired result with set up of

    Debug.Print nonEmptyFields(a, b, c, d, e, f) ' "", "b", "c", "", "", ""