Excel vba duplicate object method

Question

I am trying to use the Excel vba duplicate method to duplicate a chart.  The code produces a duplicate chart but when I try to do a copyPicture I get this error

Run-time error '440':

Method 'CopyPicture' of object 'Shape' failed

Here is the code.  I have the line (now commented out) to make sure that CopyPicture does indeed work - at least from the object to be copied.  I am working in Excel 2007 but I tried this in 2013 and got the same error. 

Here's a link to a copy of the xlsm on the Google drive.

https://drive.google.com/file/d/0Bxu8hnkSZhMGVGhadzMwRUxBOEU/view?usp=sharing

Sub x()

    Dim dup As Object

    'Sheets(1).ChartObjects(1).CopyPicture

    Set dup = Sheets(1).ChartObjects(1).Duplicate

    dup.CopyPicture

End Sub

Thanks to anyone who can help me.

Answer 1

 Do you foresee any future problems with it?

Wait, I'll get my crystal ball... where is it... damn, I can't find it, please come back in... ah... the future. ;-)))

Hint:

When you declare variables as Object (means un-typed) the Intellisense doesn't work in the VBA editor.

Try "Dim dup As Chart" and then write "dup." into the VBA editor and you'll get a dropdown list of all possible members.

If not check this settings under Tools\Options:

Andreas.

Answer 2

I tried to do error trapping I still got the yellow highlighting on my paste statement.  

At first check this settings:

And at min. your Main macros should use an error handler "On Error GoTo", like this sample:

Sub Example_ErrorHandler()

  On Error GoTo Errorhandler

  Err.Raise 1, "Example_ErrorHandler", "Error description"

  Application.EnableCancelKey = xlErrorHandler

  Do

    DoEvents

  Loop Until False

  Exit Sub

Errorhandler:

  If Err.Source = "" Then Err.Source = Application.Name

  Debug.Print "Source     : " & Err.Source

  Debug.Print "Error      : " & Err.Number

  Debug.Print "Description: " & Err.Description

  If MsgBox("Error " & Err.Number & ": " & vbNewLine & vbNewLine & _

      Err.Description & vbNewLine & vbNewLine & _

      "Enter debug mode?", vbOKCancel + vbDefaultButton2, Err.Source) = vbOK Then

    Stop 'Press F8 twice

    Resume

  End If

End Sub

Have also a look into this tutorial, there is a section for error handling and much more:

http://www.wiseowl.co.uk/blog/s161/online-excel-vba-training.htm

Andreas.

Answer 3

What can I say but "thank you again!"  I also appreciate the tips on dim as chart rather than as object and how to see/change settings in the editor.

BTW you also do a pretty imitation of the old Eight Ball toy.

You have been so helpful that I am tempted to ask one more question, but if you don't have time, no problem.  I got into this whole mess when some paste (s) were causing errors in Excel VBA and when I tried to do error trapping I still got the yellow highlighting on my paste statement.  Any thoughts - with or without your crystal ball?

Actually this problem is described in my earlier question

"VBA: see if there is a chart in the paste buffer"

Answer 4

Andreas - thank you, thank you thank you!!!

Since you were so gracious to ask if there were any more questions, perhaps I could ask one more.

I may want to do other things with my duplicate in the future, and it just seems right to have a duplicating process that when it starts with a chart produces a chart.  so I tried the following and it works - at least for CopyPicture.  Do you foresee any future problems with it?

Sub myDuplicateChart()

    Dim xShape As Shape

    Dim dup As Object

    Set xShape = Sheets(1).ChartObjects(1).Duplicate

    Set dup = xShape.Chart

    dup.CopyPicture

End Sub

Answer 5

The reason is that Duplicate returns a Shape object and you have to call the Chart inside the shape to get the picture.

Sub x()

  Dim dup As Shape

  Set dup = Sheets(1).ChartObjects(1).Duplicate

  dup.Chart.CopyPicture

End Sub

Any more questions?

Andreas.