A very good principle to remember is that nothing inside the XL object hierarchy exists *except* in reference to the application object. In some ways it's a shame that MS implemented default and implicit parent references. Cells (or Range) w/o is a *property.* It must be applied to some object. For a physical object imagine a statement like Door(Passenger, front)=blue. It's a meaningless statement unless you know what car the reference is to.
MS, in trying to be helpful, decided that an unqualified use of the Cells (or Range) property will apply to the active sheet (check help for the 'Cells property').
The result is that rs.range(cells(),cells()) is the equivalent of rs.range(activesheet.cells(),activesheet.cells()). And, since a range can have only one parent worksheet, the construct results in an unresolvable conflict *unless* rs is the activesheet.
Dots are not binding on anything. I don't know what that means. rs.range is simply a way of saying the range property of the rs object. A property (and a method for that matter) often needs an argument (just like a sub or a function).
Maybe, a physical object will help. Consider an object of type 'car.' By itself this isn't of much use. We have to use properties or methods to do something with the object. Suppose we know that there's a property called Doors. Doors() is a property of the Car class and it will return an object that is a collection of all the doors on the car. OK, so we have an object of type collection. Again, there's not much we can do with it unless we know that it has a property called Item. This Item property takes two arguments to identify a specific door. These indices can have the values: Passenger/driver-side and front/rear. So, MyCar.Doors.Item(Passenger,Front) refers to a specific door. Often, the programming language lets one skip the Item property of a collection and jsut refer to MyCar.Doors(x,y).
Two important lessons so far. A property needs an object to be meaningful. An object is useless unless one knows something about its properties and methods. This forms a very symbiotic relationship since one cannot exist without the other.
Often, in the interest of efficiency (fake efficiency, IMO), a programming language implements a default object to apply an unqualified property (or method) and a default property for an unspecified property. We see this heavily misused in something like anInteger=Cells(i,j). As an exercise, figure out how many assumptions about objects and properties were required to make that statement work.
Also, keep in mind that a property always returns something of some type. It can be a basic data type (integer, string, etc.) or it can be a object of some kind (and a collection is itself a type of object). Another thing worth noting is that a property named X can return something of type X. Nothing wrong with that. In fact, MS implemented that convention extensively in its various OMs. That is how we get the Range property of a worksheet object returning an object of type Range as in Set aRng=WS.Range(). In fact, the Range property of the Range object returns an object of type Range as in Set aRng=Range("A1:A10).range("A1")..
OK, a final but detailed example: How does one refer to the name of the first sheet of the first opened workbook? The fully qualified reference would be Application.workbooks.item(1).sheets.item(1).name. This returns an object of type string. To really understand what is going on you have to realize how compact OO programming can be and should invest time in deconstructing these statements.
[For another example see
Case Study – Understanding code
http://www.tushar-mehta.com/excel/vba/vba-Understand code.htm]
Back to our current example. Remember I wrote that *everything* in the XL OM flows from the application object?
So, start with the Application object and use its workbooks property. That returns an object of type workbooks collection. The generic type of this object is collection.
As mentioned above, collections have their own properties and methods. One of them is Item() which lets one refer to a specific item in the collection. So, the Item() property returns an object of whatever type is in the collection. This is an instance where the name of the property doesn't match the name of object type returned by it.
So, Application.workbooks.item(1) returns the first 'thing' that is in the collection. Since the workbooks collection contains only workbooks, we can be confident that the type of the returned object is 'workbook.'
The workbook object has a property named Sheets. It returns a collection of all the sheets in the workbook. Remember that sheets in a workbook can be four different types (Chart, Worksheet, XL4 macro, and DialogSheet). So, the Sheets collection -- unlike the Workbooks collection -- doesn't necessarily have homogenous objects in it.
We use the same Item property that every collection has to 'get' the first object. The generic type of this object is sheet and it may also belong to a more specific type of a collection (but we will leave that concept for another day).
Luckily for us we don't need to know what kind of a sheet we are dealing with since *every* type of sheet has a name property.
So far every property has returned an object of some kind. The last property we use (Name) returns a basic data type: string. Well, at least in VBA string is a basic data type. In other languages it is not necessarily so. But, we will leave that too for another day.
Note that the Item property is so 'fundamental' to a collection that MS doesn't require one to use it. That means Collection.Item(x) is the same as Collection(x).
Instead of accessing the name of the first sheet of the first workbook in a single statement, one could break it down into individual steps. It really helps to understand what is going on. I do this when investigating an object I am not familiar with. It really helps understand the hierarchy of the otherwise unknown object.
Code:
Dim AllWBs As Workbooks, aWB As Workbook, _
AllSheets As Object, aWS As Worksheet
Set AllWBs = Application.Workbooks
Set aWB = AllWBs.Item(1)
Set AllSheets = aWB.Sheets
Set aWS = AllSheets.Item(1)
MsgBox aWS.Name
just_jon said:
jmiskey said:
Thanks jon. I learned something new today.
It looks like the key is that you have to preface EVERY range reference with the Worksheet variable; not just the ones that start RANGE, but also the ones that start CELLS. I guess the reference in front of RANGE doesn't carry over all the way through.
Not sure, this is behavior I'm not familiar with - the structure
Workshee('ref1").Range(Cells(rowref, colref), Cells(RowRef, ColRef)) = ...
should be sufficient as the period should bind the Range to the Worksheet, no?
I'm just not seeing the obvious...