Using Special-Purpose Visual Basic Methods

There are also several special-purpose Visual Basic methods that offer a concise way to perform a specific operation on a range of cells. Like worksheet functions, these specialized methods are faster than the general-purpose Visual Basic code that accomplishes the same task.

For example, the following code changes the value in each cell in a range in a relatively slow way.

For Each c In Worksheets(1).Range("a1:a200").Cells
    If c.Value = 4 Then c.Value = 4.5
Next

The code in the following example, which uses the Replace method, performs the same operation much faster.

Worksheets(1).Range("a1:a200").Replace "4", "4.5"

The following example shows a relatively slow way to add a blue oval to each cell in the range A1:A500 that contains the value 4.

For Each c In Worksheets(1).Range("a1:a500").Cells
    If c.Value = 4 Then
        With Worksheets(1).Ovals.Add(c.Left, c.Top, c.Width, c.Height)
            .Interior.Pattern = xlNone
            .Border.ColorIndex = 5
        End With
    End If
Next

The code in the following example, which uses the Find and FindNext methods, performs the same task much faster.

With Worksheets(1).Range("a1:a500")
    Set c = .Find(4)
    If Not c Is Nothing Then
        firstAddress = c.Address
        Do
            With Worksheets(1).Ovals.Add(c.Left, c.Top, _
                    c.Width, c.Height)
                .Interior.Pattern = xlNone
                .Border.ColorIndex = 5
            End With
            Set c = .FindNext(c)
        Loop While Not c Is Nothing And c.Address <> firstAddress
    End If
End With

For more information about special-purpose Visual Basic methods, see the topic in Help that covers object you're working with, and examine the list of that object's methods. You can also examine the list of all Visual Basic methods on the Contents tab of Help.