As the name suggests, the CreateObject function is used to create objects.
But why do we use CreateObject method or function if we can directly create an object using the new keyword?
Well, that's a valid question and we have a fitting answer.
The creation of an object using the CreateObject is called Late Binding. In late binding the creation of objects happens on the run time. It does not need any references to be added. This makes the VBA code portable.
For example, if you create an application that deals with other applications and you use early binding by adding references and using the new keyword. Later you transfer that code to some other machine, then you will have to add the references on that machine too. But if you had used the CreateObject method for creating other application objects, you will not need to add the references on other machines, in case you transfer or share the code.
In this article we will learn about the CreateObject method using some examples.
Syntax of CreateObject Function:
| Set object_name= CreateObject(classname as string,[servername]) |
classname as string: It is a required variable. It is a string that refers to the name of application and object type. The application name and class of the object to be created should be declared in AppName.ObjecType. For example, if I want an object of Word Application then i would write "Word.Application". We will see it in detail in examples later.
[servername]: It is an optional variable. It is a string of the name of the network server where the object will be created. If servername is an empty string (""), the local machine is used. We will not be using this in this chapter.
Now, that we know the basics of the CreateObject function let's use them in some examples:
So, if we wanted to use early binding, we would add references to word applications using Tools-->References menu.
And our code would look like this.
Sub OpenWordApp() Dim wordApp As New Word.Application Dim wordDoc As Document wordApp.Visible = True wordDoc = wordApp.Documents.Add End Sub
The advantage of this code, that you get the assistance of intellensence of VBA and it shows you the available method and properties of the object you have created.It will work perfectly fine your system. But, if you share this code to someone else and they haven't added the reference to the microsoft word library from tools, they will get an error.
To avoid this error, use the below code.
Sub OpenWordApp()
Dim wordApp As Object
Set wordApp = CreateObject("Word.Application")
Dim wordDoc As Object
wordApp.Visible = True
Set wordDoc = wordApp.Documents.Add
End Sub
The above code will work perfectly fine on any machine. It is portable since we are doing late binding using CreateObject method to create the object.
Let's see another example:
If you are working with VBA for any amount of time, you must have created or added in workbooks using the New keyword. In this example, we will do so using CreateObject.
Sub addSheet()
' Declare an object variable to hold the object
' reference. Dim as Object causes late binding.
Dim ExcelSheet As Object
Set ExcelSheet = CreateObject("Excel.Sheet")
' Make Excel visible through the Application object.
ExcelSheet.Application.Visible = True
' Place some text in the first cell of the sheet.
ExcelSheet.Application.Cells(1, 1).Value = "This is column A, row 1"
' Save the sheet to C:\test.xls directory.
ExcelSheet.SaveAs "C:\TEST.XLS"
' Close Excel with the Quit method on the Application object.
ExcelSheet.Application.Quit
' Release the object variable.
Set ExcelSheet = Nothing
End Sub
So yeah guys, this is how you use the CreateObject method in VBA. Let's discuss the benefits and shortcomings of it.
The main advantage of the CreateObject is that it makes your code portable (when the object creation is the concern). You can share the code to anyone without worrying about if they have added the reference to the object program using or not.
Shortcoming of CreateObject
The shortcomings of CreateObject method are:
You need to know the structure of the Class you are going to use for object creation.
Once you have created the object, you are totally dependent on your memory for the methods and properties of objects, as VBA does not provide any intellisense to help you.
We can overcome the above shortcomings. I have a trick.
Whenever I write code that will be shared to others, I use the first method for creating objects (Adding references from tools). This helps me write the code faster. Once I finnish the VBA program and have tested it, I replace the New method with the CreateObject Method. This makes the code portable. You can use this trick.
So yeah guys, this is how you can use the CreateObject function to create objects in VBA. I hope I was able to explain everything. If you have any questions regarding this article or any other VBA related questions, ask me in the comments section below.
Related Articles:
Getting Started With Excel VBA UserForms| I will explain how to create a form in excel, how to use VBA toolbox, how to handle user inputs and finally how to store the user inputs. We will go through these topics using one example and step by step guide.
VBA variables in Excel| VBA stands for Visual Basic for Applications. It is a programming language from Microsoft. It is used with Microsoft Office applications such as MSExcel, MS-Word and MS-Access whereas VBA variables are specific keywords.
Excel VBA Variable Scope| In all the programming languages, we have variable access specifiers that define from where a defined variable can be accessed. Excel VBA is no Exception. VBA too has scope specifiers.
ByRef and ByVal Arguments | When an argument is passed as a ByRef argument to a different sub or function, the reference of the actual variable is sent. Any changes made into the copy of the variable, will reflect in the original argument.
Delete sheets without confirmation prompts using VBA in Microsoft Excel | Since you are deleting sheets using VBA, you know what you are doing. You would like to tell Excel not to show this warning and delete the damn sheet.
Add And Save New Workbook Using VBA In Microsoft Excel 2016| In this code, we first created a reference to a workbook object. And then we initialized it with a new workbook object. The benefit of this approach is that you can do operations on this new workbook easily. Like saving, closing, deleting, etc
Display A Message On The Excel VBA Status Bar| The status bar in excel can be used as a code monitor. When your VBA code is lengthy and you do several tasks using VBA, you often disable the screen update so that you don’t see that screen flickering.
Turn Off Warning Messages Using VBA In Microsoft Excel 2016| This code not only disables VBA alerts but also increases the time efficiency of the code. Let’s see how.
Popular Articles:
50 Excel Shortcuts to Increase Your Productivity | Get faster at your task. These 50 shortcuts will make you work even faster on Excel.
The VLOOKUP Function in Excel | This is one of the most used and popular functions of excel that is used to lookup value from different ranges and sheets.
COUNTIF in Excel 2016 | Count values with conditions using this amazing function. You don't need to filter your data to count specific values. Countif function is essential to prepare your dashboard.
How to use SUMIF Function in Excel | This is another dashboard essential function. This helps you sum up values on specific conditions.
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.
