Excel VBA - Passing a dynamic range to charts

Question

I'm writing a code that will check the status of a sheet and when it's changed it will automatically run some calcs on it. But I also wish to have a graph which will get refreshed with the new set of data from that sheet.

For that I've used the Worksheet_Change function and it works ok. It calls the sub with the calculations and calls the sub that contains the chart modification code. They run as planned with one exception. The range that gets passed to the Chrt1 sub (responsible for the chart functionality) does not get updated on the graph once it has been called out for the first time.

I'm aware that this can be overcome with Excel built-in tables function but I'd like to code this simple routine in anyways.

The Worksheet_Change function looks like this:

Sub Worksheet_Change(ByVal Target As Range)
   
Application.EnableEvents = False
      
AutoChangeTest

Application.EnableEvents = True

End Sub

And the main module code is below:

Sub AutoChangeTest()

Dim s1 As Worksheet, s2 As Worksheet
Dim i As Integer, j As Integer, lrow As Integer, lrow2 As Integer

Set s1 = Sheets("Arkusz3")

On Error GoTo Err1

lrow = s1.Cells(s1.Rows.Count, 1).End(xlUp).Row

For i = 1 To lrow
    s1.Cells(i, 2) = s1.Cells(i, 1) * 2
Next

Call Chrt1(Range(s1.Cells(1, 1), s1.Cells(lrow, 2)), s1)

Err1:

If Not IsNumeric(s1.Cells(i, 1)) Then
    s1.Cells(i, 1).Activate
End If

End Sub

Sub Chrt1(r1 As Range, s1 As Worksheet)

Dim c1 As Shape
Dim s As Worksheet
Dim cht As ChartObject
Dim i As Integer

i = 0
Set r = r1
Set s = s1

For Each cht In s.ChartObjects
    i = i + 1
Next
    
If i = 0 Then
    Set c1 = s.Shapes.AddChart
End If

c1.Chart.SetSourceData (r)

End Sub

Any hint will be greatly appreciated.

Answer 1

Some suggestions in the code below:

Sub AutoChangeTest()

    Dim ws As Worksheet 'avoid variable names with 1/l - too unclear
    Dim i As Long, lrow As Long 'always use long over integer
    
    Set ws = ThisWorkbook.Worksheets("Arkusz3")
    
    lrow = ws.Cells(ws.Rows.Count, 1).End(xlUp).row
    
    On Error GoTo exitHere
    Application.EnableEvents = False 'don't re-trigger this sub...
    For i = 1 To lrow
        With ws.Cells(i, 1)
            'easier to test than to trap an error if non-numeric
            If IsNumeric(.Value) Then
                ws.Cells(i, 2) = .Value * 2
            Else
                ws.Select
                .Select
                MsgBox "Non-numeric value found!"
                GoTo exitHere 'acceptable use of Goto I think
            End If
        End With
    Next
    
    'don't think you need a separate method for this...
    If ws.ChartObjects.Count = 0 Then ws.Shapes.AddChart 'no need to loop for a count
    'assuming there will only be one chart...
    ws.ChartObjects(1).Chart.SetSourceData ws.Range(ws.Cells(1, 1), ws.Cells(lrow, 2))
    
exitHere:
    If Err.Number <> 0 Then Debug.Print Err.Description
    Application.EnableEvents = True
    
End Sub