CODEWITHSUNDEEP
vbaexcelms-word
Ian
Ian

Reputation: 187

Should I reset a VBA object variable or close the set object then set to a new variable?

For a VBA program that uses a single object variable multiple times, is it best to reset the variable in the loop without first setting the variable to nothing (which can be done at the end outside of the loop), or should the variable always be set to nothing after each instance of use. From a reference counting perspective it seems best to set to nothing as I never have need for the previous state of the object once it has been changed to the next variable (I'm not sure how one would retrieve the previous state but I gather that there must be a function of such from this question). Another relevant question that I examined can be found here.

The application which triggered this question is one where I cycle through many Word documents, pulling relevant information from each of them. See example below:

dim objDoc as object, objWord as object
Set objWord = CreateObject("Word.Application")
for r=2 to 40
    'Some code to set filename 
    Set objDoc = objWord.Documents.Open(filename, ReadOnly:=False, Visible:=False)
    'Code to retrieve relevant info from document
    objDoc.close
    Set objDoc = nothing 'Should this go here?
next r
set objDoc = nothing 'Or should it go here?
objWord.Quit

The Question: Does it matter when you set an object variable to nothing in terms of code efficiency or problem avoidance. I have highlighted above two options for where I believe that should go with the third option being that it doesn't matter when I set the variable to nothing

Upvotes: 3

Views: 5481

Answers (3)

DisplayName
DisplayName

Reputation: 13386

Inside the loop

Which should be easily inferred from the following equivalent code:

dim objWord as object
Set objWord = CreateObject("Word.Application")

for r=2 to 40
    'Some code to set filename 
    With objWord.Documents.Open(filename, ReadOnly:=False, Visible:=False) 'Set and reference your document object
        'Code to retrieve relevant info from document
        .Close
    End With 'this “disposes” the referenced document object, so you also set it to Nothing
Next r
objWord.Quit

Of course, there’s is room for further application of the same concept to objWord, too:

With CreateObject("Word.Application") ‘ instantiate and reference your Word object
    for r=2 to 40
        'Some code to set filename 
        With .Documents.Open(filename, ReadOnly:=False, Visible:=False) 'Set and reference your document object
            'Code to retrieve relevant info from document
            .Close
        End With 'this “disposes” the referenced document object, so you also set it to Nothing
    Next r
    .Quit
End With 'this “disposes” the referenced Word object, so you also set it to Nothing

Upvotes: 0

Not Saying
Not Saying

Reputation: 332

Short answer: In the loop.

Long answer: From a problem avoidance perspective, you should set it to 'nothing' when you are immediately done with it. The problem you are going to be trying to catch is an unintended reuse. You want your code to explode if you ever do accidentally reuse the variable instead of having to debug it later when it gives weird results. Generally speaking you shouldn't reuse variables because it's all too easy to make a mistake when refactoring and the variable ends up containing the wrong value.

VBA unfortunately doesn't care if you use a variable outside of the innerscope it was dim'ed in. The following is unfortunately legal and more modern languages don't let you do this:

Sub CountInsideLoop()

    Dim i As Long
    For i = 1 To 3
        Dim count As Long
        count = count + 1
    Next i

    ' count value will be 3
    Debug.Print count
End Sub

Upvotes: 3

Mathieu Guindon
Mathieu Guindon

Reputation: 71187

I would argue that, generally speaking, having a variable that has two or more different meanings depending on context, is making the maintainer's life harder than it needs to be. In this case, whether you put it inside or outside the loop scope should make no difference at all - in fact whether Set objDoc = Nothing is specified at all, should make no difference either.

You're not showing the rest of the procedure, but as far as I can see there's not really a reason why that particular procedure wouldn't be doing 10,000 other things.

The code you've shown should be written in a bunch of procedures and functions.

'Some code to set filename

You need a function that implements the logic to get a filename, and returns a String.

'Code to retrieve relevant info from document

You need a procedure that retrieves relevant info from a document.

Pull the body of the loop into its own procedure:

For r = 2 To 40
    filename = LogicToGetTheFilename(r)
    ProcessWordDocument wordApp, filename
Next

Much higher abstraction level here - with proper naming you know what that loop does at a glance.

Private Sub ProcessWordDocument(ByVal wordApp As Object, ByVal filename As String)
    Dim docx As Object 'Word.Document
    Set docx = wordApp.Documents.Open(filename, ...)
    'do stuff
    docx.Close
    'Set docx = Nothing
End Sub

Now that the Word document object variable is living in its own scope, VBA should appropriately destroy the underlying object whenever execution exits that scope (i.e. once per loop iteration).

Because refactoring the loop into proper abstraction levels means the Word document only lives for a single iteration, I would argue (rather strongly) that Set objDoc = Nothing belongs inside the loop body.

Upvotes: 3

Related Questions