The reason why many Excel VBA beginners fail is that they try to learn Excel VBA without understanding the Excel object model first or even ignoring there’s such as fundamental VBA concept.

If you want to get started with Excel VBA and macros but are still wondering, “what the hell the Excel object model is”, then this simple Excel VBA lesson is for you because today you will finally discover:

1. Why you need an Excel object model?
2. What’s the Excel object model?
3. How to control Excel VBA objects (members) in a hierarchy (Excel object model)
4. Excel object model resources

Why you need an Excel object model?

You will understand the Excel object model using a simple analogy. In the picture below you see two rivers, 1 and 2, with fishes and the trees 1 and 2 with hanging fruits.

Please identify the fish #1 and fruit #2 in the picture above.

You quickly realize that you can’t find those objects until you know from what tree you want the fruit (tree 1 or 2) and from what river you want the fish (river 1 or 2). This simple problem arises when you deal with a collection of many trees containing a collection of many fruits, and a collection of many rivers containing a collection of many fishes.

To refer to the objects of collections (Excel has the collections of books, sheets, charts, etc.), we need something else. Just think for a moment in a phonebook with just first names…

• Name, Phone, address
• Joe, 111111111, Moriarty, NM
• Joe, 22222222, Shiloh Ranch Rd Lancaster, SC
• Joe, 23412233, Oakland Dr Lancaster, SC

You could not be sure if any particular Joe is your Joe. You need a sort of disambiguation attribute. In this particular case a last name attribute. We use last names to differentiate every “Joe” from each other.

OK, let’s do it better this time. Please identify the fruit #2 of tree #1 and the fish #1 of river #2 in the picture below…

Now you could do it easily because I specified the collection from which to pick each item.

For now we can conclude that for identifying the element of a collection unequivocally, you need to specify first what collection the item belongs to and then its relative position in that collection.

Referring to any object of the world is easy if we organize all the earth collections and their items in an “earth object model”, all the countries and their items in a “countries object model”, and so on. You can also call it “hierarchy of objects”.

You need an object model to precisely manage the items of collections.

What’s the Excel object model?

Please identify the cell A1 in the Excel spreadsheets below…

You quickly realize you can’t find the Excel cell object until you know from what Workbook (Book4 or Book5) and Sheet (Sheet1, Sheet2 or Sheet3). The same ambiguity problem arises in Excel because you have a collection of many workbooks containing a collection of many sheets, and each sheet containing one A1 cell.

You should be specific about what A1 cell you refer to. You need a sort of disambiguation attribute. In this particular case the workbook and sheet name.

OK, now please identify the cell A1 of the sheet 2 of the book 5 in the Excel spreadsheets below…

Very easy, right? Now you can find any Excel object because you know the exact object from which to pick the cell A1.

Excel macros manipulate Excel VBA objects, which are organized in collections. Here are some of the most important Excel collections…

• Workbooks collection
• Sheets collection
• Charts collection
• Pivot Tables collection
• Tables collection
• Range collection

So guess what?

When you manipulate objects in Excel through VBA code in macros, you need to point to the right objects. You can only control objects by understanding and using an Excel object model or hierarchy.

See a snapshot of the Excel object model below

How to control Excel VBA objects (members) in a hierarchy (Excel object model)

Here are some VBA code examples:

How to point to a Range Object in Excel?

Workbooks(item).Worksheets(item).Range(item)

• Workbooks(1).Worksheets(1).Range(“A1”)
• Workbooks(3).Worksheets(1).Range(“A1”)
• Workbooks(2).Worksheets(“Sheet1”).Range(“A1:B32”)

Excel object model resources:

Download an Excel 2003 object model map (pdf) here

Explore more about the Excel object model below:

• Excel object model reference (msdn): https://msdn.microsoft.com/en-us/library/wss56bz7.aspx
• Excel object model overview: https://msdn.microsoft.com/en-us/vba/excel-vba/articles/object-model-excel-vba-reference
• Excel 2007 object model map: http://msdn.microsoft.com/en-us/library/bb332345(v=office.12).aspx
• Word 2007 object model map: http://msdn.microsoft.com/en-us/library/bb288731(v=office.12).aspx
• PowerPoint 2007 object model map: http://msdn.microsoft.com/en-us/library/bb251394(v=office.12).aspx
• Outlook 2007 object model map: http://msdn.microsoft.com/en-us/library/bb277365(v=office.12).aspx