Open and Close Workbooks | best Practices

[o0omax]

Hello,

i am currently working on an VBA Project which opens and closes different Workbooks (and PowerPoints). What are Best Practices concerning this undertaking?

• Do i create a new Application for every WorkBook i open in a loop? -> set xlAPP = CreateObject("Excel.Application") [or CreateObject("PowerPoint.Application") for PowerPoint
• Do i declare a different variable for each Workbook, even the one i'm running the macro from? -> set wb = Workbooks.Open(Name)
• When do I close the application of Excel/Powerpoint? When do i close the opened workbook (here wb)

I did not find anything on the web and wanted to sharpen my understanding of handling those objects!
Thanks for the replies!

Greetings
o0omax

[Jan Karel Pieterse]

There is no benefit in creating a separate instance for each file, unless the files you are trying to open are unstable and frequently cause crashes of their app.
You close the file once you are done with it, depends on your process.

[o0omax]

Thank you Jan Karel Pieterse,

and if i Call a sub which should hide some Worksheets, do i have to pass the Workbook as an Argument or will the expression ThisWorkbook be sufficient?

The root of the question is. How do I work with the variable wb (wb as workbook) when calling multiple subs in my main Sub routine?

Call HideThngs
(or Call HideThings(wb) -> and in the Sub HideThings(wb) of ThisWorkbook i replace ThisWorkbook with wb)

Sub HideThings()
With ThisWorkbook
    .Sheets("Fusionspapier").Visible = False
    .Sheets("Data").Visible = False
    .Sheets("Name").Columns("L:L").EntireColumn.Hidden = True
    .Sheets("Name").Columns("N:N").EntireColumn.Hidden = True
End With
End Sub

[Jan Karel Pieterse]

You would give the HideThings an argument that specifies which workbook to operate on:

Sub HideThings(wb2WorkOn As Workbook)
With wb2WorkOn
    .Sheets("Fusionspapier").Visible = False
    .Sheets("Data").Visible = False
    .Sheets("Name").Columns("L:L").EntireColumn.Hidden = True
    .Sheets("Name").Columns("N:N").EntireColumn.Hidden = True
End With
End Sub

Your calling routine then calls the HideThings routine with wb as its argument:

'Some code which sets wb to a workbook
HideThings wb

[Jan Karel Pieterse]

NB: ThisWorkbook is a "nickname" for the file (workbook) that contains the VBA code. It always points to the workbook running the code.

[snb]

Since the Object Thisworkbook is a constant and public, it's no use (redundant) to assign it to an Object variable.