Defining an array as a range of cells

[kualjo]

Is there a way, once I have declared my 2-dimensional array and subscripts, to define a range of cells as the location of the array? For example, if I have

dim testarray (5, 5)
and want that 5x5 range to be cells A1:E5, how can I tell VBA to use that range as the array without having to define all 25 cells one by one? I tried something like

testarray = Range("A1:E5")
but of course this didn't work. My actual working range is going to be significantly larger, so I'm hoping for a better solution.

[Bob Phillips]

Are you trying to load the array from the range, because that is the correct syntax?

[kualjo]

Yes, the intent would be to define an x*y array, then have a cell range of x*y cells automatically assign values to each variable within the array. With the syntax above, I get this error:

Compile error:
Can't assign to array

I hope that I'm just not doing it right and that there is a way to assign all the values all at once as opposed to x*y value assignment statements.

[Bob Phillips]

It does work I can assure you.

I think I see your problem, instead of defining the array as you do, sizing it i the definition, declare it like this



Dim testarray As Variant

[kualjo]

That works! I will proceed from here, and if I have any other issues, I'll post another reply.

What I can't figure out is why none of the VBA books I have address this method. Seems like one of the most obvious ways to populate an array with values without having to write miles of code.

Thanks!