Range Dimensioning, Range and Value Referencing and Referring to Arrays

Hi,
. I have been learning VBA for about 6months now but still get a bit bogged down on getting to grips with understanding some basic Range assigning and Definition. I need help from someone that understands the ‘core’ as it were,. of how the computer or VBA actually works..
. This post and the specific questions here follows on from a question that came up coincidentally yesterday here Post #10 - # 14
http://www.excelforum.com/excel-prog...n-a-range.html
( In addition I think it is appropriate to reference two threads in which I have a lot of great help to get me this far in this general Theme)
http://www.mrexcel.com/forum/excel-q...nomaly.html?&&
http://www.mrexcel.com/forum/excel-q...-anomaly.html?
)


. If I may “Summarize” the storys so far as I see it, as it leads on nicely to my current problem:….

. Say I have some arbitrary table in a spreadsheet, say in the Range C4 to E6. There are various ways to “capture” as it were this data efficiently (in an Array)**** for use in further data manipulation, sorting, re-arranging etc. etc.

. A very common way frequently used and I think correctly and explicitly explained in the ‘green comments would be:

Option Explicit 'Force me and help me to get variables correctly defined.
Sub ArrayRangeValueCapture()

Dim LastRowTable As Long, LastClmTable As Long 'Self explanitary variables for the table. Long is a very big number, but as smaller ones are coerced into this usually by VBA there is no memory advantage of using dimensioning to smaller
Dim ScreenThings() As Variant 'Although we may the variable types, in the following line VBA initially sees a Range so we need to allow for this.

Let ScreenThings() = Range("C4").CurrentRegion.Value 'Explicitly not relying on Implicit Stuff (In other words do not forget the .Value which VBA will only sometimes "Guess right" wot you mean!!)

Let LastRowTable = UBound(ScreenThings, 1): LastClmTable = UBound(ScreenThings, 2) ' This shows one efficiency of this "Capture" Method as we have a simlpe way to get at the Table Size through Upper bounds of Rows : and Columns in the Array

End Sub 'ArrayRangeValueCapture()
So my first question 1)

…….as in this case we know the Array dimensions, why does the code below not work?

Sub ArrayRangeValueCapture2()

Dim LastRowTable As Long, LastClmTable As Long
Dim ScreenThings(1 To 3, 1 To 3) As Variant

Let ScreenThings() = Range("C4").CurrentRegion.Value

Let LastRowTable = UBound(ScreenThings, 1): LastClmTable = UBound(ScreenThings, 2)

End Sub 'ArrayRangeValueCapture2()
……………..

My second question 2 a)

. Here is what I thought would be a direct comparison with the first code except that I am trying to “Capture” in an Array an Array of Ranges. But I t does not work .. why?

Sub ArrayRangeRangeCapture()

Dim LastRowTable As Long, LastClmTable As Long
Dim ScreenThings() As Range

Set ScreenThings() = Range("C4").CurrentRegion

Let LastRowTable = UBound(ScreenThings, 1): LastClmTable = UBound(ScreenThings, 2)

End Sub 'ArrayRangeRangeCapture()
.2 b) I note that this does work, but I had to comment out the Ubound lines or it errored there****

Sub ArrayRangeRangeCapture2()

Dim LastRowTable As Long, LastClmTable As Long
Dim ScreenThings As Range

Set ScreenThings = Range("C4").CurrentRegion

'Let LastRowTable = UBound(ScreenThings, 1): LastClmTable = UBound(ScreenThings, 2)
End Sub 'ArrayRangeRangeCapture2()
…….****.could the answer be then that I have fooled myself into thinking that I have an Array of Ranges, but in fact I have an Object, that is to say a Range Object.

3) My final Question (Bit related to 1 and 2)

This works as an alternative to the very first code…..

Sub ArrayRangeValueCapture3()

Dim LastRowTable As Long, LastClmTable As Long
Dim ScreenThings As Variant

Let ScreenThings = Range("C4").CurrentRegion.Value

Let LastRowTable = UBound(ScreenThings, 1): LastClmTable = UBound(ScreenThings, 2)

End Sub 'ArrayRangeValueCapture3()
…… I note the results look exactly the same in the Watch Window for ScreenThings here as for ScreenThings() from the first code. So wot is going on here? - .. is this maybe just a case of VBA “guessing right” and returning an array. I note that most Profis would use this last code rather than the first.

Many thanks.
Alan Elston