Insert Excel Graphs using Class to define parameters

Question

I have a loop in my main sub routine which creates multiple graphs. This is run across a number of sheets, creating the same graphs for each sheet, but with different ranges and other variables.

I have a separate subroutine for each graph (two are shown below, others are similar and can be posted if necessary), however it would seem more logical to organize this better, specifically using one sub with more variables to insert a graph, as I expand and add more graphs.

I can broadly see two solutions:

Make a sub routine with a large case select statement

Create a class to do this

I'm leaning towards the latter, as it seems like a neater way to achieve what I wish. Through what I've read from CPearson and stackoverflow, I can use classes to set/let/get properties, but I'm unsure as to the best way to organize the code overall.

Do I:

Use a method to let/set all properties at once, then insert a graph by getting all of these

set each property individually in the main sub routine

Call a subroutine to specifically set the values of a class module

Or, leave the class altogether and just:

Use public variables defined in the main/ subroutine

Pass variables to the insert graph sub routine

Code for calling the insert graph sub routines is directly below, followed by the graphs themselves:

For Each b In rngQueries
    Set ToPrint = Worksheets(b.Value).Range("A" & Rows.Count).End(xlUp).Offset(3, 0)
    b.Offset(0, 3).Value = ToPrint.Address
    b.Offset(0, 4).Value = Worksheets(b.Value).Range("A1000").End(xlUp).Address
    Select Case b.Offset(0, 2).Value
        Case "Today"
            InsertBar ToPrint, b.Offset(0, 3).Value, b.Offset(0, 4).Value
        Case "TimeSeries"
            InsertLine ToPrint, b.Offset(0, 3).Value, b.Offset(0, 4).Value
    End Select
Next

Graphs:

Sub InsertLine(ToPrint As Range, PosTopLeft As String, PosBottomLeft As String)
    Dim strRange As String
    Dim rngChart As Range
    Dim myChart As Chart

    lngStartRow = Sheets(ToPrint.Worksheet.Name).Range(PosTopLeft).Row
    lngEndRow = Sheets(ToPrint.Worksheet.Name).Range(PosBottomLeft).Row

    Sheets(ToPrint.Worksheet.Name).Activate
    Sheets(ToPrint.Worksheet.Name).Range("$A$" & CStr(lngStartRow) & ":$C$" & CStr(lngEndRow)).Select

    Set myChart = ActiveSheet.Shapes.AddChart(xlLine, 500, 200).Chart

    With myChart
        .ChartArea.Format.TextFrame2.TextRange.Font.Size = 8
        .HasTitle = True
        .ChartTitle.Text = ToPrint.Worksheet.Name & " Hits & Attempts - (Last 14 Days)"
        .SeriesCollection(1).Name = Range("B" & lngStartRow - 1).Value
        .SeriesCollection(2).Name = Range("C" & lngStartRow - 1).Value
    End With
End sub

Sub InsertBar(ToPrint As Range, PosTopLeft As String, PosBottomLeft As String)
    Dim strRange As String
    Dim rngChart As Range
    Dim myChart As Chart

    lngStartRow = Sheets(ToPrint.Worksheet.Name).Range(PosTopLeft).Row
    lngEndRow = Sheets(ToPrint.Worksheet.Name).Range(PosBottomLeft).Row

    Sheets(ToPrint.Worksheet.Name).Activate
    Sheets(ToPrint.Worksheet.Name).Range("$A$" & CStr(lngStartRow) & ":$D$" & CStr(lngEndRow)).Select

    Set myChart = ActiveSheet.Shapes.AddChart(xlColumnClustered, 500, 10, , 175).Chart

    With myChart
        .ChartArea.Format.TextFrame2.TextRange.Font.Size = 8
        .HasTitle = True
        .ChartTitle.Text = ToPrint.Worksheet.Name & " Receiving Sim Stats - (Today Only)"
        .SeriesCollection(1).Name = Range("B" & lngStartRow - 1).Value
        .SeriesCollection(2).Name = Range("C" & lngStartRow - 1).Value
        .SeriesCollection(3).Name = Range("D" & lngStartRow - 1).Value
    End With
End Sub

To summarize, I am currently passing ToPrint, PosTopLeft and PosBottomLeft to the subroutine from a case select in the main sub, and defining some constants in the graphs sub routine, such as graph position, title etc. It seems more logical to organize this better. Which is the neatest, most logical way to proceed?

Finally, I should say that I am relatively new to this board, if the question is not correctly presented, or not appropriate for here, please let me know and I'll update/ remove. Thanks in advance for any help you can give.

Answer 1

I'm not sure a class is exactly what you want - a chart is already an object with the properties/methods you want. You want to create a sub that takes the arguments you need, like

Sub CreateChart(ByVal targetRange As Range, ByVal PosTopLeft As String, byval PosBottomLeft As String, ByVal lastColumn As Long, ByVal chartType As XlChartType)
    Dim strRange As String
    Dim rngChart As Range
    Dim myChart As Chart
    lngstartrow = Sheets(ToPrint.Worksheet.Name).Range(PosTopLeft).Row
    lngendrow = Sheets(ToPrint.Worksheet.Name).Range(PosBottomLeft).Row

    Sheets(ToPrint.Worksheet.Name).Activate
    Sheets(ToPrint.Worksheet.Name).Range(.Cells(lngstartrow, 1), (.Cells(lngendrow, lastColumn))).Select

    Select Case XlChartType
        Case xlLine
            Set myChart = ActiveSheet.Shapes.AddChart(xlLine, 500, 200).Chart
        Case xlColumnClustered
            Set myChart = ActiveSheet.Shapes.AddChart(xlColumnClustered, 500, 10, , 175).Chart
    End Select
End Sub

And then you can have some variable that picks the number of series by the type of chart, then add all the series with a loop. You can also have a string that picks the title based on case.

The key is to combine as many parts of the process into a single process. Combining something a little like this -

Case xlLine
    numberofseries = 2
Case xlColumnClustered
    numberofseries = 3

For i = 1 To numberofseries
    .SeriesCollection(i).Name = .Cells(lngstartrow - 1, i + 1).Value
Next

So your case statements don't have to do very much, all they need to do is assign the proper variables to be used in the next parts.