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

In this tutorial, we will guide you to learn What Is CreateObject Method In VBA And How To Use CreateObject Method In Excel. Let’s get them below!! Get an official version of MS Excel from the following link: https://www.microsoft.com/en-in/microsoft-365/excel

CreateObject Method In VBA

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.

CreateObject Function Syntax

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

Syntax Explanation:

• 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.

Example 1: Open Microsoft Word Application Using Excel VBA

• Firstly, in the Excel Worksheet, you have to go to the Developer Tab.
• Then, you need to ** ** select the Visual Basic option under the Code section.

Select Visual Basics

• Now, you have to copy and paste the code given below.

 Sub OpenWordApp()

    Dim wordApp As New Word.Application

    Dim wordDoc As Document

    wordApp.Visible = True

    wordDoc = wordApp.Documents.Add

End Sub

• After that, you need to save the code by selecting it and then close the window.

Save the code

• Again, you have to go to the Excel Spreadsheet , and click on the Developer Tab.
• You need to choose the Macros option in the Code section.

Choose Macro option

• Then, you have to make sure that your macro name is selected and click the *Run * button.

Click on the Run option

• Now, you will get user defined error message as given below.

User-defined error

To avoid this error, use the below code.

• Again, in the Excel Worksheet, you have to go to the Developer Tab.
• Then, you need to ** ** select the Visual Basic option under the Code section.
• Now, you have to copy and paste the code given below.

 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

• After that, you need to save the code by selecting it and then close the window.

Save the code

• Again, you have to go to the Excel Spreadsheet , and click on the Developer Tab.
• You need to choose the Macros option in the Code section.
• Now, you have to make sure that your macro name is selected and click the *Run * button.

Run the code

• Finally, the Microsoft Word Application has opened using Excel VBA.

Open MS Word

Example 2: Create Workbook Object Using CreateObject Function

• Firstly, in the Excel Worksheet, you have to go to the Developer Tab.
• Then, you need to ** ** select the Visual Basic option under the Code section.

Select Visual Basic

• Now, you have to copy and paste the code given below.

 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

• After that, you need to save the code by selecting it and then close the window.

Save the Code

• Again, you have to go to the Excel Spreadsheet , and click on the Developer Tab.
• You need to choose the Macros option in the Code section.

Choose Macro option

• Now, you have to make sure that your macro name is selected and click the *Run * button.

Run the code

• Finally, you will receive the result in the Word Document.

Advantages of CreateObject to create Object

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

The shortcoming of CreateObject:

• The shortcomings of the 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.

Verdict

In this tutorial, we guided you to know What Is CreateObject Method In VBA And How To Use CreateObject Method In Excel. Leave your queries/suggestions in the below comment section. Thanks for visiting Geek Excel. Keep Learning!

Keep Reading:

• What is a Name Box and Its Features in Microsoft Excel 365?
• Highlight Unique Values in Selection using Macros in Excel 365!!
• { Save As } Shortcut in Microsoft Excel 365!!! – Quick and Easy Shortcut
• Excel Formulas to Count specific word in a range ~ Easy Tutorial!!
• Excel Formulas to Get the Last Word of a Text String ~ Quick Method!!