August 6, 2020

What is CreateObject Method in VBA and How to use CreateObject Method in Excel?

As the name suggests, the CreateObject function is used to create objects.

But why do we use CreateObject method or function if we can directly create an object using the new keyword? 

Well, that’s a valid question and we have a fitting answer.

The creation of an object using the CreateObject is called Late Binding. In late binding the creation of objects happens on the run time. It does not need any references to be added. This makes the VBA code portable.

For example, if you create an application that deals with other applications and you use early binding by adding references and using the new keyword. Later you transfer that code to some other machine, then you will have to add the references on that machine too. But if you had used the CreateObject method for creating other application objects, you will not need to add the references on other machines, in case you transfer or share the code.

In this article we will learn about the CreateObject method using some examples.

Syntax of CreateObject Function:

Set object_name= CreateObject(classname as string,[servername])

classname as string: It is a required variable. It is a string that refers to the name of application and object type. The application name and class of the object to be created should be declared in AppName.ObjecType. For example, if I want an object of Word Application then i would write “Word.Application”. We will see it in detail in examples later.

[servername]: It is an optional variable. It is a string of the name of the network server where the object will be created. If servername is an empty string (“”), the local machine is used. We will not be using this in this chapter.

Now, that we know the basics of the CreateObject function let’s use them in some examples:

Example 1: Open Microsoft Word Application Using Excel VBA

So, if we wanted to use early binding, we would add references to word applications using Tools–>References menu.

And our code would look like this.

Sub OpenWordApp()


    Dim wordApp As New Word.Application

    Dim wordDoc As Document

    wordApp.Visible = True

    wordDoc = wordApp.Documents.Add


End Sub

The advantage of this code, that you get the assistance of intellensence of VBA and it shows you the available method and properties of the object you have created.It will work perfectly fine your system. But, if you share this code to someone else and they haven’t added the reference to the microsoft word library from tools, they will get an error.

To avoid this error, use the below code.

Sub OpenWordApp()

    Dim wordApp As Object

    

    Set wordApp = CreateObject("Word.Application")

    

    Dim wordDoc As Object

    wordApp.Visible = True

    Set wordDoc = wordApp.Documents.Add

End Sub

The above code will work perfectly fine on any machine. It is portable since we are doing late binding using CreateObject method to create the object.

Let’s see another example:

Example 2: Create Workbook Object Using CreateObject Function

If you are working with VBA for any amount of time, you must have created or added in workbooks using  the New keyword. In this example, we will do so using CreateObject.

Sub addSheet()

    

    ' Declare an object variable to hold the object

    ' reference. Dim as Object causes late binding.

    Dim ExcelSheet As Object

    Set ExcelSheet = CreateObject("Excel.Sheet")

    ' Make Excel visible through the Application object.

    ExcelSheet.Application.Visible = True

    ' Place some text in the first cell of the sheet.

    ExcelSheet.Application.Cells(1, 1).Value = "This is column A, row 1"

    ' Save the sheet to C:test.xls directory.

    ExcelSheet.SaveAs "C:TEST.XLS"

    ' Close Excel with the Quit method on the Application object.

    ExcelSheet.Application.Quit

    ' Release the object variable.

    Set ExcelSheet = Nothing

End Sub

So yeah guys, this is how you use the CreateObject method in VBA. Let’s discuss the benefits and shortcomings of it.

Advantages of CreateObject to create Object

The main advantage of the CreateObject is that it makes your code portable (when the object creation is the concern). You can share the code to anyone without worrying about if they have added the reference to the object program using or not.

Shortcoming of CreateObject

The shortcomings of CreateObject method are:

You need to know the structure of the Class you are going to use for object creation.

Once you have created the object, you are totally dependent on your memory for the methods and properties of objects, as VBA does not provide any intellisense to help you.

We can overcome the above shortcomings. I have a trick.

Whenever I write code that will be shared to others, I use the first method for creating objects (Adding references from tools). This helps me write the code faster. Once I finnish the VBA program and have tested it, I replace the New method with the CreateObject Method. This makes the code portable. You can use this trick.

So yeah guys, this is how you can use the CreateObject function to create objects in VBA. I hope I was able to explain everything. If you have any questions regarding this article or any other VBA related questions, ask me in the comments section below.

Leave a Reply

Your email address will not be published. Required fields are marked *