Excel VBA 1.5

Getting Started with the Excel Object Model

1: Open the Visual Basic Editor

See How

2: Click View –> Immediate Window to open the Immediate Window (or Ctrl + G )
3: You should see the Immediate Window at the bottom on VBE

This window allow you to directly test some VBA code. So let’s start, type the following into the console:

?Worksheets.

VBE has intellisense and then it should open a tooltip

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

?Worksheets.Count

Press Enter.

The expression is evaluated and it should return 1. This indicates the number of Worksheets 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 Objects in the Excel VBA reference Here

Now let’s try another expression, type (without the ? character):

Worksheets.Add().Name = “StackOveflow”

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() creates a new worksheet and returns 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 adds a new worksheet and returns 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

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.







Be the first to comment

Leave a Reply