Excel Concat Function

[BejeweledOne]

I am hoping to create a reusable function in an excel workbook.   I want to make this into a reusable function.   I am very new to functions, so.......

=CONCAT(TRANSPOSE(A1:A5)&" ")

  I tried this and didn't get any errors until I tried to run it then it tells me sub or function not defined.

Function CombineText(str As Range, sep As String)
    CombineText = Concat(Transpose(str) & sep)
End Function

In the cell:  CombineText(B1:F1, ", ")

What am I missing?

[#2 · Accepted solution]

@BejeweledOne 

CONCAT and TRANSPOSE are Excel worksheet functions, not VBA functions.

You can use them in VBA through the Application.WorksheetFunction object, but with more limited functionality - not the way you're trying to use it.

Here is an alternative using the VBA implementation of TEXTJOIN:

Function CombineText(str As Range, sep As String)
    CombineText = Application.TextJoin(sep, True, str)
End Function

Or using a pure VBA approach:

Function CombineText(str As Range, sep As String)
    Dim rng As Range
    Dim ret As String
    For Each rng In str
        ret = ret & sep & rng.Value
    Next rng
    If ret <> "" Then
        CombineText = Mid(ret, Len(sep) + 1)
    End If
End Function