excel-vba Getting started with excel-vba Getting Started with the Excel Object Model

Example

This example intend to be a gentle introduction to the Excel Object Model for beginners.

---

1. Open the Visual Basic Editor (VBE)
2. Click View --> Immediate Window to open the Immediate Window (or ctrl + G):

3. You should see the following Immediate Window at the bottom on VBE:

This window allow you to directly test some VBA code. So let's start, type in this console :

?Worksheets. 

VBE has intellisense and then it should open a tooltip as in the following figure :

Select .Count in the list or directly type .Cout to obtain :

?Worksheets.Count

4. Then press Enter. The expression is evaluated and it should returns 1. This indicates the number of Worksheet currently present in the workbook. The question mark (?) is an alias for Debug.Print.

Worksheets is an Object and Count is a Method. Excel has several Object (Workbook, Worksheet, Range, Chart ..) and each of one contains specific methods and properties. You can find the complete list of Object in the Excel VBA reference. Worksheets Object is presented here .

This Excel VBA reference should become your primary source of information regarding the Excel Object Model.

5. Now let's try another expression, type (without the ? character):

Worksheets.Add().Name = "StackOveflow"

6. Press Enter. This should create a new worksheet called StackOverflow.:

To understand this expression you need to read the Add function in the aforementioned Excel reference. You will find the following:

Add:  Creates a new worksheet, chart, or macro sheet. 
The new worksheet becomes the active sheet. 
Return Value: An Object value that represents the new worksheet, chart,
 or macro sheet.

So the Worksheets.Add() create a new worksheet and return it. Worksheet(without s) is itself a Object that can be found in the documentation and Name is one of its property (see here). It is defined as :

Worksheet.Name Property:  Returns or sets a String value that 
 represents the object name.

So, by investigating the different objects definitions we are able to understand this code Worksheets.Add().Name = "StackOveflow".

Add() creates and add a new worksheet and return a reference to it, then we set its Name property to "StackOverflow"

---

Now let's be more formal, Excel contains several Objects. These Objects may be composed of one or several collection(s) of Excel objects of the same class. It is the case for WorkSheets which is a collection of Worksheet object. Each Object has some properties and methods that the programmer can interact with.

The Excel Object model refers to the Excel object hierarchy

At the top of all objects is the Application object, it represents the Excel instance itself. Programming in VBA requires a good understanding of this hierarchy because we always need a reference to an object to be able to call a Method or to Set/Get a property.

The (very simplified) Excel Object Model can be represented as,

                            Application
                             Workbooks
                             Workbook
                            Worksheets
                             Worksheet
                              Range

A more detail version for the Worksheet Object (as it is in Excel 2007) is shown below,

The full Excel Object Model can be found here.

Finally some objects may have events (ex: Workbook.WindowActivate) that are also part of the Excel Object Model.