Facelds

The following code gives you a table of the built-in button faces, as shown in Figure 15-6. There are more than 15,000 faces in Office 2007. Note that many FaceId values represent blank images, and that the same images appear repeatedly as the numbers increase.

Note that when you run this macro, your computer may well freeze for a while. The CPU has to do a lot of hard work! You can follow the macro's progress by watching the status bar.

ListAllFaces creates a temporary toolbar, cbr, using the Add method of the CommandBars collection. The toolbar is declared:

□ Temporary, which means that it will be deleted when you exit Excel, if it has not already been deleted

□ Floating, rather than docked at an edge of the screen or a popup

□ Not to be a menu bar, which means that cbBar will be a toolbar

A temporary control is added to cbr, using the Add method of the Controls collection for the command bar, and assigned to ctl.

The Do...Loop continues looping until there are no more valid FaceId values. The Do...Loop increments iRow, which represents the row numbers in the worksheet. On every row, iColumn is incremented from 1 to 10. j represents the columns of the worksheet. The value of iRow is increased by 1 for every iteration of the code in the For...Next loop. iFaceId represents the FaceId. The FaceId property of ctl is assigned the value of iFaceId, and the resulting image is copied to the worksheet.

Some button images are blank and some are missing. The blank images are copied without error, but the missing images cause an error number 1004. When an error occurs, the code branches to the error trap at Recover:. If the error number is 1004, the code resumes executing at the statement after the one that caused the error, leaving an empty slot for the button image. Eventually the code gets to the last FaceId in Office. This causes error number -2147467259. At this point the code clears the status bar, removes the temporary command bar, and exits.

The information you have gathered with the last three exercises is not documented in any easily obtainable form by Microsoft. It is a valuable guide to the built-in button faces at your disposal. There is an add-in application, CBList.xla, available with the code that accompanies this book that makes it easy to generate these lists.