Before you can talk to an application, you need to know the objects contained within it. You can then use Dim, Private, or Public statements to point to and control various application objects. Each product comes with documentation indicating which objects it supports. You can also view the objects that a product supports by using the Object Browser. After you create an object variable, you can manipulate the object without user intervention.
To create an instance of an object, you must first create an object variable that holds a reference to the object. You can do this by using a Dim statement:
Dim objExcel As New Excel.Application
This code creates an object variable pointing to the Excel application object. It then creates a new instance of the Excel application object. This Excel object is part of the Excel application. VBA can control it using the object variable. Unless instructed otherwise, the instance of Excel is invisible. You can make it visible by using this statement:
objExcel.Visible = True
Alternatively, you can use two statements to declare and instantiate an object. The code looks like this:
Dim objExcel as Excel.Application Set objExcel = New Excel.Application
The Dim statement declares an object variable that is ready to be associated with a running instance of Excel. The Set statement launches Excel and points the object variable at the new instance of Excel. The advantage of this method is that you can better control when the instance of Excel is actually created. If, for example, the declaration is in the General Declarations section of a form, you can place the Set statement under a command button that is used to launch Excel.