How To Navigate Excel Objects from Visual Basic

Retired KB Content Disclaimer

View products that this article applies to.

This article was previously published under Q147650

SUMMARY

This article explains three methods you can use to navigate and access Microsoft Excel Application objects:

Using longhand and default properties
Using aliasing
Using the Parent and Application methods of Microsoft Excel version 5.0 objects to access any Microsoft Excel object

Back to the top

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 Microsoft Excel Application Object, which can be created from the following example:

   ' MyObject represents [Object] and OLE1 represents an OLE control
   ' that contains an Microsoft 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 Microsoft 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

Back to the top

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 Microsoft Excel documentation and Help menu for specific details about Microsoft Excel's object hierarchy.) In code, references 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.

Back to the top

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)

Back to the top

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

Back to the top

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 Microsoft Excel object, all of those statements are also valid. Regardless of the context of [Object].

This is very helpful when programming the Microsoft Excel object from Visual Basic version 4.0. Microsoft Excel exposes only the three objects that can be used as entry points to Microsoft 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 Microsoft Excel. Excel.Application.5 will point only to Microsoft 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 = "VB4 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

Back to the top

REFERENCES

Office Development Kit, Programming Integrated Solutions

Back to the top

APPLIES TO

Microsoft Visual Basic 4.0 Professional Edition
Microsoft Visual Basic 4.0 16-bit Enterprise Edition
Microsoft Excel 5.0 Standard Edition

Back to the top

kb16bitonly kbhowto kbprogramming KB147650

Back to the top

Retired KB Content Disclaimer

This article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated.

Back to the top