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.
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, 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.
- [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.
Now we will see how to initiate PowerPoint application from excel using the CreateObject function in VBA. Open excel file and go to Visual Basic Editor Window by pressing the ALT + F11 key.
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 this is because “PowerPoint” is an external object. But nothing to worry declare the variable as “Object”.
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. By entering the “Set” keyword mention the variable and put an equal sign.
Sub CreateObject_Example1() Dim PPT As Object Set PPT = End Sub
Now open CreateObject function.
Since we are referencing the external object of “PowerPoint” for the “Class” parameter of Create Object function mention the external object name in doubles quotes as “PowerPoint.Application”.
Sub CreateObject_Example1() Dim PPT As Object Set PPT = CreateObject("PowerPoint.Application") End Sub
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.
One of the problems with 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”.
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.
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 “PowerPoint” application opens up.
Once the PowerPoint application is enabled using the variable “PPT” we can start accessing the PowerPoint application.
Now we will see how to initiate an Excel application using the CreateObject function in VBA. Once again declare the variable as “Object”.
Sub CreateObject_Example2() Dim ExcelSheet As Object End Sub
The moment we declare the variable as object it causes late binding and we need to use the “Set” keyword to set the reference for the required object.
Since we referencing to excel worksheet from the application excel, enter “Excel.Sheet” in double-quotes.
Sub CreateObject_Example2() Dim ExcelSheet As Object Set ExcelSheet = CreateObject("Excel.Sheet") End Sub
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.
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 to initiate an excel workbook from other Microsoft products we can use the below code.
Sub CreateObject_Example3() Dim ExlWb As Object Set ExlWb = CreateObject("Excel.Application") ExlWb.Application.Visible = True End Sub
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 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 Createobject function in excel VBA along with practical examples and a downloadable template. Below you can find some useful excel VBA articles –