create array based a string

[sal21]

How to create array and loop it, based this string

["Abruzzo","Basilicata","Calabria","Campania","Emilia Romagna","Friuli Venezia Giulia","Lazio","Liguria","Lombardia","Marche","Molise","Piemonte","Puglia","Sardegna","Sicilia","Toscana","Trentino Alto Adige","Umbria","Valle d'Aosta","Veneto"]

[snb]

See:

https://www.snb-vba.eu/VBA_Arrays_en.html#L_5

[DocAElstein]

Code: Select all

Sub LisStringUn() '  http://www.eileenslounge.com/viewtopic.php?f=30&t=40140
Dim LisString As String
 Let LisString = "[""Abruzzo"",""Basilicata"",""Calabria"",""Campania"",""Emilia Romagna"",""Friuli Venezia Giulia"",""Lazio"",""Liguria"",""Lombardia"",""Marche"",""Molise"",""Piemonte"",""Puglia"",""Sardegna"",""Sicilia"",""Toscana"",""Trentino Alto Adige"",""Umbria"",""Valle d'Aosta"",""Veneto""]"
 MsgBox prompt:=LisString
 Let LisString = Replace(LisString, "[", "")
 MsgBox prompt:=LisString
 Let LisString = Replace(LisString, "]", "")
 MsgBox prompt:=LisString
Dim arrLisString() As String
 Let arrLisString() = Split(LisString, ",")
Dim Cnt As Long
    For Cnt = LBound(arrLisString()) To UBound(arrLisString())
     MsgBox arrLisString(Cnt)
    Next Cnt

 Let Range("A1").Resize(1, UBound(arrLisString()) + 1) = arrLisString()

 
 Let LisString = Replace(LisString, ",", vbCr & vbLf)
 MsgBox prompt:=LisString

 Let arrLisString() = Split(LisString, vbCr & vbLf)
    For Cnt = LBound(arrLisString()) To UBound(arrLisString())
     MsgBox arrLisString(Cnt)
    Next Cnt

 Let Range("A1").Resize(1, UBound(arrLisString()) + 1) = arrLisString()
End Sub

Code: Select all

Sub LisUver()  '    https://eileenslounge.com/viewtopic.php?p=310856#p310856
Dim vTemp() As Variant
 Let vTemp() = [{"Abruzzo","Basilicata","Calabria","Campania","Emilia Romagna","Friuli Venezia Giulia","Lazio","Liguria","Lombardia","Marche","Molise","Piemonte","Puglia","Sardegna","Sicilia","Toscana","Trentino Alto Adige","Umbria","Valle d'Aosta","Veneto"}]
Dim Cnt As Long
    For Cnt = LBound(vTemp()) To UBound(vTemp())
     MsgBox vTemp(Cnt)
    Next Cnt

End Sub

[SpeakEasy]

Here's a short version (it assumes your source string is in a textbox)

Code: Select all

    Dim result() As String
    result = Split(Replace(Replace(Replace(Text1.Text, """,""", Chr$(0)), "[""", ""), """]", ""), Chr$(0))

[sal21]

Code: Select all

Sub LisStringUn() '  http://www.eileenslounge.com/viewtopic.php?f=30&t=40140
Dim LisString As String
 Let LisString = "[""Abruzzo"",""Basilicata"",""Calabria"",""Campania"",""Emilia Romagna"",""Friuli Venezia Giulia"",""Lazio"",""Liguria"",""Lombardia"",""Marche"",""Molise"",""Piemonte"",""Puglia"",""Sardegna"",""Sicilia"",""Toscana"",""Trentino Alto Adige"",""Umbria"",""Valle d'Aosta"",""Veneto""]"
 MsgBox prompt:=LisString
 Let LisString = Replace(LisString, "[", "")
 MsgBox prompt:=LisString
 Let LisString = Replace(LisString, "]", "")
 MsgBox prompt:=LisString
Dim arrLisString() As String
 Let arrLisString() = Split(LisString, ",")
Dim Cnt As Long
    For Cnt = LBound(arrLisString()) To UBound(arrLisString())
     MsgBox arrLisString(Cnt)
    Next Cnt

 Let Range("A1").Resize(1, UBound(arrLisString()) + 1) = arrLisString()

 
 Let LisString = Replace(LisString, ",", vbCr & vbLf)
 MsgBox prompt:=LisString

 Let arrLisString() = Split(LisString, vbCr & vbLf)
    For Cnt = LBound(arrLisString()) To UBound(arrLisString())
     MsgBox arrLisString(Cnt)
    Next Cnt

 Let Range("A1").Resize(1, UBound(arrLisString()) + 1) = arrLisString()
End Sub

Code: Select all

Sub LisUver()  '    https://eileenslounge.com/viewtopic.php?p=310856#p310856
Dim vTemp() As Variant
 Let vTemp() = [{"Abruzzo","Basilicata","Calabria","Campania","Emilia Romagna","Friuli Venezia Giulia","Lazio","Liguria","Lombardia","Marche","Molise","Piemonte","Puglia","Sardegna","Sicilia","Toscana","Trentino Alto Adige","Umbria","Valle d'Aosta","Veneto"}]
Dim Cnt As Long
    For Cnt = LBound(vTemp()) To UBound(vTemp())
     MsgBox vTemp(Cnt)
    Next Cnt

End Sub

code in second post is for me!
tks.

[sal21]

Here's a short version (it assumes your source string is in a textbox)

Code: Select all

    Dim result() As String
    result = Split(Replace(Replace(Replace(Text1.Text, """,""", Chr$(0)), "[""", ""), """]", ""), Chr$(0))

speed is here!
tks bro!

[DocAElstein]

...code in second post is for me!
tks.

But don't forget extra { .... }

[{ ...........}]

Let vTemp() = [{"Abruzzo","B...................

[DocAElstein]

For to make list without loop

Code: Select all

Sub NonLoopIt() ' https://eileenslounge.com/viewtopic.php?p=310863#p310863
 Range("A1:A21").Clear
 Let Range("A1") = "[""Abruzzo"",""Basilicata"",""Calabria"",""Campania"",""Emilia Romagna"",""Friuli Venezia Giulia"",""Lazio"",""Liguria"",""Lombardia"",""Marche"",""Molise"",""Piemonte"",""Puglia"",""Sardegna"",""Sicilia"",""Toscana"",""Trentino Alto Adige"",""Umbria"",""Valle d'Aosta"",""Veneto""]"


 Let Range("A2").Resize(UBound(Split(Replace(Replace(Range("A1").Value2, "]", ""), "[", ""), ",")) + 1, 1) = Application.Index(Split(Replace(Replace(Range("A1").Value2, "]", ""), "[", ""), ","), Evaluate("ROW(1:" & UBound(Split(Replace(Replace(Range("A1").Value2, "]", ""), "[", ""), ",")) + 1 & ")/ROW(1:" & UBound(Split(Replace(Replace(Range("A1").Value2, "]", ""), "[", ""), ",")) + 1 & ")"), Evaluate("ROW(1:" & UBound(Split(Replace(Replace(Range("A1").Value2, "]", ""), "[", ""), ",")) + 1 & ")"))
End Sub

NoLoopIt.JPG

_.___________________________________________________________________

It do come from all this:

Code: Select all

Sub NonLoop() '  https://eileenslounge.com/viewtopic.php?p=310863#p310863
 Range("A1:A21").Clear
 Let Range("A1") = "[""Abruzzo"",""Basilicata"",""Calabria"",""Campania"",""Emilia Romagna"",""Friuli Venezia Giulia"",""Lazio"",""Liguria"",""Lombardia"",""Marche"",""Molise"",""Piemonte"",""Puglia"",""Sardegna"",""Sicilia"",""Toscana"",""Trentino Alto Adige"",""Umbria"",""Valle d'Aosta"",""Veneto""]"
Dim LisString As String
' Let LisString = "[""Abruzzo"",""Basilicata"",""Calabria"",""Campania"",""Emilia Romagna"",""Friuli Venezia Giulia"",""Lazio"",""Liguria"",""Lombardia"",""Marche"",""Molise"",""Piemonte"",""Puglia"",""Sardegna"",""Sicilia"",""Toscana"",""Trentino Alto Adige"",""Umbria"",""Valle d'Aosta"",""Veneto""]"
 Let LisString = Range("A1").Value2
 Let LisString = Replace(LisString, "[", "")
 Let LisString = Replace(LisString, "]", "")
' Or
 Let LisString = Replace(Replace("[""Abruzzo"",""Basilicata"",""Calabria"",""Campania"",""Emilia Romagna"",""Friuli Venezia Giulia"",""Lazio"",""Liguria"",""Lombardia"",""Marche"",""Molise"",""Piemonte"",""Puglia"",""Sardegna"",""Sicilia"",""Toscana"",""Trentino Alto Adige"",""Umbria"",""Valle d'Aosta"",""Veneto""]", "]", ""), "[", "")
 Let LisString = Replace(Replace(Range("A1").Value2, "]", ""), "[", "")
Dim arrLisString() As String
 Let arrLisString() = Split(LisString, ",")

Dim arrOut() As Variant
 Let arrOut() = Application.Index(arrLisString(), Evaluate("ROW(1:" & UBound(arrLisString()) + 1 & ")/ROW(1:" & UBound(arrLisString()) + 1 & ")"), Evaluate("ROW(1:" & UBound(arrLisString()) + 1 & ")"))

' Or
 Let arrOut() = Application.Index(Split(Replace(Replace(Range("A1").Value2, "]", ""), "[", ""), ","), Evaluate("ROW(1:" & UBound(Split(Replace(Replace(Range("A1").Value2, "]", ""), "[", ""), ",")) + 1 & ")/ROW(1:" & UBound(Split(Replace(Replace(Range("A1").Value2, "]", ""), "[", ""), ",")) + 1 & ")"), Evaluate("ROW(1:" & UBound(Split(Replace(Replace(Range("A1").Value2, "]", ""), "[", ""), ",")) + 1 & ")"))
 
 Let Range("A2").Resize(UBound(arrLisString()) + 1, 1) = arrOut()
 Let Range("A2").Resize(UBound(Split(LisString, ",")) + 1, 1) = arrOut()
 Let Range("A2").Resize(UBound(Split(Replace(Replace(Range("A1").Value2, "]", ""), "[", ""), ",")) + 1, 1) = arrOut()

 Let Range("A2").Resize(UBound(Split(Replace(Replace(Range("A1").Value2, "]", ""), "[", ""), ",")) + 1, 1) = Application.Index(Split(Replace(Replace(Range("A1").Value2, "]", ""), "[", ""), ","), Evaluate("ROW(1:" & UBound(Split(Replace(Replace(Range("A1").Value2, "]", ""), "[", ""), ",")) + 1 & ")/ROW(1:" & UBound(Split(Replace(Replace(Range("A1").Value2, "]", ""), "[", ""), ",")) + 1 & ")"), Evaluate("ROW(1:" & UBound(Split(Replace(Replace(Range("A1").Value2, "]", ""), "[", ""), ",")) + 1 & ")"))

End Sub

[SpeakEasy]

Assuming Sal21 is working in Excel (although I recall they work in VB6), then we can simplify the above 'no loop' solution further (assuming source string is in A1)

Code: Select all

    Dim result() As String
    result = Split(Replace(Replace(Replace(Range("A1").Value, """,""", Chr$(0)), "[""", ""), """]", ""), Chr$(0))
    Range("A2").Resize(UBound(result) + 1).Value = Application.Transpose(result)

[snb]

Code: Select all

Sub M_snb()
  sn = Split(Mid(Cells(1), 3, Len(Cells(1)) - 4), """,""")
  MsgBox Join(sn)
End Sub

[DocAElstein]

... can simplify the above....

Hi,
The main difference is you are using the Transpose function rather than doing that Transpose with Index, - fair enough.
I had some issues with transpose sometimes, I can’t remember what exactly but it had some bugs, so often I avoid it. Also I like playing around with ArrOut() = Index(ArrIn(), Rws(), Clms()) type things . Just a fetish of mine, a harmless perversion, Lol
You do transpose(arr()) ,
and I do pseudo like

Code: Select all

    Index(arr() , Rws(), Clms()
    Index(arr() ,  1   ,   1
                   1       2
                   1       3
                   1       4
 ..... etc....

This is approximate comparison of all our codings:

Code: Select all

 '    https://eileenslounge.com/viewtopic.php?p=310873&sid=c4d3ba209a7ca63a4fb9f4687669e3df#p310873
Sub SeasyVsAlanAnd_snb()
 Range("A1:A21").Clear
 Let Range("A1") = "[""Abruzzo"",""Basilicata"",""Calabria"",""Campania"",""Emilia Romagna"",""Friuli Venezia Giulia"",""Lazio"",""Liguria"",""Lombardia"",""Marche"",""Molise"",""Piemonte"",""Puglia"",""Sardegna"",""Sicilia"",""Toscana"",""Trentino Alto Adige"",""Umbria"",""Valle d'Aosta"",""Veneto""]"

Dim LisString As String, arrLisString() As String, arrOut() As Variant

' snb
 Let LisString = Mid(Cells(1), 3, Len(Cells(1)) - 4)

' SpeakEasy
 Let LisString = Replace(Range("A1").Value, """,""", Chr$(0))
 Let LisString = Replace(LisString, "[""", "")
 Let LisString = Replace(LisString, """]", "")

 Let arrLisString() = Split(LisString, Chr$(0))

' Alan
 Let LisString = Replace(Range("A1").Value, "[""", "")
 Let LisString = Replace(LisString, """]", "")

 Let arrLisString() = Split(LisString, """,""")



' Alan
 Let arrOut() = Application.Index(arrLisString(), Evaluate("ROW(1:" & UBound(arrLisString()) + 1 & ")/ROW(1:" & UBound(arrLisString()) + 1 & ")"), Evaluate("ROW(1:" & UBound(arrLisString()) + 1 & ")"))

' SpeakEasy
 Let arrOut() = Application.Transpose(arrLisString())

End Sub

Why do you do the extra replace step? (Replace(____, """,""", Chr$(0)) )
I do understand what you are doing, I think, – you are replacing the "," by a single arbitrary character and then splitting by that arbitrary character. But what is the point or advantage of that instead of just splitting by the ",", - is it just a harmless fetish?

[DocAElstein]

Clipboard way

Code: Select all

Sub ClipIt()    '    https://eileenslounge.com/viewtopic.php?p=310894#p310894
 Range("A1:A21").Clear
 Let Range("A1") = "[""Abruzzo"",""Basilicata"",""Calabria"",""Campania"",""Emilia Romagna"",""Friuli Venezia Giulia"",""Lazio"",""Liguria"",""Lombardia"",""Marche"",""Molise"",""Piemonte"",""Puglia"",""Sardegna"",""Sicilia"",""Toscana"",""Trentino Alto Adige"",""Umbria"",""Valle d'Aosta"",""Veneto""]"
 
 
 Range("A1").Copy
    With GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")  '    http://web.archive.org/web/20200124185244/http://excelmatters.com/2013/10/04/late-bound-msforms-dataobject/
    Dim StringBack As String
     .GetFromClipboard: Let StringBack = .GetText()
     Let StringBack = Replace(Replace(Replace(StringBack, "[""", ""), """]", ""), """,""", vbCr & vbLf)
     .Clear
     .SetText StringBack
     .PutInClipboard
    End With
 ActiveSheet.Paste Destination:=Range("A2")
End Sub

[snb]

Keep it simple:

Code: Select all

Sub M_snb()
   With CreateObject("New:{8BD21D20-EC42-11CE-9E0D-00AA006002F3}")
      .List = Split(Mid(Cells(1), 3, Len(Cells(1)) - 4), """,""")
      Cells(1, 10).Resize(.ListCount) = .List
    End With
End Sub

[DocAElstein]

Ahh, interesting, that looks like using something that’s normally in one of those user form big box things, ( ..I even found I had one that someone gave me and didn’t know I had it. It usually makes a box thing with a list in it inside the big userform box thing. https://i.postimg.cc/ZKqcjz8V/ListBox.jpg
https://i.postimg.cc/fWCCxR9V/ListBox.jpg
https://i.postimg.cc/XJc81V1D/List-Box.jpg )
I guess you could call it a sort of List Box, for want of a better words.
_... Now here’s a thing…
Give it ( the "List Box" thing), a 2 dimensional array ( any element type ) , and then put the list from it in a range and things look as expected in the orientation you might expect.
But then something weird: Give it a one dimensional array ( any element type ), and then put it in a range and it transposes it, ( and transposes it better than the Transpose function, apparently , ( https://www.mrexcel.com/board/threads/e ... st-3476720 ). Maybe it’s less of a spreadsheet thing, and more like a sort of list thing, so for convenience, they wire it to list the list out like we might when we make a list of something, in lines, ( unless you give it a 2 dimensional array, then it does it like you give it )

Code: Select all

Sub LisBoxWithListInItTransposies() ' https://eileenslounge.com/viewtopic.php?p=310903#p310903
Dim LisBox As Object
 Set LisBox = CreateObject("New:{8BD21D20-EC42-11CE-9E0D-00AA006002F3}")
 Let LisBox.List = Evaluate("{""a"",""b"";""c"",""d""}")
 Let Range("A10").Resize(2, 2) = LisBox.List
'   gives   a  b   as  I might expect
'           c  d

Dim arr1Row2Column(1 To 1, 1 To 2) As String
 Let arr1Row2Column(1, 1) = "a": arr1Row2Column(1, 2) = "b"
 Let LisBox.List = arr1Row2Column()
 Let Range("P10").Resize(1, 2) = LisBox.List
'  gives    a   b   as i might expect
 
 Let LisBox.List = Evaluate("{""a"",""b""}")
 Let Range("D10").Resize(2, 1) = LisBox.List
'  weird  ... it gives
'           a            strange it transposes??  and apparantly does it quite well, https://www.mrexcel.com/board/threads/excel-vba-connection-to-access.703542/#post-3476720
'           b
 Let LisBox.List = Split("a b")
' or
Dim arrStr() As String: Let arrStr = Split("a b")
 Let LisBox.List = arrStr()
 Let Range("L10").Resize(2, 1) = LisBox.List
'  weird  ... it gives
'           a            strange it transposes??  and apparantly does it quite well, https://www.mrexcel.com/board/threads/excel-vba-connection-to-access.703542/#post-3476720
'           b

 Let LisBox.List = Evaluate("{""a"";""b""}")
 Let Range("H10").Resize(2, 1) = LisBox.List
'  gives    a     as I might expect
'           b

Dim arr1Row2ColumnLongElementType(1 To 2, 1 To 2) As Long
 Let arr1Row2ColumnLongElementType(1, 1) = 1: arr1Row2ColumnLongElementType(1, 2) = 2: arr1Row2ColumnLongElementType(2, 1) = 3: arr1Row2ColumnLongElementType(2, 2) = 4
 Let LisBox.List = arr1Row2ColumnLongElementType()
 Let Range("S10").Resize(2, 2) = LisBox.List
'   gives   1  2   as  I might expect
'           3  4
End Sub

Alan

[HansV]

Guys, of course it's fine to discuss different methods to solve a problem, but keep in mind that Sal21, the original poster, works in Visual Basic 6, not in Excel.

[DocAElstein]

I thought that might be the case, ( Speakeasy mentioned it as well). Maybe that might make the "box" things more relevant, - it seems that VB6 is more old box thing orientated, whereas VBA seems to be a sort of newer Visual Basic for application thing.

(We are all just hijacking the Thread for the better advancement of mankind, :-) ).

[sal21]

Guys, of course it's fine to discuss different methods to solve a problem, but keep in mind that Sal21, the original poster, works in Visual Basic 6, not in Excel.

YES! Sure, i'm on VB6.

Tks bro, sorry me, i dont have write the language in my post.

[snb]

So, did you test the code in VB6 ?