Caution

Use of the ActiveChart object in code when something other than a chart is active (selected) results in an error.

Of course, the chart can also be referred to as Charts("Chart4") just as effectively. ActiveChart.SetSourceData Source:=Sheets("Sheet4").Range("A1:B5")

This line specifies the data source (the range that contains the source data for the chart) for the new chart, using the SetSourceData method. It is interesting to note that VBA has a corresponding method for all that you do through the user interface. In the current instance, if you right-click the chart, and select Source Data from the shortcut menu, the Source Data dialog box appears (see Figure 10.5). The Data Range tab of this dialog takes two inputs: the data range and whether the data series is in rows or columns.

The SetSourceData method is the VBA equivalent of doing just this. The complete syntax of this method is

SetSourceData(Source, PlotBy)

Where Source is a range reference and PlotBy is an Excel constant that can have one of two values: xlColumns or xlRows.

Thus, the explicitly complete line of code for specifying the data source for the sample chart would be

ActiveChart.SetSourceData Source:=Sheets("Sheet4").Range("A1:B5"),_ PlotBy:=xlColumns

Figure 10.5

The Source Data dialog is emulated by the SetSourceData method in VBA code.

Figure 10.5

The Source Data dialog is emulated by the SetSourceData method in VBA code.

Note that the recorded code does not contain the PlotBy argument. This is probably because the data structure (field names in A1 and B1) itself suggests that the data is in columns and therefore the macro recorder perceives this argument as being redundant and omits to record it. However, the macro recorder is not consistently as efficient. More often than not, it spews out large quantities of redundant code that you will find yourself deleting in the end.

ActiveChart.Location Where:=xlLocationAsNewSheet

This line corresponds to the last step, when you use the Chart Wizard (see Figure 10.6) to create a chart. In this step you specify where the newly created chart should be located: either as a new chart sheet or as an embedded object on a specific worksheet.

Figure 10.6

The Location method in VBA answers the final question in the Chart Wizard.

Figure 10.6

The Location method in VBA answers the final question in the Chart Wizard.

0 0

Post a comment