VBA CreateObject (Create Object)

This article will show you how to use the Create Object method in VBA.

VBA is an Object Orientated Language – it uses procedures to control and create Objects.

Create Object

We can use the Create Object method to create an Object in a Microsoft Office application.  For example, if we are writing VBA code in Excel, and wish to open a copy of Word, we can use the Create Object method to create a new instance of Word.

For example:

Sub CreateWordInstance()
  Dim wdApp As Object
  Set wdApp = CreateObject("Word.Application")
  wdApp.Visible = True
End Sub

Similarly, we can create a new instance of PowerPoint or Access.

Sub CreatePowerPointApplication
  Dim ppApp as Object 
  Set ppApp = CreateObject("PowerPoint.Application")
  ppApp.Visible = True 
End Sub

We can also use Create Object to create objects other than the Application Object.  We can use it to create an Excel Sheet for example.

Sub CreateExcelSheet()
  Dim xlSheet As Object
  Set xlSheet = CreateObject("Excel.Sheet")
  xlSheet.Application.Visible = True
  xlSheet.Application.Range("A2") = "Good morning"
  Set xlSheet = Nothing
End Sub

However, this actually creates a new instance of Excel – it does not create the sheet in the instance that is already open.  For that reason, we have to set Application of the new sheet (ie: the new instance of Excel) to Visible in order to see the object.

In all of the examples above, we are using Late Binding – hence we declare the variables as Objects.  We can also use Early Binding by setting a reference to Word or PowerPoint in our VBA Project and then writing the Sub Procedure as shown below. To understand more about Late and Early binding, click here.

Firstly for Early Binding, within the VBE, we set a reference to Microsoft Word.

In the Menu bar, select Tools > References and scroll down to find the reference to the Microsoft Word 16.0 Object Library.

Make sure the reference is checked, and then click OK.

NOTE: the version might not be 16.0, it all depends on what version of Microsoft Office you are running on your PC!

Now, we declare the Object using Early Binding – this means that, instead of declaring the wdApp as an Object, we declare it as a Word.Application.   The rest of the code is the same as when we used Late Binding above.

Sub CreateWordInstance() 
  Dim wdApp As New Word.Application
  Set wdApp = CreateObject("Word.Application") 
  wdApp.Visible = True 
End Sub