Excel VBA object model demystified

Why you need an object model?

I want you do me a favor, please identify the fruit #2 and the fish #1 in the picture below…

Before disambiguation

You now are thinking, well, I can’t tell you until you specify to me: from what tree you want the fruit (tree 1 or 2) and from what river you want the fish (river 1 or 2). You are very very right. 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.

You need something else. Just think for a moment in a phonebook with just first names…

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

You could not be sure what Bob is your Bob; you need a “disambiguation-tag”, in this particular case a last name. We use last names to differentiate every “Bob” of South Carolina from every “Bob” of New Mexico, and so on.

OK, again…

I want you do me a favor, please identify the fruit #2 of tree #1 and the fish #1 of river #2 in the picture below…

After disambiguation

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

For now we can conclude that to identify 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.

The organization of all the earth collections and their items is an “earth object model”, the organization of all the countries and their items is a “countries object model”, and so on. You can also call it “hierarchy of objects”. You need an object model to manage items from collections efficiently.

What’s the Excel object model?

I want you do me another favor, please identify the cell A1 in the picture below…

Different workbooks and sheets contain an A1 cell

You now are asking, well, I can’t tell you until you specify to me: from what Workbook (Book4 or Book5) and Sheet (Sheet1, Sheet2 or Sheet3) you want the A1 cell. 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 are referring to. You need a “disambiguation-tag”, in this particular case the workbook and sheet name.

OK, again…

I want you do me a favor, please identify the cell A1 of the sheet 2 of the book 5 in the picture below…

Specifying A1 cell uniquely

Very easy, right? Now you could do it because I specified the exact collection from which to pick the cell A1.

Excel macros manipulate Excel VBA objects, they come in collections; here are some examples…

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

So guess what?

When you manipulate objects in Excel through VBA macros, you need to point to the right ones. You can only do this by using an Excel object model or hierarchy.

How you refer to Excel VBA objects (members) in a hierarchy

Here are some 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”)

How to point to a Chart Object in Excel?

Workbooks(item).Worksheets (item).ChartObjects(item)

  • Workbooks(1).Worksheets(1).ChartObjects(1)
  • Workbooks(3).Worksheets(1).ChartObjects(“Chart1”)
  • Workbooks(1).Worksheets(“Sheet1”).ChartObjects(“Chart1”)

Resources:

Download a map of the Excel 2003 object model here:

{filelink=4}

{filelink=5}

Explore below some of the object models of the Excel and other main Office applications…

That’s all for now.

Did you find the explanation useful? Please leave your thoughts at the comments section below…