Tests and Notes on Range objects in Excel Cell

[DocAElstein]

This Post is #21995 #12
https://www.excelfox.com/forum/showt...age2#Post21995

---

Right Hand Side Range Range Value values
Range= equivalent to Range.Value= only sometimes. Range Range.Value Anomaly

Question from the web (https://www.mrexcel.com/board/ ( forum) )
https://www.excelfox.com/forum/showt...age2#Post21994
That was a good question, Alan.

Answer
I want to limit the discussion mainly to a general ranges code lines of these pseudo form
Let Range2.Value = Range1.Value
Let Range2 = Range1.Value
Let Range2.Value = Range1
Let Range2 = Range1
, which ijn words is something like,
Writing in Cells = Reading text and/ or numbers
LHS
Also to limit the discussion here, I want to say, and please Let me, for now that the LHS variations all do the same, which is Writing in a Cells, and consequence of that is that anything to do with number values, text values in most## cases doing something very similar to how us Human’s would physically write things in cells. A consequence of that is that whether we have a formula or not is determined by whether the first character is an = , and this means that all these sort of "things" do the same. (I would tend to call these "things" values )
FormulaR1C1Local
FormulaR1C1
FormulaR1C1Local
Value2
Value(RangeValueDataType:=xlRangeValueDefault)
Formula
(These things are handled different to other things such that we can write in a single value or all values in a multi cell rectangular range of values all in one go)
So I would like to take the liberty of leaving the LHS as this
Let Range2 =

A further simplification for the sake of clarity that I would like to make is to stay with ranges / cells, on the LHS and not get into variables too much there. Such considerations lead to further issues and complications such as whether things coerce or Excel is just written to do things in certain conditions, and possible Default Member Recursion Limits, and discussions of whether entrails should be pulled out of the LHS when sacrificing a virgin…etc, etc. I want to avoid that.


RHS
This is what it is all about, here.
I think it is an over simplification, and arguably wrong to say that the .Value is generally a Property and the default property of a range object.
For one thing the LHS writing .Value and RHS read .Value seem to be different/ only loosely related
We already discussed briefly the LHS, and we might at a stretch of the imagination be able to say that default property is value. I might argue that it is the other way around. Something like, on the writing LHS, the default of these values things,
Range.FormulaR1C1Local =
Range.FormulaR1C1 =
Range.FormulaR1C1Local =
Range.Value2 =
Range.Value(RangeValueDataType:=xlRangeValueDefaul t) =
Range.Formula =
, is
Range =
I can’t even see an Range.Value Property there. I can see a Range.Value(RangeValueDataType:=xl________) thing. That looks like a method to me. It acts like one as well, doing quite different things depending on the argument you give it.
Anyway back to the RHS
It does not appear to me to have a default property. You are reading stuff, and you had better be sure you tell the Range1 object what you want, or else people like RoryA will start confusing you with rubbish like Default Member Recursion Limits and god knows what else he either dreamt up, doesn’t understand or can’t or won’t explain.
Now if you don’t use something such as
.Value( RangeValueDataType:=xl________ )
( , or
.Value
, if you only feel like being semi – Explicit ),
, then something else happens….
I still don’t know for sure what happens, but a couple of guesses…
_ The Range1 if used on the RHS is an object. Some objects have a "value" that is a text and/or number string that is returned if you try to assign that object somewhere expecting to have some text and/or number string given to it. This "value" might be that thing you see, for example, in the Watch Window.
https://i.postimg.cc/y83k0dH3/range-...ate-Window.jpg



Example macro in next post:
Here a brief description of what is going on in it
In '0a) I have found some ways empirically to get that Number held as Text thing . The thing that they have in common is putting an element of an array in a cell ( which we can’t manually do ) , and that element must be of a string type. As a Layman, I am thinking a couple of things
_ that low level computer stuff is all about arrays of strings. I am thinking that concepts of single values, variables and the such is high level stuff that helps us Humans to interface with computers, which is what spreadsheets are about: Tables and boxes with things in them.
_ once in a while, deliberately or by accident, perhaps often deliberately for efficiency, some processes by pass some of the high level Excel stuff which results in a number not getting in a spreadsheet as a number, but as a text that looks like one. For a lot of Excel and VBA stuff that is less important since things are often obliging, as a high level thing like Excel arguably should be. So often numbers held as text is handled as if they were normal numbers. But because we can use stuff external to Excel through VBA, some of which can be more intermediate level computer stuff, Microsoft give us an indication of when a number is held in the more low level string form, as in some things you could get problems such as type mismatch, as more intermediate level stuff may not have that extra wiring to convert/ coerce that VBA does.

In '0b) , for comparison, I believe VBA may be doing something very similar to what we can do manually: It is putting a number in a cell. Excel handles it, as it does, when we do it, and even of you are pretty sure it is a string, you won’t get the Number held as Text thing
'0c) demonstrates that putting an array generally into a cell, won’t necessarily get us that Number held as Text thing .

Rem 1 This is the Answer, at least as far as I have got. Some objects, when assigned to somewhere or assigned to a variable, other than an Object variable, give a string. It might be this thing, it might not.
https://i.postimg.cc/y83k0dH3/range-...ate-Window.jpg



One thing that supports the idea is that if we repeat the experiment with a multi cell range object we get nothing in the cell and see nothing in the value place in the Immediate Window
https://i.postimg.cc/BvBQ2KRn/Multi-...ate-Window.jpg



That perhaps makes some sense, as it could be impractical to get lots of values there. Maybe never the less they are there , which might lend some tail shirt sense to it being held in an array, of string types, although then that contradicts the fact that other objects have a string value there, but that does not appear to get put n with a warning of the Number held as Text thing
So I am not much further with this Answer

Rem 2
For the case of a formula, Excel takes a similar alignment convention, but there in not that Number held as Text warning thing . Possibly that could be because in most cases a formula would be more complicated and the result would be less ambiguous.
But, the very last code line suggests that Excel is still knowing if it has a number stored as text, and will once again let you know at least from if you do the stuff from Rem 1

[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