1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Unable to store things in a variable using line continuation

Discussion in 'VBA Macros' started by shahin, Nov 22, 2017.

  1. shahin

    shahin Active Member

    Messages:
    657
    Is there any way to continue writing new items (from the next line) in the variable "word_storage" using line continuation or something other than using "Array" what I've already learnt few days back? It is doing fine as long as i keep things in a line no matter how big it is.

    Code (vb):

    Sub demo_one()
        Dim word_storage As Variant, citem As Variant

        word_storage = [{"Mango","Apple", "Kiwifruit", "Pear", "Pomegranate","1010","10piece"}]

        For Each citem In word_storage
            r = r + 1: Cells(r, 1) = citem
        Next citem
    End Sub
     
    My intention is to see the structure something like below. However, it throws an error.
    Code (vb):

    word_storage = [{"Mango","Apple", "Kiwifruit", _
        "Pear", "Pomegranate","1010", _
        "10piece","20piece","30piece"}]
     
    Once again, I've learnt how to do the same using "Array" so i wish to have any solution the way i started here. Thanks a lot in advance.
  2. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    16,002
    Hi ,

    I doubt that the method you have posted is possible.

    These two ways work.
    Code (vb):

    Public Sub temp()
              Dim word_storage As String
              Dim word_storage1 As Variant

              word_storage = "Mango,Apple,Kiwifruit," & _
                              "Pear,Pomegranate,1010," & _
                              "10piece,20piece,30piece"
              Range("A1:A9").Value = Application.Transpose(Split(word_storage, ","))

              word_storage1 = Array("Mango", "Apple", "Kiwifruit", _
                                    "Pear", "Pomegranate", "1010", _
                                    "10piece", "20piece", "30piece")
              Range("B1:B9").Value = Application.Transpose(word_storage1)
    End Sub
     
    Narayan
    shahin likes this.
  3. shahin

    shahin Active Member

    Messages:
    657
    Thanks Narayan, for your solution. The one (word_storage) you have shown first will do. I just needed to know whether there is any way i can do the same without the help of Array.
  4. Marc L

    Marc L Excel Ninja

    Messages:
    3,340

    Hi !

    Just use Evaluate statement …
  5. shahin

    shahin Active Member

    Messages:
    657
    Hi Marc L, it's nice to have you in the loop. Once you used "[{}]" this and for that it came to my mind that items used in those braces can also be re-used like list. However, I'm not familiar with this Evaluate statement. Thanks.
  6. Marc L

    Marc L Excel Ninja

    Messages:
    3,340
    As you must have READ in Evaluate VBA inner help [] is its short writing.
    From this help :
    « The advantage of using Evaluate is that the argument is a string, so you can either construct the string in your code or use a Visual Basic variable. »

    So clearly you can't use its short writing in multiple codelines
    but with Evaluate you can « construct the string in your code »
    even in multi lines, so nothing specific to this Evaluate method
    but just about VBA basics of writing any string in a code …

    Without any special need, easier is to use Array statement …
  7. shahin

    shahin Active Member

    Messages:
    657
    What to do when I use a big string and the items within it are comma separated and I would like to get each portion of comma delimited items?
    Code (vb):

    Sub demo_one()
        Dim word_storage As Variant, citem As Variant

        word_storage = [{"Mango, Apple, Kiwifruit, Pear, Pomegranate, 1010, 10piece"}]

        For Each citem In word_storage
            r = r + 1: Cells(r, 1) = citem
        Next citem
    End Sub
     
    If I run the macro the way it is now, the result is something like below in a single cell:

    Code (vb):

    Mango, Apple, Kiwifruit, Pear, Pomegranate, 1010, 10piece
     
    The way I expect to have is (each in different cells):
    Code (vb):

    Mango
    Apple
    Kiwifruit
    Pear
    Pomegranate
    1010
    10piece
     
    Last edited: Nov 29, 2017
  8. Marc L

    Marc L Excel Ninja

    Messages:
    3,340

    Just a bad writing of the matrix !
    Good matrix writing is yet in your initial post
    Each text item must be embed between double quotes
    like in post #2 Array codeline …
  9. Chihiro

    Chihiro Excel Ninja

    Messages:
    4,062
    Well, word_storage is variant array, but with only single element.

    I think you forgot to nest each element in double quotes...

    And as long as you don't exceed limit of transpose. Without using loop.
    Code (vb):
    Sub demo_one()
        Dim word_storage As Variant, citem As Variant

        word_storage = [{"Mango","Apple", "Kiwifruit", "Pear", "Pomegranate","1010","10piece"}]

        Range("A1").Resize(UBound(word_storage)) = Application.Transpose(word_storage)
    End Sub
    shahin likes this.
  10. Marc L

    Marc L Excel Ninja

    Messages:
    3,340
    And just following Excel basics, for a column :​
    Code (vb):
    Sub Demo1()
        [A1:A7] = [{"Mango";"Apple";"Kiwifruit";"Pear";"Pomegranate";1010;"10piece"}]
    End Sub
    Do you like it ? So thanks to click on bottom right Like !
    shahin likes this.
  11. shahin

    shahin Active Member

    Messages:
    657
    @sir chihiro, No, I didn't forget to put double quotes in each separated words ;rather, i did it intentionally. My expectation was to have individual fruit names separated by comma taken out from a big string. I'm going again.

    Consider this a big string:
    Code (vb):

    "A,B,C,D"
     
    And my expected output is:
    Code (vb):

    A
    B
    C
    D
     
  12. Chihiro

    Chihiro Excel Ninja

    Messages:
    4,062
    So just use split to put it into array... like we've showed you many times.
  13. Marc L

    Marc L Excel Ninja

    Messages:
    3,340
    :eek: Just see post #10 ‼
  14. shahin

    shahin Active Member

    Messages:
    657
    @sir chihiro, this is what i tried and it generates error:

    Code (vb):

    Sub demo_one()
        Dim citem As Variant, word_storage As Variant
        Dim list_items() As String

        list_items = Array("A,B,C,D")
        word_storage = Split(list_items, ",")  ''type mismatch

        For Each citem In word_storage
            r = r + 1: Cells(r, 1) = citem
        Next citem
    End Sub
     
    I know it is hard to deal with a bad student like me.

    @Marc L, I tried but it gives me badly structured data: ("Double quotes should be used once")

    Code (vb):

    Sub Demo1()
        [A1:A7] = [{"Mango;Apple;Kiwifruit;Pear;Pomegranate;1010;10piece"}]
    End Sub
     
    Last edited: Nov 29, 2017
  15. shahin

    shahin Active Member

    Messages:
    657
    However, the following works:
    Code (vb):

    Sub demo_one()
        Dim citem As Variant

        For Each citem In Split("A,B,C,D", ",")
            r = r + 1: Cells(r, 1) = citem
        Next citem
    End Sub
     
  16. Chihiro

    Chihiro Excel Ninja

    Messages:
    4,062
    ... of course. Split must be done on string. Not array of string(s).
  17. Marc L

    Marc L Excel Ninja

    Messages:
    3,340
    Yes very bad student ! Read post #8 & just try post #10 code as it is !
    See also post #9 code ! Your mod code does not respect Excel basics …

    And no need a loop to fill cells from an array !
  18. shahin

    shahin Active Member

    Messages:
    657
    @ sir Chihiro, yes "Split must be done on string. Not array of string(s)" this is the answer to my question but I was not sharp enough to catch that. Thanks.

Share This Page