|Back to Excel Homepage||Excel VBA - Reference Guide|
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).
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":
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.
Save a copy of the current workbook:
The above can also be expressed as follows:
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?
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:
assuming the first worksheet is to be renamed.
Insert a new worksheet and place it at the end of the current worksheets:
or it can shortened using the Sheets keyword instead:
(Note: Have you noticed when adding a new worksheet via Excel interface how it always inserts it to the left of the active sheet!).
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).
|VBA Keywords: Application, ActiveSheet, ActiveWorkbook, ActivePrinter, ActiveCell, ActiveChart, ActiveWindow, CreateObject, Workbooks, Worksheets, Name, MsgBox, SaveAs, Count and Add.|
© copyright 2010 TP Development & Consultancy Ltd, All Rights Reserved.