VBA, Excel, use Range to fill 2D-Array -

this question has answer here:

• why having issues assigning range array of variants 3 answers

i not understand behaviour:

sub tues()     dim a() variant     = range("a1:a10") ' works      dim b() variant     b = activesheet.range("a1:a10") ' type mismatch end sub 

the first version works 2nd version not. why? difference?

the way go adding ".value" @ end of range. idea make things explicit (the reason can omit because value default property range object)

i added values watches see going on , apparently there problem of excel not been able ( , implicitly ) cast object on fly. note in picture how expression failing "activesheet.range("a1:a10") of type: variant/object/range; transition variant object causing issue.

a way force cast correctly split process in 2 parts first 1 casts range , second 1 casts variant array. @ example

also notice if declare variable variant alone , not array of variants (dim e , not dim e()) because adapt needed.

sub tues()      'works     dim a() variant     = range("a1:a10")      ' type missmatch     dim b() variant     b = activesheet.range("a1:a10")      ' fix make cast     dim c() variant     dim r range     set r = activesheet.range("a1:a10")     c = r      ' best of options     dim d variant     d = activesheet.range("a1:a10").value      end sub 

hope makes clear.