Declaring Objects

Published on September 8, 2009

Working with objects in VBA can be somewhat tricky. They are declared similarly to a regular variable, but you also have to define a new instance of the object prior to use. You should also set the object to Nothing when you are through with it. There are two ways to declare objects and a new instance. In the example below collectionA uses a shortcut method by declaring the object and setting a new instance in one line of code. The collectionB object is done in two lines, but is the preferred method. Using the shortcut method (as in the collectionA example) you can never set the object to Nothing.

Public Sub MyMacro()
    Dim collectionA As New Collection
    Dim collectionB As Collection    
    Set collectionB = New Collection

    'Do stuff here

    'Set the objects to Nothing when you are done

    'collectionA will never be set to Nothing
    'Declaring an object as New in the Dim statement
    'will always reset the object to a New instance.
    Set collectionA = Nothing

    Set collectionB = Nothing

    'Test for Nothing
    If collectionB Is Nothing Then
        'Do stuff here
    End If

    'Test for Not Nothing
    If Not (collectionB Is Nothing) Then
        'Do stuff here
    End If
End Sub

Like this article? Share it!

Leave a Comment

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

Privacy Policy