Early and Late Binding || Excel VBA Master Class || 3.3b

Note: This webpage is designed to accompany the Youtube video posted above. The video offers detailed explanation on all topics; while this webpage will serve as a repository for the code presented in the video. Source code will be presented in the order of the sections within the video.

Syntax

'Early Binding
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Input")
    
    Dim dict As Scripting.Dictionary
    Set dict = New Scripting.Dictionary
    
    Dim pApp As PowerPoint.Application
    Set pApp = CreateObject("PowerPoint.Application")

'Late Binding
    Dim ws As Object
    Set ws = CreateObject("Excel.Sheet")
    
    Dim dict As Object
    Set dict = CreateObject("Scripting.Dictionary")
    
    Dim pApp As Object
    Set pApp = CreateObject("PowerPoint.Application")

Accessing External Applications

'Early Binding
Sub CreatePowepointPresentation()
    Dim pApp As PowerPoint.Application
    Set pApp = New PowerPoint.Application
    pApp.Visible = True
End Sub

'Late Binding
Sub CreatePowepointPresentation()
    Dim pApp As Object
    Set pApp = CreateObject("PowerPoint.Application")
'    Set pApp = GetObject(,"PowerPoint.Application")
    Dim pPres As Object
    Set pPres = pApp.Presentations.Add
    pApp.Visible = True
    pApp.Quit
End Sub

Compile-time vs Run-time

Why use late binding

Converting code to late binding

' Early Binding
' Remember to set a reference to the PowerPoint Type Library
Sub CreatePowepointPresentation()
    Dim pApp As PowerPoint.Application
    Set pApp = New PowerPoint.Application
    Dim pPres As PowerPoint.Presentation
    Set pPres = pApp.Presentations.Add
    Dim pSlide As PowerPoint.Slide
    Set pSlide = pPres.Slides.Add(1, ppLayoutBlank)
    ThisWorkbook.Sheets("Sales").Shapes("RevenueChart").Copy
    pSlide.Shapes.PasteSpecial DataType:=ppPasteEnhancedMetafile
    pPres.SaveAs "C:\Youtube\Current\VBA_Masterclass\Demo\Revenue.pptx"
    pPres.Close
    pApp.Quit
End Sub
'Late Binding
Sub CreatePowepointPresentation()
    Dim pApp As Object
    Set pApp = CreateObject("PowerPoint.Application")
    Dim pPres As Object
    Set pPres = pApp.Presentations.Add
    Dim pSlide As Object
    Set pSlide = pPres.Slides.Add(1, 12) 'ppLayoutBlank
    ThisWorkbook.Sheets("Sales").Shapes("RevenueChart").Copy
    pSlide.Shapes.PasteSpecial 2 'ppPasteEnhancedMetafile
    pPres.SaveAs "C:\Youtube\Current\VBA_Masterclass\Demo\Revenue.pptx"
    pPres.Close
    pApp.Quit
End Sub

Related Posts