The Excel object model - VBA For Excel

Many real-world objects can contain objects that are themselves objects. For example,the hardware of a computer system contains parts such as a monitor,speakers keyboard, mouse and so on.These are themselves objects sometimes collections and individual that have properties and methods associated with them. The same analogy can be made for example, when VBA for Excel interfaces with objects such as workbooks,cell ranges, cells, charts, and so on. An object model is a description of the object hierarchy.Excel/VBA contains a clearly defined set of objects that are arranged according to relationships between them.This shows a portion of the Excel object model. This is not complete but should give you an idea of the relationships between Excel objects. As we can see from Figure, the Application object is at the top level. Contained within the Application object are the Workbooks collection and all the Workbook instances within this; at the next lower level, we have the Worksheets collection along with the instances of each Worksheet in the collection. Next, we have the Range object, which does not have a plural collection.

The hierarchy of some Excel objects

hierarchy of some Excel objects

Referencing objects using With...End With

Sometimes, it may be necessary to refer to the same object properties or methods several times consecutively within a VBA macro. You can use a With...End With program structure that simplifies object reference.

The basic syntax of With End With is:

With Object
'Statements that use properties & methods of that object'
End With

To see how this works, suppose that we wanted to set the three properties with the interior of the object Range( D34 ) as follows:

Range ("D34").ColorIndex = 3 'Sets the colour to red Range ("D34").Pattern = xlSolid 'Makes the interior colour red solid Range ("D34").PatternColorIndex = xlAutomatic 'This resets to automatic the colour following the selection

Instead of the longhand individual reference to each property or method of the range,we could rewrite the above using With End With as follows:

Range("D34").Select With Selection.Interior
.ColorIndex = 3 'this colour is red
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With

Note how the statements are indented between the With Selection.Interior and End With block.

Self-assessment exercise

Use the With End With structure to rewrite the following lines:

Cell.ColourIndex =5 Cell.Pattern = xlSolid Cell.PatternColorIndex = xlAutomatic

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd Protection Status

VBA For Excel Topics