Chapter 4: Explore Range Objects

Chapter at a Glance

In this chapter, you will learn to:

• Use several properties to refer to Range objects from macro statements.

• Put values and formulas into cells.

• Simplify macros that record selections.

• Apply formatting to ranges.

• Use the Object Browser to learn about objects, properties, and methods.

The world would be much simpler if people were all the same size. You wouldn’t need adjustable seats in your car; your head would never get bumped on a door frame; your feet would never dangle from a chair. Of course, you’d have new problems as well: When you went to exchange that hideous outfit you got for your birthday, you wouldn’t be able to claim it was the wrong size.

When using Microsoft Visual Basic for Applications (VBA) to write macros for Microsoft Office Excel, you don’t need to worry about Range objects as long as all your worksheets and data files are the same size. For example, if you never insert new rows into a budget, if you always put yearly totals in column M, and if every month’s transaction file has exactly 12 columns and 120 rows, you can skip this chapter because the macro recorder can take care of dealing with ranges for you.

But in the real-live human world, people are different sizes, and consequently clothes come in different sizes and cars have adjustable seats. And in the real-live worksheet world, models and data files have different-and changing-sizes, and your macros need to fit them. Excel provides many methods and properties for working with Range objects. In this chapter, you’ll explore Range objects and in the process learn how you can use the Object Browser to learn about any new, unfamiliar object.

On The CD-Important 

Before you complete this chapter, you need to install the practice files from the book’s companion CD to their default locations. See “Using the Book’s CD” on page xv for more information.

USE the Ranges.xlsx workbook. This practice file is located in the Documents\MSP\ ExcelVBA07SBS folder.

BE SURE TO save a macro-enabled copy of the Ranges.xlsx workbook as Chapter4.xlsm _in the trusted folder location you created in Chapter 1.

OPEN the Chapter4.xlsm workbook.