Tests and Notes on Range Referrencing

**DocAElstein** · 08-24-2019, 01:01 PM

Notes and tests in support of other Threads, mostly on things related to Range Referrencing.

To be added to and edited later

**DocAElstein** · 08-24-2019, 01:56 PM

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
Closed_xlsm.JPG
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
ClosedRefInCellA1.JPG

Code:

'
'                                                                                                                                                                       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
Closed_xlsm (_b).JPG

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

Code:

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()

Code:

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

**DocAElstein** · 10-28-2021, 11:36 AM

post for later

**DocAElstein** · 10-28-2021, 11:28 AM

This is post #43 on Page 5 https://www.excelfox.com/forum/showt...ing/page5#post 15722
https://http://www.excelfox.com/forum/showth...ing/page5#post 15722

In support of this Thread: http://www.excelfox.com/forum/showth...6398#post16398

Hello Amelynn
Welcome to ExcelFox , the thinking man’s Excel forum

Originally Posted by Amelynn

Who can help me, ....

I think, therefore I am , and able to help you.

Originally Posted by Amelynn

...... I don't understand how the line ".List
= Application.Index(rng, Evaluate("ROW(1:" & rng.Rows.Count & ")"), Array(1, 3))
" works and I can't stay So....

To Explain

Array(1, 3)
It is like –
1 3
( Sometime we write in excel spreadsheet convention {1, 3} , but this usually means the same thing )

Evaluate("ROW(1:" & rng.Rows.Count & ")")

Originally Posted by Amelynn

..... evaluate (…….. used to occupy excel formulas in vba) with row,

Correct! Good! - Because you understand this, it makes the explanation for me much easier.
We are really only interested in understanding what is , =ROW(1:" & rng.Rows.Count & ")" , in excel spreadsheet formula

For your range, rng = B4:D8 , rng.Rows.Count = 5
=ROW(1: " & rng.Rows.Count & " )
=ROW(1: " & 5 & " )
=ROW(1:5)

( Green is Excel Spreadsheet, Blue is VBA in string “ “ in Evaluate( “ “ ) )

The excel spreadsheet Row( ) function is usually like for
Row( A1) = 1
but can also return a “vertical” array of values like
Row( A1:A2) = 1
Row( A1:A2) = 2
In VBA Evaluate we find that we are decoupled from spreadsheet absolute values, and so in VBA Evaluate
Row(A1:A2) = Row( 1:2) = 1
Row(A1:A2) = Row( 1:2) = 2

ROW(1:5)
It is like
1
2
3
4
5
( Sometimes we may write
{1
2
3
4
5}
or sometimes we may write in excel spreadsheet convention , ={1;2;3;4;5} , but usually this means the same thing)
Note: For “vertical” array some excel use ; but some Excel use \ – So sometime you may need ={1\2\3\4\5}

Sometimes if we are lucky, Excel will try to do array calculations and return you an array.
Like

Code:

	{	1	2		X	{	6	8		  =	{	1, 6	2, 8	
		3	4	}			7	9	}			3, 7	4, 9	}

But if you ask it to do = Index __ ROW(1:5)__ ,__ Array{1 , 3} it tries to look at columns and rows not specified.

Code:

=  {	1	?			   {	1	3	
	2	?				?	?	
	3	?		X		?	?	
	4	?				?	?	
	5	?	}			?	?	}

???? So we have a problem ?,
But we can be luckyagain, because then Excel will guess to see this instead
( This is because of Excel VBA Interception and Implicit Intersection )

Code:

=  {	1	1			   {	1	3	
	2	2				1	3	
	3	3		X		1	3	
	4	4				1	3	
	5	5	}			1	3	}

Index will try to give us

Code:

_______    Index     (		1, 1	1, 3	
				2, 1	2, 3	
				3, 1	3, 3	
				4, 1	4, 3	
				5, 1	5, 3	  )

So if your range is

Code:

			
	 rng = B4:D8 , rng.Rows.Count = 5		
			
	_B4	_C4	_D4
	_B5	_C5	_D5
	_B6	_C6	_D6
	_B7	_C7	_D7
	_B8	_C8	_D8

then Application.Index(rng, Evaluate("=ROW(1:" & rng.Rows.Count & ")"), Array(1, 3))
-__ =Application.Index(rng, Evaluate("=ROW(1:5)"), Array(1, 3))
-__ =Application.Index(rng, Evaluate("={1;2;3;4;5}"), Array(1, 3))
-__ =Application.Index(rng, Evaluate("={1;2;3;4;5}"), Evaluate("={1, 3}"))

Code:

	  =     _B4	_D4
		_B5	_D5
		_B6	_D6
		_B7	_D7
		_B8	_D8

Originally Posted by Amelynn

........use an array to determine the rows as well......

Sure, this is no problem:
One way, for example, for just 1st 3rd and 5th row
Change
Evaluate("={1;2;3;4;5}")
to
Evaluate("={1;3;5}")

Here a demo macro for you
Put some arbitrary values in your range "B4:D8" , then run this macro:

Code:

Sub Test()
Dim Rng As Range
 Set Rng = Worksheets("Sheet1").Range("B4:D8")
Dim RwsCnt As Long
 Let RwsCnt = Rng.Rows.Count '  is = 5

Dim arr_List() As Variant
 Let arr_List() = Application.Index(Rng, Evaluate("=ROW(1:" & Rng.Rows.Count & ")"), Array(1, 3))
 Let arr_List() = Application.Index(Rng, Evaluate("=ROW(1:" & RwsCnt & ")"), Array(1, 3))
 Let arr_List() = Application.Index(Rng, Evaluate("=ROW(1:5)"), Array(1, 3))
 Let arr_List() = Application.Index(Rng, Evaluate("={1;2;3;4;5}"), Array(1, 3))
 Let arr_List() = Application.Index(Rng, Evaluate("={1;2;3;4;5}"), Evaluate("={1,3}"))

 Let Worksheets("Sheet1").Range("A40").Resize(UBound(arr_List(), 1), UBound(arr_List(), 2)).Value = arr_List()

' To only select 1st 3rd and 5th row
 Let arr_List() = Application.Index(Rng, Evaluate("={1;3;5}"), Evaluate("={1,3}"))
 Let Worksheets("Sheet1").Range("A47").Resize(UBound(arr_List(), 1), UBound(arr_List(), 2)).Value = arr_List()

End Sub

Alan

**DocAElstein** · 10-28-2021, 11:28 AM

Item way of thinking as alternative to conventional row column looping

Current conventional way of thinking
This Blog post assumes that you have some basic Excel VBA programming experience, and that you are familiar with this general idea used typically when filling or getting data from cells
__For Rw = 1 To maximumRows
____For Clm = 1 To maximumColumns
_____( Rw, Clm )
____Next Clm
__ Next Rw
This basic process you should make sure you fully understand before reading further.
This Blog post suggest a slightly different way about thinking about that process

Introduction
Excel Spreadsheets can be turned into pretty pictures, computer graphics and games and “dashboards”.
But they are usually not the best at doing those things.
They are best for doing things with 2 dimensional “tables” of data.
They bridge the gap between
_ dead stores of data or long fast streams of string data,
and
_ relatively slow and static living humans interacting with them

I expect we are not a long way from interacting in a 3 dimensional way, but it will settle down, I think, to be useful in a limited way. More than 3 dimensions are difficult for humans to react easily with.
A 2 dimensional desktop is particularly convenient for us.
Smartphones are a trend that I think might go back a bit to being just glorified telephones, walkmans, live cameras and simple computer games.

We are basically talking in this Thread about organising a string of data travelling at the speed of light into a static line or row of the data, then carrying the rest of the data back, feeding in another row or line or “going down”, then doing the same again.

I think for Excel formulas and VBA we should not take the core of the thinking much further than this.
For furthering the study and manipulation of static table data something else , another concept, such as Power Query / Get and Transform might be more appropriate.

Looping along columns for every row The conventional way
__For Rw = 1 To maximumRows
____For Clm = 1 To maximumColumns
_____( Rw, Clm )
____Next Clm
__ Next Rw
This is what we have got used to, from reading books, to the workings of old printers, to how a screen is filed with data. It is an efficient and logical consequence that a spreadsheet, or a rectangular range in a spreadsheet, is filled and otherwise updated in the same way.
Usually we look at a cell when doing formula calculations, but if we increase the exposed area during a screen update, we can get an efficient, almost simultaneous, calculation across an entire range. This latter is the basis of the workings of the so called array formulas, ( “CSE” stuff ). We will come on to that in the next post

Looping the Items
Many things in VBA, and computers generally, are actually ordered somewhere by something similar to a unique item number , 1 2 3 4 5 6 … etc. ( Note: in some computer conventions the first may start at 0 so we have 0 1 2 3 4 5… etc. )
Conventionally, the Range Item numbers are also so organised, and further follow the generally accepted convention of
Across
_____down
back
Across
_____down
back
………etc.

( Conventionally in computer stream strings we may refer to those
backs as carriage returns, vbCr ,
and
the downs as line feeds, vbLf.
These may be referred to as “invisible characters”, as we do not typically see them, but they must be there in the electronic string stream to indicate the start of a new line or new row )

For example for a 2 row x 3 column range, this is where the item numbers are/ what cells the item number refers to
1 2 3
4 5 6
Furthermore, for many things that do not have such an item number they may have a consistent order such that we can assume or assign some simple count mechanism to give them an index that we can use in a similar way to the Item number

The crux of my suggestion in this Thread is to think of the basic process shown at the start of this Blog, in a slightly different way.
A couple of reasons I can think of for doing this
_ It may be helpful to think back to the more fundamental single string idea of what computers are, as the table type arrangement is only an interface for us, and is less representative of what is actually going on in a computer
_ In some situations the alternative looping may have some advantages. For example we would not be limited to a rectangular range.

Some simple required maths
What we want to do is this sort of thing as an alternative to the basic process
__For Itm = 1 To maximumItems
___( Row, Column)
__Next Itm
Before looking at the actual solution, lets simply put in words a simple solution idea, and consider again a 2 row, 3 column range
1 2 3
4 5 6
In this case, our maximumRows will be 2 and the maximumColumns will be 3
We have those sequentially numbers, and we want to convert then to row and column indices that would give us that spreadsheet range.

Row Number from Item Number
If we were to divide the actual item number by the maximumColumns , 3 , and take the integer of the result we would almost be there. But that would actually give us
0 0 1
1 1 2
If we were to first subtract 1 from the Item number, then do the same again, divide that modified number by the maximumColumns , 3, and take the integer of the result we would have,
Integer ( ( ItmNumber -1 ) / maximumColumns ) =
0 0 0
1 1 1
So we now see that we only have to add 1 to the final results and we have the row number what we want.
RowNumber = [ Integer ( ( ItmNumber -1 ) / maximumColumns ) ] + 1
1 1 1
2 2 2

Column Number from Item Number
This is slightly more tricky. But not much. I do this second as we need some of the ideas from the previous maths.
An observation:
The second row needs to be reduce by 1 x maximumColumns , and
if we had a third row the item number would need to be reduced by 2 x maximumColumns … and so on.
It also fits the same logic to apply a reduction of 0 x maximumColumns to the first row.
So we are looking for a multiplication factor based on the Item number which gives us that sort of number sequence like 0 0 0 ; 1 1 1 ; 2 2 2 etc. sequence
We can see from one of the stages in the maths from the last section that we did actually already have it at this stage:
Integer ( ( ItmNumber -1 ) / maximumColumns ) =
0 0 0
1 1 1
and that would have further gone on for more rows to
0 0 0 ; 1 1 1 ; 2 2 2 ; 3 3 3 … etc.

So we can see one formula possibility:
ItemNumber - [ Integer ( ( ItmNumber -1 ) / maximumColumns ) X maximumColumns ]
This would result in our required column Indicies of
1 2 3
1 2 3

_.______

Simple VBA macro example

Code:

Sub ItmNumbersLoopToRng()
Dim Ws1 As Worksheet: Set Ws1 = ThisWorkbook.Worksheets("Sheet1")
Dim ItmIndx As Long
Dim maxClms As Long, maxRws As Long: Let maxClms = 3: Let maxRws = 2
    For ItmIndx = 1 To 5
    Dim Rw As Long, Clm As Long
     Let Rw = Int((ItmIndx - 1) / maxClms) + 1
     Let Clm = ItmIndx - (Int((ItmIndx - 1) / maxClms) * maxClms)
     Let Ws1.Cells.Item(Rw, Clm).Value = " (" & Rw & ", " & Clm & ")"
    Next ItmIndx
End Sub

Sub ItmNumbersLoopToRng2()
Dim Ws1 As Worksheet: Set Ws1 = ThisWorkbook.Worksheets("Sheet1")
Dim ItmIndx As Long
Dim maxClms As Long, maxRws As Long: Let maxClms = 3: Let maxRws = 2
    For ItmIndx = 1 To 5
'    Dim Rw As Long, Clm As Long
'     Let Rw = Int((ItmIndx - 1) / maxClms) + 1
'     Let Clm = ItmIndx - (Int((ItmIndx - 1) / maxClms) * maxClms)
     Let Ws1.Cells.Item(Int((ItmIndx - 1) / maxClms) + 1, ItmIndx - (Int((ItmIndx - 1) / maxClms) * maxClms)).Value = " (" & Int((ItmIndx - 1) / maxClms) + 1 & ", " & ItmIndx - (Int((ItmIndx - 1) / maxClms) * maxClms) & ")"
    Next ItmIndx
End Sub

__Result:_____
_____ Workbook: OekyDoekyAmelynn.xls ( Using Excel 2007 32 bit )

_Row\^Col	A	B	C
1	(1, 1)	(1, 2)	(1, 3)
2	(2, 1)	(2, 2)
3

Worksheet: Sheet1
_.____

There is one interesting observation already that may help us understand some of the secret workings of Excel:
Note that we have not needed to use the parameter of maximumRows
Now, Consider a range object of 3 columns x 2 rows anywhere in a spreadsheet
x x x
x x x
Reasonably we would say that has 6 items, and conventionally we have, and we could confirm by experiment, that we have item 6 as shown here, X:
x x x
x x X
What is somewhat surprising perhaps, is that we can go on to reference any further item without causing any errors, and we remain in the maximum columns restriction but seem to have no limit, ( within reason ), in the rows.
For example, referring to item 12, would get us here X:
x x x
x x x
x x x
x x X

This could be by design, or might just be an accident due to some internal calculations having a similar form to those I have suggested and discussed, and they do not have any consideration of maximum row

I intend adding some further post on this thread later to discuss the ideas and develop them further.

**DocAElstein** · 10-28-2021, 11:36 AM

post for later

**DocAElstein** · 10-28-2021, 11:38 AM

**DocAElstein** · 10-28-2021, 11:41 AM

Spreadsheet cell concept and Range Objects.

In my opinion, some unfortunate choice of words can set a new user of Excel slightly of course from the very start.
Further, form my experience in learning effective use of Excel , I would recommend, that any new user of Excel should try at least to get some basics of Excel’s programming ability, Visual Basic for Appplications, ( VBA ) , or at least understand some of the basic concept at an early stage. I am not alone in people who have been very annoyed to learn at a later date how much simpler and more effective use of VBA programming can be to many spreadsheet ways of doing things.
This language is, in simple terms , the programming language used to make most things happen when you manually do things in the spreadsheet. So you can replicate and modify existing Functions or write new ones from scratch. You can further arrange for them to happen automatically as a result of other things happening, or even , for example, at a certain time of day.
But the possibilities are almost endless, ( and consequently it seems to have been given up years ago trying to make any sort of all encompassing programming manuals! )
So I cannot possibly explain all Excel programming here, but I intend to touch on , from a Layman’s point of view, some of the parallel concepts in VBA to Spreadsheet things, in particular those I have found to confuse at a later stage of learning. I am at a very early learning stage myself and am not a programming professional. Much of my explanations are from a Layman’s observation point of view

Starting right at the beginning with the default Spreadsheet “grid” or “cell” structure.
We start typing things such as text or simple formulas in a cell. Right here we can go off course. The use of the word cell or cells is not a good idea in my opinion. Better would be the box you “write a string value or number in” ( or the “box you colour or format” in some way ) or if you are familiar with basic map or Atlas reading, you can regard it as the row and column numbers being like the y and x axis.
The main reason I say this, is that Cells is used specifically to refer to something else which specifically is not a “thing” or Object. It is much better, even before you start understanding any VBA, to get in the habit of using “Range Object” for the “box” “thing”. This should become clear in the next few sections.

Cells Property ( Method ) EDIT. This section is probably inorrect - see a few post later where I question the fundamental ideas of the Cells Property for referencing Range Objects
The word Cell, or more specifically a form like Cells(1, 2) is a VBA Property, ( what a layman would more easily initially understand as a Method or “way to do something”. ( Indeed it was previously referred, and some experts still do refer, to as a Method) . This can be thought of the programming equivalent of you taking ( that is to say , observing in your mind ) , for the given example, the entire first Spreadsheet first row ( from the top) , and the entire spreadsheet second column, ( from the left ). Where these intercept is taken as the “box” of interest which you are “referring to through the Cells Property”. In other words we have a “way” of getting it. Or at least a way to make reference to it. So in the first instance we would think of it as a Method or way of doing something. Such things, ( the “boxes” and all that can be done to them or “got” from them) are often held in a large list, at least a virtual list. We can “get at”, that is to say we have access to this list. This explains why often such things may be described as a Property, that is to say, something “belonging to it”, even if it is a “way of doing something”, - we cannot necessarily use all ways to do things on differentl things.

Object Orientated Programming
Some basics of Object Orientated Programming, OOP, is required at this point. Very , very simplified we have in most case a type of hierarchical system starting from the “biggest thing”, ( Object as we call them ) , from the left. Then going along to the right via a dot, . , we “go down the chain”, to under Objects , or use a Method or Property of an Object, applied to the object to return some Property.

Getting at ( referring to ) a cell ( Range Object )
Initially we are considering a simple case , or a single cell ( “box” ), but noting that in most cases we can do similar things to a collection of cells (“boxes”). The discussions are very similar for more than one “box”

Range Object
In my Opinion, it really is worth understanding what a Range Object is, even if you are not initially interested in VBA programming.
In simple terms, all that can be done to or changed, or “seen” in a “box”, ( and much more besides, such as various information ) , are stored in a massive “thing” called a Range Object.
Using VBA you can easily “get at” all this information. In a spreadsheet, you only see some of this information. Taking for example a simple formula, =A2 , in the first “box”, ( the “box” at A1 “coordinate” ) that is to say the Range Object to be found at, that is to say by “using”, Cells(1, 1).
In the spreadsheet we can see for example the formula in “column letter and row number notation” displayed in the formula bar, ( small fx window at the top ), and the evaluated value in the first “box”. A simple VBA code can be written such as this below, to show much more detail. ( Further code could then be used to retrieve all that information )

Code:

Option Explicit
' To Run Code, Click within code, Hit play Button or F5 (or F8 to step through)
Sub Cell1EqualsCell2()
Let Worksheets("RangeObjects").Cells(1, 1).Value = "=A2"
Dim RngObj As Range ' ' EP Dim: For the Variable type declared, there should be some Blue Print or master Instruction list. If not The code will error at the start as it need to examine and therefore prepare for usage of the variable in the code run. The variable itself will go to some “Pigeon hole” that has a copy of the instructions or in some way has information on how to use the original instructions such that it can distinguish between different variables of the same type. When the code meets the variable it will look in the Pigeon Hole Pointer for instructions as to what to do in various situations. Knowing of the type allows in addition to get easily at the Methods and Properties through the applying of a period ( .Dot) ( intellisense ). Note this is a look up type list and may not be a guarantee that every offered thing is available – most will be typically.
Set RngObj = Worksheets("RangeObjects").Cells(1, 1) ' ' EP Set: Setting to a Class will involve the use of an extra New at this code line. I will then have an Object referred to as an instance of a Class. At this point I include information on my Pointer Pigeon hole for a distinct distinguishable usage of an Object of the Class. For the case of something such as a Workbook this instancing has already been done, and in addition some values are filled in specific memory locations which are also held as part of the information in the Pigeon Hole Pointer. Wed will have a different Pointer for each instance. In most excel versions we already have a few instances of Worksheets. Such instances Objects can be further used., - For this a Dim to the class will be necessary, but the New must be omitted at Set. I can assign as many variables that I wish to the same existing instance
End Sub ' Click in left margin to get brown stop icon. Run Code. It will then stop here
' Highlight either ' ' RngObj

'.Hit Shift+F9. Select Add.
' Hit +'s in Watch Window to reveal contents of Watch Window

To Run the Code:
To Install a macro:
1. Copy the code given to the clipboard. ( Highlight code and Hit Ctrl C )
2. Open a NEW workbook ( or the one you want the code in )
(2a. Either rename a Tab now to RangeObjects, or later, **change the code in two places to the name of a spare existing Worksheet tab Name ( Typically this will be “Sheet1” for English Excel, corresponding to the first tab named Sheet1 ) )
3. Press the keys ALT + F11 to open the Visual Basic Editor
At this point you should see the cursor flashing in a big empty window
( If not then select from the VB Editor ribbon ---- Insert ------ module )
4. Where the cursor is flashing, paste the code
( 5a. **Do any editing of the code you wish to at this stage. )

To run in “Development mode”
5b. Adjust window Sizes if necessary so as to see both the Visual Basic Editor and the Spreadsheet
5c. Click within code
5d(i). Hit play Button or F5 (or F8 to step through)
5d(ii). If you wish to look into filled variables, stop the code at the End Sub and follow ‘ instructions to reveal variables contents )

To Run Code in final finished form
6. Press the keys ALT + F11 to return to Excel ( or press ALT and q simultaneously to close the VB Editor )
7. When in Excel, press ALT + F8 to display the Run Macro Dialog.
8. You can choose to run the code ( alternatively Double Click the macro's name to Run it.)
Or
9. You can choose to work on it. – This option is good to find the macro if you have many and are not sure where they all are )

This is what you should see if you follow the instructions to bring up the Watch Window to reveal the contents of the variable used for the Range Object of the first Worksheet cell ( “box” )
Cells1EqualsCells2.JPG http://imgur.com/wXjd0xq
Attachment 495283 Cells1EqualsCells2.jpg

**DocAElstein** · 10-28-2021, 11:52 AM

Copy from http://www.excelforum.com/showthread...11#post4551509 https://www.excelforum.com/developme...ml#post4551509

_1 ) Referring to Range Objects in VBA

I guess to some extent, I may be questioning / commenting on two basic statements or ideas
_(i) ... “...the most basic way of referring to Excel’s VBA Range object: the Range ( or Cells ) Property..”...
and
_(ii) whether there may be an additional Range Object ( The Application Range Object ) , that is to say , additional to the
..”... Range Object is “ .. (always ) .. “ contained within the Worksheet Object...”...
The latter, _(ii) , is possibly dubious, and possibly I could alternatively be suggesting that we have a sort of Application Range(“ “) Method ( or Application Range(“ “) Property, which is an alternative _c) to _..
_a) Cells Property
_b) Range Property
_.. , and which can be used to reference a Range

_1a ) _1b) Referring to Range Object contained within a Worksheet Object
_1c ) Referring to a ( Application ? ) Range Object

The documentation is not perfectly clear on this, at least that is the opinion of much more VBA experienced people than me. So the following is partly my interpretation.( Especially _1c) ).
I would suggest we can get at a Range Object in three basic ways. I deliberately list things a bit in a jumbled order, as things in the “imaginary” world are often in a seemingly incorrect order.
_1c) Directly through some specific reference.
( http://excelmatters.com/referring-to...comment-197138 )
_1a) indirectly using the Cells Property ( Method ) already discussed. http://www.excelforum.com/showthread...84#post4551080 http://www.excelforum.com/showthread...84#post4551484
_1b) indirectly using a similar Property to that in _1a), which unfortunately someone in their wisdom decided to call the Range Property. This Range Property is a “way” or “Method like” thing and should not be confused with the final big “thing” of the Range Object.

_1a) 1b) The general typical Ideas given for referring to Range Objects in VBA.

Let’s say, I have defined Rng as a Range Object. Then this type of code ( used within the Excel Application in VBA code ): _..
_1a)(i) Rng=Worksheets(1).Cells(1, 1)
or
_1a)(i) Rng=Worksheets.Item(1).Cells(1, 1)
_.. is using the Cells Property of a Worksheet to return the Range Object of the first cell in the first Worksheet ( Index / Item Number 1 counting Worksheet Tabs from the left ).
The same again using a string name ( assuming the first Worksheets has the default English Excel name of “Sheet1” )
_1a)(i) Rng=Worksheets(“Sheet1”).Cells(1, 1)
or
_1a)(i) Rng=Worksheets.Item(“Sheet1”).Cells(1, 1)

Similarly the Range Property of the worksheet can be used
_1a)(ii) Rng=Worksheets(1).Range(“A1”)
or
_1a)(ii) Rng=Worksheets(“Sheet1”).Range(“A1”)
etc.. etc....

My experiments suggest the following is also acceptable for the same, and I am not sure if VBA may actually change _1a(ii) “internally” as it were, to this type of form
_1a)(iii) Rng=Worksheets(1).Range("='[MyFile.xlsm]Sheet1'!A1")
This last :1a)(iii) would still be using the Range Property of the worksheet. I think before and up to this point we are clearly using the Cells or Range Properties of a Worksheet. This last suggestion, _1a)(iii) , I am, in fact not too sure about and in end effect a Range Object must be returned, so this could be how VBA finally sees the final returned Range Object:-
_1b)(i) Rng=Application.Range("='[MyFile.xlsm]Sheet1'!A1")
This last one, I think, is a Range Object, but I am not too sure? We could have used this initially and hence , I think, possibly would have a more basic way of cell referencing with _1b)(i), that is to say explicitly referring to the Application Range. So finally we have _1c) Rng=Application.Range("='[MyFile.xlsm]Sheet1'!A1") ( A suggestion / proposal from me )

_._____________________________
A few further related concepts

Unqualified Range reference,
One sees most typically a shortened version, an “unqualified” Range reference, of this type of form
Rng=Range(“A1”)
This leads to uncertainties.
_A) Used within a Normal code module. The last code line above will default ( that is to say what VBA “sees” ) to this sort of form
_1b)(ii) Rng=Application.Range("='[" & ActiveWorkbook.Name & "]" & ActiveSheet.Name & "'!A1")
which is again, I think, a Range reference, possibly to be regarded as referencing explicitly or directly by a Range Object? So I think we are referring Explicitly through a Range Object.
We are defaulting here, in the case of the code line in a Normal Code module, to the Application Range and not to the Active Worksheets as often said.
It is possible that my idea of an Explicit reference through an Application Range Object could be called an Application Range Property, So I am touching on dodgy uncertain ground here.
Application Range Object *#*#*
http://www.mrexcel.com/forum/excel-q...ml#post4357003
http://excelmatters.com/referring-to...comment-197138
_B) Use of an “unqualified” Range reference for the instance of a Class Worksheets, that is to say put in, for example, the Worksheet code module of Worksheets(2), will default ( that is to say what VBA “sees” ) to this sort of form
_1a)(iii) Rng= Worksheets(2).Range("='[" & ActiveWorkbook.Name & "]" & Worksheets(2).Name & "'!A1")
Which I think is referring through a Range Property.

Range and Cells() ( and Item ) Properties of Ranges ( Range Range Property , Range Item Property ) and Range Cells Property )
We have been talking so far about the Range and Cells() Properties of Worksheets as well as my suggestion of an Application Range ( which we are leaving it uncertain currently as to whether it is an Application Range Object or Application Range Property *#*#* )
In any case we have finally in all cases a Range Object. Regardless of where this is, we have a further (a)(ii)) Range(“ “) and (a)(i)) Cells() Property!!!!!, of a form such as
Range(“A2”) and Cells(2, 1) , or pseudo like
Range(strAddress) and Cells(row_coordinate, column_coordinate)
Specifically here for the Range( ) case, we are limited to an Address string and not a full reference.
The Range and Cells() Properties of a worksheet we have seen extend from ( are referenced to ) the Top left of a Worksheet. Similarly, the Range and Cells() Properties of a Range Object extend from (are referenced to ) the Top left of the range Object. _...
_... For example:
_1c) Rng=Application.Range("=[Workbook]Worksheets(1)!A1").Range(strAddress)
Is applying the a Range Property to the Range Object from _1b)(i)
You can keep going infinitely with rng.Range.Range.Range.Range. Each Range Property returning a new modified Range Object to which the next Range property is applied.
For the case of a Range Object Range Property, negative numbers and negative column letters are not from the syntax allowed.
However for the case of the Range Object Cells() Property, negative co ordinates are permitted.. Note here however two things here:
Firstly, the Range Object will need to have its top left far enough away from the Worksheet Top left origin such that the negative co ordinate applied Cells() Property does not go “further back to the left” or “further back up” than the Spreadsheet boundaries; and secondly, zeros in such as this Property reference, Cells( 0, 0 ) , is also from the syntax valid. So For example, _..
Range("A2:C5").Cells(0, 1)
_.. will return finally the Range Object of the ( cell ) “box” at Address A1, ... ( We apply the Cells() Property to the top left cell of the range Object which is A2. O gives 1 row back up from row 2, and 1 gives the same column as column A )

( ......The Item Property: Many things in VBA can be referenced through a consecutive Item Number, or Index, like 1, 2, 3 ... etc. The order is determined in various ways for the different things. The typical syntax is then Item(Whole_Number) and is usually read only.
For the case of a Range Object, this number relates to each ( cell ) “box” in the Range Object. This ordering follows the typical Excel Spreadsheet ( cells ) “boxes” order convention of numbered from left to right and from top to bottom. We may use this to reference (cells) “boxes” outside the Range Object that lie within the Worksheet boundaries. These "Overshoot" Items follow further the row then column convention , effectively "duplicating down" the Worksheet, that is to say repeating the original Range Object vertically down the Worksheet )..
It is a further confusing aspect of the Range Object that for the Range Item Property we may also use two arguments in the ( ) bracket representing the row, and column. If this option is chosen then we can use Numbers or , for the columns , also the letter (wrapped in quotations). In the two argument case we extend beyond the original Range Object from the top left in the Original Range Object in the usual row, column way, ( we do not proceed in a duplicate type way in Ranges Objects stacked down the Worksheet ) .
!!!!!To complete the confusion_... there may be no Cells Property see next post......).
_..................................

What is all this Post about. ?
Two things really
_( One) 1a) 1b) ) A review of the more common ideas of Range Objects and Range and Cells() Properties, with a mind to going on to make a suggestion , or at least discussion on a , a possible Explicit-er Application Range Object ( or Application Range Property or even Application Range Method )_.. that is to say _1c) _..
_.. _( Two) 1c) ) The end result of all the above, is that I have got recently into always using variations of this type of way to reference a Range Object.

Code:

Sub strRefAppRngObj() ' http://www.excelforum.com/showthread.php?t=1154829&page=11&p=4551509&highlight=#post4551509
' 1c) Application Range Object
Dim strRef As String ' Prepares "Pointer" to a "Blue Print" (or Form, Questionnaire not yet filled in, a template etc.)"Pigeon Hole" in Memory, sufficient in construction to house a piece of Paper with code text giving the relevant information for the particular Variable Type. VBA is sent to it when it passes it. In a Routine it may be given a particular “Value”, or (“Values” for Objects). There instructions say then how to do that and handle(store) that(those). At Dim the created Paper is like a Blue Print that has some empty spaces not yet filled in. String is a a bit tricky. The Blue Print code line Paper in the Pigeon Hole will allow to note the string Length and an Initial start memory Location. This Location well have to change frequently as strings of different length are assigned. Instructiions will tell how to do this. Theoretically a specilal value vbNullString is set to aid in quick checks.. But...http://www.mrexcel.com/forum/excel-q...html#post44 '
Let strRef = "='[" & ThisWorkbook.Name & "]" & Worksheets.Item(1).Name & "'!A1:C3"
Dim Rng As Range ' EP Dim: For the Variable type declared, there should be some Blue Print or master Instruction list. If not The code will error at the start as it need to examine and therefore prepare for usage of the variable in the code run. The variable itself will go to some “Pigeon hole” that has a copy of the instructions or in some way has information on how to use the original instructions such that it can distinguish between different variables of the same type. When the code meets the variable it will look in the Pigeon Hole Pointer for instructions as to what to do in various situations. Knowing of the type allows in addition to get easily at the Methods and Properties through the applying of a period ( .Dot) ( intellisense ). Note this is a look up type list and may not be a guarantee that every offered thing is available – most will be typically.
Set Rng = Application.Range(strRef) ' EP Set: Setting to a Class will involve the use of an extra New at this code line. I will then have an Object referred to as an instance of a Class. At this point I include information on my Pointer Pigeon hole for a distinct distinguishable usage of an Object of the Class. For the case of something such as a Workbook this instancing has already been done, and in addition some values are filled in specific memory locations which are also held as part of the information in the Pigeon Hole Pointer. Wed will have a different Pointer for each instance. In most excel versions we already have a few instances of Worksheets. Such instances Objects can be further used., - For this a Dim to the class will be necessary, but the New must be omitted at Set. I can assign as many variables that I wish to the same existing instance
End Sub

Although the long string reference, strRef, looks daunting, I find this a robust way to reference a Range Object:
It gives me flexibility in the string build, and I have no confusion arising from where the code line might be... ( ... I reference the same Range Object, without generally any errors arising in the attempt, for the code line in any code Module )

Alan

' Rem Ref http://powerspreadsheets.com/excel-vba-range-object/

**DocAElstein** · 10-28-2021, 11:56 AM

Copy from http://www.excelforum.com/showthread...11#post4555023 https://www.excelforum.com/developme...ml#post4555023

Function and demo Codes to Demonstrate Range Item Property

Introduction, what is this post about? :
This Post explains some preliminary ideas that covers the basics behind putting displayed text into a cell through using VBA to fill in one of the Formula Properties memories of the Range Object of that cell, that is to say passing a formula string ( .Formula ( .Formula is the column letter and row number convention formula type ) ) to the Range Object to which the displayed cell obtains the value ( .Value ) it displays. ( So it ends up looking like this in the Spreadsheet http://imgur.com/k9IYFUj . For that example we will learn that in VBA we need to do something like this "=" & """""""" & "Hello from C3" & """""""" to put those values in )
The code of which the first half is described in this post, ( and shown in the next post http://www.excelforum.com/showthread...24#post4555024 is a demo code routine ( not a Function ) to illustrate the basic ideas behind the Function in the over over next posts. But it makes a good parallel alternative explanation of the
Range Item Property
The Range Item Property is used to return a single cell Range Object starting from the top left cell of a Range Object of one or more contiguous ( all there ) cells (More correctly said a Range Object of one or more Range Objects from a contiguous spreadsheet rectangular area ). It applies specifically to one area. ( A Range Object can be that of only one rectangular area of contiguous spreadsheet rectangular, but strictly speaking the first area , which mostly we talk about, is just one of many possible. Strictly speaking a Range Object is a collection of one or more Range Objects from a contiguous spreadsheet rectangular areas. Typically we use just one and when we use a code part like Rng. VBA assumes we meant to write Rng.Areas.Item(1). )
Range Item Property and Demo code description
The Range Item Property follows for two argument case the usual spreadsheet ( row_coordinate , column_coordinate ) convention starting from top left and going ( for positive coordinates ) “down” the “rows” and “across to the right” of columns.
For the single argument case, ( integer item number or integer index ) , the Range Item Property follows the usual all columns “across to the right” , then next row “down” convention.
Rem 1) The first few code lines use the Cells Property to return a Range Object of all the cells ( Range Objects ) in a Worksheet.
( Lines 50 – 70 shows how it comes about that we typically see a code line such as code line 70. I would suggest that is one of the main uses of the Cells Property http://www.excelforum.com/showthread...11#post4552374 http://www.excelforum.com/showthread...11#post4553399 )
Quotes in Excel VBA
As an aside we need to tackle this tricky theme, as a quick review will help make the following explanations and code lines a little less daunting.
http://www.excelfox.com/forum/showth...page2#post8836
http://www.mrexcel.com/forum/excel-q...ml#post4283381
http://www.excelfox.com/forum/showth...=9517#post9517
http://www.eileenslounge.com/viewtop...196259#p196259
Our final goal in this sub section is to be able to confidentially put a formula string in a cell using VBA. That is to say ...putting displayed text into a cell through using VBA to fill in one of the Formula Properties memories of the Range Object of that cell, that is to say passing a formula string to the Range Object to which the displayed cell obtains the value ( .Value ) it displays...( and doing that such that literal quotes appear within the final “seen” cell displayed text )

Display text in cell from text in Range Object .Value
Generally in VBA a start and a stop quote “ “ signal that what between is a String. So a code line like_..
Range(“A1”).Value = “Some Text”
_.. or code lines like
Dim strText As String
__ strText = "Some Text"
__ Range("A1").Value = strText
_..will recognise __ Some Text __ as a literal string and give the Range Object of the first cell that value, __ Some Text __ The .Value Property will become this string value by virtue of the ( write ) lines 100 and 110. ( The .Value Property is the Range Object value that is displayed by default in the spreadsheet cell ). This can also be seen clearly by stepping past ( F8 Debug mode code progression ) the code line 115 and hitting Ctrl+G to reveal in the Immediate ( Debug ) window, what the code line Debug.Print has “printed out” to that Window. ( The Immediate ( Debug ) Window is a help window available to us ) . The code Ws.Range("A1").Value has effectively read what is held in the Range Object of cell A1 for its .Value
( In line 140, we “write” via .Formula our string formula in column letter and row number notation type to the Range Object Property which houses those type of strings. That sounds reasonable. But using .Value will also work. I expect there is a parallel or the same thing going on there to when you manually write in a cell = ... after this Excel is expecting a formula and assigns appropriately the various values Properties, and then finally displays in the cell the .Value, and in the formula bar the .Formula )

Adding quotes to strings
To have a quote displayed requires generally within the string a double quote. If there is a strict theory to that then I expect very few people are privy to that. Two possible explanations I can think off are
_ the complier or similar simply looks inside text after a single quote for pairs and makes the appropriate addition in the held string
_ a syntax of a pair of quotes is always required and as such allowed. Effectively as in typical HTML type code these “start and stop “Tags”” signal that what within is a literal text. As such a pair like “” is valid ( and signals an empty string ) , a double quote within like “””” or ” “” “ is also valid syntax . Possibly there is some sort of confusion or uncertainty in what the complier actual sees here. Somehow I am going in and out of a literal string but at the same time joining them with a valid literal character of no length. Possibly somehow simultaneously two strings are seen but accompanying or applied to the same memory space, such as seeing the blue and purple in the following at the same point of zero length “ ““ “ . Having recognised a literal “ at “zero” length, it therefore joins the two sides of that text string with a single “ between at that point. This “ being added at point of zero length is like “inserting in” , which then somehow “shifts” appropriately, effectively adding 1 to the length ( Ref A.E. Jan 2016 )

Display text and quotes in cell from text in Range Object .Formula
Just taking the above one step further as this will also help in the understanding of the code routines discussed here. The Range Object can be given a formula, (with VBA as in Line 140) . (This typically is then seen displayed in the Formula bar). Writing manually in a cell = is recognised by Excel as indicating that what comes after is a formula. Text can be included in a formula, and the same convention is used as in VBA to indicate this by enclosing the text in a quote pair “ “. ( We would write manually like = "Some Text" ).
( _ .. At this point, in an attempt to help minimise the inevitable confusion that arises from the whole quotes theme, I use a general convention of
_ ..Blue font showing all VBA stuff which includes the quotes and ampersands used to build formula expressions
_ ..and
Green font showing what one actually would write in a spreadsheet cell and includes the literal quotes and ampersands needed to assign to the Range Object to get the final wanted result in the spreadsheet cell
_.. and Purple font showing what one needs to write ( in our discussions quote pairs ) differently within a VBA String in order to get something similar ( In our discussions a single quote ) in a String text written to a cell. For most normal simple characters and numbers ( in other words mostly non punctuation ) the color Purple translates directly to the color green , so will be shown in green to help the necessary different Purple more apparent.

In a spreadsheet one would manually write something of this form
="Some Text” or ="Some " & "Text" – Note here that I often include extra coupling through some redundant &s , as this helps constancy and compatibility / modifying and building strings.
In a string built in VBA to paste out to a cell we need to produce a string which internally looks like those_.
_. This would then take these forms in VBA code ( Lines 170 and 180 )
strText = "=" & """Some Text""" or strText = "=" & """Some “”" & "&" & """Text"""

Note: In a Range Object the formula String values, such as .Formula and the “value” String values are related. We may therefore assign out formula String, strText to either the Range Object .Formula or .Value. Regardless we will see displayed the Formula in the Formula bar, and the value, .Value, in the cell Some_Text_Formula_Value.JPG http://imgur.com/XVEzS7H

' '2b)(i)
It can be confusing to go finally to the case of a VBA string which will produce a final seen string value in a cell which may also include a quote. But really it is just a careful progression of the steps and ideas discussed thus far.
For example, say, we finally want this displayed in a cell
Some Text and a Quote "
But we want that to come from a formula ( as seen in the Formula bar )
Based on what we have disused thus far, we would need to write manually in the cell something of this form
=”Some Text and a Quote “ & “”””
One way to achieve this would be produce this String in VBA to be written to the Range Object of that cell
strText = "= " & """Some Text and a Quote """ & "&" & """"""""""
Once again for clarity here: That last line needs to produce the string in Debug.Print ( or as seen by hovering with the mouse over strText after the code has passed that line in debug, F8 mode ) which looks like we would manually write in the cell
Code lines in ' '2b)(i) take us through the above

Lines in ' '2b)(ii) follow a similar development to finally put a Formula into the Range Object of cell C3 to produce a simple test string displayed in the spreadsheet cell. HelloFromC3.JPG http://imgur.com/k9IYFUj
Note here: Lines 240 – 260 shows how it comes about that we typically see a code line such as code line 260 which I suggest gives the false idea of a Cells( ) Property which takes arguments. In fact I believe we are looking at the default Property of the Range Item Property applied to the first area Range Object of contiguous cells of the Range Object returned by the Cells Property. ( The Cells Property applied to a Worksheet can only return a single area of contiguous cells , but a Range Object is organised into a collection such areas of contiguous cells )

_.......
That covers the basics behind putting displayed text into a cell through using VBA to fill in the Formula of the Range Object of that cell, that is to say passing a formula string to the Range Object to which the displayed cell obtains the value it displays. The reason for doing that will become apparent in the explanation of the second part of the code on the over next post is reviewed
_....