© Ugur Akinci
MS Excel VBA objects follow a hierarchy. Each level has a PARENT and CHILD level except for the top-level object (“Application”) and the lowest level child objects.
Here is a graphic representation of this logical relationship between different OOP OBJECTS:
Here is a set of corresponding MS Excel VBA objects:
APPLICATION is the PARENT of WORKBOOK because an application has many workbooks but a workbook has only one application: MS Excel. For the same reason, a WORKBOOK is the CHILD of the APPLICATION. (Application > Workbook)
In a similar manner, a WORKBOOK is the PARENT of WORKSHEET because a workbook has many worksheets but a worksheet has only one workbook: the current onel. For the same reason, a WORKSHEET is the CHILD of its WORKBOOK. (Workbook > Worksheet)
“So what’s the big deal in understanding all this?”
That’s an excellent question. Glad you asked
The hierarchical manner in which MS Excel VBA objects are related to one another also determines the exact order in which they are written in a VBA macro or formula! If you mess up the order, your formula or macro will not compute.
For example, if you would like to define the value of a cell, let’s say cell Z99 to be specific, then you need to start from the very top level object, APPLICATION, and go down all the way to that individual CELL:
HINT: If you understand RUSSIAN Matryoshka DOLLS, you’ll have no difficulty in understanding the way VBA Obejcts are NESTLED inside each other:
“How does that translate into writing the actual VBA code?”
When writing the VBA code, you START with the upper-level PARENT OBJECT and go all the way down to the lowest-level CHILD OBJECT, each separated by a PERIOD.
This is a process of CONCATENATION. You add them all up together, like bringing together BEADS on a STRING:
For example, you start with APPLICATION:
What comes next? The Workbook, but the “active one.” So here is the next object to follow the Application:
See how the dolls, er, the OBJECTS, are fitting into one another?
You continue this concatenation process all the way to the end, going down the object hierarchy (we assume we target cell Z99 of worksheet #10):
Application.ActiveWorkbook.Worksheets("Sheet10").Range("Z99").Value = "HELLO WORLD!"
This VBA code places the text “HELLO WORLD!” into cell Z99 of worksheet #10.
“So do I need to start from ‘Application’ object every time I write VBA code?”
No, not really. MS Excel is so smart, if you do not mention a PARENT object, it assumes the CURRENT DEFAULT as the valid value and runs the code accordingly.
For example, you can safely ignore the “Application” object because MS Excel assumes that your default top-parent object is the MS Excel application itself; and that would always be correct.
If you do not use the “ActiveWorkbook” object in your code, MS Excel assumes that you are in your currently active workbook.
Worksheets are the same: if you do not mention anything, MS Excel assumes that you are referring to the currently active worksheet.
For example, the above code can be reduced down to
Range("Z99").Value = "HELLO WORLD!"
if you are referring to your current workbook and current worksheet.
We hope now you have a better understanding of the OBJECT HIERARCHY in MS Excel VBA and why it matters to understand that hierarchy well before writing a successful VBA code in VBE (Visual Basic Editor) that executes flawlessly.