Option Explicit
Public c As ChartObject
Public calc As Range, y As Range, y1 As Range, x As Range, numreport As Integer, s As Series
Public y_choice As Range, y1_choice As Range, x_choice As Range, v As Range
Sub tests()
' tests Macro
' Macro recorded 14.10.2010 by Amilien 19.10.2010
'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.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.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
x.Name = "x": x_choice.Name = "x_choice"
End If
' Chart configurations ---------------------------------------------------------------------------------
With Sheets("Sheet1")
Set c = .ChartObjects.Add(.Range("G15").Left, .Range("G15").Top, 800, 400)
End With
With c.Chart
.ChartType = xlLineMarkers
.SetSourceData Source:=Sheets("TX_WLAN_11n_framed_802.11n_HT40").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
End Sub
Sub rajoutSerie()
Dim c As ChartObject, s As Series
Sheet2.Select
'Calculate the number of values in a column
Set calc = Range("A:A").Find("not implemented", , xlValues, xlWhole, , , False)
'Y-axis selection---------------------------------------------------------------------------------
'Calculate the number of values in a column
y = Range(y_choice.Offset(1, 0), Cells(calc.Row - 1, y_choice.Column))
y1 = Range(y1_choice.Offset(1, 0), Cells(calc.Row - 1, y1_choice.Column))
Set c = Sheet2.ChartObjects(1)
With c.Chart
Set s = .SeriesCollection.NewSeries
With s
.Values = Sheet4.Range("y")
.Name = Sheet2.Range("A1").Text
End With
End With
End Sub