• 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...

  • 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

shahin

shahin

Active Member
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: 
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: 
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.
 
Hi ,

I doubt that the method you have posted is possible.

These two ways work.
Code: 
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
 
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.
 
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.
 
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 …

shahin said:
However, I'm not familiar with this Evaluate statement.
Click to expand...
Without any special need, easier is to use Array statement …
​
 
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: 
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: 
Mango, Apple, Kiwifruit, Pear, Pomegranate, 1010, 10piece

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

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 …
​
 
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: 
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
 
And just following Excel basics, for a column :​
Code: 
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 !
​
 
@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: 
"A,B,C,D"
And my expected output is:
Code: 
A
B
C
D
 
@sir chihiro, this is what i tried and it generates error:

Code: 
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: 
Sub Demo1()
    [A1:A7] = [{"Mango;Apple;Kiwifruit;Pear;Pomegranate;1010;10piece"}]
End Sub
 
Last edited:
However, the following works:
Code: 
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
 
shahin said:
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
Click to expand...
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 !
​
 
@ 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.
 
You must log in or register to reply here.
Back
Top