With Selection.Font

.Name = "Arial" (unchanged) .FontStyle = "Regular" (unchanged)

'.Strikethrough = False (unchanged) '.Superscript = False (unchanged) '.Subscript = False (unchanged) '.OutlineFont = False (unchanged) '.Shadow = False (unchanged) '.Underline = xlUnderlineStyleNone (unchanged) '.Colorlndex = xlAutomatic (unchanged) '.Background = xlAutomatic (unchanged) End With End Sub

Here is the macro with all the unneeded lines removed:

Sub Macro2()

ActiveChart.ChartArea.Select With Selection.Border .Colorlndex = 3 .Weight = xlMedium End With

Selection.Shadow = True With Selection.Interior

.Colorlndex = 34 End With

Selection.AutoScaleFont = False With Selection.Font

.Size = 14 End With End Sub

You can see that when we limit the code to only those items we are changing, it is much more compact. You can make the code even slicker.

Because this section of the code is meant for the chart area only, declare an object variable to stand for the chart area. Use this variable for all the remaining code. Notice that with VBA, you need not actually select the chart area to format it. The following code works even if a sheet other than the chart sheet is active at the time the macro is run:

Sub ChartArDemo()

Dim ChtArea As ChartArea

Set ChtArea = Charts("Chart1").ChartArea

With ChtArea

.Shadow = True With .Border

.Colorlndex = 3 .Weight = xlMedium End With

.Interior.Colorlndex = 34 .AutoScaleFont = False .Font.Size = 14 End With End Sub

A Word About Using Colors

When you use the line, fill, or font color from the Formatting toolbar or Format dialog box, Excel displays a palette of 56 colors to choose from. Excel comes with its own default color palette, which you can modify. You can define your own color palette on the Color tab in Tools, Options by selecting the color you want to change and clicking the Modify button. Your modified palette is saved with the workbook. The palette colors can be set or accessed from the workbook's Colors property.

Thus, instead of .Border.ColorIndex = 3

you can write

.Border.Color = ThisWorkbook.Colors(3)

You will notice that the order of the colors as they appear in the palette is not the same as the ColorIndex value. For example, in the default color palette, red (row 3, column 1) has a ColorIndex value of 3, whereas turquoise (row 4, column 5) has a ColorIndex value of 8. This can be confusing. The easiest way out is to record a macro for setting a particular color and use the ColorIndex value from the recorded code into your own code.

All colors can be derived from the three monitor colors: red, green, and blue. VBA has a RGB function to enable full control over the colors you use by specifying different intensities for each of these three components. The syntax is RGB (red, green, blue); each of the arguments can have a value from 0 to 255. For pure red, the RGB equivalent would be RGB(255,0,0). Thus, instead of .Border.ColorIndex = 3

you can write (in RGB terms) .Border.Color = RGB(255,0,0)

0 0

Post a comment