Listing Be Wary of Procedures that Contain a Lot of Literal Range Specifications

' this is an example of a rigid procedure ' rigid procedures are generally error prone ' and unnecessarily difficult to maintain/modify Sub RigidFormattingProcedure()

' Activate Test Report worksheet ThisWorkbook.Worksheets("Test Report").Activate ' Make text in first column bold ActiveSheet.Range("A:A").Font.Bo1d = True ' Widen first column to display text ActiveSheet.Range("A:A").EntireCo1umn.AutoFit ' Format date on report

ActiveSheet.Range("A2").NumberFormat = "mmm-yy" ' Make column headings bold ActiveSheet.Range("6:6").Font.Bo1d = True ' Add & format totals

ActiveSheet.Range("N7:N15").Formu1a = "=SUM(RC[-12]:RC[-1])" ActiveSheet.Range("N7:N15").Font.Bo1d = True ActiveSheet.Range("B16:N16").Formu1a = "=SUM(R[-9]C:R[-1]C)" ActiveSheet.Range("B16:N16").Font.Bo1d = True ' Format data range

ActiveSheet.Range("B7:N16").NumberFormat = "#,##0" End Sub

If I could guarantee that the format of this report would never change, that none of the items on the report would ever appear in a different location, and that no other procedures in your entire project would reference the same ranges as this procedure, then I'd consider letting this procedure slide if it wasn't for the use of Activate in the first statement.

In practice however, it's rarely the case that anything remains static. Granted, often after the development process is complete, things may stay static for long periods of time. However, during the development and testing process, things change—perhaps repeatedly. It only takes one type of change to break nearly every line in this procedure—basically any change that results in the location of items shifting in the worksheet, such as when a new row or a new column is added. This may not seem like a big deal in this example because it's only one small procedure. Realistically though, your projects may consist of multiple modules, each containing a handful to dozens of procedures. Do you really want to revisit each procedure every time a change is made that violates your original assumptions?

Figure 8.13

Results of applying basic formatting to the report shown in Figure 8.12

E2 Microsoft Office Eneel 2003 Beta - Chapter 6 EKarnples.xls

Fie Edit View Insert FEfrast lools fete Mndow Heb

A

B

C

o

e

F

G

H

I

J

K

L

1

Occupant 1 : Detail

2

Sep-03

3

t

5

Ë

J rill

Fel>

M.i >

11

May

J II II

Jill

Aug

Sep

Oct

Nov

7

Rent & Lease Payment

ipon

100

100

100

100

100

100

100

100

100

100

8

Cleaning

1,000

100

100

tpci

too

100

100

100

100

100

100

0

RAM Csnll.lit Scivices

100

100

100

100

too

100

100

100

100

100

100

1U

llliliti HH

100

100

100

100

100

100

100

100

100

too

100

It

0 th e I Occupancy

100

100

100

100

100

100

100

100

100

(00

100

12

Taxes

100

100

100

100

too

100

100

100

100

too

100

13

Depieciation & Amortization

100

100

100

1CO

100

100

100

100

100

too

10D

■ 14

Furniture anil Equipment

100

100

10]

1C0

IOC

100

100

100

100

100

10D

15

Oarn Loss cit Disposal

100

100

10]

100

100

100

100

100

100

too

100

16

Total

2.700

900

900

soo

900

000

000

90«

900

900

900

17

tfl

tWam8S~VTedRepp.t f Testiepoit[Jodilied <

Rtady tWam8S~VTedRepp.t f Testiepoit[Jodilied <

Rtady

Figure 8.14

By naming sections of a report, you can help insulate your code from structural changes to the worksheet.

Figure 8.14

By naming sections of a report, you can help insulate your code from structural changes to the worksheet.

So, what could you do to minimize the impact of changes? First, you can place every literal or hard-coded value that you can't reasonably avoid in locations that are easy to modify if necessary— especially literal values used in more than one place. Second, you can seek to limit the use of hard-coded values in the first place.

TlP Ifind that using syntax highlighting (in the VBE, select Tools ^ Options and then choose the Editor Format tab) is a big help in quickly identifying/locating places in a project where literal values are being used.. I prefer to set the background color to light gray and theforeground color to red for Normal Text. Then I set the background color to light gray for Comment Text, Keyword Text, and Identfier Text. Finally, I set theforeground color as I see fit. Hard-coded values are visually apparent now.

Take a look back at Listing 8.10. One way you can protect yourself from this type of problem is to use named ranges. The benefit of using named ranges here is that if you insert or remove rows or columns, named ranges are much less likely to be affected. Figure 8.14 shows how you could name the various sections of this report.

Assuming you create the range names shown in Figure 8.14, Listing 8.11 provides you with an example of a much more robust procedure. This procedure uses the WorksheetExists procedure presented in the last chapter as well as the RangeNameExists procedure shown in Listing 8.5.

0 0

Post a comment