This window allows you to directly test some VBA code. So let’s start, type the following into the console:
Select .Count in the list or directly type .Cout to obtain :
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”
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:
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.