Getting a Reference to the Excel Application Object

Once the proper references are set, we can declare an object variable of type Application: 

Dim XlApp As Excel.Application

which the Automation client will understand, because it can now check the server's object library. Note that we need to qualify the object name, since other object models have an Application object as well.

Next, we want to start the Excel Automation server, create an Excel Application object, and get a reference to that object. This is done in the following line: 

Set XLApp = New Excel.Application

At this point, we have complete access to Excel's object model. It is important to note, however, that the previous line starts the Excel Automation server, but does not start Excel's graphical user interface, so Excel will be running invisibly. To make Excel visible, we just set its Visible property to True: 

XLApp.Visible = True

We can now program as though we were within the Excel VBA IDE. For instance, the following code creates a new workbook, adds a worksheet to it, puts a value in cell A1, and then saves the workbook: 

Sub MakeWorkbook()

Dim XlApp As Excel.Application
Dim wb As Excel.Workbook
Dim ws As Excel.Worksheet

Set XlApp = New Excel.Application
XlApp.Visible = True

Set wb = XlApp.Workbooks.Add
Set ws = wb.Worksheets.Add
ws.Name = "Sales"
ws.Range("A1").Value = 123

wb.SaveAs "d:\temp\SalesBook"

End Sub

Note that the Excel server will not terminate by itself, even if the XLApp variable is destroyed. If we have made Excel visible, then we can close it programmatically, as well as from the user interface in the usual way (choosing Exit from the File menu, for instance). But if the Excel server is invisible, it must be closed using the Quit method: 

XlApp.Quit

(If we fail to terminate the Excel server, it will remain running invisibly, taking up system resources, until the PC is restarted.)

An Alternative Approach

The approach described for programming Excel from within another application is the preferred approach, since it is the most efficient. However, there is an alternative approach that you may encounter, so let us discuss it briefly. As before, we assume that a reference has been set to the Excel object library.

The CreateObject function can start an Automation server, create an object, and assign it to an object variable. Thus, we can write: 

Dim XLApp as Excel.Application
Set XLApp = CreateObject("Excel.Application")

This approach will execute more slowly than the previous approach using the New keyword, but it is perfectly valid.

As before, we must remember to close Excel using the Quit method (or through normal means if Excel is visible).

If Excel is already running, the CreateObject function will start a second copy of the Excel server. To use the currently running version, we can use the GetObject function to set a reference to the Application object of a running copy of Excel. This is done as follows: 

Set XLApp = GetObject(, "Excel.Application")

(The first parameter of GetObject is not used here.)

One of the problems with using GetObject is that it will produce an error if Excel is not running. Thus, we need some code that will start Excel if it is not running or use the existing copy of Excel if it is running.

The trick to this is to know that if GetObject fails to find a running copy of Excel, then it issues error number 429 ("ActiveX component can't create object"). Thus, the following code does the trick: 

Dim XLApp As Excel.Application
On Error Resume Next
' Try to get reference to running Excel
Set XLApp = GetObject(, "Excel.Application")
If Err.Number = 429 Then
    ' If error 429, then create new object
    Set XLApp = CreateObject("Excel.Application")
ElseIf Err.Number <> 0 Then
    ' If another type of error, report it
    MsgBox "Error: " & Err.Description
    Exit Sub
End If

We have been assuming that the client application has a reference to the server's object library. However, it is still possible for a client application (an Automation client) to program the objects of an Automation server (such as Excel) without such a reference. Under these circumstances, we cannot refer to objects by name in code, since the client will not understand these names. Instead, we must use the generic Object data type, as in the following code: 

Dim XLApp As Object
Dim wb As Object
Set XLApp = CreateObject("Excel.Application")
XLApp.Visible = True
Set wb = XLApp.Workbooks.Add
wb.SaveAs "d:\temp\SalesBook"

This code will run even more slowly than the previous code, which, in turn, is slower than the first version.

Thus, we have three versions of Automation:

  • Using the New keyword syntax (requires an object library reference)

  • Using CreateObject and specific object variable declarations (requires an object library reference)

  • Using CreateObject with generic As Object declarations (does not use an object library reference)

These versions of automation are sometimes referred to by the names very early binding, early binding, and late binding, respectively (although you may hear these terms used somewhat differently).

These terms refer to the time at which VBA can associate (or bind ) the object, property, and method names in our code to the actual addresses of these items. In very early binding, all bindings are done at compile time by VBA—that is, before the program runs. In early binding, some of the bindings are done at compile time and others are done at run time. In late binding, all bindings are done at run time.

The issue is now evident. The more binding that needs to be done at run time, the more slowly the program will run. Thus, very early binding is the most efficient, followed by early binding, and then late binding.