VB: create comma separated string from array

Question

I am trying to convert a list of array values to a comma separated string, with single quotes.

my array name is:

info.arr_fonts(
               (0)times
               (1)verdana
               (2)arial
               (3)tahoma
               (4)helvetica
              ) - values are dynamic, can be +/-.
enter code here

I would like to write them into a string variable like:

"times, verdana, arial, tahoma, helvetica"

can someone show me how this can be done? I tried something simple like:

Dim strFonts As String
strFonts = Join(info.FontArray, ",")

But that does not add the single quotes around each word.

UPDATE

Dim arrFontNames As Variant
Dim strFonts As String
Dim lCtr As Long

arrFontNames = Array("Doremi", "Times-Roman", "Helvetica", "Jivetalk", "Jive")

strFonts = Join(arrFontNames, ",")

content of strFonts:

"Doremi,Times-Roman,Helvetica,Jivetalk,Jive"

I need to pass the strFonts as a parameter to a stored procedure. The stored procedure needs to receive it lie this:

'Doremi,Times-Roman,Helvetica,Jivetalk,Jive'

Will the double quotes from VBA convert to single quotes once it executes the stored procedure, or do I need to do some string manipulation still?

Loop through each element and add the quotes. Join Won't help here. — cyboashu
– cyboashu, Commented Jul 15, 2016 at 20:29
for: strFonts = ""("'" & Join(info.FontArray, "','") & "')""" - compiler return expected end of statement. — erasmo carlos
– erasmo carlos, Commented Jul 15, 2016 at 20:35

cyboashu · Accepted Answer · 2016-07-15 22:18:20Z

2

add the quotes by loop.

Sub test()

    Dim lCtr As Long

    For lCtr = LBound(info.FontArray) To UBound(info.FontArray)
        info.FontArray(lCtr) = "'" & info.FontArray(lCtr) & "'"
    Next

    strFonts = Join(info.FontArray, ",")

End Sub

As per your edit to the question. for a single param in stored proc all you need to do is strFonts = "'" & Join(info.FontArray, ",") & "'"

edited Jul 15, 2016 at 22:18

answered Jul 15, 2016 at 20:32

cyboashu

10.5k2 gold badges32 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

erasmo carlos Over a year ago

what is this line doing?: info.FontArray(lCtr) = "'" & info.FontArray(lCtr) & "'"

cyboashu Over a year ago

adding quotes and updating the array element.

erasmo carlos Over a year ago

I see every loop goes through each element in the array, but rather than building a string, it replaces it. and in the end it contains the same as it originally came: "Doremi,Times-Roman,Helvetica,Jivetalk,Jive"

cyboashu Over a year ago

No, not possible. Until and unless you missed something, as per my answer strFont will have quotes around the array elements.

erasmo carlos Over a year ago

You were right, I was missing something. I got the code to work, can you please see the update?

|

Collectives™ on Stack Overflow

VB: create comma separated string from array

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related