Array function with more than 29 arguments

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

VirtualReal@gmail.com - 15 Apr 2007 14:42 GMT

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 to this Message

RB Smissaert - 15 Apr 2007 15:00 GMT

Not sure what you are trying to achieve, but maybe something like this will
work for you:

Sub test()

Dim i As Long
Dim arr(1 To 1, 1 To 30) As Long

For i = 1 To 30
arr(1, i) = i
Next i

Range("A1:AD1").Value = arr

End Sub

RBS

> Hi all,
>
[quoted text clipped - 20 lines]
>
> - Bas

Reply to this Message

Dana DeLouis - 15 Apr 2007 15:02 GMT

Hi. It doesn't work like a math program, but here's one way in Excel:

Sub ArrayTest()
Dim v1 As Variant
Dim v2 As Variant
Dim m As Variant

v1 = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15)
v2 = Array(16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30)

m = Array(v1, v2)
With WorksheetFunction
m = .Transpose(.Transpose(m))
End With
[A1].Resize(UBound(m, 1), UBound(m, 2)) = m
End Sub

Signature

HTH :>)
Dana DeLouis
Windows XP & Office 2007

> Hi all,
>
[quoted text clipped - 20 lines]
>
> - Bas

Reply to this Message

Art - 15 Apr 2007 15:40 GMT

Dana,

What happened?? You took an array with 2 elements, each of which was a
variant holding an array. You transposed it twice and got back a 2-dim
array? How does that work?

> Hi. It doesn't work like a math program, but here's one way in Excel:
>
[quoted text clipped - 37 lines]
> >
> > - Bas

Reply to this Message