Convert early binding code to late binding

Question

I have difficulty converting early binding code to late binding. I have tried several times but it doesn't work properly. When it comes to early binding it works well. Here is my code and I'd like to ask you to show me how it should look like and in which places I have to make changes and for what.

Sub PrezentacjaPP()

    Dim PowerPointA As PowerPoint.Application 'aplikacja PowerPoint
    Dim PrezentacjaPP As PowerPoint.Presentation 'prezentacja PowerPoint
    Dim NazwaPP As String 'nazwa pliku z szablonem prezentacji
    Dim SlajdPP As PowerPoint.Slide 'slajd prezentacji PowerPoint
    Dim WykresPP As PowerPoint.Shape 'wykres w formacie obrazu
    Dim Lista As String 'lista marek
    Dim Tekst As String 'tekst z info o marce i statystykami
    Dim i As Long 'numer marki
    Dim Wiersz As Long 'numer pierwszego wiersza dla danej marki

    Application.ScreenUpdating = False 'Wyłącz odświeżanie ekranu


        '1.Usunięcie starych prezentacji
        NazwaPP = Dir(ThisWorkbook.Path & "\" & "Prezentacja_*.pptx")
        If NazwaPP <> "" Then
            If MsgBox("Czy usunąć istniejące pliki z prezentacjami PowerPoint?", _
                vbYesNo + vbInformation) = vbYes Then
                Do While NazwaPP <> ""
                    Kill ThisWorkbook.Path & "\" & NazwaPP
                    NazwaPP = Dir()
                Loop
            Else
                MsgBox "Zmień nazwę istniejących prezentacji i ponownie uruchom procedurę."
                CzyZakonczyc = True
                Exit Sub
            End If
        End If

        '2. Wczytanie zmiennych
        ChDrive Left(ThisWorkbook.Path, 1) 'domyślny dysk
        ChDir ThisWorkbook.Path 'domyślny katalog
        NazwaPP = Application.GetOpenFilename(FileFilter:="Prezentacje programu PowerPoint (*.pptx), *.pptx", Title:="Zaznacz plik z szablonem prezentacji PowerPoint")
        If NazwaPP = "False" Then
            CzyZakonczyc = True
            Exit Sub
        End If

        '3. Wczytanie i otwarcie szablonu prezentacji PowerPoint
        Set PowerPointA = New PowerPoint.Application
        Set PrezentacjaPP = PowerPointA.Presentations.Open(NazwaPP) '<-- tylko w celach poglądowych podczas testowania makra

        '4. Stworzenie prezentacji
        With PrezentacjaPP

            '4.1. Uzupełnienie slajdów wstępnych
            .Slides(1).Shapes(1).TextFrame.TextRange = Cells(NrWiersza, "B").Value 'Slajd nr 1
            .Slides(2).Shapes(1).TextFrame.TextRange = "Lista marek" 'Slajd nr 2
            For i = 1 To Cells(Rows.Count, 18).End(xlUp).Row - 1
               Lista = Lista & Cells(i + 1, "R") & vbNewLine
            Next i
            .Slides(2).Shapes(2).TextFrame.TextRange = Lista

                '4.1.1. Stworzenie slajdów z aktywami
                 For i = 1 To Cells(Rows.Count, 18).End(xlUp).Row - 1
                     'Dodanie nowego slajdu (typ ppLayoutText: tytuł oraz pole tekstowe)
                     Set SlajdPP = .Slides.Add(.Slides.Count + 1, ppLayoutText)
                     With SlajdPP
                     'Wstawienie tytułu slajdu
                     .Shapes(1).TextFrame.TextRange = Cells(i + 1, "R")

            '4.2. Dodanie wykresów

                '4.2.1. Wykres 1
                ActiveSheet.Shapes.Range(Array("Picture" & " " & i * 2 - 1)).Select 'wykres w formacie obrazu w Excel'u
                Selection.Copy
                .Shapes.Paste
                Set WykresPP = .Shapes(SlajdPP.Shapes.Count)

                '4.2.2. Pozycjonowanie wykresu na slajdzie
                With WykresPP
                    .Left = 40
                    .Top = 110
                    .LockAspectRatio = msoFalse 'odblokowanie wsp. proporcji
                    .Width = 400
                    .Height = 195
                End With

                '4.2.3. Wykres 2
                ActiveSheet.Shapes.Range(Array("Picture" & " " & i * 2)).Select 'wykres w formacie obrazu w Excel'u
                Selection.Copy
                .Shapes.Paste
                Set WykresPP = .Shapes(SlajdPP.Shapes.Count)

                '4.2.4. Pozycjonowanie wykresu na slajdzie
                With WykresPP
                    .Left = 40
                    .Top = 315
                    .LockAspectRatio = msoFalse 'odblokowanie wsp. proporcji
                    .Width = 400
                    .Height = 195
                End With

            '4.3. Doddanie statystyk

                '4.3.1. Wyznaczenie numeru pierwszego wiersza dla danej marki
                Wiersz = NrWiersza + 3 + 25 * (i - 1)

                '4.3.2 Wczytanie statystyk
                Tekst = Cells(Wiersz, 2) & " " & Cells(Wiersz, 3) & vbNewLine & _
                    Cells(Wiersz + 20, 2) & " " & FormatNumber(Cells(Wiersz + 20, 4), 2) & vbNewLine & _
                    Cells(iersz + 21, 2) & " " & Cells(iersz + 21, 4) & vbNewLine & _
                    Cells(Wiersz + 22, 2) & " " & Cells(Wiersz + 22, 4) & vbNewLine & _
                    Cells(Wiersz + 22, 2) & " " & Cells(Wiersz + 22, 4) & vbNewLine & _
                    vbNewLine & _
                    Cells(Wiersz + 20, 6) & " " & FormatNumber(Cells(Wiersz + 20, 9), 2) & vbNewLine & _
                    Cells(Wiersz + 21, 6) & " " & Cells(Wiersz + 21, 9) & vbNewLine & _
                    Cells(Wiersz + 22, 6) & " " & Cells(Wiersz + 22, 9) & vbNewLine & _
                    vbNewLine & _
                    Cells(Wiersz + 20, 11) & " " & Cells(Wiersz + 20, 13) & vbNewLine & _
                    Cells(Wiersz + 21, 11) & " " & Cells(Wiersz + 21, 13)

                '4.3.3. Pozycjonowanie pola
                .Shapes(2).Top = 110
                .Shapes(2).Left = 500
                .Shapes(2).Width = 330
                .Shapes(2).Height = 410
                .Shapes(2).TextFrame.TextRange = Tekst

        End With
        Next i

        '4.4. Stworzenie slajdu końcowego

            '4.4.1. Dodanie slajdu
            Set SlajdPP = PowerPointA.Presentations(1).Slides.Add _
                (PowerPointA.Presentations(1).Slides.Count + 1, ppLayoutText)
            SlajdPP.Shapes(1).Delete

            '4.4.2. Uzupełnienie slajdu
            With SlajdPP.Shapes(1)
                .TextFrame.TextRange = "DZIĘKUJĘ ZA UWAGĘ"
                .TextFrame.HorizontalAnchor = msoAnchorCenter
                .TextFrame.VerticalAnchor = msoAnchorMiddle
                .TextEffect.FontBold = msoCTrue
                .TextEffect.FontSize = 24
                .TextFrame2.TextRange.Font.Fill.ForeColor.RGB = RGB(0, 100, 170)
            End With

        End With

        '5. Zapisanie prezentacji PowerPoint
        PrezentacjaPP.SaveAs Excel.ActiveWorkbook.Path & "\Prezentacja_PP_" & Date & ".pptx"

    Application.ScreenUpdating = True 'Włącz odświeżanie ekranu

End Sub

Answer 1

Late-binding means VBA only has run-time knowledge of the types involved. This is late-bound:

Dim PowerPointA As Object

That declaration will compile with or without the PowerPoint type library referenced.

Because VBA doesn't (can't) know what members PowerPointA has, you don't get IntelliSense at design-time, and this will happily compile...

PowerPointA.SomeMemberThatDoesNotExist

...and blow up at run-time with error 438 "Object doesn't support this property or method".

Any member call made against Object or Variant is late-bound; at run-time, VBA needs to query the interface to locate the member and determine its existence: this means late-bound code incurs a slight overhead that early-bound code doesn't need to.

---

Early-binding means VBA has compile-time knowledge of the types involved. This is early-bound:

Dim PowerPointA As PowerPoint.Application

That declaration can only ever compile when the PowerPoint type library is referenced.

Because VBA knows what members PowerPointA has, you get IntelliSense (and autocompletion) at design-time, and this will should not compile:

PowerPointA.SomeMemberThatDoesNotExist

...however Application is likely an extensible COM interface (Excel.Application is); if that's the case then VBA will compile the code, and SomeMemberThatDoesNotExist will be a late-bound member call that will blow up at run-time with error 438 "Object doesn't support this property or method".

---

When you want to "convert early-bound code to late-bound code", what you really mean is that you want to remove a type library reference from your project and still be able to compile and run the code.

Assuming that reference is the PowerPoint type library, this means you need to replace everything in your code that is defined in this library - because once you remove the reference, classes such as PowerPoint.Application, PowerPoint.Presentation, PowerPoint.Slide and PowerPoint.Shape won't be defined anymore, and stop meaning anything to VBA. Therefore:

1. Replace declarations As [PowerPoint.][ClassName] with As Object
2. Replace New [PowerPoint.][ClassName] with CreateObject("ProgIdForThatClass")
3. Remove the reference to the PowerPoint type library
4. Compile the project. Doesn't compile? Back to 1.

The ProgId for PowerPoint.Application happens to be "PowerPoint.Application", so you can replace this:

Set PowerPointA = New PowerPoint.Application

With this:

Set PowerPointA = CreateObject("PowerPoint.Application")

And that's all you need to do.

If you have code elsewhere that references constants defined in the PowerPoint type library, you'll want to either replace them with their underlying value, or define equivalent constants yourself, so instead of this:

foo = ppEffectFade

You'll have this:

foo = 1793 'PowerPoint.PpEntryEffect.ppEffectFade=1793

Or this:

Const ppEffectFade As Long = 1793

foo = ppEffectFade

Or this:

Public Enum PpEntryEffect
    ppEffectAppear = 3844
    ppEffetFade = 1793
    '...
End Enum

foo = ppEffectFade

You can locate all constants, their values, and everything defined in any referenced type library, using the Object Browser (F2):