Dimension Array Range Anomaly

DocAElstein

Banned user
Joined
May 24, 2014
Messages
1,336
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
ABCD
1
2AB
334
4
Sheet1



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

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:

Code:
[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
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
As I said the other day, you need to use .Value

Also note that this is prone to error:
Code:
Worksheets("sheet1").Range(Cells(2, 2), Cells(3, 3))
You must qualify the Cells calls with a worksheet too - either:
Code:
Worksheets("sheet1").Range(Worksheets("sheet1").Cells(2, 2), Worksheets("sheet1").Cells(3, 3))
or:
Code:
With Worksheets("sheet1")
.Range(.Cells(2, 2), .Cells(3, 3))
End With
 
Upvote 0
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:
Code:
[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 !!


Code:
[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

Code:
[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:
Rich (BB code):
Worksheets("sheet1").Range(Cells(2, 2), Cells(3, 3))
You must qualify the Cells calls with a worksheet too - either:
Rich (BB code):
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
 
Upvote 0
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

Code:
[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
 
Upvote 0
...... or better as Rory reminded me....

Code:
[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]
 
Upvote 0

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"
;)
 
Upvote 0
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
 
Upvote 0
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.........)
 
Upvote 0

Forum statistics

Threads
1,213,567
Messages
6,114,342
Members
448,570
Latest member
rik81h

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top