addchart2 behavior issues - addchart2 modifies existing charts when creating a new chart

Don OHara
Don OHara used Ask the Experts™
on
Excel VBA Charting Question.  (Office 365 on W-10)

In one VBA macro I use addchart2 to sequentially create a series of similar charts, each with differentt raw data.
The first chart is perfect.
However, when I create the second chart, the first chart has its data changed to the new data in the second chart.
Then, when I create the third chart, both the first and second charts are changed to the third chart.

I expected that addchart2 would close out (deactivate, unselect) the previous chart before creating a new chart. I have tried 'ActiveChart.Deactivate' but to no avail.

Please advise,

thanks, Don
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
byundtMechanical Engineer
Most Valuable Expert 2013
Top Expert 2013

Commented:
Please post a sample file & code that demonstrates the problem. If we can reproduce the problem, we can fix it. Most likely, the problem is with the code, but a suitable fix may depend on workbook layout.

Author

Commented:
Thanks.
Attached is the bas file and a copy of the resulting charts.

Don
trychart2aDOH.bas
trychart2achartexampleDOH.xlsx
byundtMechanical Engineer
Most Valuable Expert 2013
Top Expert 2013

Commented:
The code references worksheets Work and Sheet1, which were not included in the posted workbook.

The problem with the charts all referencing the same data is the following statement. It needs to be different for each chart added. When you post a workbook with the worksheets not previously furnished, please also specify how you want the charts to be titled, and which ranges they ought to be taking their data from.
    ActiveChart.SetSourceData Source:=Range("Work!$A$1:$B$9")

Open in new window

Mechanical Engineer
Most Valuable Expert 2013
Top Expert 2013
Commented:
The following code does not fix your problem, but it does get it to a place where I believe the fix will be easy. It runs in Excel 2016/Office 365. If it does not run at your end, please advise which version of Excel you are using.

Among other things, I fixed the following issues:
1. Your Dim statements had the variable type only at the end of the statement. In VBA, unlike a number of other languages, each variable must be declared as something. If you put the variable type only at the end, the last variable will be that type, but all others will be Variant.
2. You used .Select and .Activate throughout the code. These statements are almost never required, and will slow execution down and sometimes cause runtime errors. The better approach is to create worksheet and range variables and then use those in your code.
3. As previously noted, each chart had a hard link to data on worksheet Work cells A1:B9. I changed the code so each chart refers to column A and then either columns B, C or D.
4. I replaced the copy and paste statements with ones setting the value of the destination range equal to that of the source range.
5. I deleted dead code (statements that you had commented out).
Sub trychart2a()
'
' trychart2a Macro
'
   Dim jset As Integer, kset As Integer
   Dim lpos As Long, tpos As Long, www As Long, hhh As Long
   Dim ws As Worksheet
'
   Application.ScreenUpdating = False
   
   lpos = 0
   tpos = 0
   www = 300
   hhh = 250
   kset = 1
'
   On Error Resume Next
   Set ws = Worksheets("Profit")
   On Error GoTo 0
   If ws Is Nothing Then
        Sheets.Add.Name = "Profit"
        Set ws = Worksheets("Profit")
   End If
   
   With ws.ChartObjects
       If .Count > 0 Then .Delete
   End With
'
   For kset = 2 To 4
   
      jset = kset - 1
      Call trychart2(jset, kset, lpos, tpos, www, hhh)
      
      If (kset - 1) Mod 2 = 0 Then
         lpos = 0
         tpos = tpos + hhh + 10
     Else
         lpos = lpos + www + 10
     End If
   Next kset
End Sub

Sub trychart2(jset As Integer, kset As Integer, lpos As Long, tpos As Long, www As Long, hhh As Long)
'
' trychart2 Macro
'
    Dim Cht As Chart
    Dim rg As Range
    
    With Worksheets("Work")
        .Range("D1").Value = Worksheets("Sheet1").Range("C1:E1").Cells(1, jset).Value
        .Range("B2:B9").Value = Worksheets("Sheet1").Range("C42:E49").Columns(jset).Value
        Set rg = Range("A1:A9")
    End With

    Set Cht = Sheets("Profit").Shapes.AddChart2(227, xlLine, lpos, tpos, www, hhh).Chart
    With Cht
        .ChartType = xlXYScatterLinesNoMarkers
        .SetSourceData Source:=Union(rg, rg.Offset(, jset))
        .Axes(xlCategory).TickLabelPosition = xlLow
    End With
End Sub

Open in new window

Author

Commented:
Thanks byundt for your answers.
I now have a working program that generates 20 charts on one sheet. Your insight about not reusing the same source data location was the key answer.

Now I will modify the process to generate five sheets encompassing the 20 charts in chart groups.

Thanks, Don
byundtMechanical Engineer
Most Valuable Expert 2013
Top Expert 2013

Commented:
Don,
Please keep posting in this thread if you run into issues implementing the suggestions I made.

In the small world department, I grew up quite near you in Medfield.

Brad

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial