There are many categories (classes) of Excel objects that can be controlled in VBA. In fact, nearly all objects can be controlled in VBA that users manipulate in the Excel interface. VBA can also control more than the Excel interface provides which is one of the key reasons why 'power users' use VBA!

The Object hierarchy provides the levels of various key objects ranging from the cell ranges (the lowest level) through to the application itself (the highest level).

This section focuses on the Application, WorkBook(s), Worksheet(s) and ActiveSheet/Workbook objects (see Range & Selection objects for more extended information).

Application object

The word Application refers to the host (in this case Excel) and is deemed the top level object.

(Note: VBA can communicate beyond Excel and technically this is not the top level as you have the ability to code to Microsoft Office (Word, PowerPoint etc) and to other applications including the operating system).

Use this object as the entry point (the gateway) to the Excel object model and is implicit which means that you can omit this keyword in your code as it's the default. The following two VBA commands do the same thing:

Application.ActiveSheet.Name = "January"

ActiveSheet.Name = "January"

The first example included the Application object keyword (as explicit) and the second one excluded (as implicit) it but produced the same result.

You only need to use this keyword if you are coding with other applications (that is not Excel) or wish to communicate to Excel from another application's environment (i.e. Microsoft Word). You will need to learn about object variables and set application objects to Excel.

The following code snippet creates an Excel object from outside of Excel (which uses VBA too) and opens a workbook called "Sales.xlsx":

Sub OpenExcelWorkbook()

Dim xl As Object

Set xl = CreateObject("Excel.Sheet")

xl.Application.WorkBooks.Open("Sales.xlsx")

'executed code continues...

End Sub

ActiveWorkbook and Workbooks objects

This object appears below the Application object along with other key objects including Chart and Pivot Table and control the tasks for any workbook from creating, opening, printing to saving and closing documents.

The singular keyword Workbook refers to the current or a single file you wish to control compared with the plural keyword Workbooks which is the collection of one or more documents you wish to control

Use the Workbook object referred in code as ActiveWorkbook to open, save, print, close and manipulate the documents attributes as required.

Sub WorkBookNameExample()

MsgBox "Current workbook is " & ActiveWorkbook.Name

End Sub

Save a copy of the current workbook:

Sub SaveAsWorkBookExample1()

ActiveWorkbook.SaveAs "VBA Workbook.xlsx"

End Sub

The above can also be expressed as follows:

Sub SaveAsWorkBookExample2()

Workbooks(1).SaveAs "VBA Workbook.xlsx"

End Sub

Using the Workbooks keyword which is a collection of current workbooks, you can provide an index number (starting at 1 for the first document and incrementing by 1 for each open document) to execute code using the same identifiers as ActiveWorkbook object.

How many workbooks are currently open?

Sub WorkBookCount()

MsgBox "There are currently " & Workbooks.Count & _
" workbook(s) open"

End Sub

The Workbooks object doesn't have any parenthesis and an index number reference when dealing with a collection of many documents.

(Note: the above will also count all open and hidden documents).

ActiveSheet and Worksheets objects

Most of the time, you will work with this object along the range object as the normal practice is worksheet management in a workbook when working with the Excel interface.

Again, the singular Worksheet object referred as ActiveWorkSheet controls the current or single worksheet objects including its name. The plural keyword Worksheets refers to one or more worksheets in a workbook which allows you to manipulate a collection of worksheets in one go.

Name a worksheet:

Sub RenameWorksheetExample1()

ActiveWorkSheet.Name = "January"

End Sub

or use

Sub RenameWorksheetExample2()

WorkSheets(1).Name = "January"

End Sub

assuming the first worksheet is to be renamed.

Insert a new worksheet and place it at the end of the current worksheets:

Sub InsertWorksheet1()

Worksheets.Add After:=Worksheets(Worksheets.Count)

End Sub

or it can shortened using the Sheets keyword instead:

Sub InsertWorksheet2()

Sheets.Add After:=Sheets(Sheets.Count)

End Sub

(Note: Have you noticed when adding a new worksheet via Excel interface how it always inserts it to the left of the active sheet!).

'Active' objects

Within the Application object you have other properties which act as shortcuts (Globals) to the main objects directly below it. These include ActiveCell, ActiveChart, ActivePrinter, ActiveSheet, ActiveWindow and ActiveWorkbook.

You use the above keywords as a direct implicit reference to the singular active object in the same way (as in the above already illustrated).

Remember, you can only have one active object when working in the Excel interface and therefore the VBA code is emulating the way users are conditioned to work. Even when a range of cells is selected (Selection object) only on cell is active (the white cell).

Sub PrinterName()

MsgBox "Printer currently set is " & ActivePrinter

End Sub

Next Topic: Range & Selection objects

Want to teach yourself Access? Free online guide at About Access Databases

VBA Keywords: Application, ActiveSheet, ActiveWorkbook, ActivePrinter, ActiveCell, ActiveChart, ActiveWindow, CreateObject, Workbooks, Worksheets, Name, MsgBox, SaveAs, Count and Add.