Programming Excel with VBA and .NET

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. Visual Basic .NET provides 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 Visual Basic .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

Visual Basic .NET could get rid of Set because it also got rid of default members. In Excel 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 language—default 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, ...