Show All

SetData Method

Sets data for the specified chart object.

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

DataSourceIndex   Required Long. Can be a ChartSpecialDataSourcesEnum constant.

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

 Alternatively, you can specify a ChartErrorBarCustomValuesEnum constant for the Dimension argument to specify the values to use for error bars.

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

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