The Set Statement and the New Keyword in Automation

Article
06/14/2014

This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.

You start automation code by declaring object variables with a specific object type that represents the top-level object and then declaring any child objects you want to reference. You then create an instance of the top-level object by using the Set statement and the New keyword. However, the New keyword can't be used to create a new instance of a child object. To create an instance of a child object, use the appropriate method of the parent object along with the Set statement.

In the following example, the top-level Microsoft® Excel Application object variable is assigned by using the Set statement and the New keyword. The object variable representing the Workbook child object is assigned by using the parent object's Add method and the Set statement.

Sub CreateExcelObjects()
   Dim xlApp            As Excel.Application
   Dim wkbNewBook       As Excel.Workbook
   Dim wksSheet         As Excel.Worksheet
   Dim strBookName      As String
   
   ' Create new hidden instance of Excel.
   Set xlApp = New Excel.Application
   ' Add new workbook to Workbooks collection.
   Set wkbNewBook = xlApp.Workbooks.Add
   ' Specify path to save workbook.
   strBookName = "c:\my documents\xlautomation.xls"
   ' Loop through each worksheet and append " - By Automation" to the
   ' name of each sheet. Close and save workbook to specified path.
   With wkbNewBook
      For Each wksSheet In .Worksheets
         wksSheet.Name = wksSheet.Name & " - By Automation"
      Next wksSheet
      .Close SaveChanges:=True, FileName:=strBookName
   End With

   Set wkbNewBook = Nothing
   XlApp.Quit
   Set xlApp = Nothing
End Sub

Note The CreateExcelObjects procedure uses three Excel object variables, but only the first two are instantiated by using the Set statement. You do not need to use the Set statement to create an object variable that will be used only inside a For…Each loop.

In the next example, the top-level Microsoft® Outlook® Application object is created by using the Set statement and the New keyword. The MailItem child object variable is created by using the Application object's CreateItem method. The Recipient child object is created by using the Add method of the MailItem object's Recipients collection.

Sub CreateOutlookMail()
   Dim olApp                  As Outlook.Application
   Dim olMailMessage          As Outlook.MailItem
   Dim olRecipient            As Outlook.Recipient
   Dim blnKnownRecipient      As Boolean
   
   ' Create new instance of Outlook or open current instance.
   Set olApp = New Outlook.Application
   ' Create new message.
   Set olMailMessage = olApp.CreateItem(olMailItem)
   ' Prompt for message recipient, attempt to resolve address, and
   ' then send or display.
   With olMailMessage
      Set olRecipient = .Recipients.Add(InputBox("Enter name of message recipient", _
         "Recipient Name"))
      blnKnownRecipient = olRecipient.Resolve
      .Subject = "Testing mail by Automation"
      .Body = "This message was created by VBA code running " _
         & "Outlook through Automation."
      If blnKnownRecipient = True Then
         .Send
      Else
         .Display
      End If
   End With
   Set olMailMessage = Nothing
   olApp.Quit
   Set olApp = Nothing
End Sub

**Note **At the end of this procedure, each object variable is destroyed by explicitly setting it equal to the Nothing keyword.

You can also use the New keyword to create a new instance of the object at the same time you declare its object variable. For example:

Dim olApp As New Outlook.Application

If you do this, there is no need to use a Set statement to instantiate the object. However, this technique is not recommended because you have no control over when the object variable is created. For example, if your code must test to see if an object exists by using a statement such as If olApp Is Nothing Then, this test will return True if you have created an instance of the object in the Dim statement. Additionally, you might not need to use an object except at the user's request. If you create an instance of the object by using New in the Dim statement, the object will be created even if it isn't used. To maintain control over when an object is created, don't use the New keyword in the Dim statement, and instantiate the object by using a Set statement at the point in your code where you must use the object.

The Set Statement and the New Keyword in Automation

See Also

Additional resources