Tests and Notes on Range objects in Excel Cell

[DocAElstein]

Nov 11, 2014
https://www.mrexcel.com/board/thread.../#post-3990978

Hi,
. Getting very frustrated as a VBA Beginner on some fundamental stuff. Please Help with a simple to understand explanation.
. The problem is An anomaly I found by chance, that is to say through occasional Typos in various codes I had written which assigned range values to a cell or range of cells in a column…
. As I investigated I hit on some fundamental curiosities, the answer to which I could not find by googling or Books etc. Therefore I ask the experts here for help:
.
. A simple case to demonstrate:
. Assume in Column A I have some arbitrary values and I copy them into Column C. For the purposes of this experiment I am using Range to do this

. For one row any of the following 4 code lines work:

Sub RangeRangeDotValueAnomalySingleCells()
  Let Range("C1").Value = Range("A1").Value 'Works: returns value in cell A1 in Cell C1
 Let Range("C1") = Range("A1") 'Works: returns value in cell A1 in Cell C1
 Let Range("C1") = Range("A1").Value 'Works: returns value in cell A1 in Cell C1
  Let Range("C1").Value = Range("A1") 'Works: returns value in cell A1 in Cell C1
 End Sub

. The first line working ties up with everything one learns and sees. That the second line works I am not too sure about. The third and forth lines I expected to error with a type mismatch, that is to say comparing an Object with a Variable

………………………

Taking the experiment one stage further to consider multiple cell Ranges the results puzzle me even further.
. For Rows 3 to 10 the corresponding 4 code lines give the results indicated in the green comments alongside each line

Sub RangeRangeDotValueAnomalyMultipleCells()
 Dim rngA As Range, rngC As Range
Set rngA = Range("A3:A10")
Set rngC = Range("C3:C10")
 Let rngC.Value = rngA.Value 'Works! Returns values form First column in Third column
 Let rngC = rngA ' Returns empty column in Third column
 Let rngC = rngA.Value 'Works! Returns values form First column In Third Column
 Let rngC.Value = rngA ' Returns empty column in Third column
 End Sub

I realize on the face of it this seems a somewhat simple question, but a full understanding could be very helpful in some more difficult anomalies I have been faced with in codes evaluating various ranges.
. I very much appreciate any helpful explanations easy for a VBA novice to understand.
Alan.

(P.s. 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 VBA with distinguishing the 3 cases whereby Let or Set or nothing is allowed or required!)

' Nov 13, 2014 https://www.mrexcel.com/board/thread...nomaly.817965/

Sub RangeArrayDirect()
'Dim RangeArray() As Variant  '. 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.)
Dim RangeArray As Variant
Let RangeArray = Worksheets("RangeValuevalues").Range(Cells(2, 2), Cells(3, 3)) '
'                                Watch : + : Worksheets("RangeValuevalues").Range(Cells(2, 2), Cells(3, 3)) :  : Variant/Object/Range : Tabelle6.RangeArrayDirect
End Sub

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

Sub RangeArrayIndirect()
Dim RangeArray() As Variant
Dim rngRangeArray As Range
Set rngRangeArray = Worksheets("RangeValuevalues").Range(Cells(2, 2), Cells(3, 3))
Let RangeArray = rngRangeArray ' Watch : + : rngRangeArray :  : Range/Range : Tabelle6.RangeArrayIndirect
End Sub

Option Explicit
'    https://www.mrexcel.com/board/threads/range-equivalent-to-range-value-sometimes-range-range-value-anomaly.817446/                                                                                                                                  https://fastexcel.wordpress.com/2017/03/13/excel-range-valuevaluetype-what-is-this-parameter/
'  Nov 11, 2014
Sub RangeRangeDotValueAnomalySingleCells()
 Let Range("C1").Value = Range("A1").Value 'Works: returns value in cell A1 in Cell C1
 Let Range("C1") = Range("A1") 'Works: returns value in cell A1 in Cell C1
 Let Range("C1") = Range("A1").Value 'Works: returns value in cell A1 in Cell C1
 Let Range("C1").Value = Range("A1") 'Works: returns value in cell A1 in Cell C1

' Let Range("C1").Value = Range("A1").Range ' Complie error,  argument not optional
End Sub
Sub RangeRangeDotValueAnomalyMultipleCells()
 Dim rngA As Range, rngC As Range
Set rngA = Range("A3:A10")
Set rngC = Range("C3:C10")   '  .Range ' If you want to explicitly tell Excel to use the range object, then you can force it.  ' Complie error,  argument not optional
 Let rngC.Value = rngA.Value 'Works! Returns values form First column in Third column
 Let rngC = rngA ' Returns empty column in Third column
 Let rngC = rngA.Value 'Works! Returns values form First column In Third Column
 Let rngC.Value = rngA ' Returns empty column in Third column
 End Sub
' There is no hard documentation that I am aware of that will tell you why some of those don't work. 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. As another example
Sub huh()
    Dim vData()
    Dim Ws                    As Worksheet
    Set Ws = ActiveSheet
    vData() = Range("A1:A10").Value  ' works  Watch : + : Range("A1:A10").Value :  : Variant/Variant(1 to 10, 1 to 1) : Tabelle6.huh
    vData() = ActiveSheet.Range("A1:A10").Value    ' works  Watch : + :     ActiveSheet.Range("A1:A10").Value :  : Variant/Variant(1 to 10, 1 to 1) : Tabelle6.huh
    vData() = Ws.Range("A1:A10").Value    ' works  Watch : + :     ActiveSheet.Range("A1:A10").Value :  : Variant/Variant(1 to 10, 1 to 1) : Tabelle6.huh

    vData() = Range("A1:A10")       ' works  Watch : + :     Range("A1:A10") :  : Object/Range : Tabelle6.huh
                                        ' Watch : - : vData() :  : Variant/Variant(1 to 10, 1 to 1) : Tabelle6.huh
    vData() = ActiveSheet.Range("A1:A10")    ' gives Type Mismatch error
 '                                          Watch : + : ActiveSheet.Range("A1:A10") :  : Variant/Object/Range : Tabelle6.huh
    vData() = Ws.Range("A1:A10")    ' works Watch : + : ws.Range("A1:A10") :  : Object/Range : Tabelle6.huh
End Sub
' https://www.mrexcel.com/board/threads/range-equivalent-to-range-value-sometimes-range-range-value-anomaly.817446/#post-3991004
' The only real difference between the line that fails and the one that follows is that
'   ActiveSheet returns a generic Object so you are effectively late bound.
'From the VBA language spec, this I think is the most relevant section:
' 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.
'
'
'_


'  Nov 13, 2014      https://www.mrexcel.com/board/threads/dimension-array-range-anomaly.817965/
Sub RangeArrayDirect()
'Dim RangeArray() As Variant  '. 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.)
Dim RangeArray As Variant
Let RangeArray = Worksheets("RangeValuevalues").Range(Cells(2, 2), Cells(3, 3)) '
'                                Watch : + : Worksheets("RangeValuevalues").Range(Cells(2, 2), Cells(3, 3)) :  : Variant/Object/Range : Tabelle6.RangeArrayDirect
End Sub
'. I can overcome the problem by adding an in between step (which is probably good practice anyway) as follows:
Sub RangeArrayIndirect()
Dim RangeArray() As Variant
Dim rngRangeArray As Range
Set rngRangeArray = Worksheets("RangeValuevalues").Range(Cells(2, 2), Cells(3, 3))
'                                Watch : + : Worksheets("RangeValuevalues").Range(Cells(2, 2), Cells(3, 3)) :  : Variant/Object/Range : Tabelle6.RangeArrayIndirect
Let RangeArray() = rngRangeArray ' Watch : + : rngRangeArray :  : Range/Range : Tabelle6.RangeArrayIndirect
End Sub