Forum Discussion

BejeweledOne's avatar
BejeweledOne
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 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?

 

Formulas and Functions
  • HansVogelaar's avatar
    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

1 Reply

  • HansVogelaar's avatar
    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

Resources