3

I use the following macro to iterate through file paths stored in collection. On some test directory, total run time was 2,3 sec without setting openedApp and openedDoc to nothing and 2,75 with it. Is releasing both of these objects necessary in this case? I intend to use macro on a directory with thousands of files, so I would like to avoid any problems with memory which could cause errors or slow down macro execution.

Dim openedApp As Word.Application
Dim openedDoc As Word.Document

For Each filePath In filesCollection       

    Set openedApp = New Word.Application
    Set openedDoc = openedApp.Documents.Open(filePath)
    'openedApp.Visible = True

    openedDoc.Close
    openedApp.Quit

    Set openedApp = Nothing: Set openedDoc = Nothing

Next filePath
Ryszard Jędraszyk
  • 2,296
  • 4
  • 23
  • 52

2 Answers2

4

There is no need to explicitly set you variables to Nothing. As descibred by this answer VB6 used a reference-counting GC. The GC is triggered deterministically when the last reference to a given object is set to Nothing. Setting local references to Nothing is unnecessary, this happens as they go out of scope.

In fact, if performance matters you can move the creation of the Word application outside the loop body:

Dim openedApp As Word.Application
Dim openedDoc As Word.Document

Set openedApp = New Word.Application

For Each filePath In filesCollection       

    Set openedDoc = openedApp.Documents.Open(filePath)

    openedDoc.Close

Next filePath

openedApp.Quit
Community
  • 1
  • 1
Dirk Vollmar
  • 172,527
  • 53
  • 255
  • 316
1

There is no need to set VBA objects to nothing. The VBA garbage collector will clean up all the variables when the subroutine goes out of scope. This is legacy from the early days of the VBA when computers weren't as powerful and the garbage collector wasn't as good.

The exception to the rule is when you are using external objects that run outside of the VBA such as connections and recordsets. These should be closed and set to nothing.

Along the same lines, there is no need to erase array at the end of a subroutine. If you have a large array (millions of elements) that is no longer needed and you still have more code to run then you might get a performance boost from erasing the array.