# Copying content from Excel into PowerPoint as a picture, with error handling

The code below works, but I want to ask if my solution is adequate or if there's something I should structure differently. If it's alright, I'll apply the same code in other subs.

My code copies stuff from Excel to PowerPoint and inserts it as a picture. In order to do this, I first have to copy data from one part of the sheet into a specific range. This range is where the summary and chart draw their input from. I then copy the summary and chart for each VW into PowerPoint. The one issue that persists is that the line mySlide.Shapes.PasteSpecial DataType:=ppPasteEnhancedMetafile frequently gives me an error: "Error -2147188160 Shapes (unknown member): invalid request. The specified data type is unavailable." I had this issue in other files appear as well but was solved by using DoEvents and Application.CutCopyMode = False.

Neither helped in this case, so my next best idea is to let VBA retry copy-pasting until it works (up to 20, so it doesn't get stuck in an endless loop). I'm new to error handling and this is what I came up with (it's a sub called from the main procedure); I marked the 4 labels/GoTo statements:

Public PowerPointApp As PowerPoint.Application
Public myPresentation As PowerPoint.Presentation
Public myShape As PowerPoint.Shape
Public mySlide As PowerPoint.Slide

'in main sub:
'Set PowerPointApp = New PowerPoint.Application
'Set myPresentation = PowerPointApp.Presentations.Open("C:\....")

Sub SubSlide4(wsKAP As Worksheet, RangeArray As Variant)
'RangeArray = = Array("Range1", "Range2", "Range3"...)
Dim iSlide As Long
Dim rngVW As Variant
Dim fullNameVW As String
Dim ErrorCount4 As Long

iSlide = 4
ErrorCount4 = 0
For Each rngVW In RangeArray 'RangeArray has ~10 members

'Paste correct data for each VW
wsKAP.Range(rngVW).Copy
fullNameVW = "Test"
wsKAP.Range("C73") = fullNameVW

Set mySlide = myPresentation.Slides(iSlide)

'Copying Summary for each VW
Set rng = wsKAP.Range("C89:P97")
rng.Copy
DoEvents
mySlide.Shapes.PasteSpecial DataType:=ppPasteEnhancedMetafile
Set myShape = mySlide.Shapes(mySlide.Shapes.Count)
With myShape
.Left = 20
.Top = 71
.Height = 92
End With

'Copying Charts
RepeatOnError: '<-----------------------------------------------------
Set rng = wsKAP.Range("A30:Y69")
rng.Copy
DoEvents
On Error GoTo ErrorHandler '<-----------------------------------
mySlide.Shapes.PasteSpecial DataType:=ppPasteEnhancedMetafile
Set myShape = mySlide.Shapes(mySlide.Shapes.Count)
With myShape
.Left = 20
.Top = 187
.Width = 686
End With
iSlide = iSlide + 1
Application.CutCopyMode = False
Next rngVW

ErrorHandler: '<----------------------------------------------------------
If Err.Number = -2147188160 Then
If ErrorCount4 > 20 Then
MsgBox "Too many errors (-2147188160), canceling"
End
End If

ErrorCount4 = ErrorCount4 + 1
Debug.Print "ErrorCount4 is " & ErrorCount4
Resume RepeatOnError '<-----------------------------------------
End If
On Error GoTo 0
End Sub



So whenever error -2147188160 occurs, it should jump back to 'RepeatOnError' and try copy-pasting it once again. So far this has worked, but I wonder if this is the VBA way to go about error handling.

Someone on Stack Overflow suggested I need to use Exit Sub before the error handler— is that true?

• This is the article you should read. rubberduckvba.wordpress.com/2019/05/09/pattern-tryparse. You should also put 'Option Explicit' at the top of your module as you seem to have undeclared variables (myShape?). You might also install Rubberduck for the code inspections feedback. – Freeflow May 23 at 9:10