• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Charts, strange behavior on copying SeriesCollection(i).formula

Arnout Brandt

New Member
Hi all,

I encounter the following problem regarding the chartobject.
This is the situation:

1. I copy a chart from a master sheet to a new workbook using copy paste
2. In the target book I want to replace the formula from the seriescollection (because it is referenced to the master sheet).

Code:
chtTarget.seriesCollection(i).formula = chtSource.SeriesCollection(i).formula

Now this is what happens:
- when I run the code without breakpoints an error occurs.
- when I run the code with a breakpoint on the above mentioned line no error occurs and the code works fine.

Of course I am willing to provide more code, for now, without to many details, is any one familiar with this behavior?

Help is much appreciated.

Kind regards,

Arnout
 
Arnout

Firstly, Welcome to the Chandoo.org Forums

Sounds strange

Are you able to share the file for us to review further?
 
Tnx Hui,

Providing the entire file will not work unfortunately, it only works with a large DB.
What I can do is provide you the code of the routine and if that doesn't work for you I can try to simulate it in a small program.

this is the entire code:

Code:
Sub CopyCharts()
Dim chtSource As ChartObject
Dim chtGoal As ChartObject
Dim objSeries As Series
Dim iObjects As Integer
Dim iSeries As Integer
Dim strSource As String

'errhandler switched of for debugging purpose
'On Error Resume Next

iObjects = 0

With Workbooks(gstrMainname).Sheets("Samenvatting") 'this is the source workbook where te chart needs to be copied from
 
  If .ChartObjects.Count > 0 Then
 
      For Each chtSource In .ChartObjects
 
   'copy/paste the chart(s)
      chtSource.Copy
      ActiveSheet.Paste 'this is the target workbook
 
      iObjects = iObjects + 1
  
      Set chtGoal = ActiveSheet.ChartObjects(iObjects)
  'positioning
     chtGoal.Left = chtSource.Left
     chtGoal.Top = chtSource.Top
 
     iSeries = 0

  'copy formula
     For Each objSeries In chtSource.Chart.SeriesCollection
 
          iSeries = iSeries + 1
 
      'I tried several variants here for all of them goes:
      'when running without breakpoint: an error occurs (2nd line): 1004, application defined or object defined error
      'with breakpoint and stepping through: every thing works fine

          strSource = chtSource.Chart.SeriesCollection(iSeries).Formula
          chtGoal.Chart.SeriesCollection(iSeries).Formula = strSource

      Next objSeries
 
      Set chtGoal = Nothing 'destroy goal chart

    Next chtSource
 
  End If
 
End With
 
End Sub
 
If I use that code on some dummy data it works fine

I suspect that you are using named Formula for the charts which are relevant to the source sheet?

Can you send a copy of the Named Formula relevant to the chart that is causing it to crash ?
or a Dummy file as suggested

If you want you can email me the file directly, confidentiality assured.
 
The named formula to the sourcesheet needs to be replaced with the same formula referring to the active sheet.
That could be part of the problem.

I tried making a small program simulating it but did not manage yet to encounter the same problem.
I keep trying.

If you are interested I can show you using GotoMeeting / Teamviewer, the code is part of a larger program bound to a large sql DB, not easy to detach supply this.
 
Dear Hui and others that are interested,

I managed to solve the problem, but the reason is very strange (imho).

I found out that getting rid of one line in a totally different part of the code solved the issue.

At the very beginning of the program I do a count of the number of records that needs to handled, the output of the recordset is kept on a seperate sheet.
In the formatting of the sheet in the heading a hyperlink is set that returns to the main sheet.

Code:
   .Hyperlinks.Add Anchor:=.Range("A3"), Address:="", SubAddress:="Main!A1", TextToDisplay:="Back "

Loosing this line of code solved the problem! Don't ask me why.

Regards

Arnout
 
Back
Top