MORE INFORMATION
Each object in Microsoft Excel version 5.0 exists somewhere in the
application's hierarchy of objects. You choose among these objects by
navigating down that application's hierarchy. At the top of this hierarchy
is the Application object. Whatever events or actions you assign to the
Application object affect the entire application. For example:
' Close the application
[Object].Application.Quit
Replace [Object] with any variable that points to any valid Excel
Application Object, which can be created from the following example:
' MyObject represents [Object] and OLE1 represents an OLE control
' that contains an Excel Worksheet object.
MyObject = OLE1.Object
Set MyObject = CreateObject("Excel.Sheet")
Set MyObject = GetObject("C:\EXCEL\EXAMPLES\SAMPLES.XLS")
The Application object contains other large objects. For example, you can
use the following code to refer to the collection of Workbooks currently
loaded in Excel:
[Object].Application.Workbooks
If you want to retrieve a single workbook from the collection, use the Item
method. For example, to refer to the first workbook:
[Object].Application.Workbooks.Item(1)
To close the first workbook:
[Object].Application.Workbooks.Item(1).[Close]
Accessing Objects Using Longhand Reference or Default Properties
Each workbook contains a collection of worksheets, each worksheet contains
a collection of cells, and so on. (See the Excel documentation and Help
menu for specific details about Excel's object hierarchy.) In code,
referring to a specific cell could look like this:
' Following refers to cell A1 on Sheet1 in the first workbook.
' Enter the following two lines as one, single line:
[Object].Application.Workbooks.Item(1).
WorkSheets.Item("Sheet1").Cells.Item(1,1)
This reference can be lengthy and complex; however shortcuts are available.
Understanding the navigation operator (.) is fundamental to successful
object programming.
Short Cuts:
All objects have a default property and method. For collections the default
method is the Item method. For most objects the Name property is the
default property. This convention was implemented to simplify programming.
For example the previous sample can be simplified to:
[Object].Application.Workbooks(1).WorkSheets("Sheet1").Cells(1,1)
Accessing Objects by Aliasing Objects
You can use aliasing to simplify object programming. If you were to write a
lot of code that was manipulating Sheet1, for example, the syntax could
become lengthy. To prevent this, create an object that points to the lowest
common object. This is known as aliasing. Use the Set statement to create
an alias.
Dim Sheet1 as Object
' Alias Sheet1 to represent [Object]...WorkSheets("Sheet1")
Set Sheet1 = [Object].Application.Workbooks(1).WorkSheets("Sheet1")
' Now just use the variable Sheet1 to refer to Sheet1.
Sheet1.Cells(1,1).Value = "Title"
Sheet1.Cells(1,2).Value = "ID"
Sheet1.Cells(1,3).Value = "Cost"
Sheet1.Cells(2,1).Value = "Phone"
Sheet1.Cells(2,2).Value = 123413423
Sheet1.Cells(2,3).Value = 89.95
Accessing Objects by Using Parent and Application Methods
The Parent and Application methods allow you to navigate back up the object
hierarchy. The Application method navigates back to the application object,
and the Parent method navigates up one level of the object hierarchy. All
the examples in this article started with [Object]. As long as [Object] is
a valid Excel object, all of those statements are also valid, regardless of
the context of [Object].
This is very helpful when programming the Excel object from Visual Basic
version 3.0. Excel exposes only the three objects that can be used as entry
points to Excel. These are:
- Excel.Application.
- Excel.Sheet.
- Excel.Chart.
Don't be confused by Excel.Application.5. Excel.Application will always
point to the latest version of Excel. Excel.Application.5 will point only
to Excel version 5.0.
There is no exposed Workbook object, so there's no way to access the
Workbook object directly. However, this is not a problem because the Parent
method of a Worksheet or Chart object returns the Workbook object. The
following example code illustrates this point.
NOTE: oleExcel is an OLE control that contains an Excel.Sheet object.
' Declare object references:
Dim Xlapp As object
Dim XLWkb As object
Dim XLWks As object
Dim XLWksNew As object
oleExcel.Action = 7 ' Activate OLE Object
Set XLWks = oleExcel.Object ' Alias Worksheet object
Set XLWkb = XLWks.Parent ' Alias WorkBook object
Set Xlapp = XLWks.Application ' Alias Application object
' Add a new worksheet to the Workbook and name it:
Set XLWksNew = XLWkb.Worksheets.add ' Assign alias to new Worksheet
XLWksNew.Name = "VB3 OLE Automation" & XLWkb.Worksheets.count
' Make the 3rd Worksheet of the Workbook active:
XLWkb.Worksheets(3).Activate
' Display the dialog for InsertPicture:
Xlapp.dialogs(342).[Show] ' xlDialogInsertPicture = 342