Declaring Objects

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

EnvisionCAD

Since 1996, EnvisionCAD has been a nationally recognized leader in the configuration, customization, implementation, training & support for CAD software solutions. Our individualized approach has benefited private engineering firms and government agencies alike. Basic or advanced, we can help you get the most from your CAD technology.

Tags:

Leave a Reply

Your email address will not be published. Required fields are marked *