Döngüler Ozg

   MSDN Home >  MSDN Library Archive >  Microsoft Office 97/Visual Basic Programmer's Guide 

Archived content. No warranty is made as to technical accuracy. Content may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist.

Contents 

• Working with the Application Object 

• Working with the Workbook Object 

• Working with the Range Object 

• Working with Events
  
  

Visual Basic supports a set of objects that correspond directly to elements in Microsoft Excel, most of which you're familiar with from the user interface. For example, the Workbook object represents a workbook, the Worksheet object represents a worksheet, and the Range object represents a range of cells on a worksheet. Every element in Microsoft Excel — each workbook, worksheet, chart, cell, and so on — can be represented by an object in Visual Basic. By creating procedures that control these objects, you automate tasks in Microsoft Excel. The object model in Microsoft Excel 97 contains extensive changes and modifications. To view a graphical representation of the Microsoft Excel 97 object model, see "Microsoft Excel Objects" in Help. For a list of changes and additions, see "Changes to the Microsoft Excel 97 Object Model" in Help.

Microsoft Excel 97 adds support for event-driven programming to Visual Basic. An event is any action recognized by a Microsoft Excel object. Several objects in Microsoft Excel recognize a predefined set of events; when you want an object to respond to an event in a particular way, you can write a Visual Basic event procedure for that event. 

Working with the Application Object

Most properties of the Microsoft Excel Application object control the appearance of the application window or the global behavior of the application. For example, the value of the DisplayFormulaBar property is True if the formula bar is visible, and the value of the ScreenUpdating property is False if screen updating is turned off.

In addition, properties of the Application object provide access to objects lower in the object hierarchy, such as the Windows collection (representing all currently open windows) and the Workbooks collection (representing all currently open workbooks). You use these properties, sometimes called accessors, to move down the object hierarchy from the toplevel Application object to objects lower in the hierarchy (such as the Workbook, Worksheet, and Range objects). For more information about navigating through an object model, see Chapter 2, "Understanding Object Models." 

Some methods and properties that apply to the Application object also apply to objects lower in the object hierarchy. Using these properties or methods at the Application level usually changes all open workbooks or sheets. For example, the Calculate method applies to the Application, Workbook, and Worksheet objects. Using Application.Calculate recalculates all worksheets in all open workbooks, whereas using this method on the Workbook or Worksheet object provides greater control.

Working with the Workbook Object

When you open or save a file in Microsoft Excel, you're actually opening and saving a workbook. In Visual Basic, the methods for manipulating files are methods of the Workbook object or the Workbooks collection. 

Opening Workbooks

When you open a workbook, you use the Open method. The Open method always applies to the Workbooks collection, which you return using the Workbooks property. The following code opens the file Book1.xls (in the current folder) and then displays the value that's in cell A1 on the first worksheet in the workbook. 

Sub OpenBook1()

    Set myBook = Workbooks.Open(Filename:="BOOK1.XLS")

    MsgBox myBook.Worksheets(1).Range("A1").Value

End Sub

Notice that the return value of the Open method is a Workbook object that refers to the workbook that was just opened. 

The file name in this example doesn't contain a path; therefore, the file is assumed to be in the current folder. This is guaranteed to cause a runtime error, because as soon as the user changes the current folder, Visual Basic can no longer find the file.

There are two relatively safe places to store a workbook you want to open programmatically. One place is the folder that contains the executable file for Microsoft Excel. The other place is the Library folder, which is created automatically during setup; this folder is one level down from the folder that contains the executable file.

If you want to open a workbook that's saved in the folder that contains the executable file, you can use the Path property to return a string that specifies the folder. The PathSeparator property returns the correct separator character for the current file system (for example, a backslash (\) for MSDOS®/Windows® FAT, or a colon (:) for the Macintosh®). The following example shows filesystemindependent code you can use to open Book1.xls, assuming that Book1.xls is saved in the folder that contains the executable file. 

Sub OpenBook1()

    EXEPath = Application.Path & Application.PathSeparator

    fName = EXEPath & "BOOK1.XLS"

    Set myBook = Workbooks.Open(Filename:=fName)

    MsgBox myBook.Worksheets(1).Range("A1").Value

End Sub

The other relatively safe place to store a workbook is in the Library folder. You can use the LibraryPath property instead of the Path property to return a string that specifies the Library folder. The following code shows how you would alter the preceding example to use the LibraryPath property. 

Sub OpenBook1()

    LibPath = Application.LibraryPath & Application.PathSeparator

    fName = LibPath & "BOOK1.XLS"

    Set myBook = Workbooks.Open(Filename:=fName)

    MsgBox myBook.Worksheets(1).Range("A1").Value

End Sub

Instead of hardcoding a file name with the Open method, you may want to give the user the option of selecting a file to open. The GetOpenFilename method displays the standard Open dialog box, but the method returns a string instead of opening a file. The string contains the fully qualified path and file name. The following example demonstrates the GetOpenFilename method by displaying the return value in a message box and then opening the file. 

Sub DemoGetOpenFilename()

    Do

        fName = Application.GetOpenFilename

    Loop Until fName <> False

    MsgBox "Opening " & fName

    Set myBook = Workbooks.Open(Filename:=fName)

End Sub

Creating and Saving Workbooks

You create a new workbook by applying the Add method to the Workbooks collection. Remember to set the return value of the Add method to an object variable so that you can refer to the new workbook in your code. 

When you save a new workbook for the first time, use the SaveAs method. For subsequent saves, use the Save method. The GetSaveAsFilename method is very similar to the GetOpenFilename method, which is described in the preceding section. The following example shows how to create a new workbook and then save it using the GetSaveAsFilename method. 

Sub CreateAndSave()

    Set newBook = Workbooks.Add

    Do

        fName = Application.GetSaveAsFilename

    Loop Until fName <> False

    newBook.SaveAs Filename:=fName

End Sub

Closing Workbooks

To close a workbook, use the Close method of the Workbook object. You can close a workbook without saving changes, as shown in the following example. 

Sub OpenChangeClose()

    Do

        fName = Application.GetOpenFilename

    Loop Until fName <> False

    Set myBook = Workbooks.Open(Filename:=fName)

    ' Make some changes to myBook

    myBook.Close savechanges:=False

End Sub

This code uses the GetOpenFilename method to select the workbook to open, makes some changes to the workbook (indicated by the comments), and then closes the workbook without saving the changes.

Working with the Range Object

The Range object can represent a single cell, a range of cells, an entire row or column, a selection containing multiple areas, or a 3D range. The Range object is somewhat unusual in that it can represent both a single cell and multiple cells. There's no separate collection object for the Range object; you can think of it as being either a single object or a collection, depending on the situation. There are many different properties and methods that return a Range object, as shown in the following list. 

For more information about these properties and methods, see the individual property and method topics in Help. 

Using an A1Style String Reference or Range Name

One of the most common ways to return a Range object is to use an A1style reference or the name of a range, as shown in the following table. 

Remember that expressions such as Range("C1:E3").Value = 6 assume that the Range property operates on the active sheet. If you try to run this code with a chart sheet active, a runtime error occurs (error 1004, "Range method of Application class failed"). 

Another cause of errors is the use of the Range property in an argument to another method, without fully qualifying the Worksheet object to which the Range property applies. The following example, which is supposed to sort a range of cells on Sheet1, also causes runtime error 1004. 

Sub SortRange()

Worksheets("Sheet1").Range("A1:B10").Sort _

    key1:=Range("A1"), order1:=xlDescending

End Sub

This error is more difficult to find, because the line that contains the Sort method is correct. The error is caused by the second line, which contains the Key1 argument. This code will run correctly if Sheet1 is the active sheet, but it will fail when it's run from another worksheet or from a module. To avoid the error, use the Worksheets property in the argument. 

Sub SortRange()

    Worksheets("Sheet1").Range("A1:B10").Sort _

        key1:=Worksheets("Sheet1").Range("A1"), order1:=xlDescending

End Sub

Using Numeric Row and Column Indexes

You can also return a specific cell by specifying its row and column numbers, or indexes. You specify the row index first, followed by the column index, as shown in the following table.

Numeric row and column indexes are useful when you want to refer to cells by using loop counters. For example, the following code loops through cells A1:D10 on Sheet1. If any of the cells has a value less than 0.01, the example replaces the value with 0 (zero). 

Sub RoundToZero()

    For rwIndex = 1 to 4

        For colIndex = 1 to 10

            If Worksheets("Sheet1").Cells(rwIndex, colIndex) < .01 Then

                Worksheets("Sheet1").Cells(rwIndex, colIndex).Value = 0

            End If

        Next colIndex

    Next rwIndex

End Sub

The following example shows a quick and easy way to display items in a multiplecolumn list. The code creates a new worksheet and sets the object variable newSheet to refer to the worksheet. The code then creates a list of all the names in the active workbook and displays their formulas in A1style notation. 

Sub ListNames()

    Set newSheet = Worksheets.Add

    i = 1

    For Each nm In ActiveWorkbook.Names

        newSheet.Cells(i, 1).Value = nm.Name

        newSheet.Cells(i, 2).Value = "'" & nm.RefersTo

        i = i + 1

    Next nm

    newSheet.Columns("A:B").AutoFit

End Sub

Using the Offset Property

You often need to return a range of cells that's a certain number of rows or columns away from another range of cells. The Offset property applies to a Range object, takes a RowOffset argument and a ColumnOffset argument, and returns a new range. The following example determines the type of data in each cell in the range A1:A10. The code writes the data types in the column to the right of the input cells. 

Sub ScanColumn()

    For Each c In Worksheets("Sheet1").Range("A1:A10").Cells

        If Application.IsText(c.Value) Then

            c.Offset(0, 1).Formula = "Text"

        ElseIf Application.IsNumber(c.Value) Then

            c.Offset(0, 1).Formula = "Number"

        ElseIf Application.IsLogical(c.Value) Then

            c.Offset(0, 1).Formula = "Boolean"

        ElseIf Application.IsError(c.Value) Then

            c.Offset(0, 1).Formula = "Error"

        ElseIf c.Value = "" Then

            c.Offset(0, 1).Formula = "(blank cell)"

        End If

    Next c

End Sub

Using the CurrentRegion and UsedRange Properties

These two properties are very useful when your code operates on ranges whose size you have no control over. The current region is a range of cells bounded by empty rows and empty columns, or by a combination of empty rows, empty columns, and the edges of the worksheet.

The CurrentRegion property applies to a Range object. There can be many different current regions on a worksheet, depending on the Range object to which you apply the CurrentRegion property. 

Suppose that Sheet1 contains a list to which you want to apply a number format. The only thing you know about the list is that it begins at cell A1; you don't know how many rows or columns it contains. The following example shows how to format the list by using the CurrentRegion property. 

Sub FormatRange()

    Set myRange = Worksheets("Sheet1").Range("A1").CurrentRegion

    myRange.NumberFormat = "0.0"

End Sub

The used range is bounded by the farthest upperleft and farthest lowerright nonempty cells on a worksheet. It's a range that contains every nonempty cell on the worksheet, as well as all the empty cells that are interspersed among them. There can be only one used range on a worksheet; the UsedRange property applies to a Worksheet object, not to a Range object. 

Suppose that the active worksheet contains data from a timed experiment. The used range contains the dates in the first column, the times in the second column, and the measurements in the third and fourth columns. You want to write code that combines each separate date and time into a single value, converts that value from Greenwich Mean Time (GMT) to Pacific Standard Time (PST), and then applies a date format to the value. The data table can contain empty rows and columns. You can use the UsedRange property to return the entire used range, including any embedded blank rows. The following example shows one way to convert and format the dates and times. 

Sub ConvertDates()

    Set myRange = ActiveSheet.UsedRange

    myRange.Columns("C").Insert

    Set dateCol = myRange.Columns("C")

    For Each c In dateCol.Cells

        If c.Offset(0, -1).Value <> "" Then

            c.FormulaR1C1 = "=RC[-2]+RC[-1]-(8/24)"

        End If

    Next c

    dateCol.NumberFormat = "mmm-dd-yyyy hh:mm"

    dateCol.Copy

    dateCol.PasteSpecial Paste:=xlValues

    myRange.Columns("A:B").Delete

    dateCol.AutoFit

End Sub

Notice that the code uses the expression ActiveSheet.UsedRange.Columns("C") to return the third column from the used range (although this is the third column in the used range, it can appear in any column on the worksheet — that is, the used range can be preceded by empty columns). You can use other Range object properties and methods in a similar way to build complex expressions that return subranges or superranges of a Range object. Some properties and methods commonly used in this way are Areas, Cells, Columns, EntireColumn, EntireRow, Range, and Rows. 

Looping on a Range of Cells

There are several different ways to loop on the cells in a range. The examples in this section show the For Each...Next statement and the Do...Loop statement applied to looping on a range of cells. 

Using For Each...Next

The recommended way to loop on the cells in a range is to use the For Each...Next loop, which is also the recommended way to loop on the elements in a collection. 

The following example shows how to loop through the range A1:D10 on Sheet1, setting any number whose absolute value is less than 0.01 to 0 (zero). 

Sub RoundToZero()

    For Each r In Worksheets("Sheet1").Range("A1:D10").Cells

        If Abs(r.Value) < 0.01 Then

            r.Value = 0

        End If

    Next r

End Sub

Suppose that you want to modify this code to loop over a range of cells that a user selects. One way of doing this is to use the InputBox method to prompt the user to select a range of cells. The InputBox method returns a Range object that represents the selection. By using the Type argument and error handling, you can ensure that the user selects a valid range of cells before the input box is dismissed. 

Sub RoundToZero()

    Worksheets("Sheet1").Activate

    On Error GoTo PressedCancel

    Set r = Application.InputBox( _

            prompt:="Select a range of cells", _

            Type:=8)

    On Error GoTo 0

    For Each c In r.Cells

        If Abs(c.Value) < 0.01 Then

            c.Value = 0

        End If

    Next c

    Exit Sub

PressedCancel:

    Resume

End Sub

If you don't want the user to select the range, you may be able to use the CurrentRegion property or the UsedRange property to return a Range object. For example, if you know that the data on Sheet1 begins at cell A1 and includes no empty rows or columns, you can use the CurrentRegion property to return the entire range automatically. 

Sub RoundToZero()

    Set r = Worksheets("Sheet1").Range("A1").CurrentRegion

    For Each c In r.Cells

        If Abs(c.Value) < 0.01 Then

            c.Value = 0

        End If

    Next c

End Sub

The following two examples show two different ways to hide every other column in the used range on Sheet1. The first example shows a For Each...Next loop in which the Column property of the object variable is tested. 

Sub HideColumns()

    Set r = Worksheets("Sheet1").UsedRange

    For Each col In r.Columns

        If col.Column Mod 2 = 0 Then

            col.Hidden = True

        End If

    Next col

End Sub

The second example shows a For...Next loop that tests the loop counter. 

Sub HideColumns()

    Set r = Worksheets("Sheet1").UsedRange

    For i = 1 To r.Columns.Count

        If i Mod 2 = 0 Then

            r.Columns(i).Hidden = True

        End If

    Next i

End Sub

Using Do...Loop

Occasionally, the For Each...Next loop isn't the best way to loop on a range. Suppose that you have a column of data and you want to write a macro that sorts the data and then deletes rows that contain duplicate data. You could try to use a For Each...Next loop, as shown in the following example. 

Sub BuggyRemoveDuplicates()    ' DON'T USE THIS CODE!

    Worksheets("Sheet1").Range("A1").Sort _

        key1:=Worksheets("Sheet1").Range("A1")

    Set r = Worksheets("Sheet1").Range("A1").CurrentRegion.Columns("A")

    For Each c In r.Cells

        If c.Offset(1, 0).Value = c.Value Then

            c.Offset(1, 0).EntireRow.Delete

        End If

    Next c

End Sub

Unfortunately, this code doesn't work correctly because the Delete method is modifying the range on which For Each...Next is looping. This causes duplicates not to be deleted in some cases. 

A better solution is to use a Do...Loop structure, as shown in the following example. 

Sub GoodRemoveDuplicates()

    Worksheets("Sheet1").Range("A1").Sort _

            key1:=Worksheets("Sheet1").Range("A1")

    Set currentCell = Worksheets("Sheet1").Range("A1")

    Do While Not IsEmpty(currentCell)

        Set nextCell = currentCell.Offset(1, 0)

        If nextCell.Value = currentCell.Value Then

            currentCell.EntireRow.Delete

        End If

        Set currentCell = nextCell

    Loop

End Sub

The loop tests the object variable currentCell, exiting when it encounters an empty cell at the bottom of the column of data. You could build an equivalent loop by testing the value in currentCell against an empty string, as shown in the following example. 

    Do While currentCell.Value <> ""

        ' Code to run on cells with values

    Loop

In either case, don't forget to increment the cell at the bottom of the Do...Loop structure (Set currentCell = nextCell, for example). 

Using the Address Property to Debug Range Object Code

You can apply the Address property to any Range object. The Address property returns the cell address of a range, as a string. The following example shows how to use the Address property to debug the HideColumns procedure. 

Sub HideColumns()

    Set r = Worksheets("Sheet1").UsedRange

    MsgBox r.Address  ' debugging only!

    For i = 1 To r.Columns.Count

        If i Mod 2 = 0 Then

            r.Columns(i).Hidden = True

            MsgBox r.Columns(i).Address  ' debugging only!

        End If

    Next i

End Sub

You can also set watch expressions instead of using message boxes. For the preceding example, you could set two watch expressions — r.Address and r.Columns(i).Address — and then examine the values of the watch expressions in the Immediate window. For more information about debugging, see Chapter 14, "Debugging and Error Handling."

Working with Events

If you've used Visual Basic (Standard, Professional, or Enterprise Edition), you're familiar with eventdriven programming; most of your Visual Basic code was probably written to respond to events, such as when the user clicks a button or when a form is loaded. In Microsoft Excel, you may have used properties such as OnSheetActivate or OnEntry to cause a macro to run when a sheet is activated or changed. This is also eventdriven programming. Microsoft Excel 97 expands the available list of events and adds event procedures that receive arguments. 

With Microsoft Excel 97, you can write event procedures at the worksheet, chart, workbook, or application level. For example, the Activate event occurs at the sheet level, and the SheetActivate event is available at both the workbook and application levels. The SheetActivate event for a workbook occurs when any sheet in that workbook is activated. At the application level, the SheetActivate event occurs when any sheet in any open workbook is activated.

Worksheet and workbook event procedures are created by default for any open worksheet, chart sheet, or workbook. To write event procedures for an embedded chart or for the application, you must create a new object using the WithEvents keyword in a class module. You can also use a class module to create event procedures that can be used for more than one worksheet or workbook. For more information , see "Using Class Modules with Events" later in this chapter. 

Enabling or Disabling Events

Use the EnableEvents property to enable or disable events. For example, using the Save method to save a workbook causes the BeforeSave event to occur. You can prevent this by setting the EnableEvents property to False before you call the Save method, as in the following example. 

Application. = False

ActiveWorkbook.Save

Application. = True

Using Events on Sheets

Events on sheets are enabled by default. To view the event procedures for a particular sheet, use either of the following techniques:

• Rightclick the sheet tab, and then click View Code on the shortcut menu. In the Procedure box, click the event name. 

• On the Tools menu, point to Macro and then click Visual Basic Editor. Select the sheet in the Project Explorer, and then either click the View Code button or click Code on the View menu. In the Object box, click either Worksheet or Chart, and then click the event name in the Procedure box.
  
  

Worksheet Events

Worksheetlevel events occur when the user activates a worksheet or changes a worksheet cell, as shown in the following table.

For more information about an event, see the corresponding Help topic. 

Examples 

The following example adjusts the size of columns A through F whenever the worksheet is recalculated. 

Private Sub Worksheet_Calculate()

    Columns("A:F").AutoFit

End Sub

Some events can be used to substitute an action for the default application behavior, or to make a small change to the default behavior. The following example traps the rightclick event and adds a new menu item to the shortcut menu for cells B1:B10. 

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, _

        Cancel As Boolean)

    For Each icbc In Application.CommandBars("cell").Controls

        If icbc.Tag = "brccm" Then icbc.Delete

    Next icbc

    If Not Application.Intersect(Target, Range("b1:b10")) Is Nothing Then

            With Application.CommandBars("cell").Controls _

                .Add(Type:=msoControlButton, before:=6, _

                temporary:=True)

           .Caption = "New Context Menu Item"

           .OnAction = "MyMacro"

           .Tag = "brccm"

        End With

    End If

End Sub

Chart Events

Like worksheetlevel events, chartlevel events occur when the user activates or changes a chart, as shown in the following table.

For more information about an event, see the corresponding Help topic.

Events for chart sheets are available by default in the Visual Basic Editor. To write event procedures for an embedded chart, you must create a new object using the WithEvents keyword in a class module. For more information, see "Using Class Modules with Events" later in this chapter. 

Example 

The following example changes a point's border color when the user changes the point's value. 

Private Sub Chart_SeriesChange(ByVal SeriesIndex As Long, _

        ByVal PointIndex As Long)

    Set p = ActiveChart.SeriesCollection(SeriesIndex).Points(PointIndex)

    p.Border.ColorIndex = 3

End Sub

Workbook Events

Workbook events occur when the user changes a workbook or any sheet in the workbook.

For more information about an event, see the corresponding Help topic. 

Example 

The following example maximizes the Microsoft Excel application window when the workbook is opened. 

Sub Workbook_Open()

    Application.WindowState = xlMaximized

End Sub

Application Events

Application events occur when the user creates or opens a workbook or when the user changes any sheet in any open workbook.

For more information about an event, see the corresponding Help topic. 

Using Class Modules with Events

Unlike sheet events, embedded charts and the Application object don't have events enabled by default. Before you can use events with an embedded chart or with the Application object, you must create a new class module and declare an object of type Chart or Application with events. You use the Class Module command (Insert menu) in the Visual Basic Editor to create a new class module. 

To enable the events of the Application object, you'd add the following declaration to the class module. 

Public WithEvents App As Application

After the new object has been declared with events, it appears in the Object box in the class module, and you can write event procedures for the new object. (When you select the new object in the Object box, the valid events for that object are listed in the Procedure box.) 

Before the procedures will run, however, you must connect the declared object in the class module to the Application object. You can do this from any module by using the following declaration (where "EventClass" is the name of the class module you created to enable events). 

Public X As New EventClass

After you've created the X object variable (an instance of the EventClass class), you can set the App object of the EventClass class equal to the Microsoft Excel Application object. 

Sub InitializeApp()

    Set X.App = Application

End Sub

After you run the InitializeApp procedure, the App object in the EventClass class module points to the Microsoft Excel Application object, and the event procedures in the class module will run whenever the events occur. 

Although this may seem like a lot of work, one advantage is that you can use the same event procedure for many objects. For example, suppose that you declare an object of type Chart with events in a class module, as follows. 

Public WithEvents cht As Chart

You can then use the following code to cause the event procedures to run whenever an event occurs for either chart one or chart two. 

Dim C1 As New EventClass

Dim C2 As New EventClass

Sub InitializeCharts

    Set C1.cht = Worksheets(1).ChartObjects(1).Chart

    Set C2.cht = Worksheets(1).ChartObjects(2).Chart

End Sub

You can declare Worksheet or Workbook objects with events in a class module and use the events in the new class with several sheets, in addition to the default event procedures. You might use this technique to write an Activate event handler that runs only when either sheet one or sheet five is activated. Or you can use a Chart object declared in a class module to write an event handler for both embedded charts and chart sheets. 

This page lists some suggestions and strategies for optimizing your Visual Basic For Applications (VBA) code, so that it will run faster.   There are few absolute rules for optimizing VBA;  you'll see the best increases in performance by streamlining the basic logic. 

Accessing Cells In A Range

You do not need to use the .Cells method to access specific cells in a range.  For example, you can use

Range("MyRange")(1,2)   rather than
Range("MyRange").Cells(1,2)

See Alan Beban's explanation of this method for more details.

Related to this is the shortcut method of refering to cells.  VBA will allow you reference cells with [A1] rather than Range("A1"). While the [A1] syntax is easier to type, it is slower to execute than the Range("A1") syntax.

Calculation Mode

Normally, Excel will recalculate a cell or a range of cells when that cell's or range's precedents have changed.  This may  cause your workbook to recalculate too often, which will slow down performance.  You can prevent Excel from recalculating the workbook by using the statement:

Application.Calculation = xlCalculationManual

At the end of your code, you can set the calculation mode back to automatic with the statement:

Application.Calculation = xlCalculationAutomatic

Remember, though, that when the calculation mode is xlCalculationManual, Excel doesn't update values in cells.  If your macro relies on an updated cell value, you must force a Calculate event, with the .Calculate method, which may be applied to either a specific range (Range("MyRange").Calculate) or to the entire workbook (Calculate).

Collection Indexes

An individual item of a collection object may be accessed by either its name or by its index into the collection.  For example, if you have three worksheets ("Sheet1", "Sheet2", and "Sheet3")  in a workbook ("MyWorkbook"), you can reference "Sheet2" with either

Worksheets("Sheet2")  or
Worksheets(2)

In general, the index number method (Worksheets(2)) is considerably faster than the index name method (Worksheets("Sheet2")). 

However, the number and order of items in a collection may change, so it is usually safer and easier to refer to items in a collection by their name, rather than their index number.

Constants

Whenever you can, declare values as constants, rather than variables.   Since their values never change, they are evaluated only once when your code is compiled, rather than each time they are used at run time.

Early Binding

This is closely tied with Specific Object Type Declaration.  If you're going to work with another application, such as Word, declare your OLE object directly, rather than as an Object type variable.  By doing so, a great deal of overhead is done at compile time ("Early Binding") rather than at run time ("Late Binding").   For example, use

Dim WordObj As Word.Application        rather than
Dim WordObj As Object

Also see Using Variables (Properly) In VBA.

FOR EACH Loops 

When looping through a collection it is usually faster than the FOR EACH statement rather than using the index.  For example, the first code loop is faster than the second: 

Dim WS as Worksheet
For Each WS In Worksheets
    MsgBox WS.Name
Next WS

Dim i as Integer
For i = 1 To Worksheets.Count
    MsgBox Worksheets(i).Name
Next i

Range Objects Not Selection Object

Generally, it is not necessary to select a range before working with it.  For example, it is more efficient to use

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

Rather than

Range("A1").Select
Selection.Font.Bold = True

Screen Updating

You can turn off screen updating so that Excel does not update the screen image as your code executes.  This can greatly speed up your code.

Application.ScreenUpdating = FALSE

Be sure to restore the setting to True at the end of your macro.  Older version of Excel would automatically restore the setting; Excel97 does not.

Simple Objects Rather Than Compound Objects

If you've got to make repeated references to an object, such a range, declare an object of that type, set it to the target object, and then use your object to refer to the target.   For example,

Dim MyCell As Range
Set MyCell = Workbooks("Book2").Worksheets("Sheet3").Range("C3")
'....
MyCell.Value = 123

By referring directly to MyCell , VBA can access the object directly, rather than resolving the complete path to the object each time.  This method is useful only when you are accessing an object several times during code execution.

Specific Object Type Declaration

If possible avoid using the Object or Variant data types.  These data types require considerable overhead to determine their types.  Instead, use explicit data types, such as

Dim MySheet As Worksheet  rather than
Dim MySheet As Object 
Or
Dim NumRows As Long rather than
Dim NumRows As Variant

This is especially true with index variables in For Next loops, since a Variant type has to be re-evaluated each iteration of the loop.

Also see Using Variables (Properly) In VBA.

WITH Statements

If you are using several statement in a row that apply to the same object, use a WITH statement, rather than fully qualifying the object each time.  For example,

With Worksheets("Sheet1").Range("A1")
    .Font.Bold = True
    .Value = 123
End With

Worksheet Functions

You can use Excel's standard worksheet functions in your VBA code, rather than writing the functions in VBA.  Since these are fully executable instructions in native code, rather than interpreted VBA code, they run much faster.  For example, use

MySum = Application.WorksheetFunction.Sum(Range("A1:A100"))   

rather than

For Each C In Range("A1:A100")
    MySum = MySum + C.Value
Next C