Range referencing in a cell, Range object referencing in a Ex cell, Excel is doing that., Evaluating it as such


( For here : http://www.eileenslounge.com/viewtop...202227#p202227 )


' '_- Asside:
Excel and Range referencing.
A long standing curiosity in Excel VBA is, ( was ), Why VBA Evaluate does not work on a closed Workbook reference. http://www.eileenslounge.com/viewtop...=25213#p202227
This can be explained.( I did have I done )
We need to consider an understand Excel VBA Interception and Implicit Intersection
http://www.excelfox.com/forum/showth...0061#post10061
http://www.excelforum.com/tips-and-t...ml#post4575459
I only introduced the concept recently so it is no surprise the original question of "Why VBA Evaluate does not work on a closed Workbook reference", was difficult to answer.
When placed in a spreadsheet processes are under way which speed up the updating of values in the spreadsheet. The references above explain this in some detail to an extent not discussed previously. To function, this process requires evaluation of the cell value. Excel is written to do that when after seeing a = in a cell.
"Behind" every cell is a Range object from which this information is retrieved.
http://www.excelforum.com/the-water-...ml#post4586265
http://www.excelforum.com/developmen...ml#post4551080
http://www.excelforum.com/developmen...ml#post4563838
https://powerspreadsheets.com/excel-vba-range-object/
In the cell this value ( specifically the .Value Property ) is returned, as indeed is returned as the default Property for many uses of the Range object. This last point has possibly some more relevance to Excel Generally.

Range referencing in a cell, Range object referencing in a Ex cell, Excel is doing that., Evaluating it as such, Externally referencing a cell such, Cel, cel as a Range object is External to the cel, sort of Ex cel, or Excel

I believe that in a cell a string range reference , is recognised as such. I believe when a string address such as M40:M41 is written in a cell in a worksheet "MySheet" in a File, "MyWorkbook.xlsx", then this defaults to a string reference of such a form
strRef ='[MyWorkbook.xlsx]MySheet'!A1
Or for a Worksheet named "XLORX", such a form is "seen" by Excel
270 strRef ='[" & ThisWorkbook.Name & "]" & ThisWorkbook.Worksheets("XLORX").Name & "'!M40:M41
There are many ways to demonstrate this.
'_- Fundamental looking at Range object properties and referring to ranges and spreadsheet cells is one way:
http://www.excelfox.com/forum/showth...eadsheet-cells
http://www.excelforum.com/tips-and-t...eet-cells.html

'_- Index Example way
' '_- Index way of looking at it: Code lines 400 - 560
Another way , I would suggest, is to look more closely at how a cell, cel As Range object, is handled by Externally showing it, for example on a spreadsheet, "in Ex cel" or "Excel" as it were.
An Excel spreadsheet is arranged by default to show the value, ( .Value ) as indeed is required for Intersexual Interception Alan Theory. It probably helps in the coding to have this as the objects default given property. It appears to be that given in most cases when the range object is "presented" where one might consider a type mismatch error should occur, ( but by virtue of this default does not produce an error ) .
I would suggest that in fact fundamentally, it is a Range object which Excel is "holding" when such a reference is given ( full or in shortened address may be given, but Excel "sees" the full, guessing the default extra string section ).
I would suggest that therefore Evaluate(strRef) is fundamentally returning a Range object, despite that we see a value ( .Value ) in the spreadsheet. In the demo code we can see how The Evaluate(" ") of a spreadsheet function, Index, which clearly in all spreadsheet use, does in fact give us a range Object in Evaluate.
( I would finally suggest that in fact Range(" ") in VBA is using the same or very similar process to Evaluate(" ") but restricted to, or optimised to just working on the strRef as the Evaluate String, strEval. That is to say it will only accept a string reference as argument. This is in agreement with the general documentation.
http://www.excelforum.com/excel-prog...ket-for-2.html
)

Conclusion
The end result as seen in the code lines in the next Post is that for a single Range object reference , ( noting this can be multi spreadsheet Areas of contiguous cells ) , we are able to get a Range object returned if the receiving variable is declared ( Dimed ) appropriately. This lead to my argument that Excel "holds" fundamentally from a string ( as simple address given!! ) reference in a cell a Range object. ( !!The string reference is increased to include the default Worksheet and Workbook if not given )
Should the variable be otherwise declared, the .Value default appears to apply. As such the .Value Property applied to the Range object will return for a single cell Range object an appropriately dimensioned variable, or for the case of a Range object of more than one constituent cell, a Field ( Array ) of variant types, ( defaulting to the first Area of the Range object if no Areas item number is given . Once concatenated with anything else, Excel takes the value for the reference.
We look at all this now in more detail in the over next post.