How to fix Excel 2016 Object Required Error that worked in Excel 2010

Question

I have recently migrated to Excel 2016 from 2010 and one of the sheets I inherited from a colleague has stopped working. I am in the process of learning VBA but would appreciate help with the run-time error that I keep getting when running the below code.

I believe it results from the way variables are declared (or seemingly not in this case). The function takes in a range which overlays a chart. It then exports the chart as a .png image. Another thing I don't understand is why it works fine in Excel 2010 but not 2016?

Error 424 - Object Required:

Error 424 - Object Required

The line the error occurs:

 With .Pictures(1)

The code:

Sub createPNG(sheetName As String, rangeName As String, fileName As String)

  Dim vFilePath As Variant
  Dim rSelection As Range
  Dim sDefaultName As String

  Sheets(sheetName).Range(rangeName).Select

  Set rSelection = Selection

  vFilePath = "Z:\marginsOutput\Charts\" & fileName & ".png"
  '-- copy selected range as picture (not as bitmap)
  rSelection.CopyPicture Appearance:=xlScreen, Format:=xlPicture

  '--Create an empty chart, slightly larger than exact size of range copied
  With Sheets(sheetName).ChartObjects.Add( _
      Left:=rSelection.Left, Top:=rSelection.Top, _
      Width:=rSelection.Width + 2, Height:=rSelection.Height + 2)

  With .Chart
  ' clean up chart
  .ChartArea.Format.Line.Visible = msoFalse

  ' paste and position picture
  .Paste
  With .Pictures(1)
    .Left = .Left + 2
    .Top = .Top + 2
  End With

  ' export
  .Export CStr(vFilePath)
End With

' remove no-longer-needed chart
.Delete
  End With

End Sub

Which line of code is it breaking on? You can tell by hitting the Debug button in the run-time error pop-up. — DavidN, Jan 31 '19 at 14:47
Look at the nesting of your `With` statements. Due to triple nesting, your object is not valid. You should have `Sheets(SheetName).Pictures(1)` as your object. — Darrell H, Jan 31 '19 at 15:00
I'm not even sure you need the code within the last `With` statement? Try commenting out the line that's giving you the error + the following 3 lines and see if you're still getting a desired result. — DavidN, Jan 31 '19 at 15:07
Thanks guys, actually commenting out that code block results in the function exporting blank images. @Darrell, modifying that line gives me an 'Unable to get Pictures property of the Worksheet class' error — ussmano, Jan 31 '19 at 15:11
Unfortunately that also exports blank images and shifts the original chart — ussmano, Jan 31 '19 at 15:29
@DarrellH `With .Pictures(1)` is correct, because the picture was pasted into the chart. — Jon Peltier, Feb 15 '19 at 15:14

score 0 · Answer 1 · answered Jan 31 '19 at 15:41

I find sometimes in VBA the .paste just doesn't seem to work the first time around especially if the item is already in the clipboard! I've added a few lines to check if there is an picture present before trying to select the picture! if it's not found then it will attempt the .paste again. you should add some control to this to stop it looping on paste errors!

try the edit below;

Sub createPNG(sheetName As String, rangeName As String, fileName As String)

  Dim vFilePath As Variant
  Dim rSelection As Range
  Dim sDefaultName As String

  Sheets(sheetName).Range(rangeName).Select

  Set rSelection = Selection

  vFilePath = "Z:\marginsOutput\Charts\" & fileName & ".png"

  '-- copy selected range as picture (not as bitmap)
  rSelection.CopyPicture Appearance:=xlScreen, Format:=xlPicture

  '--Create an empty chart, slightly larger than exact size of range copied
  With Sheets(sheetName).chartobjects.Add( _
      Left:=rSelection.Left, Top:=rSelection.Top, _
      Width:=rSelection.Width + 2, Height:=rSelection.Height + 2)

  With .Chart
  ' clean up chart
  .ChartArea.Format.Line.Visible = msoFalse

  ' paste and position picture
  .Paste
  If .Pictures.Count = 0 Then
        .Paste
  End If
  With .Pictures(1)
    .Left = .Left + 2
    .Top = .Top + 2
  End With

  ' export
  .Export CStr(vFilePath)
End With

' remove no-longer-needed chart
.Delete
  End With

End Sub

Hi Max, thanks for the response. When I run that it still eventually hit the .Pictures(1) line and throws the error. However, the If .Pictures.Count = 0 condition is being met every time so it does indeed seem that that is the issue — ussmano, Jan 31 '19 at 15:52

Asger · Accepted Answer · 2019-02-14T08:38:51.877

0

It works, if you just select the ChartObject before pasting a picture into its Chart.
This is one of the seldom cases, where a .Select helps - otherwise see How to avoid using Select in Excel VBA

With Sheets(sheetName).ChartObjects.Add( _
    Left:=rSelection.Left, Top:=rSelection.Top, _
    Width:=rSelection.Width + 2, Height:=rSelection.Height + 2)
    .Select
    With .Chart
        ' ...
    End With
End With

... and during debugging, it is really annoying:
If you run the code step-by-step via (F8), it works without ChartObject.Select, but if you run it normally or via (F5) you get an error, either
Picture(1) = Error 424 (no such object)
Shapes(1) = Error -2147024809 (number 1 not there)

edited Feb 14 '19 at 08:38

answered Jan 31 '19 at 17:21

Asger

3,822
3
12
37

It's really not necessary to select the chart. You just need to give VBA a chance to finish the paste, as in Max's answer or in mine. – Jon Peltier Feb 14 '19 at 03:40
@Jon, thanks you your comment and pointing to further solutions. So I weakened my answer from "necessary" to "helps" :) – Asger Feb 14 '19 at 08:41
The F8 vs F5 behavior helps indicate that it's some kind of timing error. F8 gives VBA a chance to start and complete each statement before attempting to execute the next. This is where a combination of loops and `DoEvents` helps. I think selecting the object may also force VBA to concentrate harder on the object. (I find it helpful to anthropomorphise VBA, and treat it as a cranky toddler.) – Jon Peltier Feb 15 '19 at 15:17

Jon Peltier · Answer 3 · 2019-02-15T15:19:23.720

Like Max, I have found that many commands in Excel that operate on various objects (shapes, charts, pictures, worksheets, workbooks, etc.) take longer than they used to, and VBA doesn't wait for them to finish before trying to execute the next command. Hence the failure when it tries doing something to the picture which isn't really pasted yet.

An indication of this problem is that the code runs without error when stepping through in the VB Editor with the F8 key, but fails at full speed when running with the F5 key.

Similar to Max, I've come up with a little loop to watch for this:

With .chart
  Do Until .Pictures.Count = 1
    DoEvents
    .Paste
  Loop
End With

An alternative is to place the actual Paste in a separate function, which you would call from that part of the code, and pass in the picture being pasted and the chart that is receiving it. The barrier between different items in the call stack seems to encourage VBA to finish what it's doing before going on the the next step.

I should note that I've had to do this to many parts of my commercial Excel VBA add-in that works extensively with charts, and I the code I pasted above is taken directly out of this add-in.

How to fix Excel 2016 Object Required Error that worked in Excel 2010

3 Answers3