[VBA] Run Time Error

[Domenic]

I have a macro that moves a picture within a cell to a comment within the same cell. It does this for all pictures within the active sheet. In Excel 2013, the macro runs fine. However, in Excel 2016, if I step through the code manually, it works fine. But if I run it I get the following run-time error...

Run-time error:  '424':

Object required

...which occurs at the following line...

.Chart.Pictures(1).Delete

When I step through it manually and it gets to the above line, the picture exists within the chart. So I don't get an error. But when I run the macro and it gets to the above line, the picture doesn't exist for some reason, hence the error. Does anyone know why this is the case? Am I overlooking something? Is this some sort of bug?

[COLOR=darkblue]Option[/COLOR] [COLOR=darkblue]Explicit[/COLOR]

[COLOR=darkblue]Sub[/COLOR] MovePicturesFromCellIntoComments()

    [COLOR=darkblue]Dim[/COLOR] oShp [COLOR=darkblue]As[/COLOR] Shape
    [COLOR=darkblue]Dim[/COLOR] sSaveAsFilename [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
    
    [COLOR=darkblue]Const[/COLOR] ZoomFactor As [COLOR=darkblue]Double[/COLOR] = 2.5 [COLOR=green]'two and a half times larger[/COLOR]
    
    [COLOR=darkblue]With[/COLOR] ActiveSheet.ChartObjects.Add(Left:=0, Top:=0, Width:=250, Height:=250)
        .Border.LineStyle = 0
        [COLOR=darkblue]For[/COLOR] [COLOR=darkblue]Each[/COLOR] oShp [COLOR=darkblue]In[/COLOR] ActiveSheet.Shapes
            [COLOR=darkblue]If[/COLOR] oShp.Type = msoPicture [COLOR=darkblue]Then[/COLOR]
                sSaveAsFilename = Environ("temp") & "\" & oShp.Name & ".jpg"
                oShp.Copy
                [COLOR=darkblue]With[/COLOR] .Chart
                    .ChartArea.Width = oShp.Width
                    .ChartArea.Height = oShp.Height
                    .Paste
                    .Export sSaveAsFilename
                [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
                [COLOR=darkblue]With[/COLOR] oShp.TopLeftCell.AddComment
                    .Text Text:=""
                    [COLOR=darkblue]With[/COLOR] .Shape
                        .Width = oShp.Width * ZoomFactor
                        .Height = oShp.Height * ZoomFactor
                        .Fill.UserPicture sSaveAsFilename
                    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
                [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
                .Chart.Pictures(1).Delete
                oShp.Delete
                Kill sSaveAsFilename
            [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
        [COLOR=darkblue]Next[/COLOR] oShp
        .Delete
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
    
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

 

[JackDanIce]

A guess, but your first With object is:

ActiveSheet.ChartObjects.Add(Left:=0, Top:=0, Width:=250, Height:=250)

Which makes the error occur on:

ActiveSheet.ChartObjects.Add(Left:=0, Top:=0, Width:=250, Height:=250).Chart.Pictures(1).Delete

It could be it's the wrong attribute or object that you're trying to delete, in relation to the original With object? Maybe incorrect syntax?

 

[Domenic]

Jack, thanks for looking into this, but it doesn't appear to be the cause. When I try running a simple code that creates a chart object, copies a shape or picture, and pastes it into the chart, the shape or picture doesn't actually get pasted. Although, it does get pasted when I step through it manually. Here's the simple test that I tried...

[COLOR=darkblue]Sub[/COLOR] test()
    
    [COLOR=darkblue]Set[/COLOR] oChart = ActiveSheet.ChartObjects.Add(Left:=0, Top:=0, Width:=250, Height:=250).Chart
        
    ActiveSheet.Shapes(1).Copy

    oChart.Paste
    
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

As I mentioned, this only occurs in Excel 2016. It works fine in Excel 2013.

Any other thoughts?

 

[Sektor]

Try to insert some dummy Msgbox or Debug.Print before pasting. Or insert Windows API Sleep function call:

Public Declare Sub Sleep Lib “kernel32” (ByVal dwMilliseconds As Long)

 

[Domenic]

Thanks Sektor. I tried all three of your suggestions, but the same thing happens. The shape or picture doesn't get pasted into the chart. It must be some sort of bug. Although, I tried searching Google, but didn't find anything regarding this issue.

Any other thoughts?

 

[Sektor]

Yes, this is really strange. I've figured out that right after pasting (.Paste) .... there's no picture! MsgBox ch.Pictures.Count shows 0! Although if to go step-by-step, MsgBox shows 1.

 

[Dryver14]

I have been playing with this for a while and it doesn't seem to know that it has done all the pictures so goes looking for 1 more and hence the runtime error object required

 

[Domenic]

I have been playing with this for a while and it doesn't seem to know that it has done all the pictures so goes looking for 1 more and hence the runtime error object required

Actually, if you test the code in Post #3 in this thread you'll see that a simple copy/paste into the chart results in no picture being pasted. Note, this only occurs in Excel 2016. It works fine in Excel 2013.