1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56
| Private Sub CommandButton2_Click()
Dim i As Integer, Nb As Integer
Dim LastLig As Long, j As Long
Dim Plage As Range
Dim MonTitre As String
Dim Ch As ChartObject
Application.ScreenUpdating = False
With Sheets("curves")
For Each Ch In .ChartObjects
Ch.Delete
Next Ch
End With
With Worksheets("Data")
.CommandButton2.Caption = "Plot curves"
Nb = Int((.Cells(54, .Columns.Count).End(xlToLeft).Column - 1) / 2)
For i = 2 To Nb * 2 Step 2
MonTitre = .Cells(51, i).Value
LastLig = .Cells(.Rows.Count, i).End(xlUp).Row
Set Plage = .Range(.Cells(54, i), .Cells(LastLig, i))
TraceGraph Plage, MonTitre, j
Set Plage = Nothing
j = j + 200 '200 la hauteur des graphiques
Next i
End With
Application.ScreenUpdating = True
End Sub
Private Sub TraceGraph(PlageX As Range, ByVal MonTitre As String, ByVal t As Long)
Dim Grf As ChartObject
Set Grf = Worksheets("curves").ChartObjects.Add(0, t, 500, 200) '0: gauche, t: haut, 500: largeur, 200 hauteur
With Grf.Chart
.ChartType = xlXYScatterSmooth ' Type nuage de points
.SeriesCollection.NewSeries
With .SeriesCollection(1)
.XValues = PlageX
.Values = PlageX.Offset(0, 1)
.Trendlines.Add Type:=xlPolynomial, Order:=3, DisplayEquation:=True
End With
.HasLegend = False
.HasTitle = True
.ChartTitle.Characters.Text = MonTitre
With .Axes(xlCategory)
.HasTitle = True
.AxisTitle.Characters.Text = "X"
End With
With .Axes(xlValue)
.HasTitle = True
.AxisTitle.Characters.Text = "Y"
End With
End With
Set Grf = Nothing
End Sub |
Partager