when plot chart using vba, get misc, not desired, series plotted

[Fred_2011]

Hi Everyone, <?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
I have tried looked this question up via search engine. So far I have not been able to find the answer to my question, but I may not be typing in the correct query question. Therefore, I thought i would try posting the question to see if anyone had any suggestions. <o></o>
<o></o>
1st off:<o></o>
1. The code I have created does 'work'. <o></o>
2. I am using Excel 2007. <o></o>
<o></o>
Problem: <o></o>
I am making a chart in excel using excel vba. As far as i can tell, I am calling all objects (workbooks, sheets, cells) as precisely as possible. I made sure to dim all of my variables. I am plotting two series of data. One set of data is from one Sheet, the other set of data is from another sheet. When i plot these two sets of data on the same sheet/chartObject, they do plot successfully, but then I get a lot of other miscellaneous series plotted on the graph. I have tried everything to correctly plot Only Two series. My 'fix' was to just delete series 3 through X (52, or whatever the last series plotted was). This 'works' but makes the macro extremely slow and I know this is basically an incorrect way to go about plotting this data. I have tried several different was to plot data including one posted on Mr. Excel (http://www.mrexcel.com/forum/showthread.php?t=1098). <o></o>
<o></o>
The code does work without a hitch when i plot data off two excel spreadsheets which were created in this 2007 workbook. I copied to 2003 worksheets into this 2007 workbook ... these are the sheets which, when i tried plotting data from, I get misc other series plotted on the charts. Thanks in advance for any suggestions or comments provided. <o></o>
<o></o>
<o></o>
Below is the Code. I highlighted in blue the lines of code which I believe must be the lines which I have coded incorrectly.<o></o>
-----------------------------------------------------------<o></o>
Sub single_Plot_Per_Chart(start_Row, RowLast, x_Val_Cols, y_Val_Cols, chart_name, data_Sheet _
, titleRow, tab_Name, y_Label, x_Label, y_values_column1_end)<o></o>
<o></o>
Dim start_row_for_Data As Integer
Dim LastRow As Integer
Dim x_values_column As Integer
Dim y_values_column As Integer<o></o>
Dim sheet_with_Data As String<o></o>
Dim dummyColmn As Integer
Dim row_with_titles As Integer
Dim data_set_name As String
Dim y_axis_Label As String
Dim x_axis_Label As String<o></o>
Dim DoesSheetExist As Boolean
Dim DoesSheetExist_b As Boolean
Dim DoesChartExist As Boolean
Dim current_y_COl As Integer
Dim second_dataSheet As String
Dim secndPage_Curnt_yCol As Integer
Dim FinalRow As Integer
Dim r(0 To 3) As Range
Dim chart_name1 As String
Dim thisWorkbook As String
Dim startRow_second As Integer
Dim FinalRow_secnd As Integer
Dim xValueCol_secnd As Integer
Dim rowNumber As Integer
Dim secnd_data_set_name As String<o></o>

Application.ScreenUpdating = False<o></o>
<o></o>
start_row_for_Data = start_Row
LastRow = RowLast
' -----------------
x_values_column = x_Val_Cols
y_values_column = y_Val_Cols
y_values_column_end = y_values_column1_end
' -----------------
chart_name = chart_name
sheet_with_Data = data_Sheet
' -----------------
dummyColmn = 1 ' any column
' -----------------
row_with_titles = titleRow
' -----------------
data_set_name = tab_Name
y_axis_Label = y_Label
x_axis_Label = x_Label
plot_title = tab_Name<o></o>
' -----------------
second_dataSheet = secondSheet_TextBox.Value<o></o>
' Make sure the second sheet exists
If multiPlot_SameGraph.Value = False Then
current_y_COl = y_values_column
secndPage_Curnt_yCol = yStartCol_secnd_TextBox.Value

Do While current_y_COl < y_values_column_end + 1
Sheets(sheet_with_Data).Activate
chart_name = Sheets(sheet_with_Data).Cells(row_with_titles, current_y_COl).Value
plot_title = Sheets(sheet_with_Data).Cells(row_with_titles, current_y_COl).Value
data_set_name = Sheets(sheet_with_Data).Cells(row_with_titles, current_y_COl).Value

' Check to make sure chart sheet Name does Not exists
thisWorkbook = ActiveWorkbook.Name
chart_name1 = chart_name
DoesChartExist = IsChart(chart_name1)
If DoesChartExist = True Then
MsgBox "There is already a chart sheet with the name " & chart_name & ". Cannot overwrite sheet. Now Exiting macro", vbCritical
Exit Sub
Else

' 1st: Storing range (cells) containing x-values and y-values into array 'r'
FinalRow = TextBox7_DataEndRow.Value
Workbooks(thisWorkbook).Sheets(sheet_with_Data).Activate
Set r(0) = Workbooks(thisWorkbook).Sheets(sheet_with_Data).Range(Cells(start_row_for_Data, x_values_column), Cells(FinalRow, x_values_column))
Set r(1) = Workbooks(thisWorkbook).Sheets(sheet_with_Data).Range(Cells(start_row_for_Data, current_y_COl), Cells(FinalRow, current_y_COl))

' 2nd: Storing range (cells) containing x-values and y-values into array 'r'
If otherSheet_yesButton.Value = True Then
xValueCol_secnd = xValueCol_secnd_textBox.Value
FinalRow_secnd = lastRow_second_TextBox.Value
startRow_second = second_startRow_TextBox.Value
rowNumber = 10
Workbooks(thisWorkbook).Sheets(second_dataSheet).Activate
Set r(2) = Workbooks(thisWorkbook).Sheets(second_dataSheet).Range(Cells(startRow_second, xValueCol_secnd), Cells(FinalRow_secnd, xValueCol_secnd))
Set r(3) = Workbooks(thisWorkbook).Sheets(second_dataSheet).Range(Cells(startRow_second, secndPage_Curnt_yCol), Cells(FinalRow_secnd, secndPage_Curnt_yCol))
End If

' Creating the Chart and Defining its Type
Application.ScreenUpdating = True
SheetName = ActiveSheet.Name

Charts.Add
If plotpoint_Button_Yes.Value = True Then
ActiveChart.ChartType = xlXYScatterLines
Else
ActiveChart.ChartType = xlXYScatterLinesNoMarkers
End If


' Using Cells (Range) Information to Plot

' 1st Page
ActiveChart.SeriesCollection.NewSeries
' here are the x-value to be plotted
ActiveChart.SeriesCollection(1).XValues = r(0)
' here are the y-value to be plotted
ActiveChart.SeriesCollection(1).Values = r(1)

' 2nd Page
If otherSheet_yesButton.Value = True Then
ActiveChart.SeriesCollection.NewSeries
' here are the x-value to be plotted
ActiveChart.SeriesCollection(2).XValues = r(2)
' here are the y-value to be plotted
ActiveChart.SeriesCollection(2).Values = r(3)

secnd_data_set_name = Sheets(second_dataSheet).Cells(titles_secnd_textBox, secndPage_Curnt_yCol).Value
ActiveChart.SeriesCollection(2).Name = data_set_name & "_" & second_dataSheet
If Application.Version = "12.0" Then
ActiveChart.SeriesCollection(2).Format.Line.Weight = TextBox_LineWidth.Value
End If
End If


' puts chart on new sheet and renames the new sheet and moves the chart
' directly after sheet1
ActiveChart.Location Where:=xlLocationAsNewSheet, Name:=chart_name
ActiveChart.Move after:=Worksheets(Worksheets.Count)
ActiveChart.HasLegend = True
ActiveChart.Legend.Select
Selection.Position = xlBottom

' Labeling Chart
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = plot_title
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = x_axis_Label
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = y_axis_Label
.SeriesCollection(1).Name = data_set_name
If Application.Version = "12.0" Then
.SeriesCollection(1).Format.Line.Weight = TextBox_LineWidth.Value
End If
If otherSheet_yesButton.Value = True Then
Do While ActiveChart.SeriesCollection.Count > 2
ActiveChart.SeriesCollection(ActiveChart.SeriesCollection.Count).Delete
Loop
End If
End With

End If
current_y_COl = current_y_COl + 1
secndPage_Curnt_yCol = secndPage_Curnt_yCol + 1
Loop
Else<o></o>
-----------------------------------------------------------<o></o>
Pictures:<o></o>
http://www.flickr.com/photos/63779517@N08/

 

[Fred_2011]

I solved the problem and figured I'd write the answer.

It wasn't an excel version problem. I just needed to add the following code:

Do While ActiveChart.SeriesCollection.Count > 0
ActiveChart.SeriesCollection(ActiveChart.SeriesCollection.Count).Delete
Loop

right after the line of code:

Charts.Add


Excel's plot default (sometimes? always?) seems to be to just plot everything on the sheet. So I guess there is no way to create a fresh empty chart. Perhaps there is but the code above sure doesn't. So I needed to delete every line which was drawn on the chart by default (which lengthens the time the macro runs ... more than I was hoping for), then replot the data I did actually want on the Chart.
I would think there would be a better way around this ... like some parameter which would allow one to state they wanted to create an empty chart to begin with (ex: charts.Add(emptychart) ), but don't know what that would be.