GetObject Vs CreateObject VBA (Visual Basic for Applications)

Office Automation Server

VBA offers two different functions which usually questioned by developer that what is the difference between CreateObject method and GetObject method? CreateObject method allows developer to create a new instance of an object of any kind of COM. Wherein GetObject method helps getting an existing running COM object.

GetObject

is used to attach to a running instance of an automation server. There are a few different ways to call GetObject, but the syntax that is recommended for the Microsoft Office applications is as follows:

Code example

Sub AutomationServer()
    On Error GoTo errh
    'Get an already opend Word Application
    Dim wordApp As Variant
    Set wordApp = GetObject(, "Word.Application")
    'Get an already opend Excel Application
    Dim xlApp As Variant
    Set xlApp = GetObject(, "Excel.Application")
    'Get an already opend PowerPoint Application
    Dim pptApp As Variant
    Set pptApp = GetObject(, "PowerPoint.Application")
    'Get an already opend InternetExplorer Application
    Dim ieApp As Variant
    Set ieApp = GetObject(, "InternetExplorer.Application")
errh:
    If Err.Number <> 0 Then
        'Capture error number 429 if application is not running
        If Err.Number = "429" Then
            Debug.Print "Fail to get object"
        End If
    End If
End Sub

If an instance of Microsoft Excel is running when this code is executed, you have access to the running instance’s object model through the xlApp variable. If no instance is running, you receive the following run-time error message:

Run-time error ‘429’:
ActiveX component can’t create object

If multiple instances of Microsoft Excel are running, GetObject attaches to the instance that is launched first. If you then close the first instance, another call to GetObject attaches to the second instance that was launched, and so forth. You can attach to a specific instance if you know the name of an open document in that instance. For example, if an instance of Excel is running with an open workbook named Book2, the following code attaches successfully to that instance even if it is not the earliest instance that was launched:

Code example

Sub GetObjectExample()
    On Error GoTo errh
    Set xlApp = GetObject("Book2").Application
errh:
    If Err.Number <> 0 Then
        'Capture error number 429 if application is not running
        If Err.Number = "429" Then
            Debug.Print "Fail to get object"
        End If
    End If
End Sub

Let’s create a dynamic code which will check if specific application is running or not. If application is not running the it will create new instance.

Code example

Sub AutomationServerCodeExample()
    On Error GoTo errh
    'Get an already opend Word Application
    Dim wordApp As Variant
    Set wordApp = GetObject(, "Word.Application")
    wordApp.Visible = True
errh:
    If Err.Number <> 0 Then
        'Capture error number 429 if application is not running
        If Err.Number = "429" Then
            'Create word object and launch
            Err.Clear
            Set wordApp = CreateObject("Word.Application")
            Resume Next
        End If
    End If
End Sub

CreateObject

Starts a new instance of an Automation server irrespective an existing application is running or not:

Code example

Public Sub CreateObjectExample()
	Dim xlApp As Application
	set xlApp = CreateObject("Excel.Application")
End Sub

Next>>InputBox VBA

Share on Facebook

Tweet