Tests and Notes on Range Referrencing

[DocAElstein]

Some notes in support of these forum postings
https://teylyn.com/2017/03/21/dollarsigns/#comment-191
https://www.excelfox.com/forum/showt...ll=1#post18640 https://www.excelfox.com/forum/showt...page18#p314208
https://www.excelfox.com/forum/showt...ll=1#post23991
https://eileenslounge.com/viewtopic....314281#p314281

---

Some explanations of the basic formula of this type:

Range("D1:D3") = "=A1"

LHS multicells range = RHS Single cell formula ???
_1 When we tell / ask / give excel to do something, we often do that in a singular, or similarly simplified way that is more humanly conveniently for us. The original idea of Excel is to reduce our tedious work, in particular tedious repetitive work. As part of this, a common practice is that we ask something to be done across a range of cells, that is the LHS and on the RHS in the most simplified explanation, we give it the information for a single cell, conventionally top left of the range. Depending on exactly what information we give, ( that is _2 ) , Excel will apply what it thinks we want to all the cells in the range.

RHS , = "=A1"
_ 2 What we give
We are giving a single cell reference. Excel has two conventions , or "languages" that it understands when giving and receiving cell references. We are using the so called "A 1 column letter and row number notation" ( The other is the so called "R C row and column number notation”, which we will mention briefly )
Those two conventions differ mainly only in the way they are physically shown. Both conventions have within them two basic concepts/ things that we can use. One is simple: An absolute grid or co ordinate or x y reference to a single cell. ( Some thing looking like
this $A$1 $E$34 etc. in the "A 1 column letter and row number notation" way of showing).
this equivalent R1C1 R34C5 etc. in the "R C Row number and Column number notation" way of showing).
If we give stuff like that, then Excel very easily sees that across the range given on the LHS, the same cells will always be referenced to regardless of which cells are used for the final output that we are looking for.
In other words if we used on the RHS = $E$34 or used on the RHS = R34C534 or even put in a cell manually = $E$34 , then we would be referring to the 34th Row in the 5th column, (and most likely be getting from that the value in the 34th Row in the 5th column, even if technically its returning us a range object with most likely more things in it, but that’s another subject not considered here).
That is simple, no great intelligence, Human of artificial to do that. We are not concerned with that here.
The second way/ concept, that we are concerned with here, is the concept that helps Excel to give us what we want, if we want something slightly less simple. Because in everyday requirements this slightly less simple way is more often what we want, then the way we see or give Excel these cell references most often is in this apparent simpler A1, E34, etc. way, - this apparent simplicity unfortunately disguising that it is actually indicating the more advanced of the two possibilities.
Now, _1 told us that what we give as a single formula in the RHS will be taken as best Excel can to be put in all the cells in the range we give on the LHS. In fact although it’s not obvious, it simply puts the same thing in each cell. It gives not an absolute grid or co ordinate or x y reference as it might be assumed, but rather a fixed vector or relative cell reference.
For this simple example, we are referring to a cell in the same row, and , (if we take the usually Excel convention of a direction left to right being positive, and right to left negative) -3 columns from a cell in column D
Using this coding,

Sub My2EurosA1() '  https://www.excelfox.com/forum/showthread.php/2909-Appendix-Thread-Evaluate-Range-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=18640&viewfull=1#post18640    https://www.excelfox.com/forum/showthread.php/2909-Appendix-Thread-Evaluate-Range-(-Codes-for-other-Threads-HTML-Tables-etc-)/page18#p314208
 Let Range("D1:D3") = "=A1"
End Sub

, we will see this appear in the formula bar for the contents of Cell D2
https://i.postimg.cc/zXBwdStc/Cell-F...e-D1-D3-A1.jpg






As we are passing a formula from within VBA, we could also use the other so called "R C row and column number notation" notation

Sub My2EurosRC() '  https://www.excelfox.com/forum/showthread.php/2909-Appendix-Thread-Evaluate-Range-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=18640&viewfull=1#post18640    https://www.excelfox.com/forum/showthread.php/2909-Appendix-Thread-Evaluate-Range-(-Codes-for-other-Threads-HTML-Tables-etc-)/page18#p314208
 Let Range("D1:D3") = "=R[0]C[-3]"   '  Same Row [no offset row]  -3 Columns offset
End Sub

, and usually Excel recognises that we are actually telling Excel exactly the same thing, (but that we are just using the other "language" for range referencing). The results in the formula bar are identical
https://i.postimg.cc/Vs4BBHRt/Cell-F...D3-R-0-C-3.jpg


(The reason why we see in the spreadsheet the same "A 1 column letter and row number notation" way of showing, is that Excel by default settings is those to show "A 1 column letter and row number notation" . VBA in its low level workings will have past the fixed vector thing, as it will recognise it by either notation, and the Excel spreadsheet setting determine how that vector is presented to us).

So there are no differences in what we see in the spreadsheet, using either of the last two codings

Why showing =B1 and not =R[0]C[-3] in the cell
I told you already, This is simply because the usual default settings of Excel are set to display to us in the "A 1 column letter and row number notation" language way of displaying.

We could change these to the "R C row and column number notation" , for example in Excel 2007, like this
https://i.postimg.cc/RZ9NHdnY/Excel-2007-Options.jpg https://i.postimg.cc/X79rqmXF/Excel-...e-language.jpg
We will then see, for example in our current example in the formula bar if any of the cells are selected after running either macro exactly the same formula using that =R[0]C[-3] in VBA on the RHS.
https://i.postimg.cc/d3KGVSzV/R-C-re...first-cell.jpg https://i.postimg.cc/Y9F1kwDP/R-C-result-in-cell-D3.jpg
=R[0]C[-3] in D 1 and D3.jpg
Note that this is slightly different to previously where we had the default setting of showing us a formula in the "A 1 column letter and row number notation" convention. If we went back to that default setting , and then selected some cells in the D column, and looked in the formula bar, then we would see they are showing a bit differently
https://postimg.cc/y3ZyGnHq
=A3 in D 3, =A1 in D 1.JPG
But let’s clarify that, - regardless of our settings, what any formula we are looking at in the D column, it is telling us is that we have something like a fixed vector or offsets to take us to the cell from which to get the values, specifically
0 row offset , in other words the same row
and
1 column offset to the left. (The general convention in Excel is that left to right is positive and visa versa , so to the left is negative)
The "A 1 column letter and row number notation" makes it look like we put a different formula in each cell, but we don’t. Excel is just sowing the same fixed vector in a different way: by showing us the cell at the one end of the vector when the other end is put in the cell where the formula is. This way of showing things can explain the idea of dragging a formula down, - if a formula is written in the relative ( fixed vector ) way of the "A 1 column letter and row number notation", then dragged down, it will appear that Excel is adjusting the formula to reference the appropriate cells, but its not really. Its really dragging down exactly the same thing which then is shown in a way that will make it appear different in different cells.
If we change our settings to show us formulas in the "R C row and column number notation", then the formulas have not changed, but we are just being shown them in a different way. We are being told specifically the fixed offset(s)



Clearly this is all capable of confusing. But it is worth tackling and understanding this all clearly as it will make further learning on related concepts wither easier or , more likely, only so possible.

(Looking at the top left in the last discussed screen shots, you could be forgiven for thinking Microsoft got confused themselves. In the top left we are shown the actual cell you are considering ( the selected active cell in the D column)
It is shown in the absolute grid or co ordinate or x y reference for the "R C row and column number notation" , whereas we remain in the relative or fixed vector cell reference for the "A 1 column letter and row number notation". There is nothing wrong with that, but one might wonder if for more constancy it might be more helpful if, for example, in the the "A 1 column letter and row number notation", there could have been the $s included in that top left indication of what cell is selected / active, since that is inferring a referral to the specie cell / grid reference, as is in the "R C row and column number notation".
This somewhat inconsistency is mirrored perhaps in when we typically say
"A 1 column letter and row number notation"
"R C row and column number notation"
, whereas perhaps this would be more consistent
"$A $1 column letter and row number notation"
"R C row and column number notation" )








( Ref
https://teylyn.com/2017/03/21/dollarsigns/#comment-191
Windows display formulas https://eileenslounge.com/viewtopic....314221#p314221
Excel option R C or A 1 type display https://i.postimg.cc/RZ9NHdnY/Excel-2007-Options.jpg https://i.postimg.cc/X79rqmXF/Excel-...e-language.jpg
https://www.excelfox.com/forum/showt...ll=1#post23185 )