addchart2 behavior issues - addchart2 modifies existing charts when creating a new chart
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
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
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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.
Thanks.
Attached is the bas file and a copy of the resulting charts.
Don
trychart2aDOH.bas
trychart2achartexampleDOH.xlsx
Attached is the bas file and a copy of the resulting charts.
Don
trychart2aDOH.bas
trychart2achartexampleDOH.xlsx
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.
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")
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).
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
Premium Content
You need an Expert Office subscription to comment.Start Free Trial