First, I would just like to state that I need to use LateBinding due my program running in different machines with different versions of excel.
I have declared a Public Sub in a module that can be called to initiate the creation of object for the excel application.
'Declare Public Excel Variable to Call from one place and easy manipulation
Public xlApp As Object
Public wb As Object
Public wb2 As Object
Public ws As Object
Public ws2 As Object
Public Sub InitializeExcel()
'Create the Excel Objects
Set xlApp = CreateObject("Excel.Application")
End Sub
And then in every Subroutine that I create which uses the excel application, I call InitializeExcel and at every end do the following:
On Error Resume Next
xlApp.Quit
Set xlApp = Nothing
Set wb = Nothing
Set wb2 = Nothing
Set ws = Nothing
Set ws2 = Nothing
My question now is this:
Do I need to call InitializeExcel on every Subroutine that needs the excel application or can I just call it once at the start of the application?
I have tested a few things and had these results:
InitializeExcel
needs to be called every time since at the end of every Subroutine as mentioned sets the xlapp which is the Excel Application Object to Nothing
If I remove the Set xlApp = Nothing
in each subroutine, then I can "re-use" the xlApp
as an Excel Application Object. This however cause an issue in which the Excel Application is kept running in the background and every time I open a new Workbook, then another instance of the Excel Application runs.
So I suppose, the best way was my initial setup in which I call InitializeExcel
and then set everything to Nothing
at each routine the procedure was called.