This chapter has shown you the most important properties and methods that can be used to manage ranges of cells in a worksheet. The emphasis was on techniques that are difficult or impossible to discover using the macro recorder. The properties and methods discussed include the following:

□ Activate method

□ Cells property

□ Columns and Rows properties

□ CurrentRegion property

Offset property

Range property

Resize property

Select method

SpecialCells method

Union and Intersect methods

You also saw how to assign a worksheet range of values to a VBA array for efficient processing, and how to assign a VBA array of data to a worksheet range.

This chapter also emphasized that it is very rarely necessary to select cells or activate worksheets, which the macro recorder invariably does because it can only record what you do manually. Activating cells and worksheets is a very time-consuming process and should be avoided if you want your code to run at maximum speed.

The final examples showed that it is usually best to utilize Excel's existing capabilities, tapping into the Excel object model, rather than to write a VBA-coded equivalent. And bear in mind that some Excel techniques are better than others. Experimentation might be necessary to get the best code when speed is important.

0 0

Post a comment