Hintergrund

Hier geht es ausser um Excel-VBA auch um die IEnumVariant-Schnittstelle - eine grundlegende Schnittstelle von COM (ActiveX).

Excel-VBA basiert auf COM, ein eigentlich längst überholtes Konzept aus den 1990er Jahren, das uns im VBA-Umfeld aber auf Schritt und Tritt begleitet.

Warum es zuweilen ganz hilfreich ist, wenn man sich ein wenig mit diesen Konzepten befasst, zeigt dieser Artikel am Beispiel Item-Methode der Sheets- (Worksheets-) Auflistung.

Was hat es mit der Item-Methode der IEnumVariant-Schnittstelle auf sich?

Der folgende VBA-Code kopiert Werte von einem Arbeitsblatt in ein anderes und scheint durchaus in Ordnung zu sein, zumindest funktioniert er zuverlässig.

    Const ExpectedRange As Long = 1000
    Dim currentRow As Long
    For currentRow = 1 To ExpectedRange
        Sheets("Tabelle2").Cells(currentRow, 1) =
            Sheets("Tabelle1").Cells(currentRow, 1)
    Next
    

Zunächst sollte man sich vergegenwärtigen, dass die Codezeile innerhalb der For-Schleife 'eigentlich' wie folgt lautet:

    Sheets.Item("Tabelle2").Cells(currentRow, 1) =
        Sheets.Item("Tabelle1").Cells(currentRow, 1)
    

Die Item-Methode ist die Standardeigenschaft der Sheets- (Worksheets-) Klasse. Deswegen wird irgendwo in den Tiefen von ActiveX 'Sheets("Tabelle1")' in 'Sheets.Item("Tabelle1")' übersetzt.

Ein Aufruf wie 'Sheets.Item("Tabelle1")' führt intern mindestens zwei Anweisungen aus:

• Zuerst wird geprüft, ob es sich bei dem Aufrufparameter um einen numerischen Index oder einen zeichenbasierten Schlüssel handelt (man könnte ja auch 'Sheets.Item(1)' schreiben').
• Davon abhängig wird anschließend eine von zwei möglichen Arten der Suche nach dem gewünschten Element (hier 'Tabelle1') innerhalb der internen Auflistung von Elementen durchgeführt.

Zusätzlich kann noch eine Zeigertypumwandlung erforderlich sein, weil die Objekte in der internen Auflistung als Objekt vom Typ der generischen Schnittstelle 'IUnknown' gespeichert sind.

Letztendlich kann man zwar nicht genau wissen, welche Anweisungen im einzelnen ausgeführt werden, ganz sicher ist aber, dass mehrere Anweisungen ausgeführt werden.

Lösung

Sobald Sie in Ihrem VBA-Code innerhalb einer Routine mehr als einmal die gleiche 'Item'-Methode aufrufen, passen Sie Ihren Code an.

Statt immer wieder 'Sheets.Item("Tabelle1")' aufzurufen, deklarieren Sie eine Variable, der Sie 'Sheets.Item("Tabelle1")' zuweisen und verwenden Sie anschließend die Variable.

    Const ExpectedRange As Long = 1000
    Dim sourceSheet As Worksheet
    Set sourceSheet = Sheets("Tabelle1")
    Dim targetSheet As Worksheet
    Set targetSheet = Sheets("Tabelle2")
    Dim currentRow As Long
    For currentRow = 1 To ExpectedRange
        targetSheet.Cells(currentRow, 1) = sourceSheet.Cells(currentRow, 1)
    Next
    

Gewinn

Dieser Code wird doppelt so schnell ausgeführt, als der Ausgangscode.

Vielleicht auch interessant für Sie: VBA-Extension Tool. Produktivitätssteigerung für VBA durch Vereinfachung von Routinearbeiten.