0

I have read countless times that you should always release objects at the end of your projects, such as:

Sub Test()
    Dim obj As Object
    Set obj = GetObject(, "xxxxxx.Application")

    ' Code your project...

    Set obj = Nothing
End Sub

However, I use a program that calls upon Excel's object. I call Excel's object so often in so many different routines, I decided to make a function to make things simpler:

Public Function appXL As Object
   Set appXL = GetObject(, "Excel.Application")
End Function

Although that is an extremely simple function, there is no way for me to release the object appXL at the end of any subroutine I use, at least as far as I am aware. But a bigger concern that I have is that a subroutine that uses appXL 100+ times is actually grabbing the object the same number of times.

I honestly don't see why it would be a big issue since I am using GetObject as opposed to CreateObject. I am not creating a new instance of Excel everytime the function is called, so is this something that I should be concerned about when coding? Do I just need to completely get rid of the function and declare appXL on every routine I use so I am able to release it in the end?

  • 3
    The premise of your statement is wrong. You have no need to release objects. There are released at every `end sub`/`end function`. If implicitly declared at the end of that line. Also while a bit slower `getobject` doesn't really matter as you suspect. Don't be afraid to use global variables if it makes sense. If you are accessing the same object from other functions make it a global. See https://blogs.msdn.microsoft.com/ericlippert/2004/04/28/when-are-you-required-to-set-objects-to-nothing/ (he wrote a lot of VBScript). –  Oct 07 '16 at 01:28
  • 1
    Also see my answer here for other links http://stackoverflow.com/questions/39215460/declaring-variables-memory-leaks –  Oct 07 '16 at 01:31
  • 1
    This is the COM object that manages running apps and GetObject uses IRunningObjectTable https://msdn.microsoft.com/en-us/library/windows/desktop/ms695276(v=vs.85).aspx –  Oct 07 '16 at 01:53
  • 1
    Your big concern should be automating instances of Excel *at all*. There are so many ways for this to go wrong and hang your program, corrupt data in another open Workbook, leave Excel running after your program ends, etc. It should be a last resort if no other alternative exists. – Bob77 Oct 07 '16 at 07:34
  • Appreciate the knowledge on this. Thanks! –  Oct 07 '16 at 14:15
  • Just for emphasis, I repeat the link to this blog: https://blogs.msdn.microsoft.com/ericlippert/2004/04/28/when-are-you-required-to-set-objects-to-nothing/ – StayOnTarget Oct 10 '16 at 11:19

0 Answers0