The MSChart control allows you to plot data in charts according to your specifications. You can create a chart by setting data in the control’s properties page, or by retrieving data to be plotted from another source, such as a Microsoft Excel spreadsheet. The information in this topic focuses on using an Excel worksheet as a data source.
The simplest way of plotting a chart is to create an array of numeric values, and then set the ChartData property to the array, as shown in the following example:
' This code might be pasted into the Load event
' of a Form that has an MSChart control named
' "MSChart1".
Dim arrPrices(1 to 10)
Dim i As Integer
For i = 1 to 10
arrPrices(i)= i * 2
Next i
MSChart1.ChartData = arrPrices
The code above produces a simple, single-series chart. A “series” in a chart, is set of related data points. For example, a typical series might be the prices of a commodity over the course of a year. The chart below shows a single-series chart.
To create a more complex, multi-series chart, you must create a multi-dimensioned array, as shown in the following example:
' The number of series are determined by the second
' dimension. In this example, the chart will have two
' series, with five data points in each series.
Dim arrPriceQuantity(1 to 5, 1 to 2)
Dim i as Integer
For i = 1 to 5
arrPriceQuantity(i, 1) = i ' Series 1
arrPriceQuantity(i, 2) = 0 - i ' Series 2
Next i
MsChart1.ChartData = arrPriceQuantity
This will produce the following chart:
When you create a multi-dimensioned array, the first series can be assigned a string; when the array is assigned to the ChartData property, the strings become the labels for the rows. The following code shows this feature.
Dim arrValues(1 to 5, 1 to 3)
Dim i as Integer
For i = 1 to 5
arrValues(i, 1) = "Label " & i ' Labels
arrValues(i, 2) = 0 + i ' Series 1 values.
arrValues(i, 3) = 2 * i ' Series 2 values.
Next i
MsChart1.ChartData = arrValues
The above code produces the chart shown below:
As you can see, creating a chart using the ChartData property can be quick and simple. However, the problem with using an array is getting the data into the array. Most users of this kind of data will probably prefer to use a spreadsheet program, such as Microsoft Excel, or perhaps a database program, such as Microsoft Access, to store and retrieve the data.
Once you have created a chart, using an array from a spreadsheet or other data source, you may also want to set or return the value of a particular data point. This can be done by first setting the Row and (if applicable) Column properties, then setting or returning the Data property. For example, in a simple (single-series) chart, the following code would change the third data point.
With MSChart1
' Change third data point to 50.
.Row = 3
.Data = 50
End With
If the chart has more than one series use the Column property to designate the series, then set the Row and Data properties as above.
With MSChart1
' Set the second data point of the fourth series
' to 42.
.Column = 4
.Row = 2
.Data = 42
End With
If you've started to explore the MSChart control, you will notice that it has a large number of events. These events allow you to program the chart to respond to practically any action of the user. As an example of this programmability, the PointActivated event is used in the following example to show how a data point can be changed using the Series and DataPoint parameters. (The PointActivated event occurs whenever a data point is double-clicked.) The Series and DataPoint parameters correspond to the Column and Row properties, and can thus be used to set the Data property:
Private Sub MSChart1_PointActivated(Series As _
Integer, DataPoint As Integer, MouseFlags As _
Integer, Cancel As Integer)
With MSChart1
.Column = Series
.Row = DataPoint
.Data = InputBox _
("Change the data point:", , .Data)
End With
End Sub