Getting a list of fonts

If you need to get a list of all installed fonts, you'll find that Excel does not provide a direct way to retrieve that information. In previous editions of this book, I presented a technique that read the font names from the Font control on the Formatting toolbar. Excel 2007 no longer has a Formatting toolbar, and it's impossible to access the controls on the Ribbon using VBA. For compatibility purposes, however, Excel 2007 still supports the old CommandBar properties and methods - it's just that they don't work as you would expect.

In any case, I was able to modify my old procedure so it works reliably in Excel 2007. The ShowInstalledFonts macro displays a list of the installed fonts in column A of the active worksheet. It creates a temporary toolbar (a CommandBar object), adds the Font control, and reads the fonts from that control. The temporary toolbar is then deleted.

Sub ShowInstalledFonts()

Dim FontList As CommandBarControl Dim TempBar As CommandBar Dim i As Long

' Create temporary CommandBar

Set TempBar = Application.CommandBars .Add Set FontList = TempBar.Controls.Add(ID:=172 8) ' Put the fonts into column A Range("A:A").ClearContents For i = 0 To FontList.ListCount - 1

' Delete temporary CommandBar

TempBar.Delete End Sub

As an option, you can display each font name in the actual font (as shown in Figure 11-15 ). To do so, add this statement inside of the For-Next loop:

A

B

1

Cambria

2

Calibri

3

Agency FB

4

ttybfl

5

F^bo rrfccttxr

6

C^toitäpQ?

7

ALGERIAN

3

AKBENT

9

Arial

10

Arial Black

11

Arial Narrow

12

Arial Rounded MT Bold

13

Arial Unicode MS

14

Arizona

15

16

Baskervilie Old Face

17

Bouhovj93

18

Bell MT

19

Berlin Sarii FB

20

Berlin lon$ FB Demi

Figure 11-15: Listing font names in the actual fonts.

Be aware, however, that using many fonts in a workbook can eat up lots of system resources, and it could even crash your system.

CD-ROM

This procedure is available on the companion CD-ROM. The file is named list fonts.xlsm .

0 0

Post a comment