Repetative Tasks and Variables

[Guest]

I want to tidy up and reduce the amount of code if possible by handling
repetative actions such as adding Series to a chart.

Currently I am using something like this below to add a second Series to the
ActiveChart but also use almost identical code for Series 3, 4, 5 etc up to a
dozen or so. I sthere any way I can reference different Variables within
this code e.g. for Pri_Chart_Source_Sheet_2, Pri_Chart_Source_Sheet_3 ,
Pri_Chart_Source_Sheet_4 etc

With ActiveChart
If Pri_Series_Data_Column_2 <> Empty Then
Set PlotValuesSeries =
Sheets(Pri_Chart_Source_Sheet_2).Range(Pri_Series_Data_Column_2 & "2:" &
Pri_Series_Data_Column_2 & Last_Row_of_Pri_Source_Sheet_2)
Set PlotXValuesSeries =
Sheets(Pri_Chart_Source_Sheet_2).Range(Pri_Timestamp_Data_Column_2 & "2:" &
Pri_Timestamp_Data_Column_2 & Last_Row_of_Pri_Source_Sheet_2)
Series_Index_No = Series_Index_No + 1
.SeriesCollection.Add
Sheets(Pri_Chart_Source_Sheet_2).Range(Pri_Series_Data_Column_2 & ":" &
Pri_Series_Data_Column_2)
.SeriesCollection(Series_Index_No).Select
With Selection.Border
.Weight = xlHairline
.LineStyle = xlAutomatic
.ColorIndex = 5
End With
With Selection
.MarkerStyle = xlNone
End With
With Worksheets(Pri_Chart_Source_Sheet_2)
ActiveChart.SeriesCollection(Series_Index_No).XValues =
PlotXValuesSeries
ActiveChart.SeriesCollection(Series_Index_No).Values =
PlotValuesSeries
End With
End If
End With

 

[Bob Phillips]

Something like this

Sub Main()
FillSeries activechar, _
Sheets(Pri_Chart_Source_Sheet_2), _
Pri_Series_Data_Column_2, _
Pri_Timestamp_Data_Column_2, _
Last_Row_of_Pri_Source_Sheet_2
FillSeries activechar, _
Sheets(Pri_Chart_Source_Sheet_3), _
Pri_Series_Data_Column_3, _
Pri_Timestamp_Data_Column_3, _
Last_Row_of_Pri_Source_Sheet_3
End Sub

Sub FillSeries(chart As Object, sh As Worksheet, _
SeriesCol As String, TSCol As String, lastrow As Long)

If col2 <> Empty Then
With chart
Set PlotValuesSeries = _
sh.Range(SeriesCol & "2:" & SeriesCol & lastrow)
Set PlotXValuesSeries = _
sh.Range(TSCol & "2:" & TSCol & lastrow)
Series_Index_No = Series_Index_No + 1
.SeriesCollection.Add sh.Range(TSCol & ":" & SeriesCol)
With .SeriesCollection(Series_Index_No)
With .Border
.Weight = xlHairline
.LineStyle = xlAutomatic
.ColorIndex = 5
End With
.MarkerStyle = xlNone
End With
End With
With sh
chart.SeriesCollection(Series_Index_No).XValues = _
PlotXValuesSeries
chart.SeriesCollection(Series_Index_No).Values = _
PlotValuesSeries
End With
End If

End Sub


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Guest]

Thanks - sorry for the delay in getting back to you. Tried several times and
the Forum was down.

Got the following to work pretty well (with this example data). Now if only
I could do something neater with my repeated Calls to the Function!!

Many thanks again

Alec

Sub main()

Pri_Chart_Source_Sheet_2 = "SynchroELEVATIONSYNCHROTELLBACK"
Pri_Series_Data_Column_2 = "I"
Pri_Timestamp_Data_Column_2 = "B"
Last_Row_of_Pri_Source_Sheet_2 = 10916


Pri_Chart_Source_Sheet_3 = "SynchroELEVATIONSYNCHROTELLBACK"
Pri_Series_Data_Column_3 = "J"
Pri_Timestamp_Data_Column_3 = "B"
Last_Row_of_Pri_Source_Sheet_3 = 10916

Series_Index_No = 1


Call Add_Chart_Series(ActiveChart, Sheets(Pri_Chart_Source_Sheet_2),
Pri_Series_Data_Column_2, Pri_Timestamp_Data_Column_2,
Last_Row_of_Pri_Source_Sheet_2)
Call Add_Chart_Series(ActiveChart, Sheets(Pri_Chart_Source_Sheet_3),
Pri_Series_Data_Column_3, Pri_Timestamp_Data_Column_3,
Last_Row_of_Pri_Source_Sheet_3)
Call Add_Chart_Series(ActiveChart, Sheets(Pri_Chart_Source_Sheet_4),
Pri_Series_Data_Column_4, Pri_Timestamp_Data_Column_4,
Last_Row_of_Pri_Source_Sheet_4)


End Sub

Function Add_Chart_Series(Chart As Object, Source_Sheet As Worksheet,
Data_Column As Variant, Time_Column As Variant, lastrow As Variant)

If Data_Column <> Empty Then
With ActiveChart
Set PlotValuesSeries = Source_Sheet.Range(Data_Column & "7:" &
Data_Column & lastrow)
Set PlotXValuesSeries = Source_Sheet.Range(Time_Column & "7:" &
Time_Column & lastrow)
Series_Index_No = Series_Index_No + 1
.SeriesCollection.Add Source_Sheet.Range(Data_Column & "7:"
& Data_Column & lastrow)
.SeriesCollection(Series_Index_No).Select
With Selection.Border
.Weight = xlHairline
.LineStyle = xlAutomatic
' .ColorIndex = 5
End With
With Selection
.MarkerStyle = xlNone
End With
With Source_Sheet
Chart.SeriesCollection(Series_Index_No).XValues =
PlotXValuesSeries
Chart.SeriesCollection(Series_Index_No).Values =
PlotValuesSeries
End With
End With
End If

End Function