Sets data for the specified chart object.
expression.SetData(Dimension, DataSourceIndex, DataReference)
expression An expression that returns a ChChart, ChErrorBars ChSeries,or ChartSpace object.
Dimension Required ChartDimensionsEnum constant. Specifies the data dimension to be set.ChartDimensionsEnum can be one of these ChartDimensionsEnum constants. |
chDimBubbleValues Set the values for the markers on a Bubble chart. |
chDimCategories Set the values to use as categories. |
chDimCharts Sets the source fields for new charts when the HasMultipleCharts property is set to True. |
chDimCloseValues Set the closing values for a Stock chart. |
chDimFilter Sets the fields to place on the filter axis. |
chDimFormatValues Set the values to use in a format map. |
chDimHighValues Set the high values for a Stock chart. |
chDimLowValues Set the low values for a Stock chart. |
chDimOpenValues Set the opening values for a Stock chart. |
chDimRValues Set the R values for a Polar chart. |
chDimSeriesNames Set the values to use as series names. |
chDimThetaValues Set the Theta values for a Polar chart. |
chDimValues Set the values to be charted. |
chDimXValues Set the x values for an XY (Scatter) or Bubble chart. |
chDimYValues Set the y values for an XY (Scatter) or Bubble chart. |
DataSourceIndex Required Long. Can be a ChartSpecialDataSourcesEnum constant.
ChartSpecialDataSourcesEnumcan be one of these ChartSpecialDataSourcesEnum constants. |
chDataBound Binds the specified object to the external data source specified in the DataReference argument. |
chDataLinked Binds the specified object to another dimension. Use this value when you specify chDimFormatValues in the Dimension argument to creata a format map. |
chDataLiteral Binds the specified object to the literal data specified in the DataReference argument. |
chDataNone Clears the specified object. |
DataReference Optional Variant. For ChChart and ChSeries objects, this argument specifies the data reference as a Microsoft Excel-style range reference ("A1:D4" , for example), or a row-set column name. When the DataSourceIndex argument is set to chDataLiteral, you can set DataReference to a one-dimensional array or a comma-delimited list. For ChErrorBars objects, this argument specifies an array of Double or String values you can use for error-bar values. Note that you can use this argument only with custom error bars (the error-bar Type property must be set to chErrorBarTypeCustom).
Alternatively, you can specify a ChartErrorBarCustomValuesEnum constant for the Dimension argument to specify the values to use for error bars.
ChartErrorBarCustomValuesEnum can be one of these ChartErrorBarCustomValuesEnum constants. |
chErrorBarMinusValues |
chErrorBarPlusValues |
You can bind a chart to only one data source. For example, if you have two charts in a ChartSpace, you cannot bind them to different data sources. However, you can bind a chart or data series to a set of literal data once the chart or ChartSpace has been bound to an external data source.
When binding to an OLAP data source, the DataReference argument can bind to a field set, but not a field. You can pass an array of fields to the DataReference argument to bind to a specific field or fields when connected to an OLAP data source.
This example creates a chart using literal data arrays.
Sub BindChartToArrays()
Dim asSeriesNames(1)
Dim asCategories(7)
Dim aiValues(7)
Dim chConstants
Dim chtNewChart
asSeriesNames(0) = "Satisfaction Data"
asCategories(0) = "Very Good"
asCategories(1) = "Good"
asCategories(2) = "N/A"
asCategories(3) = "Average"
asCategories(4) = "No Response"
asCategories(5) = "Poor"
asCategories(6) = "Very Poor"
aiValues(0) = 10
aiValues(1) = 22
aiValues(2) = 6
aiValues(3) = 31
aiValues(4) = 5
aiValues(5) = 14
aiValues(6) = 12
Set chConstants = ChartSpace1.Constants
' Add a new chart to Chartspace1.
Set chtNewChart = ChartSpace1.Charts.Add
' Specify that the chart is a column chart.
chtNewChart.Type = chConstants.chChartTypeColumnClustered
' Bind the chart to the arrays.
chtNewChart.SetData chConstants.chDimSeriesNames, chConstants.chDataLiteral, asSeriesNames
chtNewChart.SetData chConstants.chDimCategories, chConstants.chDataLiteral, asCategories
chtNewChart.SeriesCollection(0).SetData chConstants.chDimValues, chConstants.chDataLiteral, aiValues
End Sub
This example creates a chart that is bound to a spreadsheet. The series name is in cell B1, the category names are in cells A2:A28, and the values are in cells B2:B28.Sub BindToSpreadsheet()
Dim chConstants
Dim chtChart1
Set chConstants = ChartSpace1.Constants
' Set the data source of ChartSpace1 to Spreadsheet1.
Set ChartSpace1.DataSource = Spreadsheet1
' Set a variable to a new chart in Chartspace1.
Set chtChart1 = ChartSpace1.Charts.Add
' Set the chart type.
chtChart1.Type = chConstants.chChartTypeLineMarkers
' Bind the series name to cell B1 in the first sheet of Spreadsheet1.
chtChart1.SetData chConstants.chDimSeriesNames, chConstants.chDataBound, "B1"
' Bind the category axis to cell A2:A28 in the first sheet of Spreadsheet1.
chtChart1.SetData chConstants.chDimCategories, chConstants.chDataBound, "A2:A28"
' Bind the values of the data series to cells B2:B28 in the first sheet of Spreadsheet1.
chtChart1.SeriesCollection(0).SetData chConstants.chDimValues, chConstants.chDataBound, "B2:B28"
End Sub
The following example binds Chartspace1 to the Order Details table in the SQL Server Northwind database. Then, a format map is created. The smaller values are displayed in white, then larger values are displayed in a light shade of blue, and finally the largest values in the chart are displayed in dark blue.
Sub Window_Onload()
Dim serSeries1
Dim segSegment1
Dim chConstants
Set chConstants = ChartSpace1.Constants
' The following two lines of code bind Chartspace1 to the Order Details table in the
' Northwind SQL Server database.
ChartSpace1.ConnectionString = "Provider=SQLOLEDB.1;persist Security Info=TRUE;User ID=sa;Initial " & _
"Catalog=Northwind;Data Source=ServerName;PASSWORD=;"
ChartSpace1.DataMember = "Order Details"
' The following two lines of code bind Chartspace1 to the Quantity and ProductID fields
' in the Order details table.
ChartSpace1.SetData chConstants.chDimCategories, chConstants.chDataBound, "ProductID"
ChartSpace1.SetData chConstants.chDimValues, chConstants.chDataBound, "Quantity"
' Create a format map.
ChartSpace1.SetData chConstants.chDimFormatValues, chConstants.chDataBound, "Quantity"
' Set a variable to the first series in the first chart in Chartspace1.
Set serSeries1 = ChartSpace1.Charts(0).SeriesCollection(0)
' Add a segment to the format map.
Set segSegment1 = serSeries1.FormatMap.Segments.Add
' Specify that the divisions in formatting be created automatically.
segSegment1.HasAutoDivisions = True
' Measure the segment boundaries based upon a percentage.
segSegment1.Begin.ValueType = chConstants.chBoundaryValuePercent
segSegment1.End.ValueType = chConstants.chBoundaryValuePercent
' Set the beginning value to 0%, and the ending value to 100%.
segSegment1.Begin.Value = 0
segSegment1.End.Value = 1
' Format the interior of the matching values.
segSegment1.Begin.Interior.Color = "White"
segSegment1.End.Interior.Color = "Blue"
End Sub