Excel Training VBA Lesson 6

Worksheet

The Worksheet Object will most likely be an Object that you will encounter often.  You will need to refer to it to gain access to it's Objects, with the exception of the ActiveSheet.  If the Worksheet is not identified in your code Excel will by default assume the Active sheet.  As with the Workbooks collection we can refer to a Worksheet via its Name (tab name) or its Index number.  One thing you should be aware of is we cannot access a Chart Sheet via the Worksheets Collection Object as a Chart sheet is not a Worksheet and as such not a member of the Worksheets Collection.  To access a Chart sheet we would use the Sheets Collection Object.  The Sheets Collection Object represents ALL sheets in the ActiveWorkbook or the specified Workbook (including Chart Sheets).

To use its Name you would use:

Sub UsingTabName()
    Worksheets("Sheet2").Activate
End Sub

Or

Sub UsingTabName2()
    Sheets("Sheet2").Activate
End Sub

To use its Index number you would use:

Sub UsingIndexNumber()
    Worksheets(2).Activate
End Sub

Or

Sub UsingIndexNumber2()
    Sheets(2).Activate
End Sub

This would activate the second Worksheet in a Workbook.  The Index numbers run from left to right.

Personally I use the Sheets Object most of the time not only is it easier to type but it also allows us to use much better method of accessing a Worksheet and that is via its CodeName.  I wont go into too much detail on the Sheets Codename at this point as we will cover it in more detail in your Debugging code lesson later.  What I will say is it can be found in the Properties Window of the Sheet object and also in the Project Explorer.  It is the

name NOT in the parenthesis.  So for the two examples above you could use:

Sub UsingCodeName()
    Sheet2.Activate
End Sub

I will stress now that using the CodeName Method is one of those habits you should form early.  When we do your Debugging lesson this will become apparent.

Range

Without doubt the Range Objects is by far the most used and important aspects of not only VBA for Excel but for Excel itself.  As Excel is primarily a spreadsheet application its whole concept depends on the Range.  To put it in layman's terms, Excel is a load of little boxes (116777216 per Worksheet).  We can access any one of these Cells (little boxes) by nominating its unique address.  We do this by using a grid pattern and this is very similar to finding a Street on a road map it is that simple! When referring to a Cell or a Range of Cells within Excel we use either the R1C1 method, where R=Row and Column=Column or the A1 method.  The A1 is the preferred Method so that is what we will use.  Now as I have said, Excel has 116777216 Cells on each Worksheet these are represented by 256 Columns and 65536 Rows remember these 2 numbers as they come in very handy especially the Row number.  The amount of Columns and Rows on a worksheet are fixed (and as a consequence so are the cells), we cannot add more and we cannot remove any.  As we have far more Rows than columns we should always attempt to work with this in mind.  Excel is geared up for tables to be set up with Columns as headings and Rows for data storage.

When we are working with the Range Object we should try to avoid ever having to Select it you will find we can achieve this aim 99% of the time.  The two most common ways to refer to a Cell are:

Cells(1, 1).Select

Or

Range("A1").Select

Yes , I know I used the word Select :o)

I much prefer to use the latter as I find it much simpler to use a letter for a Column and number for a row.  I guess most people do, which is why Excel introduced the A1 style reference.  There also another way to refer to a cell and is the way we should use whenever possible, this way is to use its name (if it has one).  If we had a Cell or Range of Cells named "MyRange" we would use:

Range("MyRange").Select

I will stress here again that form the habit early of naming Ranges in Excel.  This is very important in VBA as users are inserting Rows, Cells and Columns or cutting and pasting may have you referring to the wrong cell.  Once a Cell or Range of Cells are named you wont encounter this potential problem as the Name will move with the Range.  Don't misunderstand me here, as I'm not suggesting for a minute you name all Cells within a Workbook but DO name Columns and Rows that you need to remain constant then you can (with reasonable confidence) refer to a particular Cell within the Named Column or Row and access the intended one.  Imagine we have Named Row two "MyRange" and this Row will contain all our Headings we can refer to the second Cell in this Range like this:

Sub ReferToANamedRangesCellMethod1()
    Range("MyRange").Range("B1").Select
End Sub

Or

Sub ReferToANamedRangesCellMethod2()
    Range("MyRange").Cells(1, 2).Select
End Sub

So if a not so helpful user comes along and inserts a Row above our named Row we wont be effected.

You will notice that we are using "Range("B1")" and "Cells(1,2)" to refer to a Range in Row 2 or maybe even Row 500.   It doesn't matter where our Named Range "MyRange" ends up we would always use the same method to access the second cell within the Range.  This is because using the Range (or Cells) Method after the Range Object makes it Relative.  Perhaps the easiest way to see this is to Record a Macro using the Relative button doing the following:

Selecting the cell immediately to the right of the ActiveCell.

No matter which Cell is the ActiveCell you will always end up with the code:

ActiveCell.Offset(0, 1).Range("A1").Select

Now as with most Recorded code we can edit it, in this case we can use:

ActiveCell.Offset(0, 1).Select

Or

ActiveCell.Range("B1").Select

Or

ActiveCell.Cells(1,2).Select

So when working with the Range Object we have many options open to us.

A common problem faced in VBA for Excel when working with Ranges, is how to find the first and last cell in a range.  This is where the number of Rows in an Excel Worksheet comes in very handy.  Let's assume you want to find the very last used cell in Column A.  In this case we would use:

Sub LastUsedCellInColumnA()
    Range("A65536").End(xlUp).Select
End Sub

To find the first blank cell in Column A:

Sub FirstBlankCellInColumnA()
    Range("A1").End(xlDown).Select
End Sub

To find the first blank cell in Row 1:

Sub FirstBlankCellInRow1()
    Range("A1").End(xlToRight).Select
End Sub

To find the LastUsedCellInRow1:

Sub LastUsedCellInRow1()
    Range("IV1").End(xlToLeft).Select
End Sub

Once we can find these four points of a range we can apply some VBA to achieve our ultimate aim.

All of the Objects we have discussed above contain many more Object, Properties and Methods than I have shown here.  But once you know the Objects hierarchy and how to refer to them it is possible to access all of them.  As you become more familiar with VBA you will find that there is virtually nothing that cannot be achieved, all it takes is an open mind as some lateral thinking.

I have included a Workbook example with this lesson that includes all the code mentioned here.  It also includes two exercises for you to have a go at.  As I have said before, if you solve the problem look at this as the icing on the cake, the cake itself is in trying to solve it.