Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Repetative Tasks and Variables
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Repetative Tasks and Variables
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I reduce repetative code | Excel Discussion (Misc queries) | |||
Formula needed for repetative spreadsheet | Excel Worksheet Functions | |||
Repetative text | Excel Worksheet Functions | |||
Repetative Row Deletes | Excel Discussion (Misc queries) | |||
Repetative conditional formatting | Excel Programming |