Sub tests()
' tests Macro
' Macro recorded 14.10.2010 by Amilien 19.10.2010
Dim c As ChartObject
Dim calc As Range, y As Range, y1 As Range, numreport As Integer
nbpages = InputBox("veuillez saisir le nombre de page a tracer")
'Si l'utilisateur clique sur le bouton Annuler,
'la fonction renvoie une chaîne de longueur nulle ("").
If nbpages = "" Then
MsgBox "annulé"
Exit Sub
Else
MsgBox nbpages
End If
With Sheets("Sheet1")
Set c = .ChartObjects.Add(.Range("G15").Left, .Range("G15").Top, 800, 400)
End With
For i = 1 To nbpages
'Calculate the number of values in a column
Set calc = Range("A:A").Find("not implemented", , xlValues, xlWhole, , , False)
'Y-axis selection---------------------------------------------------------------------------------
If Not calc Is Nothing Then
Set y_choice = Application.InputBox("Choose the Y axis column (address title's cell)", Type:=8)
'Calculate the number of values in a column
Set y = Range(y_choice.Offset(1, 0), Cells(calc.Row - 1, y_choice.Column))
y.Select
For Each v In y
v.Value = v.Value * 1 'String to number
Next
y.Name = "y": y_choice.Name = "y_choice"
End If
If Not calc Is Nothing Then
Set y1_choice = Application.InputBox("Choose the Y1 axis column (address title's cell)", Type:=8)
Set y1 = Range(y1_choice.Offset(1, 0), Cells(calc.Row - 1, y1_choice.Column))
y1.Select
For Each v In y1
v.Value = v.Value * 1 'String to number
Next
y1.Name = "yy": y1_choice.Name = "y1_choice"
End If
'X-axis selection----------------------------------------------------------------------------------
If Not calc Is Nothing Then
Set x_choice = Application.InputBox("Choose the X axis column (address title's cell)", Type:=8)
Set x = Range(x_choice.Offset(1, 0), Cells(calc.Row - 1, x_choice.Column))
x.Select
For Each v In x
v.Value = v.Value * 1 'String to number
Next
x.Name = "x": x_choice.Name = "x_choice"
End If
' Chart configurations ---------------------------------------------------------------------------------
With c.Chart
.ChartType = xlLineMarkers
.SetSourceData Source:=ActiveSheet.Range("y,yy"), PlotBy:=xlColumns
.SeriesCollection(1).XValues = "=TX_WLAN_11n_framed_802.11n_HT40!x"
.SeriesCollection(2).XValues = "=TX_WLAN_11n_framed_802.11n_HT40!yy"
.SeriesCollection(2).AxisGroup = 2
.HasTitle = True
.ChartTitle.Characters.Text = "XY Graph"
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = Range("x_choice")
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = Range("y_choice")
.Legend.Position = xlBottom
.Legend.Font.size = 8
.Legend.Font.Bold = True
.ChartTitle.Text = "XY Graph"
.ChartTitle.Font.size = 8
.ChartTitle.Font.Bold = True
.Axes(xlCategory).TickLabels.Font.size = 8
.Axes(xlValue).TickLabels.Font.size = 8
.Axes(xlCategory, xlSecondary).HasTitle = False
.Axes(xlValue, xlSecondary).HasTitle = True
.Axes(xlValue, xlSecondary).AxisTitle.Characters.Text = Range("y1_choice")
.SeriesCollection(1).Name = Range("y_choice")
.SeriesCollection(2).Name = Range("y1_choice")
End With
Next
End Sub
