VBA uses the Chart object to represent a chart on a chart sheet and a ChartObject object to represent an embedded chart on a worksheet. The ChartObject object contains a Chart object, which you can manipulate by accessing it through the ChartObject object. Now you see one reason why object classification schemes can be a bit confusing: the ChartObject object seems a bit redundant, doesn't it?

Whichever approach you use, when writing a procedure you create the chart or chart object in a different order than when working interactively and doing things by hand within Excel. Here are the steps you take when creating charts programmatically (via code rather than interactively via a mouse and keyboard):

expression.Add(Before, After, Count, Type)

Dim myChartSheet As Chart
Set myChartSheet = ActiveWorkbook.Sheets.Add _
    (After:=ActiveWorkbook.Sheets(ActiveWorkbook.Sheets.Count), _
    Type:=xlChart)

expression.Add(Left, Top, Width, Height)

Dim myChartObject As ChartObject
Set myChartObject = ActiveSheet.ChartObjects.Add(Left:=200, Top:=200, _
    Width:=400, Height:=300)

So far, the chart (on the chart sheet or in the Chart object) is blank. To give it contents, specify the chart's source data by using the SetSourceData method of the Chart object. For example, the following statement specifies the range A1:E5 on the worksheet named Chart Data in the active workbook as the source data of the Chart object in the ChartObject object named myChartObject:

myChartObject.Chart.SetSourceData Source:= _
    ActiveWorkbook.Sheets("Chart Data").Range("A1:E5")

To specify the chart type, set the ChartType property of the Chart object. Excel offers too great a variety of charts to list here (73 different types), but you can easily identify the chart types from their enumeration constant names. For example, the constant xl3DArea represents the 3-D Area chart type, xlColumnStacked represents the Stacked Column chart type, and xlDoughnutExploded represents the Exploded Doughnut chart type.

The following statement sets the type of the chart represented by the object variable myChart to the Stacked Column type:

myChart.ChartType = xlColumnStacked

To work with the series in a chart, you use the SeriesCollection collection, which contains all the series in the specified chart.

expression.Add(Source, Rowcol, SeriesLabels, CategoryLabels, Replace)

Sub test()

Dim myChartObject As ChartObject
Dim MyChart As Chart

Set myChartObject = ActiveSheet.ChartObjects.Add(Left:=100, Top:=100, _
    Width:=400, Height:=300)

Set MyChart = myChartObject.Chart
MyChart.ChartType = xlConeBarStacked

MyChart.SeriesCollection.Add _
 Source:=ActiveSheet.Range("A4:K4"), Rowcol:=xlRows

End Sub

Extending an Existing Series

To extend an existing series, use the Extend method with the appropriate SeriesCollection object. The syntax is as follows:

expression.Extend(Source, Rowcol, CategoryLabels)

Here are the components of this syntax:

• expression is a required expression that returns a SeriesCollection object.

• Source is a required Variant argument that specifies the source of the data for the new series. You can supply the data either as a range or as an array of data points.

• Rowcol is an optional argument that you can set to xlRows to specify that the new values are in rows in the specified range or use the default setting, xlColumns, to specify that the new values are in columns. If you omit this argument, Excel uses xlColumns.

• CategoryLabels is an optional Variant argument that you can set to True to specify that the first row or column contains the name for the category labels or set to False to specify that it does not contain them. If you omit this argument, Excel tries to work out whether the first row or column contains a category label. It's best to specify this argument to avoid confusion. If Source is an array, VBA ignores this argument.

Figure 23.1. This chart was generated in a procedure, using the Add method of the SeriesCollection object.

For example, the following statement extends the series in the chart identified by the object variable myChart using the data in the cells P3:P8 on the worksheet named Chart Data:

myChart.SeriesCollection.Extend _
Source:=Worksheets("Chart Data").Range("P3:P8")

myChart.SeriesCollection.NewSeries

To add a legend to the chart, set its HasLegend property to True. To manipulate the legend, work with the properties of the Legend object. Key properties include these:

For example, the following statements add the legend to the chart represented by the object variable myChart and apply 16-point Arial font to it:

With myChart.Legend
    .HasLegend = True
    .Font.Size = 16
    .Font.Name = "Arial"
End With

To add a title to the chart, set its HasTitle property to True, as in this example:

myChart.HasTitle = True

Excel adds the title with the default text Chart Title. To change the text, set the Text property of the ChartTitle object, which represents the chart title. Here's an example:

myChart.ChartTitle.Text = "Industrial Disease in North Dakota"

To position the title, set its Top property (specifying the number of points from the top edge of the worksheet) and its Left property (specifying the number of points from the left edge of the worksheet), as in this example:

With myChart.ChartTitle
    .Top = 100
    .Left = 150
End With

To format the text of the title, work with its Font object, as follows:

myChart.ChartTitle.Font.Name = "Arial"

To work with an axis of a chart, use the Axes method to access the appropriate axis. The syntax is as follows:

expression.Axes(Type, Group)

Here, expression is a required expression that returns a Chart object. Type is an optional Variant argument that specifies the axis to return. Use xlValue to return the value axis, xlCategory to return the category axis, or xlSeriesAxis to return the series axis (on 3D charts only). Group is an optional argument that you can set to xlSecondary to specify the second axis group instead of xlPrimary (the default setting), which specifies the first axis group.

For example, the following statements work with the category axis in the primary group of the chart, applying its title, adding text, setting the font and font size, and turning major gridlines on and minor gridlines off. Note that this With structure should be placed within a second, outer With structure representing the chart itself:

With MyChart
   With .Axes(Type:=xlCategory, AxisGroup:=xlPrimary)
.HasTitle = True
      .AxisTitle.Text = "Years"
      .AxisTitle.Font.Name = "Times New Roman"
      .AxisTitle.Font.Size = 12
      .HasMajorGridlines = True
      .HasMinorGridlines = False
    End With

End With

To open a new window on a workbook, use the NewWindow method of the appropriate Window object. This method takes no arguments. For example, the following statement opens a new window showing the contents of the first window open on the workbook identified by the object variable myWorkbook:

myWorkbook.Windows(1).NewWindow

To close a window, use the Close method with the appropriate Window object. The syntax is as follows:

expression.Close(SaveChanges, Filename, RouteWorkbook)

Here, expression is a required expression that returns a Window object. This syntax is the same as for closing a workbook (see "Closing a Workbook" in the previous chapter). The difference is that if two or more windows are open on the same workbook, closing the second or subsequent window does not close the workbook, so the arguments are not relevant. (If the window you're closing is the workbook's last window, however, you do need to specify the windows—otherwise, Excel prompts the user to save any unsaved changes.) For example, the following statement closes all windows open on the workbook referenced by the object variable myWorkbook except for one window:

Do While myWorkbook.Windows.Count > 1
    myWorkbook.Windows(myWorkbook.Windows.Count).Close
Loop

To activate a window, use the Activate method of the appropriate Window object. For example, the following statement activates the first window open on the workbook Planning.xlsx:

Workbooks("Planning.xlsx").Windows(1).Activate

Similarly, you can activate the previous window by using the ActivatePrevious method or the next window by using the ActivateNext method.

To arrange windows, use the Arrange method with the appropriate Windows collection. The syntax is as follows:

expression.Arrange(ArrangeStyle, ActiveWorkbook, SyncHorizontal, SyncVertical)

Here are the components of this syntax:

For example, the following statement arranges the windows in the workbook Budget.xlsx vertically and sets synchronized scrolling on them:

Workbooks("Budget.xlsx").Windows.Arrange _
    ArrangeStyle:=xlArrangeStyleVertical, _
    ActiveWorkbook:=True, SyncVertical:=True

You can maximize, minimize, or restore the application window by setting the WindowState property of the Application object to xlMaximized, xlMinimized, or xlNormal. Similarly, within the application window, you can maximize, minimize, or restore a document by setting its WindowState property.

When a window is in a "normal" state (xlNormal; not maximized or minimized), you can position it by using the Top and Left properties to specify the position of the upper-left corner of the window and size it by setting its Height and Width properties. Check the UsableWidth property and the UsableHeight property of the Application object to find the amount of space available in the Application window. (Similarly, you can check the UsableWidth property and the UsableHeight of the Window object to see how much space is available in the window—for example, so that you can size or position an object correctly.)

The following example declares two Window object variables, myWindow1 and myWindow2, assigns myWindow1 to the active window and myWindow2 to a new window showing the same worksheet as myWindow1. The example then sizes and positions the two windows so that each is the full height available in the application window, with myWindow1 taking one-quarter of the available width and myWindow2 taking the remaining three-quarters of the available width:

Dim myWindow1 As Window, myWindow2 As Window
Set myWindow1 = ActiveWindow
Set myWindow2 = myWindow1.NewWindow
With myWindow1
    .WindowState = xlNormal
    .Top = 0
    .Left = 0
    .Height = Application.UsableHeight
    .Width = Application.UsableWidth * 0.25
End With
With myWindow2
    .WindowState = xlNormal
    .Top = 0
    .Left = (Application.UsableWidth * 0.25) + 1
    .Height = Application.UsableHeight
    .Width = Application.UsableWidth * 0.75
End With

To change the zoom, set the Zoom property of the appropriate Window object. For example, the following statement zooms the active window to 150 percent:

ActiveWindow.Zoom = 150

In some procedures, you may need to change the display of the Excel window to ensure that certain features are (or are not) available to the user. Use the Boolean properties DisplayScrollBars, DisplayStatusBar, and DisplayFormulaBar to control whether Excel displays the scroll bars, status bar, and formula bar. Use the DisplayFullScreen property to toggle full-screen view on and off.

For example, the following statements make sure that the scroll bars and status bar are hidden and that the formula bar is displayed:

With Application
    .DisplayScrollBars = False
    .DisplayStatusBar = False
    .DisplayFormulaBar = True
End With

The syntax for the Range object's Find method is as follows:

expression.Find(What, After, LookIn, LookAt, SearchOrder, SearchDirection, MatchCase, MatchByte, SearchFormat)

Here are the components of this syntax:

The following example code searches for 2008 in formulas in cells after the active cell, without searching for formatting:

Cells.Find(What:="2008", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
    :=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
    True, SearchFormat:=False).Activate

After you have executed a search using the Find method, you can use the FindNext method to find the next instance of the search item or the FindPrevious method to find the previous instance. The syntax is as follows:

expression.FindNext(After)
expression.FindPrevious(After)

Here, expression is a required expression that returns a Range object, and After is an optional Variant argument that specifies the cell after which you want to search (for the FindNext method) or before which you want to search (for the FindPrevious method). After must be a single cell.

For example, the following statement finds the next instance of the search item:

Cells.FindNext

To replace using VBA, use the Replace method with the Range object. The syntax is as follows:

expression.Replace(What, Replacement, LookAt, SearchOrder, MatchCase, MatchByte, SearchFormat, ReplaceFormat)

The components of the syntax are the same as for the Search method except for the following:

For example, the following statement replaces the instances of the word Sales in column B of the active worksheet with the words Sales & Marketing, using case-sensitive matching:

ActiveSheet.Columns("B").Replace What:="Sales", _
    Replacement:="Sales & Marketing", SearchOrder:=xlByColumns, _
    MatchCase:=True

To search for formatting, use the FindFormat property of the Application object to define the formatting, and then set the SearchFormat argument of the Find method to True. Similarly, use the ReplaceFormat property of the Application object to define the replacement formatting, and then set the ReplaceFormat property of the Replace method to True.

For example, the following statements use a With structure to set the Application.FindFormat.Font properties for which to search, a With structure to set the Application.ReplaceFormat.Font with which to replace them, and the Replace method of the Cells collection to effect the replacement:

With Application.FindFormat.Font
        .Name = "Arial"
        .Size = "12"
        .Bold = True
    End With
    With Application.ReplaceFormat.Font
        .Name = "Arial Black"
        .Bold = False
    End With
    Cells.Replace What:="5", Replacement:="5", LookAt:=xlPart, SearchOrder _
        :=xlByColumns, MatchCase:=False, SearchFormat:=True, ReplaceFormat:=True