Repetative Tasks and Variables
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
"Bob Phillips" wrote:
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)
"alecbowman" wrote in message
...
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_Timesta mp_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).XVal ues =
PlotXValuesSeries
ActiveChart.SeriesCollection(Series_Index_No).Valu es =
PlotValuesSeries
End With
End If
End With
|