Why we can't assign to static parametrized array VBA? -

why can't assign values range static array :

sub test() 'error occours dim a(1 10) double 'also don't work : 'dim a(1 10, t 1) double = range("a1:a10")  end sub 

because array has been allocated, regardless of how many dimensions use.

dim x(1 10, 1 1) variant '// you've allocated array x = range("a1:a10").value         '// can't allocate allocated array 

you can declare array of type variant without allocating , use instead:

dim x() variant        '// array not allocated x = range("a1:a10")       '// x = array sized 1 10, 1 1 

assigning range directly array in way return type variant/variant , receiving array must of type variant also.

---

you create udf you, kind of defies point of assigning directly range:

sub so()     dim variant     = rangetoarray(range("a1:a10")) end sub   function rangetoarray(rng range) variant redim x(1 rng.rows.count, 1 rng.columns.count) variant dim r long, c long  r = 1 rng.rows.count     c = 1 rng.columns.count         x(r, c) = rng.cells(r, c)     next c next r  rangetoarray = x  end function