Ranges

Just chillin'
Photo: Holly Vickery

The Range object

A Range object can represent a bunch of cells in a worksheet. Range objects make working with data tables easier. Easy is good.

(You don't need to know what an object is, but there's a page about Excel objects, if you're curious.)

Start by downloading messing-with-a-range.xlsm. We'll use it to explore some stuff about ranges. (Remember, Alt+F11 is the shortcut for looking at the VB Editor. On Windows.)

The workbook has three different worksheets. Choose the first one, "About Ranges". That's what we'll be messing with in this lesson.

Open the file, and you'll see some goat data, and a bunch of buttons.

The fields are name, happiness, and coolness.

So far, you've only seen one button, attached to one VBA Sub. But you can actually make as many buttons as you want, and as many Subs as you want.

Look at the code, and you'll see:

Each Sub is attached to a different button. Right-click on a button and click Assign Macro, and you'll see of list of Subs in the workbook you can attach to a button.

(If you did that, hit Cancel, so you don't change anything.)

Making a Range object

A Range is a bunch of rows and columns in a worksheet. It's like a subset of the worksheet.

You specify a Range by giving its corners. For example, this range...

... goes from Cells(3, 1) to Cells(6, 3).

This Range ...

... goes from Cells(4, 2) to Cells(13, 4).

Click on the "Select range" button in the sample worksheet. You'll see:

Let's check out the first part of the code.

Line 3 adds a new data type. Remember that we have Single, and String. Now, we have a new type of thing, a Range.

Here's the code again:

You need to create a Range object before you can do anything with it. That's what line 5 does:

Set rGoatDataTable = Range(Cells(3, 1), Cells(3, 1).End(xlToRight).End(xlDown))

It's an assignment statement, so, the thing on the right gets put into the thing on the left. But with objects, you have to add Set in front of it. Because... well, don't worry about why, just do it.

Let's look at the Range function call:

Range(Cells(3, 1), Cells(3, 1).End(xlToRight).End(xlDown))

What does the mean?

Remember, a Range is a rectangular block of cells. Like this:

The way you specify a range is to tell VBA what the top left cell is, and what the bottom right cell is. (There are other ways, too, but let's stick with this.)

So it's:

The top left cell of the data table is row 3, column 1. So:

OK. The bottom right cell is 15, 3. So we could do this.

There's a problem, though. The number of goats changes every day. Today, it's 13. Tomorrow, it's 128. Next year, it's 65,536. How to write code that works, no matter how big the data table is? The starting cell will always be 3, 1. But the ending cell will be changing all the time!

The trick is to simulate what you do when you use Excel with the keyboard. Not everyone knows it, but you can select a data table with just a few keystrokes. Click in cell 3,1.

Now, Ctrl+Shift+↓. That is, hold down control and shift, and press the down arrow. Excel will select all of the data downward, until it finds an empty row.

Next, Ctrl+Shift+→. That's control, shift, right arrow. Excel will select cells to the right, until it runs out of data.

Woohoo! So that's:

• Click in the upper left cell of the range.
• Ctrl+Shift+↓
• Ctrl+Shift+→

We can make Excel act the same way in code. This...

Cells(3, 1).End(xlDown).End(xlToRight)

... says "Start in cell 3,1, then go to the end cell (the last one with data) in the down direction, then go to the end cell in the right direction."

We can use this to get to the last field of the last record, no matter how many records there are. 13? 128? 65,536? No worries. Cells(3, 1).End(xlDown).End(xlToRight) will handle them all.

Here's the line of the code that makes the Range object.

Set rGoatDataTable = Range(Cells(3, 1), Cells(3, 1).End(xlToRight).End(xlDown))

This says: "Make a Range object, with the upper left at 3,1, and the lower right at wherever you end up when you start at 3,1, then go to the right until you run out of data, and down until you run out of data."

(Notice that I was going down and then right, and this one goes right and then down. Doesn't matter. Same result.)

Here's the code again:

So we make a Range object corresponding to our data table, and then:

rGoatDataTable.Select

That just selects it. So we see:

Try it. Download the worksheet if you haven't already.

Now, a question for you.

Right. Start at cell 3, 1, select right and down until you come to some empty cells. That's what happened.

Put the row back in again. Undo (Ctrl+Z) is the easiest way.

Number of records and fields

You'll often need to know the number of records in your Range. Here's some code:

Line 6 sets up the Range, as usual. Line 8 asks the Range how many rows it has. We're using one row per record, so the row count is also the record count.

You can do the same kind of thing to get the number of fields.

Line 8 asks the Range how many columns it has. We're using one column per field, so the column count is also the field count.

Access data in the Range

So far in the course, we've been using Cells() to get data from a cell in the worksheet. So Cells(2, 2) gets the data in cell B2 of the worksheet.

You can also use Cells() to get data in a Range. Here's the data again.

So...

... means row 3, column 2 in the Range. That's 6.

Cells(3, 2) is 5, but rGoatRange.Cells(3, 2) is 6.

Right!

Try the "Show Happiness" button in the worksheet. You give it a record number, and it shows you a value:

Here's the code for the button:

Line 10 sets up the Range as usual.

Then we ask the user what record number they want.

They could have asked for a record we don't have, like record 66, when we only have 13 records. The next few lines validate the user's input.

We get the number of records in the Range, using rGoatDataTable.Rows.Count. If the user asks for a record beyond that, show an error message.

Then we get the data from the record the user wanted.

Finally, show the output to the user.

Comments for variables

You can add comments when you declare variables, to make it easier to understand what they mean. Put a comment above the Dim for the variable the comment is about. Like this:

Notice that both of the comments have examples. Adding an example in a comment can really help.

Using constants for column numbers

In the business world, the data that you're analyzing could come from anywhere. Internal data, a service your company subscribes to, a public data set on the web... lots of places.

It's common for the format of data sets to change. For example, the data set we have here has the fields name, happiness, and coolness:

Maybe whomever supplies us with the data set adds another column, age. They add it after name.

Now we have more data to play with. Hooray!

Except, now some of our code is brokeh. Here's what we have in the get-happiness code:

Oh, spit! Happiness is now field 3, not field 2. Not a big deal to change it, just for this program. But a typical data set will have dozens of fields. We have to change the code for many of them. Coolness is now field 4 instead of field 3, and so on.

It would be easy to miss one of the field numbers, or type it in wrongly.

Wouldn't it be nice if we could code something like this instead?

Instead of 2 for the field, we type a name instead, like HAPPINESS_FIELD.

Wouldn't that be cool?

Sure would.

That's easy to set up in VBA, with things called Consts. A Const is a name you give to a constant, or a fixed value. You declare them along with variables, like this:

Whenever you use NAME_FIELD in your code, VBA replaces it with 1. When you use HAPPINESS_FIELD VBA replaces it with 2. And so on. So:

What happens when the age field is added? You change the Consts, and leave the rest of the code alone:

Changes are all in one place. They're easy to check, and test.

Module-level Consts

There's still an issue, though. Remember that this worksheet has six buttons, with six Subs.

We could have the same Consts in each Sub:

If we need to add the age field, we'd need to change a lot of code. Ack!

There's a solution, though. We could move the Consts outside of the Subs:

We can use the Consts in any Sub, as before. But now we have them defined just once. Easy to change. W00t!

The Consts are said to be declared at the module level. Module-level things are available to all Subs. Things declared inside a Sub (as we have been doing in the course so far) are only available in that Sub. They are local to the Sub.

Yes! Good thinking, Georgina! We can do that. Something like:

Now rGoatDataTable is declared just once, and all Subs use it.

Let's not worry about module-level variables right now, though. Let's just do Consts at the module level. There's enough new stuff to fill our brains.

Summary

• A Range is an area in a worksheet.
• Ranges are good for working with data tables (records with fields).
• Make a Range with Set rDataTable = Range(...).
• Get the number of records in a data table: rDataTable.Rows.Count
• Get the number of fields in a data table: rDataTable.Columns.Count
• Access a value in a data table: Cells(33, 3).
• Define Consts, and you can use them for the field number: Cells(33, FIELD_NAME).
• Define Consts at the module level, and all Subs in the module can use them.

Exercise

Up next

Now you know how to create a Range object, and access its properties, like Rows.Count. The next step: processing all of the data in a Range, with a loop.