Dimension Array Range Anomaly

[DocAElstein]

Hi,
. I spent many hours (or Days on and off!) in different codes with wot I thought were inconsistent errors. Eventually I found the problem and solved it. But no googling could help me explain exactly wot was going on. I expect it may lie in the complex way VBA is actually doing stuff. Can anyone Help.

. Assume for demonstration purpose I have a simple range as follows……

Book1
	A	B	C	D
1
2		A	B
3		3	4
4
Sheet1

…. And I wish to “capture it” into an array with the following simple code

[color=darkblue]Sub[/color] RangeArrayDirect()
 
  [color=green]'Dim RangeArray() As Variant[/color]
  [color=darkblue]Dim[/color] RangeArray [color=darkblue]As[/color] [color=darkblue]Variant[/color]
 
  [color=darkblue]Let[/color] RangeArray = Worksheets("sheet1").Range(Cells(2, 2), Cells(3, 3))
 
[color=darkblue]End[/color] [color=darkblue]Sub[/color]

. As you can see I have “Commented out” the first Dim statement as if I use this instead I get a Type Mismatch error ( I prefer to use such a Dim statement to be as explicit as possible in dimensioning which I believe is good practice.)

. I can overcome the problem by adding an in between step (which is probably good practice anyway) as follows:

[color=darkblue]Sub[/color] RangeArrayIndirect()
 
  [color=darkblue]Dim[/color] RangeArray() [color=darkblue]As[/color] [color=darkblue]Variant[/color]
 
  [color=darkblue]Dim[/color] rngRangeArray [color=darkblue]As[/color] Range
  [color=darkblue]Set[/color] rngRangeArray = Worksheets("sheet1").Range(Cells(2, 2), Cells(3, 3))
 
  [color=darkblue]Let[/color] RangeArray = rngRangeArray
 
[color=darkblue]End[/color] [color=darkblue]Sub[/color]

. Can anyone explain to me why I am only able to Dim as an array with Dim() in the second code.

Thanks
Alan

 

[Kyle123]

There's being explicit and there's using Let ...

 

[DocAElstein]

There's being explicit and there's using Let ...

...I realize that Let is unnecessary, it is just a throw back to 25 years ago – the last time I did any programming and as it “Lets” me do it I find it helpful as a beginner in <acronym title="visual basic for applications" style="border-width: 0px 0px 1px; border-bottom-style: dotted; border-bottom-color: rgb(0, 0, 0); cursor: help; color: rgb(51, 51, 51); font-size: 13.3333339691162px; background-color: rgb(250, 250, 250);">VBA</acronym> with distinguishing the 3 cases whereby Let or Set or nothing is allowed or required! Sorry if that confuses the issue as well!