Tests and Notes on Range Referrencing

[DocAElstein]

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.

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]

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.

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]

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.

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]

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

[DocAElstein]

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

[DocAElstein]

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

[DocAElstein]

advlkdln

[DocAElstein]

sdlvjsdj

[DocAElstein]

dnvlkadsnvdnv

[DocAElstein]

lsddvlksgn