The Range object is the most important Excel object to know if you want to become proficient developing in Excel. As with most things, a solid understanding of the fundamentals is more important than knowledge of all of the exotic and little-used features or techniques. When it comes to the Range object, the fundamentals consist of referring to specific ranges, navigating around a worksheet, and handling input/output operations.

It's extremely easy to use the Range object to create rigid, though fragile, applications. You could refer to worksheets by name in your procedures and refer to ranges using standard Al-style addresses whenever you need to reference a certain range. Hopefully you have an appreciation of why it isn't a good idea to do this unless you are developing a quick, one-off utility for your own use. Referring to objects using literal values is error prone, makes modifications unnecessarily difficult, and can limit the potential for reuse.

In order to avoid creating fragile applications, you must consider how your procedures will interact with worksheets and ranges. In addition to the standard defensive tactics such as validating the existence of specific worksheets and named ranges, you can "feel" your way around a worksheet examining values, formatting, or identifying other characteristics to find a range of interest. In order to move around a worksheet, you can use the Cells property of the Worksheet object, the Range property of the Worksheet object, the End property of the Range object, and the Offset property of the Range object.

Displaying output to a worksheet is accomplished using the Value property of the Range object. Again, the primary difficulty is accounting for the possible actions an end user can take that have the possibility of causing run-time errors due to the assumptions you build into your procedures. If you collect input from a worksheet, the difficulty level increases because you need to account for the possibility that the data type may not be the type your procedure is expecting. Well-written procedures always contain a dose of defensive programming aimed at validating the assumptions made during the development process.

In the next chapter, you'll examine some of the more useful of the many properties and methods of the Range object.

0 0

Post a comment