Figure

Output produced by the UsingRange procedure from Listing 8.3.

L ' MiLrubufl ( Hi. r LhlcI ZOQ3 BeUi - GhnpttS 0 f xam|il».xl*

|;f] Fie Edit

View

Insert

Format

Iwk Window Hefp

Miype a questicn for help

- s

X

! £

» 100%

: 10 .

B I U S

S !fi.| £ . :a! ,

>,c> ir- "P-

: I m - 3»

- A À

i

; _J

j ^ ■

% Jj2 tiMJ.

f J Reply. v«ith Changes-,

A11

A

1 B"

c

D

E

F

G

H

1

J

1

3

1 1

2

1

1 _1_

3

t

S

6

7

8

9

10

1

It

I !

V

N 4

► M \Sheet 1 / Sheet27I<

>J

| Ready

Listing 8.3 demonstrates a couple of formatting-oriented properties of the Range object: the Font Property, which returns a Font object, and the HorizontalAlignment property. Once you set a reference to the range that you need to format, formatting is a snap. Again, the approach is to work with groups of cells collectively rather than by looping through individual cells and applying formatting to each cell individually.

Finally, Listing 8.3 illustrates one technique I find myself using constantly. Consider the following statement from Listing 8.3:

Set rg = ws.Range(ws.Ce11s(1, 1), ws.Ce11s(10, 10))

This statement sets a reference to a range of cells by specifying the top-left cell and the bottom-right cell using the Cells property. Why not just use ws.Range("A1:J10")? The reason is that many times you don't know what the address of the range will be at development time. For example, if you are working with a list of user-entered data, you won't know how many rows the user will enter. You may know the top-left cell, but you'll probably have to dynamically determine (or determine at run-time) the bottom-right cell. This is exactly the kind of situation in which using the Cells property makes sense.

Referring to Named Ranges Can Be Tricky

Named ranges can give you fits. Part of the problem is that many people don't have a basic understanding of, or even realize that there are, two kinds of named ranges in Excel.

Workbook-named ranges are probably the most common type of named range. Workbook-named ranges can be used in formulas throughout the workbook by entering the name of the range as shown in Figure 8.3.

Worksheet-named ranges are named ranges that are specific to a worksheet. You can refer to a worksheet-named range within the same worksheet by entering the name of the range in a formula. On other sheets, however, you must prefix the name of the range with the name of the worksheet. Whereas workbook named ranges must be unique, worksheet named ranges only need be unique to the worksheet in which they are created. As you can see in Figure 8.4, to create a worksheet named range, you include the name of the worksheet when specifying the named range.

0 0

Post a comment