MS-Excel / Excel 2003

Modifying the settings for VBA properties

Even when you don't know anything about programming in VBA (and aim to keep it that way), you can get the gist of the more obvious properties in a macro that change certain settings, such as number format or font attribute, by experimenting with assigning them new values.

For example, in the code of the Company_Name macro, you can probably identify the section of VBA commands that begins with the line With Selection.Font and ends with the line End With as containing the procedure for assigning various font attributes for the current cell selection.

Going a step further, you probably can figure out that most of these attributes are being reset by making the attribute equal to a new entry or value, such as

.Name = "Arial"

which sets the text font to Arial, followed by

.Size = 12

which sets the text size to 12 point. You can also see in this section that particular attributes are being turned on by setting them equal to True or False, such as

Selection.Font.Bold = True

which makes the text in the current cell selection bold.

You can make your macro behave differently just by doing some careful editing of these settings. For example, if you want the final font size to be 24 points instead of 12, all you have to do is edit the Size property by changing its value from 12 to 24 as follows:

.Size = 24

Likewise, you can have the macro apply single underlining to the cell selection by editing the xlUnderlineStyle property from none to single as follows:

.Underline = xlUnderlineStyleSingle

When the allowable settings for a particular property are not obvious (such as in the case of the Underline property), select the property in the Code window without selecting its current setting and then Press F1 to open the VBA Help window with information on that property. Usually the Example section at the bottom of this Help window gives you an idea of the different types of values that the property can take.

[Previous] [Contents] [Next]