This article will show you how to use the Create Object method in VBA.
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.
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
VBA Coding Made EasyStop searching for VBA code online. Learn more about AutoMacro - A VBA Code Builder that allows beginners to code procedures from scratch with minimal coding knowledge and with many time-saving features for all users!