O'Reilly logo

Programming Excel with VBA and .NET by Steve Saunders, Jeff Webb

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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:

ApplicationWorkbookWorksheetRangeFont (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.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required