Open and Close Workbooks | best Practices

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

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.

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)

Code:

---

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

---

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

Code:

---

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:

Code:

---

'Some code which sets wb to a workbook
HideThings wb

---

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

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