Populating a 2-D array
 

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

Populating a 2-D array
 

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)

"Hotbird" wrote in message
...
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

Populating a 2-D array
 

Hotbird wrote:

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/groups?hl=es...59-1%26hl%3Des

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

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

Populating a 2-D array
 

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.


--
Regards,
Tom Ogilvy
"Hotbird" wrote in message
...
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

Populating a 2-D array
 

Tom Ogilvy wrote:
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

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
End Sub
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.

Populating a 2-D array
 

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,""}]

Populating a 2-D array
 

Not using brackets. You can with Evaluate.

--
Regards,
Tom Ogilvy

"Hotbird" wrote in message
...
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,""}]

Populating a 2-D array
 

Tom Ogilvy wrote:

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,""}]

Populating a 2-D array
 

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


Dave Peterson wrote in message
...
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.

Beto wrote:

Tom Ogilvy wrote:

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