Creating New Objects in Excel VBA

Many objects in VBA applications already exist within Excel—these are visible objects such as Range, Worksheet, and so forth. However, there are also helper objects that you create during the execution of your VBA program; these objects are essentially « invisible. » Examples include a connection to a database (see Section 9.8.2, “Example Database: Establishing a Connection”) or a collection object (see Section 6.6, “Collections”).

Such objects are created using the keyword New. At the same time or shortly thereafter, a reference to the newly created object is established.

Here is an example demonstrating both approaches:

Sub NewObjectExample()
    Dim C1 As New Collection
    Dim C2 As Collection
    Set C2 = New Collection 
    Set C1 = Nothing
    Set C2 = Nothing
End Sub

Explanation:

• Using Dim C1 As New Collection creates a new, empty collection object immediately, and C1 is simultaneously set as a reference to this new collection.
• In contrast, Dim C2 As Collection declares C2 as a reference variable that can point to a collection object—but at this point, no actual collection exists.
• Only when you execute Set C2 = New Collection is a new, empty collection created, and C2 becomes a reference to it.
• Assigning Nothing to C1 and C2 releases the references to the objects. When there are no more references to a created object, it becomes inaccessible, and the system can reclaim the memory allocated to it.