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

Hi Norman,

Hope you are doing good !!!

Just an addition to my above question, can you please tell me how can i add this function to add-ins so that i can access this function whenever i open a new workbook.

Regards,

Hafeez

Answer 4

This is just more than a answer for me !!! the is super cool solution

Hi Hafeezur,

Thank you for your kind feedback. I am pleased that the function is useful.

===

Regards,

Norman

Answer 5

This is just more than a answer for me !!! the is super cool solution