Workbook and Worksheet Object

Here you'll find Excel divided in its objects: Workbook and Worksheet and Range. How to programm the object.

Excel VBA programming involves working with an object hierarchy. It means an object can contain another object, and that object can contain another object, etc. Objects are the fundamental building blocks of Visual Basic. An object is a special type of variable that contains both data and codes. The objects are connected with a dot.

The mother of all objects is Excel itself, the Application object. The Application object contains other objects, for example, in the collection of Workbooks the Workbook(without -s) object (Excel file). This can be any workbook you have created.

The Workbook object contains other objects, such as the Worksheet object. The Worksheet object contains other objects, such as the Range object.

A Collection is a group of objects of the same class. The most used Excel objects in VBA programming are Workbook, Worksheet, Sheet, and Range.

Workbook

A workbook is the same as an Excel file. The Workbook collection contains all the worksheets that are currently opened. Inside of a workbook contains at least one worksheet.

Range is a property of the Worksheets object. To be specific we use the Worksheets object first, then the Range property. Like this:

Worksheets(1).Range("A1").Select

When you use Range by itself - without Worksheets - Excel takes the currently active worksheet, the one you currently have selected and displayed. If you want to refer to a different worksheet, say Sheet 2, you need to refer to it by name:
Worksheets(2).Range("A1").Select
Or

Worksheets("Sheet2").Range("A1").Select

Range

A Range is an individual cell or a group of cells.

Range("A1") 'an individual cell

Range("A1:B7") ' refers to a group of cells.

Range("A1", "B7") 'another way to refer to a group of cells

Range("A1").Select selects the range A1

Range("A1:B7").Select ' Selects the group of cells. Select is a method of the Range object.

Programming the object

As example we take the various properties of the ActiveCell. Let's say we want to change the different possiblities of its font. The basic syntax is this:


With Object
   .property
   .property2
End With

So the object is ActiveCell. The property we want to change comes behind the object with a dot. After following dots we type the desired property values, as Bold = True.


With ActiveCell.Font
.Bold = True
.Color = vbBlue
.Name = "Arial"
.Size = 14
.Italic = True
End With

With Statements are usefull when you have to type the same object more than once.