The stuff in the last post is all, I think, that one needs to know in order to effectively refer to, and return, Range Objects using range Properties.
In this post I try to clarify a few things that can lead to confusion ***
Referring to Ranges in VBA. Misconceptions
The last post covers the basic information for using Referring to Ranges in VBA using the two main properties , Range( ) Properties and Range Item Property
But there are a lot of pit falls to be aware of. This post summarises them and suggests solutions
_3 ) Cells Property
There is no Cells Object. As noted in the previous Post, cells is often used in a general imprecise way to talk about aspects of the “boxes” we see in a spreadsheet.
There is , however, a specific VBA Cells Property.
But it is actually a property with no arguments which simply returns a Range Object comprising of the entire cells ( or single cell Range Objects ) contained in the Object to which it is applied.
It has very limited use. The only one I can think of is for returning a Range Object comprising of a single area all cells within a Worksheet. This allows you to do all with the entire Worksheet cells that you can do with a Range Object.
A couple of situations arise which cause confusion and lead to a false discussion of a Cells property which is typically explained as having the same arguments as the Range Item Property. One will very commonly see something of this form_..
Cells(row, column)
_..explained as a Cells property available to refer to a range. ( I even did it myself for a long time, and apologise for confusing any OPs! )
A couple of examples to clarify and explain how the confusion comes about
_3a ) Cells Property and Range Objects
It is perfectly acceptable to apply the Cells Property to a Range Object . Indeed it is frequently done. But it is totally redundant as far as I can see.
The results of doing this however, are frequently to present and explain a cells(row, column) type property incorrectly. To understand this it is important to understand the Range Item Property. So make sure you understand that, ( see 1st Post ), before going any further. OK?
Now consider some arbitrary Range Object. Say that associated with the first cell Item of a worksheet , Set to a variable such:
Dim Rng1 As Range
_ Set Rng1 = ThisWorkbook.Worksheets("Sheet1").Range("A1") ' Using Range Property of a Worksheet to return a Range Object
Using the Range Item Property to return from this Range Object a new Range Object, say that of the second worksheet Item cell could be done explicitly as follows.
In most cases the first and only Area is being considered, which is the default, so .Areas.Item(1) is usually omitted. As with most VBA Objects, for the Range Object the default Property is the Item. This allows then from the syntax the .Item part in the Range Item Property statement to be removed. One acceptable shortened form would then be such as this:
_ Set Rng2 = Rng1(1, 2)
As noted we may apply the Cells property to a Range Object ( even though it is totally redundant because it returns the same Range Object )
So we could also write_..
_ Set Rng2 = Rng1.Cells(1, 2)
_.. Here the same Range Object is returned by Cells , and that returned Range Object is having the Range Item Property applied to it. - .Cells is redundant here and can be removed. http://stackoverflow.com/questions/2...91641#41491641
_3b ) Cells Property and Worksheet Objects
This comes a bit closer to getting it correct, but not quite.
The Cells property can be used to return directly a Range Object ( containing all the cells ) of a Worksheet. Without using the Cells Property we cannot therefore apply the Range Item Property.
So this is valid for our previous example
_ Set Rng2 = Worksheets(“Sheet1”).Cells.Areas.Item(1).Item(1, 2)
Once again .Areas.Item(1) can be omitted, and .Item(1, 2) simplified to (1, 2), resulting in
_ Set Rng2 = Worksheets(“Sheet1”).Cells(1, 2)
We could just as well do this
_ Set Rng2 = Worksheets(“Sheet1”).Range(“A1”).Areas.Item(1).Item(1, 2)
and again simplify this to
_ Set Rng2 = Worksheets(“Sheet1”).Range(“A1”)(1, 2)
One could argue that Cells(1, 2) looks a bit neater than Range(“A1”)(1, 2). I personally would probably use Range(“A1”)(1, 2) just to remind me that I am using the Range Item Property in end effect. It might also be that creating a Range Object initially of just the first cell rather than of the entire worksheet cells might have some different effect.
_4) Workbooks and Worksheets referencing in range property arguments
I mentioned a few times that in the Range( ) statement syntaxes other than simple Address strings are accepted.
My experiments suggest that this could be not directly as a conscious design to aid in ease of flexibility of use in range property constructs, but rather a by product of making the Range( ) compatible in use for a more direct way of referencing a Range Object. Briefly:
_4a) In the single argument Range( ) case, it is possible use a full reference, or part thereof , in the string, using a form such as this
Range("='[MyFile.xlsm]Sheet1'!A1") or Range("='Sheet1'!A1")
_4b) In the two argument case it is possible to do something similar in this form
Range(Workbooks("MyFile.xlsm").Worksheets("Sheets1").Range("A1"), Workbooks("MyFile.xlsm").Worksheets("Sheets1").Range("A1"))
or
Range(Worksheets("Sheets1").Range("A1"), Worksheets("Sheets1").Range("A1"))
_4c)(i) Defaulting Address
I am thinking that the Workbooks and Worksheets referencing discussed in _4a and _4b) allows some sort of pseudo direct Object referencing as opposed to referring through a Property. This effectively uses the Range( ) statement to return the Application Range and the Range( ) statement argument must full qualify the Range Object as there is no preceding Object from which to obtain the necessary Worksheets reference. ( A cell or rather Range Object is an Object under / in a Worksheet ). This is not needed in the property usage case. But as it is there in the Range( ) statement “workings” for the above reason then it will still be accepted by a call of Range( ) in a property code line.
It is found that if the Workbooks and Worksheets referencing in range property arguments is not the same as the Workbook and the Worksheet to which the Property is applied, the Range( ) statement errors due to the “method of range object for that Worksheet failing”. This is consistent with that the full referencing is not really appropriate in the Properties referring cases. It works only if the final addressed cell or cells are within the Worksheet to which the property is being , ( or to the worksheet in which the Range Object to which it is applied is in ).
_4c)(ii) Defaulting Address and some named range
Another reason for the acceptance of a string other than the Address can be seen when considering the use of a named range in the constructs for the property referring. A named range may be used in place of any Address reference. However a point to note is that if we give the name of a range in a Worksheet appropriate for the property reference , "some_named_range", this named range must not have scope, that is to say, must not be accessible, from that Worksheet. The way Excel handles this situation is that actually it adds to our name. For example, consider that our named range is in Worksheet "Sheet1", but has been scoped to Worksheets "Sheet2". Excel actually holds this name as "Sheet2!some_named_range". A construct as this is then acceptable, and indeed needed: Worksheets("Sheet1").Range("A1,Sheet2!some_named_range")
We might write_..
Worksheets("Sheet1").Range ("A1,some_named_range")
_.. but Excel reads that as the former. ( We note that Excel has the sometimes unnerving effect of changing string references to suit appropriately https://powerspreadsheets.com/excel-...ent-3105025065 ) . So this is another reason for the "acceptance" of a string argument larger than the simple Address
_4d) Set Rng=Ws.Range(ws.Cells(1, 1), Ws.Cells(2, 2)) – What is this about?
It is often argued that the two argument Range( ) type Property is useful when a column Letter may not be known, allowing the column number to be used in such statements
Range("A1", Workbooks("MyFile.xlsm").Worksheets("Sheets1").Cells(Lr, Lc))
or
Range("A1", Worksheets("Sheets1").Cells(Lr, Lc))
or
Ws.Range("A1", Ws.Cells(2, 2))
etc..
I personally would not use this as I think it is not designed for this purpose and gives the danger of inappropriately constructed references. For this problem I would use a simple function to convert the column number to the corresponding column Letter – http://www.excelforum.com/showthread...8643&highlight
The two argument Range( ) type need then not be used as the string in the single argument case can then be modified. It would then look, for example, using the last example, something like Ws.Range("A1B:" & CL(2) & "")
_5) “Application Range( ) Object”
This is just as an aside, an idea from me: ...so I am thinking that in range property arguments the syntaxes other than Address strings may not be appropriate. They arise possibly so as to allow the Range( ) statement to be used in some way such as:
Application.Range("='[MyFile.xlsm]Sheet1'!A1")
or
Application.Range("='Sheet1'!D5:F7,G1,J1:J10,some_named_range")
or
Application.Range(Worksheets("Sheets1").Range("A1"), Worksheets("Sheets1").Cells(Lr, Lc))
etc....
Such a statement will work to return the Range Object specified in the ( ) of the Range( ) statement. This could be argued as being some direct referring to a Range Object as an alternative to Referring to Ranges in VBA using two main properties you can use Range( ) Properties and Range Item Property.
' Rem Ref https://powerspreadsheets.com/excel-...ent-3079546170
' Rem Ref http://www.excelforum.com/showthread...11#post4551509
' Rem Ref http://excelmatters.com/referring-to...comment-185788
' Rem Ref http://spreadsheetpage.com/index.php...your_vba_code/
Conclusions from this post
_ Minimise the use of Cells. Only use it for returning a Range Object comprising of a single area all cells within a Worksheet when you actually need that, for example in then referring to all cells in a worksheet as a Range Object. ( Code lines such as Cells.ClearContents for clearing values for all cells in a worksheet I find useful. Whether .ClearContents is here a Property of the Cells Property or the Range Object it returns is debatable. )
_ Avoid anything other than simple Address strings in the Range( ) , statement. ( This subsequently means the use of the two argument version of the Range( ) statement is not needed, which is probably a good idea as it is often a source of error due to inappropriately constructed references. https://powerspreadsheets.com/excel-...ent-3108277121
' Rem Ref
' http://excelmatters.com/referring-to-ranges-in-vba/
' https://powerspreadsheets.com/excel-vba-range-object/
' http://spreadsheetpage.com/index.php...your_vba_code/
' http://stackoverflow.com/questions/2...91641#41491641
' http://www.excelfox.com/forum/showth...eadsheet-cells
Bookmarks