VBA – Cells and Ranges in Excel (A bit more than the standard story)

Excel’s ranges and cells are both complicated and very simple. They are simple, because everyone has an idea what is a range and what is a cell, and they are complicated, because these are both:

  • Properties of the worksheet object
  • Properties of the range object

Yup, range is a property of the range object. Thus, something like this is a valid code in VBA:

Close your eyes and think for a couple of seconds what would be printed in the Immediate window after running the code above. If you manage to guess both of the results then you may treat yourself anything. Try them and let me know how it works.

Worksheet.Range Property

This is the classic Range in Excel. Using only this one, you probably never ever need to go to the other 3. If you are not sure what to use, you would probably need to use this. It is something like Worksheets(1).Range("A1:Z10")  or just Worksheets(1).Range("B2"), if it is a single range. What is needed to know here, is to declare the parent worksheet (which is a must everywhere actually!) and to make sure you are using the key word Set, as this is an object:

Worksheet.Cells Property

The Wokrsheet.Cells property in general declares the cells of a worksheet, using the coordination system from A1. E.g, there are about 5 different ways to obtain the cells property of a worksheet:

Range.Range Property

Here is the reason of the funky code from the beginning of the article (Please, do not ever use it in any VBA project, like never-ever). This range property, when is relative to the Range object. Thus, in our code, we had Range("C1").Range("B2").Address, which somehow returns D2, because this is the relation of B2 to C1 . E.g., if C1 was magically transformed to A1 , then its B2 should be D2 , in order to keep the relation of rows and columns. This is quite tough to get probably, thus try to imagine it with this picture:

It is a kind of magic…

It is probably easier to explain with the following example:

It is quite visible that the A1 range of D2:Z10 is the top left cell, e.g. D2

Range.Cells Property

What you need to know about the Range.Cells property, is pretty much that it gives the relative position by rows and columns of the top left cell of the range. And because the Range.Item property is the default one of the Range object the word Cells could be omitted. Please, do not omit this word, it makes code more readable:

Thank you for staying with me until the end of the article. I hope you are not confused now. 🙂

About

A VBA Developer.

Tagged with: , , ,