Create Object in VBA
Create object is a function that is used to create and reference the ActiveX objects. An ActiveX object is an object which is used for automation interfaces. Objects are entities in VBA which comprises of code, Excel VBA Create Object allows us to create objects in VBA. To reference objects in there are two types of bindings that are invoked in VBA one is early binding which uses the library or VBA and another is late binding which is used to reference by set statement.
Create Object can be classified into two parts, one is mandatory while another is optional. The Mandatory part is known as the class which is the application name or the interface name which we are trying to create and another optional part is known as server name means the location of the server where the object will be created.
How to Use Create Object Function in VBA Excel?
To create an object we need to declare any variable with an object data type and then we use the set statement to reference it. After that, we use to create object to create an object with reference to the application we are referring to.
Example #1
In this first example, we will try to use Create Object and open word file application. For this, follow the below steps:
Step 1: Insert a new module inside Visual Basic Editor (VBE). Click on Insert tab > select Module.
Step 2: Once we have a module in our project explorer we can begin with our example, write the subprocedure of VBA Create Object Function.
Code:
Sub Example1() End Sub
Step 3: Now we declare word and doc as an object.
Code:
Sub Example1() Dim word As Object Dim doc As Object End Sub
Step 4: Now let us assume that we may encounter an error so we will use error handling from the first hand.
Code:
Sub Example1() Dim word As Object Dim doc As Object On Error Resume Next End Sub
Step 5: As soon as we declare an object in VBA it invokes late binding which means it overrides the virtual method so we need to use the Set keyword.
Code:
Sub Example1() Dim word As Object Dim doc As Object On Error Resume Next Set word = GetObject(, "word.application") End Sub
Step 6: The above statement will generate an error if the word is not open and the error will be “429” which means we have given too many requests for VBA in a little amount of time so, we take note that to avoid this error we need to keep word open for best circumstances, but if we do encounter an error we can clear it by the following code as shown below.
Code:
Sub Example1() Dim word As Object Dim doc As Object On Error Resume Next Set word = GetObject(, "word.application") If Err.Number = 429 Then Err.Clear End Sub
Step 7: Now we will create the object for word application using the create object method.
Code:
Sub Example1() Dim word As Object Dim doc As Object On Error Resume Next Set word = GetObject(, "word.application") If Err.Number = 429 Then Err.Clear Set word = CreateObject("Word.Application") End If End Sub
Step 8: Now we can check if Word is open or not and if it is not open we can open it by the following code.
Code:
Sub Example1() Dim word As Object Dim doc As Object On Error Resume Next Set word = GetObject(, "word.application") If Err.Number = 429 Then Err.Clear Set word = CreateObject("Word.Application") End If If Not word Is Nothing Then word.Visible = True End Sub
Step 9: Also if word is not open we can show an error message.
Code:
Sub Example1() Dim word As Object Dim doc As Object On Error Resume Next Set word = GetObject(, "word.application") If Err.Number = 429 Then Err.Clear Set word = CreateObject("Word.Application") End If If Not word Is Nothing Then word.Visible = True Else MsgBox "Cannot open Word." End If End Sub
Step 10: When we run the above code by pressing function key F5 and to run the code, click on the Play button. we can see that word is open.
Example #2
In this example, we will use the excel application to open excel and write a value in any row. For this, follow the below steps:
Step 1: In the same module we can begin declaring another subprocedure as shown below,
Code:
Sub Example2() End Sub
Step 2: We will Create an excel object in the code as shown below,
Code:
Sub Example2() Dim ExcelSheet As Object End Sub
Step 3: Now we know that as soon as we declare an object and it invokes late binding so we need to set the object as shown below,
Code:
Sub Example2() Dim ExcelSheet As Object Set ExcelSheet = CreateObject("Excel.Sheet") End Sub
Step 4: So now we need to open excel means we have to make it visible and only by that way we will be able to use it as shown below,
Code:
Sub Example2() Dim ExcelSheet As Object Set ExcelSheet = CreateObject("Excel.Sheet") ExcelSheet.Application.Visible = True End Sub
Step 5: Now we can write anything in excel so for this example let us try it in the first row as shown below,
Code:
Sub Example2() Dim ExcelSheet As Object Set ExcelSheet = CreateObject("Excel.Sheet") ExcelSheet.Application.Visible = True ExcelSheet.Application.Cells(1, 1).Value = "This is column A, row 1" End Sub
Step 6: We can also save the excel file for future references.
Code:
Sub Example2() Dim ExcelSheet As Object Set ExcelSheet = CreateObject("Excel.Sheet") ExcelSheet.Application.Visible = True ExcelSheet.Application.Cells(1, 1).Value = "This is column A, row 1" ExcelSheet.SaveAs "D:\TEST.XLS" ExcelSheet.Application.Quit Set ExcelSheet = Nothing End Sub
Step 7: Now we can free the excel object from the following codes.
Code:
Sub Example2() Dim ExcelSheet As Object Set ExcelSheet = CreateObject("Excel.Sheet") ExcelSheet.Application.Visible = True ExcelSheet.Application.Cells(1, 1).Value = "This is column A, row 1" ExcelSheet.SaveAs "D:\TEST.XLS" End Sub
Step 8: When we run the above code by pressing function key F5 and to run the code, click on the Play button. we can see that an excel sheet is created in the path provided in the above line of code also we can see from the result of the code in the file created as follows.
Things to Remember
There are some key points which we need to remember about VBA Create Object and they can be classified as follows:
- When we reference an object it invokes two types of Binding Late Binding and Early Binding.
- Set statement is used to reference the object when late binding is invoked.
- Late Binding is also known as dynamic binding.
- Intellisense is not accessible in create object method.
Recommended Articles
This is a guide to the VBA Create Object. Here we discuss how to use create object function in excel VBA along with practical examples and downloadable excel template. You can also go through our other suggested articles –