Listing The Font ObjectA Simple Straightforward Object

Sub DemonstrateFontObject() Dim nColumn As Integer Dim nRow As Integer Dim avFonts As Variant

Dim avColors As Variant

For nColumn = 1 To 5

With ThisWorkbook.Worksheets(1).Columns(nColumn).Font .Size = nColumn + 10

If nColumn Mod 2 = 0 Then .Bold = True .Italic = False

Else

.Bold = False .Italic = True End If End With

Next avFonts = Array("Tahoma", "Arial", "MS Sans Serif", _

"Verdana", "Georgia") avColors = Array(vbRed, vbBlue, vbBlack, vbGreen, vbYellow)

With ThisWorkbook.Worksheets(1).Rows(nRow).Font .Color = avColors(nRow - 1) .Name = avFonts(nRow - 1)

If nRow Mod 2 = 0 Then .Underline = True

Else

.Underline = False End If With

Next End Sub

The output of Listing 10.2 is shown in Figure 10.3. One thing that this example demonstrates is the use of the With.. .End With statement. When you perform formatting operations, you'll commonly need to execute multiple formatting statements on a given range. Using With.. .End With saves time in the development process by reducing the amount of keystrokes you need to enter, and it also performs better at run-time.

The DemonstrateFontObject procedure demonstrates a few things that you haven't looked at for a while. The VBA Mod operator divides one number by another and returns the remainder. In this procedure Mod is used to check whether a number is even or odd.

Another technique you haven't used for a while is the use of a variant array. DemonstrateFontObject uses two variant arrays—one to store five colors and another to store five font names. Notice that you can use the Array function to create the array. By placing these items in an array you can easily assign font names and colors as you loop through the rows in Listing 10.2.

Figure 10.3

This is the output of the Demonstrate-FontObject procedure.

Figure 10.3

This is the output of the Demonstrate-FontObject procedure.

E3 Microsoft Office Excd Z003 Beta - Chapter 10 Examples.xls

BO®

p

Filo Edt Vbtw Insert Format Tools

Data Window Help

Tyiio a qoestian 1er help * _

5 X I

is

^ [ Tiloma

. 11 - B

ÜÍB1-JI* - -A it ^

EË - • - J

: -J

"Ii "J -I ¡¿ J

3* ¡3- Al flj 1 r ÄS* weh liiaroeî.

Al

f* Font Test

A

B

c

D

E

1

Font Test

Font Test

Font Test

Font Test

Font Test

2

Font Test

Font Test

Font Test

Font Test

Font Test

3

Font Test

Font Test

Font Test

Font Test

Font Test

A

Font Test

Font l est

Font Test

Font Test

Font Test

5

V

M *

M\sheetl ■ 1 <

.iL

-'I

\ Ready

The Patterns tab of the Format Cells dialog box is the public face of the functionality afforded by the Interior object.

Figure 10.4

The Patterns tab of the Format Cells dialog box is the public face of the functionality afforded by the Interior object.

Number Alignment Font

Border | Patterns | Protection

Coll shading Color:

NoCcfor

□ BDLIBDBD IIDQIIII

Sample

Pattern: ¿¡1

TIP The Macro Recorder is an excellent toolfor learning about many of the objects covered in this chapter. I frequently use the Macro Recorder when I need to perform extensive formatting—especially on objects that I haven't had to use for a while. Though the code produced by the Macro Recorder is usually inefficient and rigid, it is easy to tweak the resulting code to your needs. If nothing else, it is an easy way to discover the objects, properties, or methods that you need to use to achieve the task at hand..

0 0

Post a comment