User Defined function to concatenate string

Question

People,

I am trying to create a UDF in vba to concatenate a string with inverted commas, end results should look like  --- '1234567',

can anybody help me please.

Answer 1

Hi Hafeezur,

Considerably preferable to my earlier suggestion would be:

'=========>>

Public Function ConcatQuotes(ParamArray rng()) As Variant

    Dim arr() As Variant

    Dim rArea As Variant, rCell As Range

    Dim i As Long, j As Long

    ConcatQuotes = CVErr(xlErrNA)

    For Each rArea In rng

        For Each rCell In rArea.Cells

            If Not rCell.Value = vbNullString Then

                i = i + 1

                ReDim Preserve arr(1 To i)

                arr(i) = "'" & rCell.Value & "'"

            End If

        Next rCell

    Next rArea

        If CBool(i) Then

             ConcatQuotes = Join(arr, ",")

        End If

End Function

'<<========

===

Regards,

Norman

Answer 2

People,

I am trying to create a UDF in vba to concatenate a string with inverted commas, end results should look like  --- '1234567',

can anybody help me please.

Hi Hafeezur,

Try:

Alt-F11 to open the VBA editor

Alt-IM to insert a new code module

In the new module, paste the following code

'=========>>

Public Function ConcatQuotes(aCell As Range)

   ConcatQuotes = "'" & aCell.Value & "'"

End Function

'<<========

Alt-Q to close the VBA editor

Use the UDF in a worksheet as

     =ConcatQuotes(D7)

===

Regards,

Norman

Answer 3

simple and elegant solution Norman !!! This solves my problem, thanks..

Answer 4

Hi Hafeezur,

Re-reading your post, I realise that I may have misinterpreted your requirement. 

Try, therefore:

'=========>>

Public Function ConcatQuotes(rng As Range)

Dim arr As Variant

Dim i As Long, j As Long

i = rng.Cells.Count

ReDim arr(1 To i)

For j = 1 To i

    arr(j) = "'" & rng(j).Value & "'"

Next j

   ConcatQuotes = Join(arr, ",")

End Function

'<<========

===

Regards,

Norman

Answer 5

Hi,

Have a look to my add-in:

JP's Extension

It has a function

=RangeConcat(ConcatRange;Separator;TextQualifier;Suppress)

Note that my default seaparator is the semi^-colon but it will work with comma also.