VBA CreateObject

CreateObject Function in VBA

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

VBA-CreateObject

You are free to use this image on your website, templates etc, Please provide us with an attribution linkHow to Provide Attribution?Article Link to be Hyperlinked
For eg:
Source: VBA CreateObject (wallstreetmojo.com)

What is the CreateObject in Excel VBA?

“Create Object” as the name itself says it 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

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

Example of Create Object Function in Excel VBA

Below are the examples of VBA CreateObject.

You can download this VBA CreateObject Excel Template here – VBA CreateObject Excel Template

Example #1

Now we will see how to initiate PowerPoint application from excel using the CreateObject function in VBA. Open the excel file and go to Visual Basic Editor Window by pressing the ALT + F11 key.

Code:

Sub CreateObject_Example1()

End Sub
VBA CreateObject Example 1

Declare the variable as PowerPoint.Application.

VBA CreateObject Example 1-1

As you can see above, when we start typing the word “PowerPoint,” we don’t see any IntelliSense list showing the related searches. This is 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
VBA CreateObject Example 1-2

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

Code:

Sub CreateObject_Example1()

Dim PPT As Object

Set PPT =

End Sub
VBA CreateObject Example 1-3

Now open CreateObject function.

VBA CreateObject Example 1-4

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
Powerpoint.applictaion Example 1-5

Now Create Object function will initiate the PowerPoint application. Once the object is initiated, we need to make it visible by using the variable name.

Example 1-6

One of the problems with the Create Object method or late binding method is we don’t get to see the IntelliSense list at the point in time. You need to be absolutely sure about the code that you are writing.

For the variable “PPT,” use “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
excel VBA Create Object Example 1-7

To add a slide to PPT, define the below line VBA codeVBA CodeVBA code refers to a set of instructions written by the user in the Visual Basic Applications programming language on a Visual Basic Editor (VBE) to perform a specific task.read more.

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.

VBA CreateObject Example 1-9

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
Example 2

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.

Example 1-8

Since we are referencing to 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
Example 2-1

Once the reference for the excel sheet is set, we need to make it visible to use it. This 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
Example 2-2

Now it will activate the excel worksheet.

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

Code:

Sub CreateObject_Example3()

Dim ExlWb As Object

Set ExlWb = CreateObject("Excel.Application")

ExlWb.Application.Visible = True

End Sub
VBA CreateObject Example 3

Things to Remember About CreateObject in VBA

  • In VBA, the CreateObject function is used to reference objects.
  • Create Object function causes a late-binding process.
  • Using the create object function, we don’t get to access the IntelliSense list of VBA.

This has been a guide to CreateObject in VBA. Here we discuss how to create a reference object using the Createobject function in excel VBA along with practical examples and a downloadable template. Below you can find some useful excel VBA articles –

  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>