VBA CreateObject

Objects are very important concepts in VBA coding, and understanding an object’s work model is quite complex. When we reference the objects in VBA coding, we do it in two ways: “Early Binding” and “Late Binding.” “Early Binding” is the process of setting the object reference from the references library of the VBA. When we send the file to someone else, they must also set the reference to those respective objects. However, “Late Binding” does not require the user to set any object references because, in late binding coding, we set the reference to the respective object using the VBA “CreateObject” function.

“Create Object,” as the name says, will create the mentioned object from the Excel VBA. So, the Create Object function returns the reference to an object initiated by an Active X component.

Below is the syntax of the CreateObject function in VBA: -

• Class: The name of the object that we are trying to initiate and set the reference to the variable.
• : This is an optional parameter; if ignored, it will use the local machine only.

Below are the examples of VBA CreateObject.

Example #1

Now, we will see how to initiate a PowerPoint application from Excel using the CreateObject function in VBA. But, first, open the Excel file and go to the Visual Basic Editor window by pressing the ALT + F11 key.

Code:

Sub CreateObject_Example1()

End Sub

Declare the variable as PowerPoint.Application.

As you can see above, when we start typing the word “PowerPoint,” we don’t see any IntelliSense list showing the related searches because “PowerPoint” is an external object. But nothing to worry declare the variable as “Object.”

Code:

Sub CreateObject_Example1()

Dim PPT As Object

End Sub

Since we have declared the variable as “Object,” we need to set the reference to the object by using the “Set” keyword. Enter the “Set” keyword, mention the variable, and put an equal sign.

Code:

Sub CreateObject_Example1()

Dim PPT As Object

Set PPT =

End Sub

Now, open the CreateObject function.

Since we are referencing the external object of “PowerPoint” for the “Class” parameter of the Create Object function, mention the external object name in doubles quotes as “PowerPoint.Application.”

Code:

Sub CreateObject_Example1()

Dim PPT As Object

Set PPT = CreateObject("PowerPoint.Application")

End Sub

Now, the CreateObject function will initiate the PowerPoint application. Once we initiate the object, we need to make it visible using the variable name.

One of the problems with the CreateObject method or late binding method is we don’t get to see the IntelliSense list now. So it would be best if you were sure about the code you are writing.

For the variable “PPT,” use the “Visible” property and set the status as “True.”

Code:

Sub CreateObject_Example1()

Dim PPT As Object

Set PPT = CreateObject("PowerPoint.Application")

PPT.Visible = True

End Sub

To add a slide to PPT, define the below-line VBA code.

Code:

Sub CreateObject_Example1()

Dim PPT As Object

Set PPT = CreateObject("PowerPoint.Application")

PPT.Visible = True
PPT.Presentations.Add

End Sub

Now, execute the code manually or through the F5 key and see the “PowerPoint” application opens up.

Once the PowerPoint application is enabled using the variable “PPT,” we can start accessing the PowerPoint application.

Example #2

Now, we will see how to initiate an Excel application using the CreateObject function in VBA. Once again, declare the variable as “Object.”

Code:

Sub CreateObject_Example2()

Dim ExcelSheet As Object

End Sub

The moment we declare the variable as an object, it causes late binding, and we need to use the “Set” keyword to set the reference for the required object.

Since we are referencing an Excel worksheet from the application Excel, enter “Excel. Sheet” in double quotes.

Code:

Sub CreateObject_Example2()

Dim ExcelSheet As Object

Set ExcelSheet = CreateObject("Excel.Sheet")

End Sub

Once we set the reference for the Excel sheet, we need to make it visible to use it. It is similar to how we made the PowerPoint application visible.

Code:

Sub CreateObject_Example2()

Dim ExcelSheet As Object

Set ExcelSheet = CreateObject("Excel.Sheet")

ExcelSheet.Application.Visible = True

End Sub

Now, it will activate the Excel worksheet.

Similarly, we can use the code to initiate an Excel workbook from other Microsoft products.

Code:

Sub CreateObject_Example3()

Dim ExlWb As Object

Set ExlWb = CreateObject("Excel.Application")

ExlWb.Application.Visible = True

End Sub

• In VBA, we can use the CreateObject function to reference objects.
• The Create Object function causes a late-binding process.
• Using the CreateObject function, we do not get to access the IntelliSense list of VBA.