Working with Ranges

Most of your VBA programming probably involves worksheet ranges. (For a refresher course on Range objects, refer to Chapter 8. When you work with Range objects, keep the following points in mind:

11 Your VBA doesn't need to select a range to work with it. 1 If your code does select a range, its worksheet must be active.

i The macro recorder doesn't always generate the most efficient code. Often, you can create your macro by using the recorder and then edit the code to make it more efficient.

i It's a good idea to use named ranges in your VBA code. For example, using Range(Total) is better using than Range(D45). In the latter case, if you add a row above row 45, you need to modify the macro so it uses the correct range address (D46).

i When running a macro that works on the current range selection, the user might select entire columns or rows. In most cases, you don't want to loop through every cell in the selection (that could take a long time). Your macro should create a subset of the selection consisting of only the nonblank cells.

i Excel allows multiple selections. For example, you can select a range, press Ctrl, and select another range. (Do your range selection with the mouse, of course.) You can test for this in your macro and take appropriate actions.

The examples in this section, which are available at this book's Web site, demonstrate these points.

If you prefer to enter these examples yourself, press Alt+F11 to activate the VBE. Then insert a VBA module and type the code. Make sure that the workbook is set up properly. For example, if the example uses two sheets named Sheet1 and Sheet2, make sure that the workbook has sheets with those names.

0 0

Post a comment