Listing Experimenting with Format Codes

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = Me.Range("FormatCode").Address Then

ApplyFormatCode End If End Sub

Private Sub ApplyFormatCode()

' if we attempt to apply an invalid

' number format code an error will ' occur - we need to catch it On Error GoTo ErrHandler

' clear any prior invalid code message Me.Range("FormatCode").0ffset(0, 1).Va1ue = "" ' attempt to apply the format code Me.Range("TestFormatCode").NumberFormat = _

Me.Range("FormatCode").Va1ue Exit Sub

ErrHandler:

' oops - invalid format code ' set the format to General

Me.Range("TestFormatCode").NumberFormat = "General" ' let the user know what happened

Me.Range("FormatCode").0ffset(0, 1).Va1ue = "Invalid Format Code!" End Sub

TIP Rather than struggle through creating a complex format code from scratch, see if you can start with one of the predefined codes defined on the Number tab of the Format Cells dialog box. Once you select one of the predefined number formats, you can switch to the Custom category and the format code of the number format you selected will be displayed in the Type text box. You can copy the format code displayed into your code or use it as the basis for your more complex format code.

Figure 10.11

Testing number format codes using the ApplyFormatCode procedure

TIP Rather than struggle through creating a complex format code from scratch, see if you can start with one of the predefined codes defined on the Number tab of the Format Cells dialog box. Once you select one of the predefined number formats, you can switch to the Custom category and the format code of the number format you selected will be displayed in the Type text box. You can copy the format code displayed into your code or use it as the basis for your more complex format code.

Figure 10.11

Testing number format codes using the ApplyFormatCode procedure

E3 Microsoft Office Ewcel Z003 Bela - Chapter 10 Examptesjils

BS®

m

Fils Edt Vieiw Ercscrt Format TdoIs Data Wtidaw Hdp

- 5

1

]M

¿;l0 . D / = = = m ' TiS -is 1 I? ^ Lü ""

à.-

J

\ -j

..: J ! ■ J J 1 Ü) * Él ÎIJ 1 ßHSes

.M& i. I

1 FormstCode * « [Redl(ftfflü.00). [Redl- - "

A B

c ■

A

1S

16

N iter Formât Co Je: »«Dt: [Rsi|pr«0. D01: IfWl 1

I

17

IS

10

19

pJ.OO)-

20

34

21

5,000

22

345.334

23

676

24

p.ôbj

25

1

26

1,232

27

36

7P

V

H <

> M >uml)er formats / 5tälnn .|

_si

1 Ready

Save Tons of Time When Scaling Worksheets

How many worksheets have you seen or created that multiplied each value by .001 or divided by 1000 to scale a worksheet by 1000? There is a much, much better way to do this. Scaling cell values by modifying the actual value of the cell in this manner is not a good idea. Although it solves the presentation aspect by showing numbers in thousands, it creates other problems. One of the biggest is that any dependent ranges need to know how the worksheet was scaled and act accordingly. In a large, complex model this can get messy really fast. Another problem is that in some instances, you may want to see the real, unscaled value. For example, if you're viewing an income statement for a large division in a company, scaling the numbers makes sense. On the other hand, the manager of a single cost center or department may be managing to the dollar (or hundreds of dollars) and is interested in seeing the unscaled numbers. The process of using formulas to scale values doesn't lend itself to switching back and forth very easily.

The answer, of course, is to leave the underlying values alone and scale numbers for presentation purposes using number formats. That's right, you can scale numbers using number formats. Take a look back at Figure 10.10 row 12. The underlying value is 1,234, but the value displayed is 1.

You can easily scale worksheets on the fly if you know ahead of time the range that contains the values to be scaled. For example, consider the worksheet shown in Figure 10.12.

It is a piece of cake to provide scaling capability to this report. Notice that the selected range is named ScaleRange. All you need to do is create a procedure that sets a reference to the ScaleRange and sets its number format appropriately.

Figure 10.12

A simple income statement report

D Microsoft Excel - Chapter 10 EKamples.Kfs

Bfoiisn

I i'éJ Eile Édt lreert Format lools

Data WÉidaw Hdp

.3x1

ijjlgjlo Hi = =

A - J

1 ScaleRanp - f, 15600

A 1

B I

I C I

D

E

_rsl

1

SIMPLS INCOME STATEMENT

2

SIMPLE CORP.

3

Marember 29, 2003

^

5

Seated

Norma!

6

7

J ¿in

Feb

Mär

1st Ore

a

SALARIES

15,500

15SOO

15,500

40,500

a

COMMISSIONS

2,500

2500

2,500

7.500

10

FRINGE BENEFITS

2.000

2 an

2,000

6.000

ii

OTHER EMPLOYEE EXPENSES

300

3J0

300

900

12

TRAVEL & ENTERTAINMENT

750

750

750

2,250

13

OTHER COS

100

100

100

300

14

FREIGHT & POSTAGE

750

750

750

2.250

15

MANUFACTURING

10.000

T.UUU

10.000

30.000

16

MARKETING

5.000

5.QQ0

5.00Q

15,000

17

TELEPHONE L NETWORK CHARGES

2 pOO

2.000

2,OK)

0,000

ie

STATIONARY & SUPPLIES

700

700

700

2,100

19

MAINTENANCE & FACILITIES

0,000

B.OOO

8,OH)

2-1.000

JH

20

PROFESSIONAL FEES

1.000

1.000

1.000

3.000

21

DEPRECIATION

3.000

3£00

3.000

9,000

22

MISCELLANEOUS EXPENSES

1,500

15QD

1,500

J.500

23

TOTAL EXPENSES

■ 53,100

■ 53.100

53,100

159,300

24

V

Ii 1

r H1 Njrnher Formats Xbfälirtij/ Basl<

■Éill'l^M'

1 Ready

Sum-637,200

0 0

Post a comment