Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming,microsoft.public.excel,microsoft.public.excel.misc
|
|||
|
|||
Array function with more than 29 arguments
Hi all,
I want to write more than 29 array varibles (defined in a my VBA code) in a workbook range. Is there a way to accomplish this? Here is my sample code (check my remark): Sub ArrayTest() Dim Array1 As Variant Dim Array2 As Variant Dim CompleteArray As Variant Array1 = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15) Array2 = Array(16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30) CompleteArray = Array(Array1, Array2) Range("A1:AD1").Value = (CompleteArray) 'Result: Range A1 & B2 is empty while the rest is #N/A End Sub Thanks for any feedback! - Bas |
#2
Posted to microsoft.public.excel.programming,microsoft.public.excel,microsoft.public.excel.misc
|
|||
|
|||
Array function with more than 29 arguments
One way:
Dim CompleteArray As Variant CompleteArray = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, _ 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, _ 24, 25, 26, 27, 28, 29, 30) Range("A1:AD1").Value = CompleteArray By making an array of arrays, CompleteArray is two dimensional rather than a single array. Just for illustration, take a look at Public Sub ArrayTest() Dim Array1 As Variant Dim Array2 As Variant Dim CompleteArray As Variant Array1 = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15) Array2 = Array(16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, _ 28, 29, 30) CompleteArray = Array(Array1, Array2) Range("A1:O2").Value = Application.Transpose( _ Application.Transpose(CompleteArray)) End Sub In article .com, wrote: Hi all, I want to write more than 29 array varibles (defined in a my VBA code) in a workbook range. Is there a way to accomplish this? Here is my sample code (check my remark): Sub ArrayTest() Dim Array1 As Variant Dim Array2 As Variant Dim CompleteArray As Variant Array1 = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15) Array2 = Array(16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30) CompleteArray = Array(Array1, Array2) Range("A1:AD1").Value = (CompleteArray) 'Result: Range A1 & B2 is empty while the rest is #N/A End Sub Thanks for any feedback! - Bas |
#3
Posted to microsoft.public.excel.programming,microsoft.public.excel,microsoft.public.excel.misc
|
|||
|
|||
Array function with more than 29 arguments
Wow! :-o
I thought, from the beginning of my work, that the maximum arguments from an array was 29. Mea culpa, and thanks very much! On Apr 15, 12:23 pm, JE McGimpsey wrote: One way: Dim CompleteArray As Variant CompleteArray = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, _ 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, _ 24, 25, 26, 27, 28, 29, 30) Range("A1:AD1").Value = CompleteArray By making an array of arrays, CompleteArray is two dimensional rather than a single array. Just for illustration, take a look at Public Sub ArrayTest() Dim Array1 As Variant Dim Array2 As Variant Dim CompleteArray As Variant Array1 = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15) Array2 = Array(16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, _ 28, 29, 30) CompleteArray = Array(Array1, Array2) Range("A1:O2").Value = Application.Transpose( _ Application.Transpose(CompleteArray)) End Sub In article .com, wrote: Hi all, I want to write more than 29 array varibles (defined in a my VBA code) in a workbook range. Is there a way to accomplish this? Here is my sample code (check my remark): Sub ArrayTest() Dim Array1 As Variant Dim Array2 As Variant Dim CompleteArray As Variant Array1 = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15) Array2 = Array(16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30) CompleteArray = Array(Array1, Array2) Range("A1:AD1").Value = (CompleteArray) 'Result: Range A1 & B2 is empty while the rest is #N/A End Sub Thanks for any feedback! - Bas |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Array function with more than 29 arguments | Excel Discussion (Misc queries) | |||
If Function with 3 arguments | Excel Worksheet Functions | |||
2 way Vlookup - Creating array arguments from columns | Excel Discussion (Misc queries) | |||
Is it possible to use more than 8 arguments in a function? | Excel Discussion (Misc queries) | |||
How do I set up an array using countif for 2 separate arguments. | Excel Worksheet Functions |