SetData Method

Article
07/11/2006

Sets data for the specified chart object.

expression.SetData(Dimension, DataSourceIndex, DataReference)

*expression * An expression that returns a ChChart, ChErrorBars ChSeries,or ChartSpace object.

ChartDimensionsEnum

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.

ChartSpecialDataSourcesEnum

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).

Remarks

ChartErrorBarCustomValuesEnum

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.

Example

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;" & _
                                   "Integrated Security=SSPI;Initial Catalog=Northwind;" & _
                                   "Data Source=ServerName;"
    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

Applies to | ChartSpace Object | ChChart Object | ChErrorBars Object | ChSeries Object

Share via