Populating a 2-D array

[Hotbird]

I have successfully used the Array statement to initialise a 1-dimensional
array, as follows:

NUMWORD = Array("One", "Two", "Three", "Four", "Five", "Six", "Seven", _
"Eight", "Nine", "Ten", "Eleven", "Twelve", "Thirteen", "Fourteen", _
"Fifteen", "Sixteen", "Seventeen", "Eighteen", "Nineteen", "Twenty")

But I have been unable to apply the same technique to a 2-dimension array
(12 x 4), and am left having to enter 48 lines of repetive code - as
follows. Is there an easier way?

' FIRST PARAMETER IS FORMATTED WORD 1 - 12
' SECOND PARAMETER IS: length, colour, line, character

W(1, 1) = Len("FASTEST")
W(1, 2) = 1 ' BLACK
W(1, 3) = 5 ' line 5
W(1, 4) = 6 ' char 6
W(2, 1) = Len("CREATE")
W(2, 2) = 1 ' BLACK
W(2, 3) = 5 ' line 5
W(2, 4) = 25 ' char 25
W(3, 1) = Len("NINE")
W(3, 2) = 1 ' BLACK
W(3, 3) = 5 ' line 5
W(3, 4) = 43 ' char43
W(4, 1) = Len("ANSWERS")
W(4, 2) = 1 ' BLACK
W(4, 3) = 6 ' line 6
W(4, 4) = 10 ' char 10
W(5, 1) = Len("WRONG")
W(5, 2) = 3 ' RED
W(5, 3) = 6 ' line 6
W(5, 4) = 38 ' char 38
W(6, 1) = Len("CHAIN")
W(6, 2) = 1 ' BLACK
W(6, 3) = 7 ' line 7
W(6, 4) = 12 ' char 12
W(7, 1) = Len("THE")
W(7, 2) = 1 ' BLACK
W(7, 3) = 7 ' line 7
W(7, 4) = 32 ' char 32
W(8, 1) = Len("CHAIN")
W(8, 2) = 1 ' BLACK
W(8, 3) = 7 ' line 7
W(8, 4) = 50 ' char 50
W(9, 1) = Len("BANK")
W(9, 2) = 5 ' BLUE
W(9, 3) = 9 ' line 9
W(9, 4) = 18 ' char 18
W(10, 1) = L3 ' CONTESTANT GOING FIRST
W(10, 2) = 1 ' BLACK
W(10, 3) = 18 ' line 18
W(10, 4) = 31 ' char 31
W(11, 1) = Len("£20")
W(11, 2) = 1 ' BLACK
W(11, 3) = 21 ' line 21
W(11, 4) = 28 ' char 28
W(12, 1) = Len("CLOCK")
W(12, 2) = 1 ' BLACK
W(12, 3) = 23 ' line 23
W(12, 4) = 12 ' char 12

 

[Bob Phillips]

Hotbird,

This is how to do it, courtesy of Dana DeLouis

W=[{7,1,5,6;6,1,5,25;4,1,5,23}]

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Beto]

I have successfully used the Array statement to initialise a 1-dimensional
array, as follows:

NUMWORD = Array("One", "Two", "Three", "Four", "Five", "Six", "Seven", _
"Eight", "Nine", "Ten", "Eleven", "Twelve", "Thirteen", "Fourteen", _
"Fifteen", "Sixteen", "Seventeen", "Eighteen", "Nineteen", "Twenty")

But I have been unable to apply the same technique to a 2-dimension array
(12 x 4), and am left having to enter 48 lines of repetive code - as
follows. Is there an easier way?

I made a similar question about one or two weeks ago, read the thread:

http://groups.google.cl/[email protected]+array&ie=ISO-8859-1&hl=es

You'll have to put all the link in one line carefully. Sorry.

Regards,

 

[Tom Ogilvy]

Using evaluate instead of square brackets might be a little more friendly:

Sub SetArray()
Dim sVal As String, sStr As String
Dim i As Long, j As Long
sVal = "6,1,5,6;5,1,5,25;4,1,5,43;7,1,6,10;5,3,6,38;" & _
"5,1,7,12;3,1,7,32;5,1,7,50;4,5,9,18;2,1,18,31;3,1,21" & _
",28;5,1,23,12"
varr = Evaluate("{" & sVal & "}")
For i = LBound(varr, 1) To UBound(varr, 1)
sStr = ""
For j = LBound(varr, 2) To UBound(varr, 2)
sStr = sStr & varr(i, j) & ", "
Next
Debug.Print Left(sStr, Len(sStr) - 2)
Next

Note that in my experience, this is only useful for small strings of values.
Less than around 128 characters as I recall. Your array is up to about 109
characters. Also, you had L3 as one of your values. I assumed you wanted
the len("L3") since that followed your pattern.

 

[Alan Beban]

Also, you had L3 as one of your values. I assumed you wanted
the len("L3") since that followed your pattern.

And if instead you wanted the name in Cell L3 of the active sheet
instead of the length of the text string L3, you can add the following
just before End Sub:

varr(10,1) = Range("L3") .Value

Alan Beban

 

[Hotbird]

This is how to do it, courtesy of Dana DeLouis

W=[{7,1,5,6;6,1,5,25;4,1,5,23}]
HTH

Bob Phillips

Thanks for this elegant solution, which works well for me. I have applied
it to a 7 x 10 array, but had to put it all on one enormous line. The
usual line continuation character ( _) seems not to work. Is there a work
around for this?

Vote =
[{7,8,9,1,2,3,4,5,6,"";3,4,5,7,8,"",9,1,2,"";"",3,4,5,7,"",8,9,2,"";"",9,"",
2,4,"",5,7,8,"";"",8,"",9,"","",2,4,7,"";"",9,"","","","",2,7,8,"";"","","",
"","","",8,9,7,""}]

 

[Tom Ogilvy]

Not using brackets. You can with Evaluate.

--
Regards,
Tom Ogilvy

This is how to do it, courtesy of Dana DeLouis

W=[{7,1,5,6;6,1,5,25;4,1,5,23}]
HTH

Bob Phillips

Thanks for this elegant solution, which works well for me. I have applied
it to a 7 x 10 array, but had to put it all on one enormous line. The
usual line continuation character ( _) seems not to work. Is there a work
around for this?

Vote =
[{7,8,9,1,2,3,4,5,6,"";3,4,5,7,8,"",9,1,2,"";"",3,4,5,7,"",8,9,2,"";"",9,"",2,4,"",5,7,8,"";"",8,"",9,"","",2,4,7,"";"",9,"","","","",2,7,8,"";"","","",
"","","",8,9,7,""}]

 

[Beto]

Not using brackets. You can with Evaluate.

How would that go????

Regards,
--
Beto
Reply: Erase between the dot (inclusive) and the @.
Responder: Borra la frase obvia y el punto previo.

This is how to do it, courtesy of Dana DeLouis

W=[{7,1,5,6;6,1,5,25;4,1,5,23}]
HTH

Bob Phillips

Thanks for this elegant solution, which works well for me. I have applied
it to a 7 x 10 array, but had to put it all on one enormous line. The
usual line continuation character ( _) seems not to work. Is there a work
around for this?

Vote =

[{7,8,9,1,2,3,4,5,6,"";3,4,5,7,8,"",9,1,2,"";"",3,4,5,7,"",8,9,2,"";"",9,"",

2,4,"",5,7,8,"";"",8,"",9,"","",2,4,7,"";"",9,"","","","",2,7,8,"";"","","",

"","","",8,9,7,""}]

 

[Dave Peterson]

Something like:

Dim Vote As Variant

Vote = Application.Evaluate( _
"{7,8,9,1,2,3,4,5,6,"""";" _
& "3,4,5,7,8,"""",9,1,2,"""";" _
& """"",3,4,5,7,"""",8,9,2,"""";" _
& """"",9,"""",2,4,"""",5,7,8,"""";" _
& """"",8,"""",9,"""","""",2,4,7,"""";" _
& """"",9,"""","""","""","""",2,7,8,"""";" _
& """"","""","""","""","""","""",8,9,7,""""}")

Each double quote gets doubled:

"" becomes """"

This line:
& """"",3,4,5,7,"""",8,9,2,"""";" _
has double quotes surrounding this string:
"""",3,4,5,7,"""",8,9,2,"""";
since it's a string.

Not using brackets. You can with Evaluate.

How would that go????

Regards,
--
Beto
Reply: Erase between the dot (inclusive) and the @.
Responder: Borra la frase obvia y el punto previo.

This is how to do it, courtesy of Dana DeLouis

W=[{7,1,5,6;6,1,5,25;4,1,5,23}]
HTH

Bob Phillips

Thanks for this elegant solution, which works well for me. I have applied
it to a 7 x 10 array, but had to put it all on one enormous line. The
usual line continuation character ( _) seems not to work. Is there a work
around for this?

Vote =

[{7,8,9,1,2,3,4,5,6,"";3,4,5,7,8,"",9,1,2,"";"",3,4,5,7,"",8,9,2,"";"",9,"",

2,4,"",5,7,8,"";"",8,"",9,"","",2,4,7,"";"",9,"","","","",2,7,8,"";"","","",

"","","",8,9,7,""}]

 

[Tom Ogilvy]

If you don't like typing """" here is a slight variation:

Sub Tester4()

sStr = "{7,8,9,1,2,3,4,5,6,@;" _
& "3,4,5,7,8,@,9,1,2,@;" _
& "@,3,4,5,7,@,8,9,2,@;" _
& "@,9,@,2,4,@,5,7,8,@;" _
& "@,8,@,9,@,@,2,4,7,@;" _
& "@,9,@,@,@,@,2,7,8,@;" _
& "@,@,@,@,@,@,8,9,7,@}"

sStr = Application.Substitute(sStr, "@", """""")
Debug.Print sStr
vote = Application.Evaluate(sStr)
Debug.Print LBound(vote, 1), UBound(vote, 1)
Debug.Print LBound(vote, 2), UBound(vote, 2)
End Sub

--
Regards,
Tom Ogilvy

Something like:

Dim Vote As Variant

Vote = Application.Evaluate( _
"{7,8,9,1,2,3,4,5,6,"""";" _
& "3,4,5,7,8,"""",9,1,2,"""";" _
& """"",3,4,5,7,"""",8,9,2,"""";" _
& """"",9,"""",2,4,"""",5,7,8,"""";" _
& """"",8,"""",9,"""","""",2,4,7,"""";" _
& """"",9,"""","""","""","""",2,7,8,"""";" _
& """"","""","""","""","""","""",8,9,7,""""}")

Each double quote gets doubled:

"" becomes """"

This line:
& """"",3,4,5,7,"""",8,9,2,"""";" _
has double quotes surrounding this string:
"""",3,4,5,7,"""",8,9,2,"""";
since it's a string.

Not using brackets. You can with Evaluate.

How would that go????

Regards,
--
Beto
Reply: Erase between the dot (inclusive) and the @.
Responder: Borra la frase obvia y el punto previo.

This is how to do it, courtesy of Dana DeLouis

W=[{7,1,5,6;6,1,5,25;4,1,5,23}]
HTH

Bob Phillips


Thanks for this elegant solution, which works well for me. I have applied
it to a 7 x 10 array, but had to put it all on one enormous line. The
usual line continuation character ( _) seems not to work. Is there a work
around for this?

Vote =

[{7,8,9,1,2,3,4,5,6,"";3,4,5,7,8,"",9,1,2,"";"",3,4,5,7,"",8,9,2,"";"",9,"",

2,4,"",5,7,8,"";"",8,"",9,"","",2,4,7,"";"",9,"","","","",2,7,8,"";"","","",

"","","",8,9,7,""}]