Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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,""}] |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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,""}] |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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,""}] |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Populating an array | Excel Worksheet Functions | |||
meaning of : IF(Switch; Average(array A, array B); array A) | Excel Worksheet Functions | |||
Populating another tab. | Excel Discussion (Misc queries) | |||
Populating a Temporary Array | Excel Programming | |||
populating multicolumn listbox with an array instead of... | Excel Programming |