Using VBA to select a dynamic range of cells and create a chart

Question

I am attempting to use VBA to create a chart using dynamic ranges. Specifically, I have an Excel table as follows

Based on this data, I would like to create a chart, with the date ranges changed as per requirement. For example, at one instance, I would be required to produce a chart for 1st July - 6th July, and at another, from 10th July - 14th July.

The following is my attempt at generating such a chart, but I feel there would be much better ways other than mine. Hence, my question, is there any other, better way?

1- I first enter the date values in 'helper cells' for which a chart is sought. In this case, cell M24 has the value 10th July, while cell M26 has the value 14th July.

2- Then, I use the match() function to find the positions from the date column of my Table. The function is =MATCH(M24,Table1[Dates],0), and =MATCH(M26,Table1[Dates],0).

3- Given that I have the relative positions for the dates, I then use the following VBA code to generate the chart:

Private Sub CommandButton1_Click()
    Dim mySheet As Worksheet
    Dim myShape As Shape
    Dim myChart As Chart
    Dim myVal1 As String
    Dim myVal2 As String

    Set mySheet = ActiveWorkbook.Worksheets("dataSheet")
    If myShape Is Nothing Then
        Set myShape = mySheet.Shapes.AddChart(XlChartType:=xlColumnClustered, _
            Left:=CommandButton1.Left + CommandButton1.Width + 2, _
            Width:=370, Height:=200)
    End If

    'In the following, I am offsetting from the first cell
    'of my Table, which contains the `value 1-Jul.
    'My objective is to use the range 10-Jul to 14th Jul,
    'so I also add a column offset
    'Cells O24 and O26 contain the results of the match functions

    myVal1 = Range("B4").Offset(Range("O24").Value, 0).Address
    myVal2 = Range("B4").Offset(Range("O26").Value, 4).Address
    Set myChart = myShape.Chart
    myChart.ChartType = xlLine
    myChart.SetSourceData Source:=Sheets("dataSheet") _
        .Range(CStr(myVal1 & ":" & myVal2))
End Sub

So, now hoping that my question is clear, could somebody please educate me of a better method than this one? This seems to be more a hacking method than proper coding to me...

Many thanks in advance!

Answer 1

As what Dave said, it is pretty solid. But you can try this one:

Private Sub CommandButton1_Click()
    Dim d1 As Range, d2 As Range
    Dim ws As Worksheet: Set ws = Thisworkbook.Sheets("datasheet")
    '~~> Look for the dates
    With ws.Range("Table1[Dates]")
        Set d1 = .Find(ws.Range("M24").Value, .Cells(.Cells.Count))
        Set d2 = .Find(ws.Range("M26").Value, .Cells(.Cells.Count))
    End With
    '~~> Handle unavailable dates, interchanged inputs
    Dim i As Long, j As Long
    If d1 Is Nothing Or d2 Is Nothing Then MsgBox "Invalid coverage": Exit Sub
    If d2.Value > d1.Value Then i = 0: j = 4 Else i = 4: j = 0
    '~~> Set the chart source
    Dim chsource As Range
    Set chsource = ws.ListObjects("Table1").HeaderRowRange
    Set chsource = Union(chsource, ws.Range(d1.Offset(0, i), d2.Offset(0, j)))
    '~~> Clean up existing chart
    Dim sh As Shape
    For Each sh In Me.Shapes
        If sh.Type = msoChart Then sh.Delete
    Next
    '~~> Create the chart
    With Me.Shapes.AddChart(, Me.CommandButton1.Left + _
        Me.CommandButton1.Width + 2, Me.CommandButton1.Top, _
        370, 200).Chart
        .ChartType = xlLine
        .SetSourceData chsource
        .SetElement msoElementChartTitleAboveChart
        .ChartTitle.Text = "Trend Chart"
    End With
End Sub

You still retrieve dates on M24 and M26 respectively, but no need to use additional ranges with the formulas.
If the values aren't found, it returns a message box.
As long as the dates are found it will create the graph regardless where the user put it.
Also I did 2 ways of accessing the Table, 1 is using Range and the other is using ListObjects.
That is intentional for you to get a hang of both. Sometimes one is better than the other.
Also I am explicit in using Me (which pertains to the sheet that contain your CB).
I also think that your graph should have the correct legends instead of Series(x) names so I added the header to the source. HTH.