Listing A More Flexible Procedure for Working with Structured Ranges

Sub RigidProcedureDeRigidized()

Dim ws As Worksheet

If Not WorksheetExists(ThisWorkbook, "Test Report") Then

MsgBox "Can't find required worksheet 'Test Report'", vbOKOnly Exit Sub

End If

Set ws = ThisWorkbook.Worksheets("Test Report")

If RangeNameExists(ws, "REPORT_TITLE") Then _ ws.Range("REPORT_TITLE").Font.Bold = True

If RangeNameExists(ws, "REPORT_DATE") Then With ws.Range("REPORT_DATE") .Font.Bold = True .NumberFormat = "mmm-yy" .EntireColumn.AutoFit End With

End If

If RangeNameExists(ws, "ROW_HEADING") Then _ ws.Range("ROW_HEADING").Font.Bold = True

If RangeNameExists(ws, "COLUMN_HEADING") Then _ ws.Range("COLUMN_HEADING").Font.Bold = True

If RangeNameExists(ws, "DATA") Then _

ws.Range("DATA").NumberFormat = "#,##0"

If RangeNameExists(ws, "COLUMN_TOTAL") Then With ws.Range("COLUMN_TOTAL")

.Formula = "=SUM(R[-9]C:R[-1]C)" .Font.Bold = True .NumberFormat = "#,##0" End With

End If

If RangeNameExists(ws, "ROW_TOTAL") Then With ws.Range("ROW_TOTAL")

.Formula = "=SUM(RC[-12]:RC[-1])" .Font.Bold = True .NumberFormat = "#,##0" End With

End If

Set ws = Nothing End Sub

NOTE See Listing 7.2 for the WorksheetExists procedure. See Listing 8.5 for the RangeNameExists procedure.

This procedure fulfills the same responsibility as Listing 8.10. Yes, it is longer. Yes, it still contains literal values. However, the payoff is huge. Here is why. Rather than rigid range addresses, the literal values are now the names of named ranges. As you saw earlier, it's easy to validate named ranges. Further, named ranges insulate you from most of the effects of changing the worksheet's structure. If you add or remove rows or columns, the named ranges adjust accordingly. If you need to adjust the range boundaries of a named range, you can do it from the worksheet rather than modifying code. For example, let's say you need to add a new expense line and insert two more rows between the report title and the column headings. The new worksheet structure is shown in Figure 8.15. The new rows are highlighted. The original procedure is shown in Figure 8.16.

As you can see, a little extra work can really pay off. Because you used named ranges, you now have the ability to perform validation on the expected regions in your report and can make structural changes to the worksheet without having to modify your procedures. If need be, you can simply adjust the range that a named range refers to from Excel rather than modifying your code.

Figure 8.15

How will your two formatting procedures handle this new structure?

EU Mowpfl 't' 1 R8i>) 2003 t'. l a

t.hapter ft i V I;I [.!:'. v 1,

QlEjg

| : Fits Bit ViBhv Insert: Fornat Tools Uz' Whdow Help

1 ; Type a qjsstion for help

-.ax

UjJJ £ -J loo^t

- S

el» - m

J u

ïitii

ni , » .0 lod /o t .00 j-.o

SÉ iS

-1 -

I

i J

■j äjs ii -L

-

1 E

A

B

c

D

E

F

ni

H

1

m

m

L

M

o -

= A

1

Occupant;/ Expense Detail

2

37665

3

5

Prepared try S.Hansen Midwest Region

1

Ei

7

S

Jan

Fab

Mar

Apr

May

J un

Jul

Aug

Sep

Ocl

New

Dae

9

Rent & Lease

1000

100

100

100

100

100

100

too

100

100

100

100

111

Cleaning

1ÜÜÜ

100

100

100

too

100

100

100

100

100

100

100

11

RiMi' Contract

100

100

tOD

100

100

100

10Ü

100

1ÜO

1DD

100

ICQ

12

Utilities

100

100

100

1C0

100

100

100

too

100

100

100

100

13

Other Occupan

100

1CG

too

100

100

100

100

too

100

100

100

ICO

U

Taxes

100

100

too

100

100

100

100

100

100

100

100

100

15

Depreciation &

100

100

too

100

100

100

100

100

100

100

100

100

16

Maintenance E'

100

100

tOD

100

100

100

100

TDD

100

1DD

100

100

17

Furniture and E

100

100

too

100

10D

100

100

too

100

100

100

100

18

Gain^Loss on D

100

100

too

100

100

100

1CO

100

100

100

100

ICO

19

Tool

20

21

V

22

1 1

-j

v <

1 Reedy

The original procedure fails miserably.

S Microsoft Office EkccJ 2003 Beta - Chapter 8 Examples,kIs

I Fr'e Edit YIew Insert Format Tools *. Whijw H:Jp

type a question for he£

¡aJAil * I -a *i JJ I rx^o -Atii'j»»» endjiMm..

I Fr'e Edit YIew Insert Format Tools *. Whijw H:Jp

A

B

C

t

E

F

G

H

-1

K

L

M

N

1

Occupancy Expense Detail

2

Sap-03

3

1

J

Prepared by S.Hansen

5

Midwest Region

6

7

0

a

Jan

Feb

Mar

Apr

May

Jun

Jul

Aus

Sep

0 c!

Nov

Dec

P

y

Rem & Lèiisè Payment

1,000

100

100

100

100

100

100

too

100

TOD

100

100

2,100

10

Clean in <j

1,010

100

1 DO

100

100

ICO

¡00

100

100

100

ICO

100

2,100

11

RAM Contract Services

100

¡00

I DO

100

100

100

¡00

IDG

100

1DD

ÏDQ

¡00

1,200

12

Utilities

1 DO

100

1 DO

100

100

100

100

IDG

100

10D

1G0

100

1200

1,3

Other Occupancy

1C0

100

too

100 10D

100

100

IDG

100

10D

ICO

100

1.200

11

Taxes

1C0

100

too

100

100

100

100

100

J DO

100

100

100

1.200

IS

Depredation & Amortization

100

100

100

100

100

100

100

100

100

500

100

100

1.200

w

Maintenance Expense

2,M0

il)P

/du

roo

/PU

TOO

/uu

fW>

«Il

/pit

700

roo

10.200

17

Furniture and Equipment

100

100

too

100

100

100

100

too

100

100

100

100

m

G ai fi'Loss on Disposal

100

100

too

100

100

100

100

100

100

100

ICO

100

is

Total

20

h

► m IX Test ftepart original procedure /

Test '

<

The revised procedure runs flawlessly.

S Microsoft Office EkcH 2003 Bcto - Chapter 0 EKamptcsjils

I Edfc yew Insert Fermai locte Qata ^jndowi Ijeip

S Microsoft Office EkcH 2003 Bcto - Chapter 0 EKamptcsjils

I Edfc yew Insert Fermai locte Qata ^jndowi Ijeip

! -J ^ ■ ■ 1 ii tiu 1

End&Hda

d

s

n - *

A

e

C

D

E

F

G 1

H

!

J

K

L

M

M 1 -g

1

Occupancy Ex pens« Net* it

■ ïï

2

a

t

I:'■t:H:M by S.Hansen

5

Wildwest Re (lion

6

7

8

j.m

F el]

M.ii

Äpi

M.iy

Jtin Jul

Ally

Se|>

Oct

Nov

Dec

Tut.il

a

Rem & Leas« Payment

1 .00

1 DO

100

100

:0C

100

ILO

■OD

100

1 DO

100

100

2.100

10

Cleaning

I.COO

1 DO

100

100

1 DO

100

100

100

100

1 DO 100

100

2.100

il

R&M' Contract Services

100

1 DO

100

100

100

100

100

100

100

1 DO 100

100

1.200

12

Utilities

10Ü

100

100

100

too

100

100

100

100

1 DO

100

100

1.200

13

Other Occupancy

ion

100

100

100

too

100

100

IM)

100

100

100

100

1.200

Id

Taxes

100

ID0

100

100

100

100

100

100

100

1 DO

100

100

1.200

15

Depieciation & Amoitlzatlm

too

1 DO

100

ICO

1DO 100

irai

100

100

1 DO

100

100

1,200

16

Maintenance Expense

too

1 DO

100

100

100

100

ICO

100

100

IDO

10d

ICO

1.200

17

Furnituie ami Equipment

too

100

100

100

100

100

100

100

100

1 DO

100

100

1.200

18

Gain Loss on Disposal

10U

100

100

100

ICO

100

100

100

100

100

100

100

1.200

19

Total

1.SUQ

»00

900

M0

0M

300

900

000

900

000

900

MO

11,700

23

l< 4

► H[VTest Report revised procedure

<

> I

Ready

0 0

Post a comment