Tests and Notes on Range Referrencing

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

Postz for later use

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

Postz for later use

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

post for later

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

post for later

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

post for later

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

post for later

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

**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: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