3
\$\begingroup\$

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?

\$\endgroup\$
  • \$\begingroup\$ 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. \$\endgroup\$ – Freeflow May 23 at 9:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.