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?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Todd Huttenstine

    chr() for quotation mark

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

    excel save as csv quotation mark

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

    printing a quotation mark in a text file

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

    Single quotation mark prefix

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