VBA : Can child sub inherit properties from its parent sub-CodePudding

I am creating an Excel macro that populated as template PowerPoint slide deck with some charts from the workbook.

The act of copy and pasting a chart from Excel to PowerPoint takes a lot of lines of code, so I am trying to move this into a subroutine that I call from the main routine, to keep everything modular. The below illustrates what I am trying to do.

To me, the only arguments that should vary for each call of the PasteChart subroutine is the name of the chart I am copying across (eg Figure1, Figure2, etc) and the placeholder position on the slide (2, 3, etc). The slide number I want to define before calling the routines as I need to do other things to the slide as well.

To me, the arguments wb, pptApp, ppt, SlideNumber should be inherited from the main routine so that I don't have to keep passing them to the PasteChart subroutine. Ie, these 4 arguments are going to be the same for all calls of the PasteChart subroutine.

But if I don't pass them as I have below, my macro won't work.

Is there a way to more elegantly structure the below subroutine? Or in other words, how can I get the PasteChart subroutine to inherit the arguments wb, pptApp, ppt, SlideNumber from the main routine?

Parent Subroutine

Sub CreateSlideDeck()

    Set wb = ActiveWorkbook()

    Set pptApp = CreateObject("PowerPoint.Application")
    Set ppt = pptApp.Presentations.Open("D:\Template\Template.pptx", untitled:=msoTrue)
    

    SlideNumber = 3

    Call PasteChart("Figure1", 2, wb, pptApp, ppt, SlideNumber)
    Call PasteChart("Figure2", 3, wb, pptApp, ppt, SlideNumber)
    
End Sub

Child Subroutine

Sub PasteChart(ChartName, Position, wb, pptApp, ppt, SlideNumber)

    wb.Sheets("Charts").ChartObjects(ChartName).Activate
    ActiveChart.ChartArea.Copy
    
    pptApp.Activate
    ppt.Slides(SlideNumber).Select
    ppt.Slides(SlideNumber).Shapes.Placeholders(Position).Select
    ppt.Windows(1).view.Paste

End Sub

CodePudding user response：

This is untested but you can create them as global variables within the module.

Dim wb As Workbook
Dim pptApp As Object
Dim ppt As Object
Dim SlideNumber As Long

Sub CreateSlideDeck()
    Set wb = ActiveWorkbook()

    Set pptApp = CreateObject("PowerPoint.Application")
    Set ppt = pptApp.Presentations.Open("D:\Template\Template.pptx", untitled:=msoTrue)
    
    SlideNumber = 3

    Call PasteChart("Figure1", 2)
    Call PasteChart("Figure2", 3)
End Sub

Sub PasteChart(ByVal ChartName As String, ByVal Position As Long)
    wb.Sheets("Charts").ChartObjects(ChartName).Activate
    ActiveChart.ChartArea.Copy
    
    pptApp.Activate
    ppt.Slides(SlideNumber).Select
    ppt.Slides(SlideNumber).Shapes.Placeholders(Position).Select
    ppt.Windows(1).View.Paste
End Sub

Would I do that? Probably not but each to their own and if you have a use case and it works for you then so be it.

CodePudding user response：

You could use a configuration array - and then iterate:

Sub createSlideDeck()

Set wb = ActiveWorkbook()

Set pptApp = CreateObject("PowerPoint.Application")
Set ppt = pptApp.Presentations.Open("D:\Template\Template.pptx", untitled:=msoTrue)


Dim arr(3, 2)   'array that takes configuration of all charts
'Chartname | Position | Slidenumber
arr(0, 0) = "Figure1": arr(0, 1) = 2: arr(0, 2) = 3
arr(1, 0) = "Figure2": arr(1, 1) = 3: arr(1, 2) = 3
arr(2, 0) = "Figure3": arr(2, 1) = 4: arr(2, 2) = 3

Dim i As Long
For i = 0 To UBound(arr, 1)
    PasteChart arr(i, 0), arr(i, 1), wb, pptApp, ppt, arr(i, 2)
Next

End Sub