Private Sub Form_Load()
MSChart1.Visible = False
End Sub
Private Sub Command1_Click()
Dim i As Long
Dim j As Long
Dim sHeader As String
Dim sLine As String
Dim aItems() As String
Dim sFileName As String
Dim nFileNum As Long
Dim aData() As String
Screen.MousePointer = vbHourglass
nFileNum = FreeFile
Open App.Path & "\" & "yourcsv.csv" For Input As #nFileNum
'/ set headers
Line Input #nFileNum, sHeader
aItems = Split(sHeader, ",")
' 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.
ReDim Preserve aData(LBound(aItems) To UBound(aItems), i)
For j = 0 To UBound(aItems)
aData(j, i) = aItems(j)
Next j
'/
' loop through file and populate rest of the multi-dimensioned array
i = 1
Do While Not EOF(nFileNum)
' read line
Line Input #nFileNum, sLine
If Not sLine = sHeader Then
' split values on comma
aItems = Split(sLine, ",")
' When you're using ReDim Preserve on a multidimensional array,
' you can resize only its last dimension:
ReDim Preserve aData(UBound(aItems), i)
For j = 0 To UBound(aItems)
aData(j, i) = aItems(j)
Next j
i = i + 1
End If
Loop
Close #nFileNum
'/ create a multi-dimensioned array for our chart
Dim n As Long
ReDim aDataForChart(i - 1, UBound(aItems))
For n = 0 To i - 1
For j = 0 To UBound(aItems)
aDataForChart(n, j) = aData(j, n)
Next j
Next n
'/
With MSChart1
.Visible = True
'/ set the chart type
' set the chart type to a 2d Line
.chartType = VtChChartType2dLine
' set the chart type to a 2d Bar
'.chartType = VtChChartType2dBar
'/
' Plot Data Using Array and the ChartData Property
.ChartData = aDataForChart
.Height = 3400
' add chart title
'.TitleText = "Frequency of Numbers"
With .Title
.Text = "Frequency of Numbers"
.VtFont.Size = 12
.VtFont.VtColor.Set 255, 0, 0
End With
' set chart Labels
' (aDataForChart(0,0) : "Number")
' (aDataForChart(0,1) : " Frequency")
For n = 1 To i - 1
.Row = n
.RowLabel = aDataForChart(n, 0)
Next n
Dim serX As Series
For Each serX In MSChart1.Plot.SeriesCollection
If serX.LegendText = "Number" Then
' Hide "Number" Serie
serX.Position.Hidden = True
' Set XAxis Title
'Dim XAxis As MSChart20Lib.Axis
'Set XAxis = .Plot.Axis(VtChAxisIdX, 1)
'XAxis.AxisTitle = serX.LegendText
.Plot.Axis(VtChAxisIdX, 1).AxisTitle = serX.LegendText
ElseIf serX.LegendText = "Frequency" Then
With serX.StatLine
' VtChStatsMean - Shows the mathematical mean of the values in the series.
.Flag = VtChStatsMean
.Style(VtChStatsMean) = VtPenStyleDitted 'VtPenStyleDashed 'VtPenStyleDotted
.VtColor.Set 255, 0, 0
.Width = 1
End With
' Set YAxis Title
' VtChAxisIdY2 Identifiziert die sekundäre Y-Achse
'Dim YAxis As MSChart20Lib.Axis
'Set YAxis = .Plot.Axis(VtChAxisIdY2, 1)
'YAxis.AxisTitle = serX.LegendText
.Plot.Axis(VtChAxisIdY2, 1).AxisTitle = serX.LegendText
End If
Next
' Set Chart footnote
With .Footnote
.Location.Visible = True
.Location.LocationType = VtChLocationTypeBottomLeft
.Text = "sorted by frequency in ascending order"
.VtFont.VtColor.Set 128, 128, 128
End With
End With
Screen.MousePointer = vbDefault
End Sub