You are currently offline, waiting for your internet to reconnect

How To Navigate Excel Objects from Visual Basic

This article was previously published under Q147650
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.
This article explains three methods you can use to navigate and accessMicrosoft 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
Each object in Microsoft Excel version 5.0 exists somewhere in theapplication's hierarchy of objects. You choose among these objects bynavigating down that application's hierarchy. At the top of this hierarchyis the Application object. Whatever events or actions you assign to theApplication object affect the entire application. For example:
   ' Close the application   [Object].Application.Quit				
Replace [Object] with any variable that points to any valid Microsoft ExcelApplication 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 canuse the following code to refer to the collection of Workbooks currentlyloaded in Microsoft Excel:
If you want to retrieve a single workbook from the collection, use the Itemmethod. For example, to refer to the first workbook:
To close the first workbook:

Accessing Objects Using Longhand Reference or Default Properties

Each workbook contains a collection of worksheets, each worksheet containsa collection of cells, and so on. (See the Microsoft Excel documentationand Help menu for specific details about Microsoft Excel's objecthierarchy.) 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 areavailable. Understanding the navigation operator (.) is fundamental tosuccessful object programming.

Short Cuts

All objects have a default property and method. For collections, thedefault method is the Item method. For most objects, the Name property isthe default property. This convention was implemented to simplifyprogramming. For example, the previous sample can be simplified to:

Accessing Objects by Aliasing Objects

You can use aliasing to simplify object programming. If you were to write alot of code that was manipulating Sheet1, for example, the syntax couldbecome lengthy. To prevent this, create an object that points to the lowestcommon object. This is known as aliasing. Use the Set statement to createan 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 objecthierarchy. The Application method navigates back to the application object,and the Parent method navigates up one level of the object hierarchy. Allthe examples in this article started with [Object]. As long as [Object] isa 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 fromVisual Basic version 4.0. Microsoft Excel exposes only the three objectsthat 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 alwayspoint to the latest version of Microsoft Excel. Excel.Application.5 willpoint only to Microsoft Excel version 5.0.

There is no exposed Workbook object, so there's no way to access theWorkbook object directly. However, this is not a problem because the Parentmethod of a Worksheet or Chart object returns the Workbook object. Thefollowing 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				
Office Development Kit, Programming Integrated Solutions
officeinterop w_VBApp W_Excel WM_OLE OA OLE Automation

Article ID: 147650 - Last Review: 10/11/2006 18:40:47 - Revision: 2.3

  • Microsoft Visual Basic 4.0 Professional Edition
  • Microsoft Visual Basic 4.0 16-bit Enterprise Edition
  • Microsoft Excel 5.0 Standard Edition
  • kb16bitonly kbhowto kbprogramming KB147650