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
wsKAP.Range("tab.StartHeader").PasteSpecial Paste:=xlPasteValues
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?