Programming Excel with VBA and .NET

Chapter 9. Working with Worksheets and Ranges

If workbooks are the documents of Excel, then worksheets and ranges are the chapters and paragraphs, and individual cells are the words. Most of the work you do takes place on a worksheet and involves manipulating ranges of cells or individual cells. From a programmer’s perspective, you are most often working with Worksheet and Range objects, although of course you use other objects to accomplish specific tasks.

One concept that any beginning Excel programmer encounters is that, within the world of Excel objects, a cell is not a Cell object—it is a single-cell Range object. So you will often use a Range object to manipulate individual cells. You use a Worksheet object to control what happens at the worksheet level, and you use a Range object whenever you work with a cell or cells.

In this chapter, I show how to:

• Work with worksheets

• Get cells in a worksheet

• Work with the Sheets collection

• Work with outlines

• Work with ranges

• Find and replace text in a range

• Use named ranges

• Format and change text

• Work with scenarios

This chapter includes task-oriented reference information for the following objects and their related collections: Worksheet, Outline, Range, and Scenario.

Tip

Code used in this chapter and additional samples are available in ch09.xls.