CODEWITHSUNDEEP
excelvbaexcel-charts
Chris
Chris

Reputation: 11

Passing a dynamic range to charts

I want to check the status of a sheet and when changed automatically run some calculations. I also wish refresh a graph with the new data from that sheet.

I used the Worksheet_Change function. 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:

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

The main module code:

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

Upvotes: 0

Views: 206

Answers (3)

Chris
Chris

Reputation: 11

Thank you all for support. The basic code that works is shown below. It isn't the best looking but it does the job.

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


Set cht = s.ChartObjects(1)


cht.Chart.SetSourceData Source:=r


End Sub

Upvotes: 0

Jon Peltier
Jon Peltier

Reputation: 6053

In your Chrt1 procedure, this bit

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

can be replaced by the following:

If s.ChartObjects.Count = 0 Then
    Set c1 = s.Shapes.AddChart
End If

But what is c1 if you don't have to add a chart? You haven't defined it, and the On Error means you never find out that it's broken.

Assuming you want the last chart object to be the one that is changed:

If s.ChartObjects.Count = 0 Then
    Set c1 = s.Shapes.AddChart
Else
    Set c1 = s.ChartObjects(s.ChartObjects.Count)
End If

And you should declare c1 as a ChartObject.

Finally, remove the parentheses around r in this line:

c1.Chart.SetSourceData r

Upvotes: 1

Tim Williams
Tim Williams

Reputation: 166126

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

Upvotes: 1

Related Questions