Understanding Visual BASIC for Applications (VBA): An intuitive explanation of Object Hierarchy Model (the Basic Building Block of VBA)
Excel is one of the most commonly used spreadsheet applications in the world.Visual BASIC for Applications (VBA) is a programming language used in excel and as excel became more popular, VBA as a language to simplify and automate tasks gained popularity. BASIC in VBA stands for “Beginner’s All-purpose Symbolic Instruction Code”. In the 1960's, it was used to teach programming techniques to the college graduates.
The concepts discussed here should be considered as the building blocks of the process to understand VBA. A good understanding of the object hierarchy model in excel is a good starting point to learn this language.
Each of the Microsoft products (Excel, Word etc.) has its own unique object hierarchy model. The secret of using VBA lies in understanding this model.
Therefore to understand VBA, it’s helpful to think in terms of objects that can be manipulated manually or with the help of VBA. There are more than 200 classes of objects in excel that can be controlled directly or indirectly using VBA.
Objects have properties, property can be considered as a setting of an object. VBA makes use of methods to manipulate the objects.
Analogy to explain the relationship between objects, properties and methods:
To get a more intuitive understanding of the concept of objects, properties and methods, let’s consider the following:
Compare Excel to a chain of hotels.
A. Objects:
As discussed earlier, Excel is based on the object hierarchy model.
As per the object hierarchy model, an object contains other objects, for example workbook contains worksheets objects, the worksheet contains objects such as range, pivot tables etc.
Similarly in a hotel chain, an individual hotel includes room, room contain objects such as TV, bed etc.
B. Properties and Methods:
In a hotel room, TV is an object and one of the properties of a TV is its volume. The volume can be controlled (increased/decreased) by pressing a button. Therefore, there is a method to change the property of the TV (object).
Similarly, in excel the “Range” object has a value (property), this value can be changed i.e. there is a method to change the property of the Range object.
C. Manipulating the Objects:
In a hotel chain, the TV in a room of the hotel can be controlled by anyone. Similarly in excel the value of the range can be changed manually or by using VBA. This means that VBA can be used to manipulate the objects. This requires the knowledge of the properties and methods of the objects.
How does the above help in understanding the use of VBA as programming language to automate and simplify tasks?
To get a practical understanding of the VBA object model, let’s consider the following example:
Consider that you have a excel workbook with one worksheet. The range(“A1”) of the worksheet has the following content/value i.e. Range(“A1”) = 100
Task is to delete the content using VBA.
Steps to be followed:
1. Identify the objects in the hierarchy (object hierarchy model):
a. Excel workbook (currently open workbook) is the 1st level object in the hierarchy
b. Worksheet (Sheet1) is the 2nd level object in the hierarchy
c. Range(“A1”) is the 3rd level object in the hierarchy.
2. Our task here is to delete/clear contents of the range. Following is the code (written in the Visual Basic Editor) to accomplish this task. We start by referencing the objects and then apply the method “ClearContents” to delete the value in range.
Note: Workbook name is the first object in the hierarchy model but is not part of the code above because VBA considers the workbook object by default.While working with multiple workbooks, it is important to reference the workbook.
How to know what are the different properties/methods associated with the objects in excel?
When we write the code in the Visual Basic Editor (VBE) in excel a “Intellisense Drop Down Menu” appears as soon as we reference the object.
For example, after the Range(“A1”)., the list of properties and methods associated with this range appear automatically.
The above list indicated by the “Intellisense dropdown menu” includes both properties and methods.
How to identify a method or property in this list?
The easiest way is to look at the icon, before the name. Example-Property and Method are identified by the following icons in the “intellisense drop down menu”.
Conclusion:
Visual Basic for Applications can help simplify a lot of tasks which otherwise require a lot of manual efforts. To understand functioning of VBA as a programming language, understanding of the object hierarchy model is required. The intent of this article is to give an intuitive explanation of the fundamental concepts of VBA to beginners.
Note: This article does not discuss other details such as events, types of procedures (sub-procedure/functions) in excel. Although VBA is object based but it should not be confused with Object Oriented Programming language because it does have all the features associated with such languages.
Reference:
Walkenbach, John. Excel 2013 Power Programming with VBA. John Wiley & Sons, 2013
