VBA objects

VBA uses objects to control Excel behavior so if you want to master this programming language and make the most of it you need to get acquainted with objects and collections, as well as with their properties and methods. In this text, I will introduce you to the most frequently used Excel objects: Application, Workbooks, Worksheets, Cells … In some of the following articles, we will deal more closely with each of them.

The highest in hierarchy is the Application object. It has multiple properties and methods, but most often we will use its subordinate objects. They are hierarchically arranged and, by naming in the given order, we approach the subordinate objects, their properties and methods. We achieve this by dividing the subordinate entities by points. For example, if we type the word Application the automatic syntax completion option will offer us subordinate objects, properties, or methods that can be entered.

If we choose ActiveSheet, we indicate that we want to manipulate the active worksheet. If we continue to use the Cells object and Value property we could enter the desired value in cell A1.

Application.ActiveSheet.Cells(1, 1).Value = 10

We could have done this without Value property:

Application.ActiveSheet.Cells(1, 1)= 10

Often it is possible to perform certain task in several ways, and which one to use is a matter of personal choice!

Workbooks and Worksheets are objects used to access Excel documents or worksheets. For example, if we want to select the range of cells from A1 to B4 in the first worksheet, we would write the following expression:

Application.Worksheets(1).Range(“A1:B4”).Select

… and we could have write only:

Worksheets(1).Range(“A1:B4”).Select

or

ActiveSheet.Range(“A1:B4”).Select

We can also access objects as subordinate instances of the VBAProject object. In that case we would write:

VBAProject.Sheet1.Cells(1, 2) = 10

By this procedure, we entered value of 10 in cell A2.

When you are thoroughly familiar with Excel objects you will be able to create additional functionalities and automate daily work.