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 |
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 |
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 |
All times are GMT +1. The time now is 05:22 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com