What is a cell and a Range Object.
As Jorge G says here, https://powerspreadsheets.com/excel-vba-range-object/ , cells is probably the first thing that springs to mind when thinking about Excel. Often, cells is used very generally and imprecisely ( and occasionally very wrongly *** ). Mostly one uses “cells” as a very general term to refer to the "boxes" as you see them in a spreadsheet. Usually what you see as such is an extremely small amount of what is associated with, and can be done with, the cells. The word sticks in the mind from most people's initial experience with looking at Excel as a spreadsheet full of square boxes or cells with possibly some values in it, or seeing coloured rectangular areas etc. I will use italics where I use that or similar words generally and imprecisely. I will use bold for a specific defined Excel or VBA Object
More precisely Excel organises its cells into groups of one or more cells and stores all information about these grouped cells in what it calls a Range Object. In simple terms this is just something that stores all the information. ( There is no VBA Cell or Cells Object. There is a Cells Property, but this is badly misunderstood. I will attempt to add some clarity in the next post ***).
It follows that the smallest Range Object is that associated with a single cell.
Even for a single cell, the information associated with a Range Object is massive . It is worth taking a look at it, - see here for example: http://www.excelforum.com/showthread...11#post4551080
The Range Object is arguably the most important, the most biggest , and the most incorrectly understood Excel thing. Microsoft recently corrected some of their literature after I bombarded their various feedback parts of web sites with Comments explaining to them their mistakes and telling them it how I saw it.
What is this Post about ( points to note )
There are several ways to refer to ( or in a Object Oriented Hierarchical Programming type language we often say “to return” ) a Range Object.
This post concentrates on the 2 - 3 Main ways to refer to or "return” a Range Object in VBA by "applying" to some Object "Properties" of the type having a coordinate type argument. For a beginner, this will come across a bit vague and imprecise at this stage . A good way to think of what I am on about is to think about how you get to a point of, or an area within , a map. Alternatively think of it as similar to navigating the globe through a pair of axis at 90 Degrees to each other.
The other very important point to note at an early stage is that a Range Object can be, and is typically, associated with a single cell or a single rectangular spreadsheet area full of cells. The latter here, of all the cells within it is often referred to as a group of contiguous cells. ( In layman's terms contiguous means all of them – no spaces – none missing.. etc.).
However, more precisely defined , The Range Object is organised into one or more of such single cells or rectangular groups of contiguous cells. Each one of these is named an Area. Because of the definition of a Range Object, it follows that each Area is itself a Range Object. ( That could further be sub divided into further areas, but rarely is).
One last point here to note, is that in many ( not all ) cases the use of a variable in VBA for a Range Object in a code in this sort of from_..
Rng.
_.. is actually defaulting to the first Area that the Range Object has in its Areas list. ( It must, by definition, have at least one area ). As such, VBA effectively would "see" this in many, ( not all ), cases as the implied default.
Rng.Areas.Item(1).
The properties discussed in this post all apply to a single area, defaulting to the first area if none is given.
http://www.excelforum.com/showthread...11#post4551484 http://www.excelforum.com/showthread...11#post4551080
http://www.excelforum.com/showthread...=9#post4521752
http://www.excelforum.com/showthread...9#post4521753_
Referring to ranges in VBA ( What is a range )
As with the word cells, the word ranges is equally very loosely used. Approximately it is referring to all the cells in a Range Object, or more commonly just the first and only area thereof. So once again it is referring to the part of the Range Object which we typically "see".
Almost everything about the Range Object seems to have been designed to confuse. I expect it is based on the deep down workings of Excel. Deep down Excel has pseudo only one of anything, and what we "see" as multiple things is based on a complex system based on increments and offsets stored as sets of coordinates. Where numbers or sets of numbers match or "intercept" based on some algorithm results in our imaginary grid, aka. The spreadsheet we see in front of us.
So Excel thinking is about referring via offsets from some point.
The spreadsheet convention in Excel is fairly consistent in that it starts at, (that is to say row 1 and column 1 ( or "A" ) is), "top left" . A further very common and widely used convention is to " go along from the top left for all columns, then down to the next row, go along all columns from the left, then down to .. etc.."
In our Property discussions we are using either
_the sequential Item or Index following that convention, or
_ the row number and column number, or, mostly,
_ the default row number and column letter coordinate convention system.
By the Latter row number and column letter coordinate convention system here we are talking about the typical default Excel Address strings like "A1" for a single cell and , "B2:C3" for a cell area where "B2" is top left, and "C3" is bottom right. ( There are some other syntaxes which are "allowed" , but it is best not to use then in my opinion. As others also note, Excel is probably defaulting in such cases back to the row number and column letter or row number and column letter or Item number , and there is the risk that we may end up with inappropriately constructed references when relying on the implicit default, https://powerspreadsheets.com/excel-...ent-3096080590 ) .
_1) Range( ) type Properties
Although not defined as such, the statement Range( ) "works" generally something like a method returning the Range Object given by or referred to by the string reference argument in the ( ). As noted, we should strictly restrict ourselves with range Properties to simple Address strings in the row number and column letter notation. Other syntaxes are accepted but can lead to problems.
This Property type allows us to return a single cell, a multiple cell single area, or any combination thereof to give us a multi Areas Range Object. We have a single and double argument option.
The single argument option is the most common as we can use it to refer to all possible Range Object types. For example, we would have forms such as
_ a single cell area like Range("A1") ,
_ a single area of multiple contiguous cells like Range("B2:C3"), and
_ a multi area Range Object like Range("B2,D5:F7,G1,J1:J10,Named_Range") etc.. etc...
Range( ) type Properties are confined to "+ve directions", that is to say going to the right or down from top left
The double argument option is less common and is an alternative for, and restricted to, a single range area of one or cells. It is a "top left, bottom right" notation. So from the examples above, Range("A1") would be written Range("A1", "A1") and Range("B2:C3") would be written Range("B2", "C3"). Once again syntaxes other than row number and column letter notation are accepted but can lead to problems. ***
_1a) Worksheets Range Property
A Worksheet Object has a Property called Range. This is the Range ( ) discussed above using the row number and column letter Address notation based on "A1" being the first cell ( top left ) in the worksheet.
It is advisable to always specify explicitly the Worksheet with a code line such as_..
Worksheets("Sheet1").Range ("B2:C3")
_... rather than relying on the default worksheet taken by
Range ("B2:C3") .
Similarly using variables helps keep things well organised:
Dim Ws1 As Worksheet
Set Ws1 = ThisWorkbook.Worksheets("Sheet1")
Dim Rng As Range
Set Rng = Ws1.Range("B2:C3")
_1b) Range Range Property
That title above, Range Range, is not a typo!. ( Frequently Excel use the same word for different things which helps confuse us). Range is the Range Object and Range or Range Range is the Property explained in this section.
Having established a Range Object, such as for example, the Rng in _1a) , then the Range( ) can be used in exactly the same way once again, but this time the top left is the top left of the Range Object to which it applies
So, as example, Rng, above was the single area Range Object of the 2row x 2column cells area starting top left in the Worksheet at B2, ( and extending to bottom right of C3).
Therefore Rng.Range("B2:C2") will return a Range Object of a 1row x 2 columns cells area in the Worksheet in which Rng is in. The range starts top left at coordinate B2 relative to Address "B2" in the Worksheet. So finally the Address in the Worksheet of this new Range Object will be "C3:D3"
Once Again all types of single and multi area cells and range construction is possible, but as the referencing will again only be able to refer to cells to the right and down from the original Range Object top left, then one cannot reference any cells to the left of, or back up from top left, in this case, B2 of the original Range Object, Rng in the Worksheet.
_2) Range Item Property of a single cells area
This is currently less commonly known, mostly because it is incorrectly referred to as a cells Property.
Microsoft have changed some of their documentation following my recommendations to clarify the situation.
This is a simpler coordinate referencing convention limited to referring to a single cell.
It is more restrictive to the type, that is to say multiple cells ranges cannot be referred to, but in some of the argument options that it has, it allows “-ve and +ve direction” referring, which actually, strangely, allows for all cells in the Worksheet to be referenced , regardless of the start top left cell. ( But not with all argument options )
_2a) VBA Items and range items. ( Range Item Property single argument ( ) case )
Generally in VBA, for Objects or ( Properties) which have ( or can refer to ) a collection of similar things, the individual things can be referenced through a consecutive number or index like 1, 2, 3 .. etc.. Typically the syntax is like .Item(x), where x is usually an integer from 1 to the total Count of the similar things. ( For such an Object, the Object may be referred to as a Collection Object ).
For the case of a Range Object of a single rectangular area of spreadsheet contiguous cells , things are a bit different*. Amongst other things, this number relates to each cell in the Range Object and usually to a some outside it. The ordering follows the typical Excel Spreadsheet cells order convention of numbered from left to right and from top to bottom. *Strangely, for the case of a Range Object we may use this to reference cells outside the Range Object that lie within the Worksheet boundaries. So we are not restricted to the Items count. For the single argument option, these "Overshoot" Items follow further the typical row then column convention , so effectively we can refer to and return a single cell Range Object anywhere from top left of the original range, and extending down to the bottom of, (but not beyond), the Worksheet in a column of width equal to that of the original range
_2 b) Range Item Property double ( , ) argument case
*It is a further confusing aspect of the Range Object that for the Range Object 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 for both, or , for the columns , the column letter (wrapped in quotations) may be used as an alternative. In the two argument case we may extend beyond the original Range Object from the top left in the Original Range Object in the usual row, column way, ( we are not restricted to a particular width as with the one argument option). Note further, that when using the two argument option for the Range Object Item Property, negative co ordinates are permitted also, and we are again not restricted to the original range . This allows us to refer to all cells in the Worksheet.... Note however:
_ 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 does not go “further back to the left” or “further back up” than the Spreadsheet boundaries ;
_ zeros in such as this Property reference, ( 0, 0 ) , is also from the syntax valid. Such a reference will effectively go “back up, and back left” by 1 ;
_ there are no “-ve Letters” . So –ve column referencing is restricted to using column number referencing
_..
_ Returning multi cell and multi area Range Objects which contain cells back to the left or back up from top left of a Range Object can obviously be achieved by applying initially –ve Range Item Properties, followed by the appropriate Range Range Property.
The point to note here is that the range Properties return a Range Object to which one can further apply range properties
_...
The above is all, I think, that one needs to know in order to effectively refer to, and return, Range Objects using range Properties.
In the next post I try to clarify a few things that can lead to confusion ***
Reply With Quote
Bookmarks