Excel - VBA - Array and sheet *Resolved*

Hi,

I know that you can put an array into a range and it posts in it beautifully. And Quickly.

Now how do you do the opposite ad get a range into an array of variant??

I tried the opposite :

VB Code:

Redim aryTitles(21)
aryTitles() = shtSrc.Range(shtSrc.Cells(1, 1), shtSrc.Cells(1, 21))

This doesn't work and the array is reset to 0 elements.

Any ideas?


Vince

You need to use a variant to store the returned values, rather than an array. eg:

VB Code:

Dim aryTitles
aryTitles = shtSrc.Range(shtSrc.Cells(1, 1), shtSrc.Cells(1, 21))

Ahh..

Just one variable that is variant... not a redimmed array of variant?

Is this variant variable like an array or do I need to change it into an array?


Vince

Yep.. this way you get a variant containing an array (no need for any conversion), rather than an array or variants.

This is needed as you cant directly assign all values to an array in one statement (but you can set a variant to contain an array).

oh, and by the way (in case you dont know)... the array it returns is dimmed as follows:

(1 to <num rows> , 1 to <num cols>)

Ta