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

About Me and the Active Object

The Visual Basic Me keyword provides a way to refer to an instance of the object created by the current class. I know that’s a little confusing; here’s how it works: if you write code in the ThisWorkbook class, Me is the same as ThisWorkbook, as shown by Figure 4-11.

If you write that same code for one of the Worksheet classes, you get a different result as shown by the following code:

    ' In Objects sheet class.
    Sub AboutMe( )                           ' Displays:
        Debug.Print Me.Name                 '   Objects
        Debug.Print ThisWorkbook.Name       '   ch04.xls
        Debug.Print Me Is Sheets("Objects") '   True
    End Sub
ThisWorkbook and Me are the same here

Figure 4-11. ThisWorkbook and Me are the same here

That’s because Excel creates an object out of the class at runtime, and Me refers to that object. You can use Me to refer to members of the class using the dot notation:

    Sub DemoMe( )
        Me.AboutMe    ' Calls preceding AboutMe procedure.
    End Sub

You can’t use Me in a Module. It’s valid only in classes since it refers to the instance of the object created from the class and modules don’t have instances—modules are static code.

Excel provides a number of properties that return objects that currently have focus in the Excel interface. Some of these properties were included in the list of shortcuts shown in Table 4-1, but they bear repeating in Table 4-3.

Table 4-3. Active object properties

Property

Returns

ActiveCell

Range containing the cell that currently has focus for input

ActiveChart

Chart that has focus

ActiveMenuBar

MenuBar currently displayed in Excel

ActivePane

Pane within the active window

ActivePrinter

Name of the default printer in Excel (not an object)

ActiveSheet

Worksheet, Chart, or other sheet type that has focus

ActiveWindow

Window that has focus

ActiveWorkbook

Workbook that has focus

Selection

Selected item (may be a Range, Chart, drawing object, or other object)

These properties are very useful in code because they tell you what the user is looking at. Often you’ll want your code to affect that item—for example, you might want to display a result in the active cell. Normally, the user can change the active object in Excel by clicking on a worksheet tab, switching to a new window, and the like, but she can’t do that while a Visual Basic procedure is running, as shown in Figure 4-12.

The user can’t change the active object while a procedure runs

Figure 4-12. The user can’t change the active object while a procedure runs

Code can change the active object, however. Many objects provide Activate methods that switch focus within Excel, and some objects, such as Window, provide ActivateNext and ActivatePrevious methods as well. If you rely on active objects, you need to be careful about changing the active object in code.

Many Excel programmers rely on activation a little too much in my opinion, as shown here:

    Sub DemoActivation1( )
        Dim cel As range
        ' Make sure a range is selected.
        If TypeName(Selection) <> "Range" Then Exit Sub
        For Each cel In Selection
            ' Activate the cell.
            cel.Activate
            ' Insert a random value
            ActiveCell.Value = Rnd
        Next
    End Sub

In reality, there’s no good reason to do this since you’ve got a perfectly good object reference (cel) that you can use instead:

    Sub DemoActivation2( )
        ' Make sure a range is selected.
        If TypeName(Selection) <> "Range" Then Exit Sub
        Dim cel As range
        For Each cel In Selection
            ' Insert a random value
            cel.Value = Rnd
        Next
    End Sub

DemoActivation2 runs faster because it avoids an unneeded Activate step in the For Each loop. There’s nothing wrong with using the active object when you need it; I just see it overused a lot.

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