Shortcuts to Active Objects

This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.

As with other Microsoft® Office XP application object models, the Microsoft® Excel Application object exposes several properties you can use to work with a currently active Excel object. For example, you often will write Microsoft® Visual Basic® for Applications (VBA) procedures designed to work with information in the currently selected cell, or with the currently active worksheet. The Application object exposes the ActiveCell, ActiveChart, ActivePrinter, ActiveSheet, ActiveWindow, and ActiveWorkbook properties, which you can use to return a reference to the currently active cell, chart, printer, sheet, window, or workbook. The following examples illustrate various ways you might use some of these properties:

' ActiveWorkbook property example:
Function SaveBookAs(strFileName As String) As Boolean
   ActiveWorkbook.SaveAs ActiveWorkbook.Path & "\" & strFileName
End Function

' ActiveCell property example:
Function CustomFormatCell()
   With ActiveCell
      If IsNumeric(.Text) And .Formula < 0 Then
         With .Font
            .Bold = True
            .Italic = True
         End With
         .Borders.Color = 255
      End If
   End With
End Function

' ActiveSheet property example:
Function ChangeName(strNewName As String) As Boolean
   ActiveSheet.Name = strNewName
End Function

In addition to the ActiveWorkbook property, you can use the Application object's Workbooks and Worksheets properties to return equivalent Excel objects. The Workbooks property returns the Workbooks collection that contains all the currently open Workbook objects. The Worksheets property returns the Sheets collection associated with the currently active workbook. The following example uses the Workbooks property to determine if a workbook is already open, and if not, to open it:

Function OpenBook(strFilePath As String) As Boolean
   ' This procedure checks to see if the workbook
   ' specified in the strFilePath argument is open.
   ' If it is open, the workbook is activated. If it is
   ' not open, the procedure opens it.
   Dim wkbCurrent       As Excel.Workbook
   Dim strBookName      As String
   
   On Error GoTo OpenBook_Err

   ' Determine the name portion of the strFilePath argument.
   strBookName = NameFromPath(strFilePath)
   If Len(strBookName) = 0 Then Exit Function
   If Workbooks.Count > 0 Then
      For Each wkbCurrent In Workbooks
         If UCase$(wkbCurrent.Name) = UCase$(strBookName) Then
            wkbCurrent.Activate
            Exit Function
         End If
      Next wkbCurrent
   End If
   Workbooks.Open strBookName
   OpenBook = True
   
OpenBook_End:
   Exit Function
OpenBook_Err:
   OpenBook = False
   Resume OpenBook_End
End Function

Note   In the preceding example, the OpenBook procedure calls a custom procedure named NameFromPath that returns the file name portion of the full path and file name passed to the OpenBook procedure in the strFilePath argument.

See Also

Working with Microsoft Excel Objects | Understanding the Excel Application Object