Tests and Notes on Range objects in Excel Cell

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

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

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

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: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 Attachment 3659

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