Section 5.8.Work with Excel Objects in .NET

5.8. Work with Excel Objects in .NET

Once you've got an instance of the Excel Application object, you can use it to get at any of the other objects in the Excel object library.

5.8.1. How to do it

VB.NET has an Imports declaration that you can use to create a shortcut for referring to objects from a particular library. For example, the following class-level declaration:

    Imports Microsoft.Office.Interop 

shortens the Excel application declaration to:

    Dim WithEvents m_xl As Excel.Application

which is easier to type and read. Notice that you don't use Set to get object references in VB.NET. For example, the following code gets a reference to Workbook and Range objects to display powers of 2 on a worksheet:

    ' .NET code.    Dim wb As Excel.Workbook, rng As Excel.Range    ' Create a new workbook.    wb = m_xl.Workbooks.Add(  )    ' Add some data    For i As Integer = 1 To 10        rng = wb.Worksheets(1).Cells(1, i)        rng.Value = 2 ^ i    Next

VB.NET could get rid of Set because it also got rid of default members. In VBA, you can assign a value to a Range object because the Value property is the default member of the Range object. This is a clearer approach to a languagedefault members were never a very good idea.

This change can take some getting used to, especially if you don't explicitly declare a type for a variable. For example, the following .NET code gets a reference to a Range object, but then replaces that reference with an integer:

    Dim obj    ' Gets a reference to the A1 range object.    obj = wb.Worksheets(1).Cells(1, 1)    ' Assigns a number to obj (does not set [A1].Value!)    obj = 42

Because of this, it is a good idea to declare variables with explicit data types when programming in VB.NET. Using explicit types also enables the Intellisense and autocomplete features when working with variablesso there are a lot of good reasons to be explicit!

: Help!

You can't get help on Excel objects from within Visual Studio. It's a good idea to create a shortcut to the Excel VBA help files and open those files manually when you need reference information on Excel objects.

5.8.2. What about...

To get help on Excel objects	Open
For Excel 2003	C:\Program Files\Microsoft Office\OFFICE11\1033\VBAXL10.CHM
For Office 2003 (includes some objects used in Excel)	C:\Program Files\Microsoft Office\OFFICE11\1033\VBAOF11.CHM
For Excel XP	C:\Program Files\Microsoft Office\OFFICE10\1033\VBAXL10.CHM
For Office XP	C:\Program Files\Microsoft Office\OFFICE10\1033\VBAOF10.CHM

---

Tip: Excel VBA Help kept the same filename between the XP and 2003 versions, but there are differences between the two help files.

---