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

[DocAElstein]

I have a closed workbook, Closed.xlsm. ( It is the same folder as an open workbook, so I can get the path to it from a code line in the open workbook like ThisWorkbook.Path )

ExecuteExcel4Macro
_a) From that closed workbook (Closed.xlsm) I can get a value, using ExecuteExcel4Macro , from a single cell like this
somevalue = application.ExecuteExcel4Macro("'C\somepath\[Closed.xlsm]SomeSheet'!R1C1")
somevalue = application.ExecuteExcel4Macro("'" & ThisWorkbook.Path & "\[Closed.xlsm]SomeSheet'!R1C1")

_b) I cannot do something similar, using ExecuteExcel4Macro , to return a 2D array from Closed.xlsm like
some2Darray() =Application.ExecuteExcel4Macro("'C\path\[Closed.xlsm]somesheet'!R1C1:R5C2")
That last code line will not work. It will error!

However, I can do something similar to _a) and _b) using a similar approach with a simple closed workbook reference.

Closed Workbook Reference
_a) A simple formula , placed in an arbitrary spare cell , say the first, A1 , will return the value from a cell in a closed workbook
So for example, to return the value from the first cell in a closed workbook into the first cell of an open workbook, a simple code line like this can be used in the open workbook:
Range("A1").Value = "='" & ThisWorkbook.Path & "\[Closed.xlsm]somesheet' R1C1"

This is what the closed workbook, Closed.xlsm, looks like
Closed_xlsm.JPG : https://imgur.com/CBbun1h
Attachment 2370
If Closed.xlsm is closed and in the same folder as another workbook, macro.xlsm, then either of the following simple code lines in the following macro will return me the value from the first cell in the closed workbook, into the first cell in macro.xlsm

So after running the simple macro I will get this:
ClosedRefInCellA1.jpg : https://imgur.com/6N25bDe
Attachment 2371

'
'                                                                                                                                                                       https://www.excelforum.com/excel-programming-vba-macros/1286750-getting-2d-array-from-a-closed-workbook-using-executeexcel4macro.html
Sub Testie()
 Range("A1").Value = "='" & ThisWorkbook.Path & "\[Closed.xlsm]somesheet'!R1C1"
 Range("A1").Value = "='" & ThisWorkbook.Path & "\[Closed.xlsm]somesheet'!$A$1"
 Workbooks("macro.xlsm").Worksheets.Item(1).Range("A1").Value = "='" & ThisWorkbook.Path & "\[Closed.xlsm]somesheet'!$A$1"
 ThisWorkbook.Worksheets.Item(1).Range("A1").Value = "='" & ThisWorkbook.Path & "\[Closed.xlsm]somesheet'!$A$1"
 Let ThisWorkbook.Worksheets.Item(1).Range("A1").Value = "='" & ThisWorkbook.Path & "\[Closed.xlsm]somesheet'!$A$1"

End Sub

_b) Lets say that I want to get the information from a "2D array" in the closed workbook. Lets say I want the information from the first 2 columns, and first 5 rows, like
R1C1:R5C2 or $A$1:$B$5

I can do this using closed workbook references. One way is to use the same fixed vector reference ** within a closed workbook reference applied across some spare range in the open workbook, macro.xlsm.

Lets say I want to put the reference into the first 2 columns and first 5 rows of the open workbook, macro.xlsm.
The fixed vector reference to apply to this range will be that, from a worksheets first cell to the worksheets first cell. In other words , A1 or R[0]C[0], ( or RC, as R defaults to R[0] etc.. ). I apply that reference to the first two columns and first 5 rows in the workbook, A1:B5

So if this is my closed workbook:
Closed_xlsm (_b).JPG : https://imgur.com/3f9U6M1
Attachment 2372

then either of the following simple code lines in the following macro will return me the range of values from the range in the closed workbook, into the first two columns and first 5 rows in macro.xlsm

So after running the macro, Sub TestieClosedArrayRangeReference() , my first worksheet in macro.xlsm , will look like this
ClosedRefsInCellsA1toB5.JPG https://imgur.com/SzvBlea

Sub TestieClosedArrayRangeReference() '          http://www.excelfox.com/forum/showthread.php/2355-Tests-and-Notes-on-Range-Referrencing?p=11431&viewfull=1#post11431
 Range("A1:B5").Value = "='" & ThisWorkbook.Path & "\[Closed.xlsm]somesheet'!R[0]C[0]"
 Range("A1:B5").Value = "='" & ThisWorkbook.Path & "\[Closed.xlsm]somesheet'!RC"
 Range("A1:B5").Value = "='" & ThisWorkbook.Path & "\[Closed.xlsm]somesheet'!A1"
 Workbooks("macro.xlsm").Worksheets.Item(1).Range("A1:B5").Value = "='" & ThisWorkbook.Path & "\[Closed.xlsm]somesheet'!A1"
 ThisWorkbook.Worksheets.Item(1).Range("A1:B5").Value = "='" & ThisWorkbook.Path & "\[Closed.xlsm]somesheet'!A1"
 Let ThisWorkbook.Worksheets.Item(1).Range("A1:B5").Value = "='" & ThisWorkbook.Path & "\[Closed.xlsm]somesheet'!A1"

End Sub

Get 2D array from closed workbook
Finally, if I want to put that range of values into an array, then I simply use the available single line capture of a range of values using the .Value Property "Method" applied to that range in the macro.xlsm. ( After this I can delete, ( Clear ) , the temporary range of values).
As example: The following macro is in the uploaded file macro.xlsm. Download both macro.xlsm and Closed.xlsm into the same Folder.
Open just macro.xlsm. Leave Closed.xlsm closed. Run Sub Get2DArrayfromClosedWorkbook()
The values in range A1:B5 from the closed workbook, Closed.xlsm, will be placed in the array, MyArr()

Sub Get2DArrayfromClosedWorkbook()
 Let ThisWorkbook.Worksheets.Item(1).Range("A1:B5").Value = "='" & ThisWorkbook.Path & "\[Closed.xlsm]somesheet'!A1"

Dim MyArr() As Variant
 Let MyArr() = ThisWorkbook.Worksheets.Item(1).Range("A1:B5").Value
 ThisWorkbook.Worksheets.Item(1).Range("A1:B5").Clear

 MsgBox Prompt:="As example of my array content, here is value from B4 in closed workbook:" & vbCrLf & "MyArr(4, 2) value is    """ & MyArr(4, 2) & """"
End Sub









Ref
fixed vector reference ** In simple terms , this means simply a range reference in relative terms, ( so neglecting the $ in Column Letter and row number notation,  or including the [ ] in row number and column  number notation:  https://teylyn.com/2017/03/21/dollarsigns/#comment-191 

Ref
fixed vector reference ** In simple terms , this means simply a range reference in relative terms, ( so neglecting the $ in Column Letter and row number notation, or including the [ ] in row number and column number notation: https://teylyn.com/2017/03/21/dollarsigns/#comment-191