Programming Excel with VBA and .NET

Get Excel Objects

In Excel, you always get one object from another, and everything starts with the Application object. So, if you want to change the font of cell C2 to bold, you would simply type:

    Application.ActiveWorkbook.ActiveSheet.Range("C2").Font.Bold = True

Not really! Application, ActiveWorkbook, and ActiveSheet are all global members in Excel, so you can shorten your code to:

    ActiveWorkbook.ActiveSheet.Range("C2").Font.Bold = True

or:

    ActiveSheet.Range("C2").Font.Bold = True

or more likely:

    Range("C2").Font.Bold = True

Each of the members in the original line of code returns an object reference that navigates from the top-level object (the Excel Application object) to the low-level object (a Font object) for which you want to set the Bold property. The order of objects looks like this:

Application → Workbook → Worksheet → Range → Font (set Bold property)

In other words, Excel’s objects are arranged hierarchically, but global members provide shortcuts through that hierarchy. Table 4-1 lists some commonly used shortcuts for navigating to Excel objects.

Table 4-1. Excel’s global shortcut members

Member	Returns	Use to
ActiveCell	Range object containing a single cell	Work with the currently selected cell or get the upper-lefthand corner of a selected block of cells.
ActiveChart	Chart object	Get the chart that currently has focus.
ActiveSheet	Worksheet, Chart, or other sheet object.	Get the sheet that has focus. The returned object may be a Worksheet, a Chart, or one of the obsolete sheet types.
ActiveWorkbook	Workbook object	Get the workbook that has focus.
Cells	Range object	Work with cells on the active worksheet.
Range	Range object	Work with a specific set of cells on the active worksheet.
Selection	Varies	Get the selected object. That may be a range of cells, a chart, or some other object.
Sheets	Collection of Worksheet, Chart, or other sheet objects	Get a sheet by its numeric index or name.
ThisWorkbook	Workbook object	Get the workbook that contains the current Visual Basic project. This contrasts with ActiveWorkbook, which may be different from ThisWorkbook if the user has switched focus.
UsedRange	Range object	Get the block of cells on the active worksheet that contains data.
Workbooks	Collection of Workbook objects	Get a workbook by its numeric index or name.
Worksheets	Collection of Worksheet objects	Get a worksheet by its numeric index or name.

In general, the members that return only one type of object are easier to work with than members that can return various types. This is probably best demonstrated by contrasting the Sheets and Worksheets methods. Sheets can return several different types of objects: Worksheet, Chart, DialogSheet (which is obsolete), and so on. Worksheets returns only Worksheet objects. That means Visual Basic knows the object type when you are working with Worksheets, but not when working with Sheets. You can tell that because Auto Complete doesn’t work with Sheets. It also means you have to be careful what methods you call objects returned by Sheets, since trying to use a Worksheet method, like Range, will fail if the object is a Chart.

Therefore, if you want to do something to all worksheets in a workbook, you use the Worksheets method:

    Sub UseWorksheets( )
        Dim ws As Worksheet
        For Each ws In Worksheets 
 

            ' Do some task
        Next
    End Sub

If you want to do something to all of the sheets in workbook, use the Sheets method as shown here:

    Sub UseSheets( )
        Dim obj As Object, ws As Worksheet, chrt As Chart
        For Each obj In Sheets
            Select Case Typename(obj)
                Case "Worksheet"
                    ' OK to use Worksheet methods.
                    Set ws = obj
                Case "Chart"
                    ' OK to use Chart methods.
                    Set chrt = obj
                Case Else
                    ' An obsolete sheet type.
            End Select
        Next
    End Sub

In the preceding code, I set the generic object returned by Sheets to a specific Worksheet or Chart type so that I could make sure I wasn’t using any members that weren’t allowed for the object. If I were doing a task that is common to all objects, such as setting the Name property, I could avoid that step and just use the returned obj variable.

This points up a problem for Excel programmers: there is no ActiveWorksheet property. The ActiveSheet property returns a generic object type—that might be a Worksheet, a Chart, or something else. Sometimes you definitely know that the sheet that has focus is a Worksheet—for example, when you create a new worksheet in code. In this case, you can safely use the Worksheet members. Otherwise, you need to test if the object is a worksheet before proceeding as shown here:

     If TypeName(ActiveSheet) = "Worksheet" Then
        ' OK to use Worksheet members on ActiveSheet
    End If

This isn’t really an oversight by the Excel team. If they did provide an ActiveWorksheet property, it would return Nothing if a chart sheet had focus. You’d still have to write similar code to test for that condition!

Tip

Checking and working with specific types of objects, rather than using the generic Object type, is sometimes called type-safe programming , and it’s a good technique to help prevent errors in your code.