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

 

[Norie]

You are missing .Value.

 

[RoryA]

As I said the other day, you need to use .Value

Also note that this is prone to error:

Worksheets("sheet1").Range(Cells(2, 2), Cells(3, 3))

You must qualify the Cells calls with a worksheet too - either:

Worksheets("sheet1").Range(Worksheets("sheet1").Cells(2, 2), Worksheets("sheet1").Cells(3, 3))

or:

With Worksheets("sheet1")
.Range(.Cells(2, 2), .Cells(3, 3))
End With

 

[DocAElstein]

You are missing .Value.

As I said the other day, you need to use .Value
.........

OK, Thanks, I had tried so many variations in my attempt to track down the problem that I missed that out, sorry.
. But I note in looking back now at many codes from other people that I have they actually all miss out the .Value in such a case. I guess then by Dim as variant, Excel sort of guesses and gets it right.

So this works for me:

[color=darkblue]Sub[/color] RangeArrayDirectdotValue()
 
  [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)).Value
 
[color=darkblue]End[/color] [color=darkblue]Sub[/color]

. But I note in looking back now at many codes from other people that I have they actually all miss out the .Value in such a case. They then Dim as Variant. I guess then by Dim as variant, Excel sort of guesses and gets it right.

. Another thing that threw me off in my endless attempt to find the problem is another anomaly, -that is that I see that this strangely does work with or without the .Value !!

[color=darkblue]Sub[/color] RangeArrayDirectwithoutWorksheet()
 
  [color=darkblue]Dim[/color] RangeArray() [color=darkblue]As[/color] [color=darkblue]Variant[/color]
 
  [color=darkblue]Let[/color] RangeArray = Range(Cells(2, 2), Cells(3, 3))
  [color=green]'Let RangeArray = Range(Cells(2, 2), Cells(3, 3)).Value[/color]
 
[color=darkblue]End[/color] [color=darkblue]Sub[/color]

… maybe Excel just guessing differently this time, -as was the case of my second example in the original Post #1 which should probably be better written as

[color=darkblue]Sub[/color] RangeArrayIndirectwithDotValue()
 
  [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.Value
 
[color=darkblue]End[/color] [color=darkblue]Sub[/color]

. Again in all the codes I have seen the .value was missed out. So Perhaps I can be a bit forgiven by slipping up there because of the practice of professionals that Know from instinct wot they can miss out where excel would guess it right!!!!!!!

Thanks again Guys. The experienced eye is invaluable

…………………………………………..

.......
Also note that this is prone to error:

Worksheets("sheet1").Range(Cells(2, 2), Cells(3, 3))

You must qualify the Cells calls with a worksheet too - either:

Worksheets("sheet1").Range(Worksheets("sheet1").Cells(2, 2), Worksheets("sheet1").Cells(3, 3))

or:
........

– I did have that in some of my variations I was trying, just omitted it for clarity. But good to confirm I thought it was better to do that. - Thanks again.

Alan

 

[DocAElstein]

P.s. or EDIT:- I think somewhere in that last post I meant to say that my first code in post #1 should be better written as

[color=darkblue]Sub[/color] RangeArrayDirectwithDotValue()
 
  [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)).Value
 
[color=darkblue]End[/color] [color=darkblue]Sub[/color]

. Alan

 

[DocAElstein]

...... or better as Rory reminded me....

[color=darkblue]Sub[/color] RangeArrayDirectwithDotValueFull()
  
  [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(Worksheets("sheet1").Cells(2, 2), Worksheets("sheet1").Cells(3, 3)).Value


[color=darkblue]End[/color] [color=darkblue]Sub[/color]

 

[RoryA]

So Perhaps I can be a bit forgiven by slipping up there because of the practice of professionals that Know from instinct wot they can miss out where excel would guess it right

Not really, considering this: http://www.mrexcel.com/forum/excel-...lue=-sometimes-range-range-value-anomaly.html

 

[DocAElstein]

Not really, considering this: http://www.mrexcel.com/forum/excel-questions/817446-range%3D-equivalent-range-value%3D-sometimes-range-range-value-anomaly.html

OK I guess you mean the professionals all know this rather than it being instint.

....

5.6.2.3 Default Member Recursion Limits
Evaluation of an object whose default Property Get or default function returns another object can lead to a recursive evaluation process if the returned object has a further default member. Recursion through this chain of default members may be implicit if evaluating to a simple data value and each default member has an empty parameter list, or explicit if index expressions are specified that specifically parameterize each default member.
An implementation may define limits on when such a recursive default member evaluation is valid. The limits may depend on factors such as the depth of the recursion, implicit vs. explicit specification of empty argument lists, whether members return specific classes vs. returning Object or Variant, whether the default members are functions vs. Property Gets, and whether the expression occurs on the left-hand-side of an assignment. The implementation may determine such an evaluation to be invalid statically or may raise error 9 (Subscript out of range) or 13 (Type mismatch) during evaluation at runtime.


..... but I wonder if that is true. Maybe for some clever profis or "persistent hacks"

 

[RoryA]

No, I just meant that I already said to you:

Suffice to say, when you mean .Value, write .Value. Never use implicit defaults because it will bite you when you don't expect it if you do

 

[DocAElstein]

No, I just meant that I already said to you: Suffice to say, when you mean .Value, write .Value. Never use implicit defaults because it will bite you when you don't expect it if you do

OK . I also meant to say that a lot of Profi's just don't do that, that is to say they miss out the .Value. Which is fine, keeps everything "looking" simpler. Just catches me out sometimes. I allways try to add bits missing when I look through codes. Just missed out the .Value here, as along the way there is often RangeA=RangeB stuff as well, which of course should not have .Value if they are Ranges.
. As you say then best thing (at least for a Beginner) allways be explicit with stuff.






.... If I could be so bold (maybe not worthy to be really, but anyways....A Tip for some Profi's also ...Never use implicit defaults because it will bite you when you don't expect it if you do .. - at the latest when an idiot like me Bugs you for Help.

Alan












P.s. (Did not mean that I had any problems with your codes you gave me .....looking back they all had the .Value on, (Although you were only improving (greatly!) mine where I has the .Value already on .. -but I highly expect you allways do it.........)