VBA stands for visual basic for application. Excel VBA is an object-based programming language, it is used while recording a macro i.e., it uses the idea of Encapsulation and stores the state (data) and operation (functions) inside the object. Excel objects are arranged in a hierarchy that governs the excel object. Every element in excel is represented by an object in VBA. Below are the basic excel elements,
Excel Objects
In the above picture, we can have the following different objects:
- Workbook Object – We have one excel workbook open so, we have 1 workbook object. For example, if we have 3 different excel workbooks, then we have 3 workbook objects.
- Worksheet Object – A workbook object has a worksheet object. Here, we have 3 different worksheet objects.i.e., Sheet1, Sheet2, and Sheet3.
- Range Object – Inside a worksheet object we can have a range object. Here, we have RangeObject(B5:D9).
- ActiveCell Object – Inside a range object or a worksheet object we can have a cell object.
- Cell Object – Inside a cell object we can have Font, Size, Color, Value, Border, and so on.., various objects.
The excel objects are arranged in a hierarchy and they act as the container for the other excel objects. In the hierarchy we have Excel Application Object at the top, the excel application object contains WorkbookObject, WindowObjcet, etc., the workbook object contains WorksheetObject, ChartsObject, etc., the worksheet object contains RangeObject, ShapeObject, ChartObject, etc., the range object contains CellObject, the cell object contains Font Object, Interior Object, Border Object, etc.
Example: In this example, we will define a macro in excel, we will enter the code inside that macro, and execute the code to understand the working of various excel objects.
Step 1: In this step, we will write our VBA code in the visual basic editor. For this go to Developer and then select Visual Basic Editor.
The Visual Basic Code Editor is now open, we can begin writing our VBA script.
Step 2: In this step, we will write our VBA scripts. Double-clicking ThisWorkbook under Microsoft Excel Objects in the left pan will do this. The editor will then be opened, and the following code will be entered into it.
‘function name
Sub ObjectHierarcy()
Application.Workbooks(1).Worksheets(“Sheet1”).Range(“A1”).Value = “GeeksForGeeks”
‘Here, we are using Application Object to access Workbook Object
‘and then using workbook object we are accessing WorkSheet Object
‘and adding Value “GeeksForGeeks” in cell A1
End Sub
We will write this code in the VBS script editor and execute it. This will print the output string in the cells(A1) defined in the code.
Once we run it by pressing the Run button, the result will look like this.