VBA Create Object

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 –

1. VBA LBound
2. VBA Solver
3. VBA Login
4. VBA Month