Safely Automating Other Applications with CreateObject in Office VBA

This article is a technical commentary and implementation example created using AI. The codes and procedures presented are based on primary sources, but operation checks on actual devices by the author have not been conducted. Operations may vary depending on the environment and version.

The CreateObject function in Office VBA is used to create and return a reference to an ActiveX object. It is positioned in the official specifications as a fundamental mechanism for operating applications that support automation, such as Word and Excel, from VBA. Based on primary sources, this article organizes the syntax of the CreateObject function, details of its arguments, the process from object generation to release, and related specifications.

Syntax and Arguments of the CreateObject Function

According to primary sources, the syntax of the CreateObject function is as follows.

CreateObject(class, [servername])

The arguments provided are class and servername. Let’s look at the role of each.

• class (Required / Variant(String)): Specifies the application name and class of the object to create.

• servername (Optional / Variant(String)): Specifies the name of the network server where the object is to be created. If an empty string ("") is specified, the local machine is used.

Additionally, the class argument is structured in the format appname.objecttype.

• appname: The name of the application providing the object.

• objecttype: The type or class of the object to create.

Object Variable Declaration and Late Binding

To obtain an object from the target application for automation, assign the reference returned by CreateObject to an object variable.

Primary sources explain how to declare object variables using the As Object clause. Using this declaration method allows the variable to hold any type of object reference.

' [To be verified in a Windows environment]
Dim ExcelSheet As Object
Set ExcelSheet = CreateObject("Excel.Sheet")

When declared with As Object in this manner, access becomes late binding. In other words, binding takes place at program runtime.

After assigning a reference to the object variable, you can access properties and methods of the application object, collections, and so on. Primary sources illustrate a sequence of operations where Excel is made visible, text is entered into a cell, and the file is saved and closed.

' [To be verified in a Windows environment]
ExcelSheet.Application.Visible = True
ExcelSheet.Application.Cells(1, 1).Value = "This is column A, row 1"
ExcelSheet.SaveAs "C:TEST.XLS"
ExcelSheet.Application.Quit
Set ExcelSheet = Nothing

At the end of processing, it is important to execute Set ExcelSheet = Nothing to ensure the object variable is released.

Configuring Implementation via Early Binding

Primary sources mention not only late binding, but also early binding (compile-time binding) by specifying a specific class ID.

By explicitly declaring variables with a specific type, performance improvements can be expected, but they will only be able to hold references of the class specified at declaration.

' [To be verified in a Windows environment]
Dim xlApp As Excel.Application 
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.WorkSheet

Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Add
Set xlSheet = xlBook.Worksheets(1)

In this configuration, each object for the application, workbook, and worksheet is handled according to its respective type.

Creating Objects on Remote Computers Over the Network

The CreateObject function also makes it possible to create objects on remote computers over the network by utilizing the second argument, servername.

Pass a string corresponding to the machine name portion of a share name as the argument. For example, if the share name is MyServerPublic, the server name is "MyServer".

Let’s check the remote execution example described in the primary sources.

' [To be verified in a Windows environment]
Dim xlApp As Object
Set xlApp = CreateObject("Excel.Application", "MyServer")
Debug.Print xlApp.Version

Note that primary sources explain that a run-time error will occur if the specified remote server does not exist or is unavailable.

Overview of the Processing Flow

The mechanisms from object generation using CreateObject to its release, as well as remote referencing explained so far, are organized in a Mermaid flowchart.

flowchart TD
    A["Execute VBA Code"] --> B{"Is servername specified?"}
    B -- Local ("") or Omitted --> C[CreateObject("appname.objecttype")]
    B -- Remote Server Name --> D["CreateObject("appname.objecttype", "ServerName")"]
    C --> E["Assign to Object Variable"]
    D --> E
    E --> F["Operate Properties / Execute Methods"]
    F --> G["Quit Application via Quit Method, etc."]
    G --> H["Release via Set Variable = Nothing"]

Precautions When Using CreateObject

Primary sources list several important precautions when using CreateObject.

1. Launching a New Instance Use CreateObject when no currently running instance of the object exists. Even if an instance is already running, a new instance will be started, and an object of the specified type will be created.

2. Handling Existing Instances and GetObject If you want to use an already running instance or start an application and have it load a file, you are advised to use the GetObject function instead of CreateObject.

3. Single-Instance Objects If an object self-registers as a single-instance object, only one instance is created no matter how many times CreateObject is executed.

Reference Information

• Office VBA CreateObject function