VB Function to UDF

Public Function ConcatString(ByVal Seperator As String, ParamArray StringValues() As Variant) As String
'---------------------------------------
'|Concatonate elements in passed array.|
'---------------------------------------
On Error GoTo Err_ConcatString
Dim varValue As Variant
Dim strOutput As String
    
    If Not IsArray(StringValues) Then GoTo Exit_ConcatString
    
'Loop thru' array evaluating array members and adding valid members to output string.
    For Each varValue In StringValues
        varValue = Nz(varValue, "")
        Do Until varValue = Replace(varValue, "  ", "")
            varValue = Replace(varValue, "  ", "")
        Loop
        If Not varValue = "" Then
            If Not strOutput = "" Then
                strOutput = strOutput & Seperator
            End If
            strOutput = strOutput & varValue
        End If
    Next varValue
    
'Return function value.
    ConcatString = strOutput
  
Exit_ConcatString:
    Exit Function
  
Err_ConcatString:
    MsgBox "Err_ConcatString " & Err.Number & ": " & Err.Description
    Resume Exit_ConcatString
End Function

Select allOpen in new window