EXCELlent Office Adventures

David Shank
Microsoft Corporation

January 4, 2001

It is the first week of the new year and no matter how we spent our holidays, most of us have at least two things in common this week: We are back in the Office (as I like to say in this column) and we are trying to figure out how to pay for all the gifts and celebrations of last month.

For some of us, when it comes to number crunching, it is visions of Microsoft Excel that dance in our heads. And since I have not talked about working with Excel in this column, as many of you have reminded me, I've decided that now is the perfect time.

This is the first part of a two-part discussion on working with Excel. In this month's column, I will discuss the basics of what you need to know to work with Excel programmatically. Next month, I will delve in a bit deeper to discuss working with ranges, regions, and cells.

The Excel Application Object

The Excel Application object is the top-level object in Excel's object model. You use the Application object to determine or specify application-level properties or execute application-level methods. The Application object is also the entry point into the rest of the Excel object model.

When you work with properties and methods of the Application object by using Visual Basic for Applications (VBA) from within Excel, the Application object is available to you by default. This is known as an implicit reference to the object. To work with Excel objects from another Office application, you must first create an object variable that represents the Excel Application object. This is known as an explicit reference to the object. For example, the following two procedures return the name of the currently active Worksheet object. The ShowNameFromInsideXL procedure is designed to work from within Excel and uses an implicit reference to the Application object. In other words, it references the ActiveSheet property of the Application object without explicitly referencing the Application object itself. You run the ShowNameFromOutsideXL procedure from outside Excel and so must use an explicit reference to the Application object.

Sub ShowNameFromInsideXL()
    MsgBox "'" & ActiveSheet.Name & "' is the active worksheet."
End Sub

Sub ShowNameFromOutsideXL()
    Dim xlApp As Excel.Application

    Const XL_NOTRUNNING As Long = 429

    On Error GoTo ShowName_Err
    Set xlApp = GetObject(, "Excel.Application")
    MsgBox "'" & xlApp.ActiveSheet.Name & "' is active."
    xlApp.Quit
    Set xlApp = Nothing

ShowName_End:
    Exit Sub
ShowName_Err:
    If Err = XL_NOTRUNNING Then
        ' Excel is not currently running.
        Set xlApp = New Excel.Application
        xlApp.Workbooks.Add
        Resume Next
    Else
        MsgBox Err.Number & " - " & Err.Description
    End If
    Resume ShowName_End
End Sub

Notice that the ShowNameFromOutsideXL procedure uses the GetObject function to get a reference to the currently running instance of Excel. If Excel is not running when this procedure is called, an error occurs. The error handler uses the New keyword to create a new instance of Excel, then adds a new Workbook object. Since a workbook will contain at least one worksheet, the remaining code in the procedure will execute correctly.

Note: To use the New keyword, you must have a reference set to the Excel object model from the project that contains the ShowNameFromOutsideXL procedure.

Understanding Excel's shortcuts to active objects

Like other Office application object models, the Excel Application object exposes several properties you can use to work with a currently active Excel object. For example, you will often write 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
            .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

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:

Function NameFromPath(strPath As String) As String
    ' This procedure takes a file path and returns
    ' the file name portion.
    Dim lngPos          As Long
    Dim strPart         As String
    Dim blnIncludesFile As Boolean

    ' Check that this is a file path.
    ' Find the last path separator.
    lngPos = InStrRev(strPath, "\")
    ' Determine if string after last backslash
    ' contains a period.
    blnIncludesFile = InStrRev(strPath, ".") > lngPos
    strPart = ""

    If lngPos > 0 Then
        If blnIncludesFile Then
            strPart = Right$(strPath, Len(strPath) - lngPos)
        End If
    End If
    NameFromPath = strPart
End Function

The Excel Workbook Object

In the Excel object model, the Workbook object appears just below the Application object. The Workbook object represents an Excel .xls or .xla workbook file. You use the Workbook object to work with a single Excel workbook. You use the Workbooks collection to work with all currently open Workbook objects.

You can also use the Application object's ActiveWorkbook property to return a reference to the currently active workbook. The Workbooks collection has a Count property you can use to determine how many visible and hidden workbooks are open. By default, Excel typically has one hidden workbook named Personal.xls. Excel uses the Personal.xls workbook as the default location to store macros. If the hidden Personal.xls workbook is the only open workbook, the ActiveWorkbook property returns Nothing, but the Workbooks collection's Count property returns 1. The Workbooks collection's Count property will return 0 only when there are no hidden or visible open workbooks.

Working with Workbook objects

You create a new Workbook object by using the Workbooks collection's Add method. The Add method not only creates a new workbook, but also immediately opens the workbook. The Add method also returns an object variable that represents the new workbook just created. The new workbook will contain the number of worksheets specified in the Sheets in new workbook box on the General tab of the Options dialog box (Tools menu). You can also specify the number of sheets a new workbook will have by using the Application object's SheetsInNewWorkbook property.

You can save a new workbook by using the Workbook object's SaveAs method and specifying the name of the workbook you want to save. If a workbook by that name already exists, an error occurs. Once a workbook has been saved by using the SaveAs method, additional changes are saved by using the Workbook object's Save method. You can also save a copy of an existing workbook with a different file name by using the SaveCopyAs method. You can supply a file name to be used with the SaveAs or SaveCopyAs method, or you can use the Application object's GetSaveAsFileName method to let the user supply the name to be used to save the workbook. If the user clicks Cancel in the Save As dialog box, the GetSaveAsFileName method returns False.

Before you save a new workbook by using the SaveAs method, the Workbook object's Name property setting is a value assigned by Excel, such as Book1.xls. After you save the workbook, the Name property contains the name you supplied in the **Filename **** argument of the SaveAs method. The Name property is read-only; to change the name of a workbook, you must use the SaveAs method again and pass a different value in the Filename argument.

Note: A Workbook object's FullName property contains the object's path and file name, whereas the Path property contains only the saved path to the current workbook. Before a new workbook is saved, the FullName property has the same value as the Name property, and the Path property has no value.

The Workbooks collection's Open method opens an existing workbook. When you open a workbook by using the Open method, it also becomes the active workbook. You can supply a file name to be used with the Open method, or you can use the Application object's GetOpenFileName method to let the user select the workbook to open. If the user clicks Cancel in the Open dialog box, the GetOpenFileName method returns False.

You use a Workbook object's Close method to close an open workbook. To specify whether pending changes to the workbook should be saved before the object is closed, you use the SaveChanges argument. If the SaveChanges argument is omitted, the user is prompted to save pending changes. You can also use the Close method of the Workbooks object to close all open workbooks. If there are unsaved changes to any open workbook when this method is used, the user is prompted to save changes. If the user clicks Cancel in this Save dialog box, an error occurs that your code must handle. You can suppress this Save dialog box by setting the Application object's DisplayAlerts property to False before executing the Close method. When you use the Workbooks object's Close method in this manner, any unsaved changes to open workbooks are lost. After the Close method has run, remember to set the DisplayAlerts property to True.

Note: The Auto_Open and Auto_Close event procedures are ignored when a workbook is opened or closed by using the Open or Close methods. You can force these procedures to run by using the Workbook object's RunAutoMacros method. The VBA code in a workbook's Open and BeforeClose event procedures will be executed when the workbook is opened or closed by using the Open or Close methods.

The following example illustrates how to create a new workbook and specify the number of worksheets it will have:

Function CreateNewWorkbook(Optional strBookName As String = "", _
         Optional intNumSheets As Integer = 3) As Workbook
    ' This procedure creates a new workbook file and 
    ' saves it by using the path and name specified 
    ' in the strBookName argument. You use the intNumsheets
    ' argument to specify the number of worksheets in the 
    ' workbook; the default is three.

    Dim intOrigNumSheets      As Integer
    Dim wkbNew                As Excel.Workbook

    On Error GoTo CreateNew_Err

    intOrigNumSheets = Application.SheetsInNewWorkbook
    If intOrigNumSheets <> intNumSheets Then
        Application.SheetsInNewWorkbook = intNumSheets
    End If
    Set wkbNew = Workbooks.Add
    If Len(strBookName) = 0 Then _
        strBookName = Application.GetSaveAsFilename
    wkbNew.SaveAs strBookName
    Set CreateNewWorkbook = wkbNew
    Application.SheetsInNewWorkbook = intOrigNumSheets

CreateNew_End:
    Exit Function
CreateNew_Err:
    Set CreateNewWorkbook = Nothing
    wkbNew.Close False
    Set wkbNew = Nothing
    Resume CreateNew_End
End Function

A Workbook object's Saved property is a Boolean value that indicates whether the workbook has been saved. The Saved property will be True for any new or opened workbook where no changes have been made and False for a workbook that has unsaved changes. You can set the Saved property to True. Doing this prevents the user from being prompted to save changes when the workbook closes but does not actually save any changes made since the last time the workbook was saved by using the Save method.

A note about working with workbooks through automation

When you are using Automation to edit an Excel workbook, keep the following in mind.

Creating a new instance of Excel and opening a workbook results in an invisible instance of Excel and a hidden instance of the workbook. Therefore, if you edit the workbook and save it, the workbook is saved as hidden. The next time the user opens Excel manually, the workbook is invisible and the user has to click Unhide on the Window menu to view the workbook.

To avoid this behavior, your Automation code should unhide the workbook before editing it and saving it. Note that this does not mean that Excel itself has to be visible.

The Excel Worksheet Object

Most of the work you will do in Excel will be within the context of a worksheet. A worksheet contains a grid of cells you can use to work with data, and hundreds of properties, methods, and events you can use to work with the data in a worksheet.

To work with the data contained in a worksheet, in a cell or within a range of cells, you use a Range object. The Worksheet and Range objects are the two most basic and most important components of any custom solution you create within Excel. I will talk more about the Range object in next month's column.

The Workbook object's Worksheets property returns a collection of all the worksheets in the workbook. The Workbook object's Sheets property returns a collection of all the worksheets and chart sheets in the workbook.

Each Excel workbook contains one or more Worksheet objects and can contain one or more chart sheets as well. Charts in Excel are either embedded in a worksheet or contained on a chart sheet. You can have only one chart on a chart sheet, but you can have multiple charts on a worksheet. Each embedded chart on a worksheet is a member of the Worksheet object's ChartObjects collection. Worksheet objects are contained in the Worksheets collection, which you can access by using the Workbook object's Worksheets property. When you use VBA to create a new workbook, you can specify how many worksheets it will contain by using the Application object's SheetsInNewWorkbook property.

Referring to a worksheet

Because a Worksheet object exists as a member of a Worksheets collection, you refer to a worksheet by its name or its index value. In the following example, both object variables refer to the first worksheet in a workbook:

Sub ReferToWorksheetExample()
    ' This procedure illustrates how to programmatically refer to
    ' a worksheet.
    Dim wksSheetByIndex     As Excel.Worksheet
    Dim wksSheetByName      As Excel.Worksheet

    With ActiveWorkbook
        Set wksSheetByIndex = Worksheets(1)
        Set wksSheetByName = Worksheets("Main")
        If wksSheetByIndex.Index = wksSheetByName.Index Then
            MsgBox "The worksheet indexed as #" _
                & wksSheetByIndex.Index & vbCrLf _
                & "is the same as the worksheet named '" _
                & wksSheetByName.Name & "'", _
                vbOKOnly, "Worksheets Match!"
        End If
    End With
End Sub

Working with a worksheet

You can add one or more worksheets to the Worksheets collection by using the collection's Add method. The Add method returns the new Worksheet object. If you add multiple worksheets, the Add method returns the last worksheet added to the Worksheets collection. If the Before or After arguments of the Add method are omitted, the new worksheet is added before the currently active worksheet. The following example adds a new worksheet before the active worksheet in the current collection of worksheets:

Dim wksNewSheet As Excel.Worksheet

Set wksNewSheet = Worksheets.Add
With wksNewSheet
    ' Work with properties and methods of the
    ' new worksheet here.
End With

You use the Worksheet object's Delete method to delete a worksheet from the Worksheets collection. When you try to programmatically delete a worksheet, Excel will display a message (alert); to suppress the message, you must set the Application object's DisplayAlerts property to False, as illustrated in the following example:

Function DeleteWorksheet(strSheetName As String) As Boolean
    On Error Resume Next

    Application.DisplayAlerts = False
    ActiveWorkbook.Worksheets(strSheetName).Delete
    Application.DisplayAlerts = True
    ' Return True if no error occurred;
    ' otherwise return False.
    DeleteWorksheet = Not CBool(Err.Number)
End Function

Note: When you set the DisplayAlerts property to False, always set it back to True before your procedure has finished executing, as shown in the preceding example.

You can copy a worksheet by using the Worksheet object's Copy method. To copy a worksheet to the same workbook as the source worksheet, you must specify either the Before or After argument of the Copy method. You move a worksheet by using the Worksheet object's Move method. For example:

Worksheets("Sheet1").Copy After:=Worksheets("Sheet3")
Worksheets("Sheet1").Move After:=Worksheets("Sheet3")

The next example illustrates how to move a worksheet so that it is the last worksheet in a workbook:

Worksheets("Sheet1").Move After:=Worksheets(Worksheets.Count)

Note: When you use either the Copy or the Move method, if you do not specify the Before or After argument, Excel creates a new workbook and copies the specified worksheet to it.

Where to Get More Info

The techniques discussed in this column will get you started working with Excel programmatically. For additional information, check out the following resources:

• For more information on the Excel object model see the Microsoft Excel Object Model.
• As always, check in regularly at the Office Developer Center for information and technical articles on Office solution development.

David Shank is a programmer/writer on the Office team specializing in developer documentation. Rumor has it he lives high in the mountains to the east of Redmond and is one of the few native Northwesterners who still lives in the Northwest.