Forum Discussion

Brass Contributor

Nov 03, 2021

Solved

Excel Concat Function

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 ...

Formulas and Functions

HansVogelaar

Nov 03, 2021

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

HansVogelaar

MVP

Nov 03, 2021

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