Inability to add quotation mark around a variable by concatenation in VBA

Discussion in 'Excel Programming' started by Mark Stephens, Apr 9, 2014.

  1. Hi,

    I am outputting some data for another programmer to parse.

    He has requested that I add quotation marks around each variable, but when I attempt to do it VBA will have none of it, and wants me to insert 2 quotation marks or none!

    Here's my code:

    Dim bCount As Byte
    For bBandNo = 1 To 9

    If arrbBandValue(bBandNo) > 0 Then

    bCount = bCount + 1
    'Last one needs special treatment (no comma after and quotes at the end
    If bCount = bBandCount Then

    sBandValues = sBandValues & arrbBandValue(bBandNo) & ";" & bBandNo
    Exit For

    End If

    sBandValues = sBandValues & arrbBandValue(bBandNo) & ";" & bBandNo & ","

    End If

    Next bBandNo

    psBandValues = sBandValues


    I tried two approaches the first being to add them at the end which would be easiest. I just amended the last line:

    psBandValues = """ & sBandValues & """

    However this destroys my variable returning instead of "MyVariableString" as I intend, instead it returns

    The second to do it 'at source' which yielded the same result.

    Maybe I am missing something (probably) but should it be so hard to simply add quotation marks around a variable???

    Any help much appreciated, M.
     
    Mark Stephens, Apr 9, 2014
    #1
    1. Advertisements

  2. Mark Stephens

    GS Guest

    Try...

    psBandValues = Chr(34) & sBandValues & Chr(34)

    --
    Garry

    Free usenet access at http://www.eternal-september.org
    Classic VB Users Regroup!
    comp.lang.basic.visual.misc
    microsoft.public.vb.general.discussion
     
    GS, Apr 9, 2014
    #2
    1. Advertisements

  3. Re: Inability to add quotation mark around a variable byconcatenation in VBA

    On Wednesday, April 9, 2014 10:40:37 AM UTC+8, Mark Stephens wrote:
    > Hi,
    >
    >
    >
    > I am outputting some data for another programmer to parse.
    >
    >
    >
    > He has requested that I add quotation marks around each variable, but when I attempt to do it VBA will have none of it, and wants me to insert 2 quotation marks or none!
    >
    >
    >
    > Here's my code:
    >
    >
    >
    > Dim bCount As Byte
    >
    > For bBandNo = 1 To 9
    >
    >
    >
    > If arrbBandValue(bBandNo) > 0 Then
    >
    >
    >
    > bCount = bCount + 1
    >
    > 'Last one needs special treatment (no comma after and quotes at the end
    >
    > If bCount = bBandCount Then
    >
    >
    >
    > sBandValues = sBandValues & arrbBandValue(bBandNo) & ";" & bBandNo
    >
    > Exit For
    >
    >
    >
    > End If
    >
    >
    >
    > sBandValues = sBandValues & arrbBandValue(bBandNo) & ";" & bBandNo & ","
    >
    >
    >
    > End If
    >
    >
    >
    > Next bBandNo
    >
    >
    >
    > psBandValues = sBandValues
    >
    >
    >
    >
    >
    > I tried two approaches the first being to add them at the end which would be easiest. I just amended the last line:
    >
    >
    >
    > psBandValues = """ & sBandValues & """
    >
    >
    >
    > However this destroys my variable returning instead of "MyVariableString" as I intend, instead it returns
    >
    >
    >
    > The second to do it 'at source' which yielded the same result.
    >
    >
    >
    > Maybe I am missing something (probably) but should it be so hard to simply add quotation marks around a variable???
    >
    >
    >
    > Any help much appreciated, M.


    Hey Garry, that worked like a dream thanks so much for the help, M.
     
    Mark Stephens, Apr 9, 2014
    #3
  4. Mark Stephens

    GS Guest

    > Hey Garry, that worked like a dream thanks so much for the help, M.

    Glad to help...

    --
    Garry

    Free usenet access at http://www.eternal-september.org
    Classic VB Users Regroup!
    comp.lang.basic.visual.misc
    microsoft.public.vb.general.discussion
     
    GS, Apr 9, 2014
    #4
  5. Mark Stephens

    Martin Brown Guest

    Re: Inability to add quotation mark around a variable by concatenationin VBA

    On 09/04/2014 03:40, Mark Stephens wrote:
    > Hi,
    >
    > I am outputting some data for another programmer to parse.
    >
    > He has requested that I add quotation marks around each variable, but when I attempt to do it VBA will have none of it, and wants me to insert 2 quotation marks or none!
    >
    > Here's my code:
    >
    > Dim bCount As Byte
    > For bBandNo = 1 To 9
    >
    > If arrbBandValue(bBandNo) > 0 Then
    >
    > bCount = bCount + 1
    > 'Last one needs special treatment (no comma after and quotes at the end
    > If bCount = bBandCount Then
    >
    > sBandValues = sBandValues & arrbBandValue(bBandNo) & ";" & bBandNo
    > Exit For
    >
    > End If
    >
    > sBandValues = sBandValues & arrbBandValue(bBandNo) & ";" & bBandNo & ","
    >
    > End If
    >
    > Next bBandNo
    >
    > psBandValues = sBandValues
    >
    >
    > I tried two approaches the first being to add them at the end which would be easiest. I just amended the last line:
    >
    > psBandValues = """ & sBandValues & """
    >
    > However this destroys my variable returning instead of "MyVariableString" as I intend, instead it returns
    >
    > The second to do it 'at source' which yielded the same result.
    >
    > Maybe I am missing something (probably) but should it be so hard to simply add quotation marks around a variable???


    It isn't but you have to escape them in. So to add one " you need two.

    """" = " as a string

    So

    a = """" & "test" & """"
    debug.print a

    will print "test"
    >
    > Any help much appreciated, M.
    >



    --
    Regards,
    Martin Brown
     
    Martin Brown, Apr 9, 2014
    #5
    1. Advertisements

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.
Similar Threads
  1. Todd Huttenstine

    chr() for quotation mark

    Todd Huttenstine, Jul 27, 2004, in forum: Excel Programming
    Replies:
    6
    Views:
    135
    Norman Jones
    Jul 29, 2004
  2. ViestaWu
    Replies:
    3
    Views:
    91
    Bob Phillips
    Jul 31, 2006
  3. Briankwei1234567

    excel save as csv quotation mark

    Briankwei1234567, Aug 2, 2007, in forum: Excel Programming
    Replies:
    1
    Views:
    136
  4. stephen

    printing a quotation mark in a text file

    stephen, Oct 24, 2007, in forum: Excel Programming
    Replies:
    3
    Views:
    96
    JRForm
    Oct 24, 2007
  5. Sian
    Replies:
    5
    Views:
    111
  6. Chris Hankin

    Single quotation mark prefix

    Chris Hankin, Feb 15, 2008, in forum: Excel Programming
    Replies:
    5
    Views:
    113
    Chris Hankin
    Feb 15, 2008
  7. Mark Stephens
    Replies:
    3
    Views:
    97
    Bob Phillips
    Apr 24, 2008
  8. andreashermle
    Replies:
    4
    Views:
    93
    andreashermle
    Apr 22, 2010
Loading...