KB0070: Warum ist mein Excel-Makro langsam, wenn thinkcell aktiviert ist?

Ein häufiges Problem, das Leistungsprobleme in VBA-Makros verursachen kann, ist die Verwendung der Funktion .Select. Jedes Mal, wenn in Excel eine Zelle ausgewählt wird, wird jedes einzelne Excel-Add-In (einschließlich thinkcell) über dieses Auswahländerungsereignis benachrichtigt, was das Makro erheblich verlangsamt. Makros, die mit dem Makrorekorder erstellt wurden, sind besonders anfällig für diese Art von Problem.

• MSDN: Improving Performance in Excel 2007: "Reference Excel objects such as Range objects directly, without selecting or activating them" (in the section, "Faster VBA Macros").

Beispiel: So vermeiden Sie die Verwendung der Anweisung .Select

Sehen wir uns das folgende einfache Makro AutoFillTable an:

Sub AutoFillTable()
    Dim iRange As Excel.Range
    Set iRange = Application.InputBox(prompt:="Enter range", Type:=8)
    Dim nCount As Integer
    nCount = iRange.Cells.Count
    For i = 1 To nCount
        Selection.Copy
        If iRange.Cells.Item(i).Value = "" Then
            iRange.Cells.Item(i).Range("A1").Select
            ActiveSheet.Paste
        Else
            iRange.Cells.Item(i).Range("A1").Select
        End If
    Next
End Sub

Diese Funktion öffnet ein Eingabefeld, in dem der Benutzer aufgefordert wird, einen Zellbereich anzugeben. Die Funktion durchläuft alle Zellen im Bereich. Wenn sie eine Zelle findet, die nicht leer ist, kopiert sie den Zellinhalt in die Zwischenablage. Die Funktion fügt den Inhalt der Zwischenablage in jede nachfolgende leere Zelle ein.

AutoFillTable verwendet die Zwischenablage, um den Zellinhalt zu kopieren. Daher muss die Funktion jede Zelle auswählen, die bearbeitet wird, damit Excel weiß, aus welcher Zelle kopiert und in welche Zelle eingefügt werden soll. Die empfohlene Lösung wird in der folgenden Funktion AutoFillTable2 gezeigt:

Sub AutoFillTable2()
    Dim iRange As Excel.Range
    Set iRange = Application.InputBox(prompt:="Enter range", Type:=8)
    Dim iCellMaster As Excel.Range
    For Each iCell In iRange.Cells
        If iCell.Value = "" Then
            If Not iCellMaster Is Nothing Then
                iCellMaster.Copy (iCell)
            End If
        Else
            Set iCellMaster = iCell
        End If
    Next iCell
End Sub

AutoFillTable2 unterscheidet sich in zwei wichtigen Punkten von seinem Vorgänger:

1. Vor allem muss keine Zelle mehr ausgewählt werden. Stattdessen wird eine gefundene Zelle, die nicht leer ist, in der Variablen iCellMaster gespeichert. Danach wird jedes Mal, wenn eine leere Zelle gefunden wird, der gesamte Inhalt von iCellMaster in iCell kopiert.
2. Es verwendet das Visual Basic-Sprachfeature For Each … Next, um jede Zelle im Bereich zu durchlaufen. Das verbessert bereits die Lesbarkeit.