VBA in Excel/ Code-Optimierung

| Hoch zu „Inhaltsverzeichnis“ |

Die folgende Grundsätze verhelfen zu einer optimalen Ablaufgeschwindigkeit Ihres VBA-Programms:

Konstanten

Deklarieren Sie, wo immer möglich, Konstanten statt Variablen.

Objektindex

Wenn es die Klarheit des Codes nicht stört, verwenden Sie bei Objekt-Schleifen den Index des Objektes, nicht den Namen.

Worksheets(intCounter)

ist schneller als

Worksheets("Tabelle1")

Allerdings gehen For-Each-Schleifen vor, denn

For Each wksData In Worksheets
wksData
Next

ist schneller als

Worksheets(intCounter)

Grundsätzlich sollten im Code keine Adressen stehen, die sich ändern könnten. Eine bessere Lösung ist die Nutzung von Konstanten,

    
    Const TabName = "Tabelle1"
    Const TabIndex = 1

    Worksheets(TabName)
    Worksheets(TabIndex)

wobei die Konstanten zur besseren Übersicht am Anfang stehen.

Direkte Objektzuweisungen

Verwenden Sie nach Möglichkeit keine allgemeinen Objektzuweisungen. Das folgende Beispiel zeigt immer genauere Verweise:

Dim wksData
Dim wksData As Object
Dim wksData As Worksheet

Hinweise zur dritten Art der Deklaration:

• Sie ist nicht immer möglich, denn sie setzt voraus, dass die Bibliothek über die Verweise (References) eingebunden ist.
• Sie hat den Vorteil, dass IntelliSense nach Eingabe eines Punktes Vorschläge machen kann, welche Eigenschaften und Methoden zu dem Objekt passen.

Wenn die Objekte einer anderen Anwendung entstammen (z.B. Word oder Access), muss zunächst der Verweis auf die Objektbibliothek eingefügt werden, damit Intellisense funktioniert.

• Fehler im Code werden leichter gefunden, weil bei Variante 1 der Variablen wksData jeder Datentyp zugewiesen werden kann und in Variante 2 immer noch jeder Objektdatentyp.

Selektieren

Wählen Sie keine Arbeitsmappen, Blätter, Bereiche oder andere Objekte aus, um beispielsweise einen Wert zu schreiben:

Workbooks("Test.xls").Activate
Worksheets("Tabelle1").Select
Range("A1").Select
ActiveCell.Value = 12

Referenzieren Sie stattdessen exakt:

Workbooks("Test.xls").Worksheets("Tabelle1").Range("A1").Value = 12

Der Umweg über die Selektion bedeutet auch, dass das Blatt sichtbar gemacht wird und der Monitor nach Ablauf des Makros das zuletzt selektierte Objekt zeigt.

Keine eckigen Klammern

Verwenden Sie für Zellbereiche nicht die Schreibweise in eckigen Klammern:

[b3] = [d4]

Schreiben Sie stattdessen (Ausführungszeit ca. 66% von vorigem):

Range("B3").Value = Range("D4").Value

Noch etwas schneller (Ausführungszeit ca. 90% von vorigem bzw. 60% von ersterem):

Cells(3,2).Value = Cells(4,4).Value   ' Cells(ZeilenNr, SpaltenNr)

Hinweis: Beachten Sie, dass bei Angabe des Zellbezugs als String die Range-Eigenschaft verwendet werden muss, wohingegen bei der Angabe als Zahlen die Cells-Eigenschaft verwendet werden muss.

Direkte Referenzierung

Referenzieren Sie - wenn der Programmablauf es nicht erforderlich macht - nicht hierarchieweise:

Set wkbData = Workbooks("Test.xls")
Set wksData = wkbData.Worksheets("Tabelle1")
Set rngData = wksData.Range("A1:F16")

Referenzieren Sie stattdessen direkt das Zielobjekt:

Set rngData = Workbooks("Test.xls").Worksheets("Tabelle1").Range("A1:F16")

Dimensionierung

Dimensionieren Sie die Variablen nicht allgemeiner als dies erforderlich ist:

Dim intCounter As Integer
ist schneller als: 
Dim varCounter as Variant

Hinweise:

• Wenn eigentlich der Datentyp Byte ausreichen sollte, kann eine Subtraktion manchmal einen Unterlauf verursachen. Die Gefahr besteht vor allem bei FOR-Schleifen mit einem negativen Argument für STEP. In diesem Falle bei INTEGER bleiben.
• In bestimmten Fällen kann man den Datentyp Variant nicht vermeiden, beispielsweise hier:
  • Der Rückgabewert einer Funktion soll bei Fehlern auch einen Fehlerwert der Funktion CVErr() ausgeben
  • VBA hat keinen eigenen Datentyp für lange Dezimalzahlen vom Typ Dec (Umwandlung mit CDec())
  • Bei optionalen Argumenten (mit Option MyVar einer Sub/Function kann mit IsMissing() nur auf ausgelassene Argumente geprüft werden, wenn der Datentyp Variant ist. Andernfalls erhält man immer die default-Belegung des Datentyps von MyVar, wenn das Argument ausgelassen wird
  • Wenn eine Variable ein Array aufnehmen soll, muss sie vom Typ Variant sein

Tipp: Noch etwas schneller als der Integer ist der Datentyp Long! Das liegt vermutlich daran, dass Integer 16-bittig ist während Long 32-bittig ist und alle neueren Prozessoren für 32-Bit optimiert sind.

With-Rahmen

Verwenden Sie With-Rahmen. Langsam ist:

Worksheets("Tabelle1").Range("A1:A16").Font.Bold = True
Worksheets("Tabelle1").Range("A1:A16").Font.Size = 12
Worksheets("Tabelle1").Range("A1:A16").Font.Name = "Arial"
Worksheets("Tabelle1").Range("A1:A16").Value = "Hallo!"

Schneller ist:

With Worksheets("Tabelle1").Range("A1:A16")
    With .Font
        .Bold = True
        .Size = 12
        .Name = "Arial"
    End With
    .Value = "Hallo!"
End With

Der Vorteil ist außerdem, dass die Ziele (Tabelle1 und A1:A16) nur einmal im Code stehen, was die Gefahr von Tippfehlern verringert.

Excel-Funktionen

Ziehen Sie Excel-Funktionen VBA-Routinen vor. Langsam ist:

For intCounter = 1 To 20
    dblSum = dblSum + Cells(intCounter, 1).Value
Next intCounter

Schneller ist:

dblSum = WorksheetFunction.Sum(Range("A1:A20"))

Wenn Sie große, zusammenhängende Zellbereiche berechnen müssen, setzen Sie zur eigentlichen Berechnung Excel-Formeln ein. Die Formeln können Sie danach in absolute Werte umwandeln:

Sub Berechnen()
    Dim intRow As Integer
    intRow = Cells(Rows.Count, 1).End(xlUp).Row
    Range("C1").Formula = "=A1+B1/Pi()"
    Range("C1:C" & intRow).FillDown
    Columns("C").Copy
    Columns("C").PasteSpecial Paste:=xlValues
    Application.CutCopyMode = False
    Range("A1").Select
End Sub

Dasselbe Ergebnis hat folgende Prozedur, die auch With-Klammern verwendet und bei der Ersetzung der Formeln durch Werte ohne Copy/PasteSpecial auskommt:

Sub Berechnen2()
    Dim lngRow As Long
    lngRow = Cells(Rows.Count, 1).End(xlUp).Row
    With Range("C1:C" & lngRow)
      .Formula = "=A1+B1/Pi()" ' trägt die Formeln ein
      .Formula = .Value        ' ersetzt die Formeln durch Werte; .Value = .Value geht auch
    End With
    Range("A1").Select ' nur, wenn das nötig/erwünscht ist
End Sub

Tipp: Wenn Sie auf eine große Anzahl Zellen zugreifen müssen, dann ist es am schnellsten, wenn Sie die Werte mit einem Befehl in ein Array kopieren und dann aus dem Array lesen:

Function Berechne3()
    Dim Matrix As Variant   ' Array mit Inhalten der Tabelle
    Dim Summe As Long       ' Summe, Ergebnis
    Dim ZeNr As Long        ' Zeilenindex
    Dim SpNr As Long        ' Spaltenindex
    
    ' Übernahme des Tabellenbereichs in das Array
    Matrix = Range("A1:H800").Value
    
    For SpNr = 1 To 8  ' 1=A...8=H
       For ZeNr = 1 To 800
           Summe = Summe + Matrix(ZeNr, SpNr)
       Next ZeNr
    Next SpNr
    
    ' Ausgabe des Ergebnisses / Rückgabewert
    Berechne3 = Summe
End Function

Das Beispiel wurde der Einfachheit halber gewählt. Wie oben bereits erwähnt, würde man eine reine Summenberechnung mit einer Tabellenfunktion in VBA abbilden.

Array-Formeln

Setzen Sie temporäre Excel-Array-Formeln zur Matrixberechnung ein. Wenn Sie in VBA zwei Zellbereiche auf Übereinstimmung überprüfen wollen, müssen Sie einzelne Zellvergleiche vornehmen. Mit Einsatz einer Excel-Array-Formel sind Sie schneller. Im nachfolgenden Code werden zwei große Zellbereiche auf Übereinstimmung überprüft. Über VBA müsste man jede einzelne Zelle des einen mit der des anderen Bereiches vergleichen. Die Excel-Array-Formel liefert das Ergebnis unmittelbar nach dem Aufruf:

Function MatrixVergleich(strA As String, strB As String) As Boolean
    Range("IV1").FormulaArray = "=SUM((" & strA & "=" & strB & ")*1)"
    If Range("IV1").Value - Range(strA).Cells.Count = 0 Then
        MatrixVergleich = True
    End If
    Range("IV1").ClearContents
End Function

Sub Aufruf()
    MsgBox MatrixVergleich("C1:D15662", "E1:F15662")
End Sub

Zellbereiche schnell bearbeiten

Wenn eine Funktion einen Zellbereich abarbeiten soll, so kann dies sehr lange dauern, wenn der Bereich unnötig groß ist.

Beispiel: Der Benutzer möchte mit einer VBA-Funktion die Zellen A1:C300 bearbeiten. Das könnte so gehen:

1. Der Benutzer markiert den Bereich A1:C300
2. Der Benutzer startet ein Makro, wie das folgende AlleZellenBearbeiten():

Public Sub AlleZellenBearbeiten()
    Dim Zelle As Range
    On Error GoTo WeiterNächsteZelle

    For Each Zelle In Selection.Cells
        ' Jede Zelle einzeln bearbeiten
        Zelle.Value = ...
        
WeiterNächsteZelle: ' Sprung hierher bedeutet: Eine Zelle wegen Fehler nicht bearbeitet
    Next Zelle
End Sub

Oft ist es für den Benutzer einfacher, die Spalten A:C zu markieren, statt bis zum Ende des Bereiches zu scrollen und alles bis zur Zelle C300 zu markieren. Doch dadurch wird die Funktion sehr lange brauchen, da insbesondere ab Office 2007 3 Millionen Zellen markiert wurden. Im Makro kann man hier die Bearbeitung auf den benutzten Bereich einschränken. Dazu wird mit Intersect die Schnittmenge aus Markierung (Selection) und benutztem Bereich (UsedRange) als Bearbeitungsbereich festgelegt. Dabei ist der von Intersect zurückgegebene Bereich der jeweils kleinere Bereich:

Public Sub AlleZellenBearbeiten()
    Dim Zelle As Range
    On Error GoTo WeiterNächsteZelle
    For Each Zelle In Intersect(Selection, UsedRange).Cells
        ' Jede Zelle einzeln bearbeiten
        Zelle.Value = ...

WeiterNächsteZelle: ' Sprung hierher bedeutet: Eine Zelle wegen Fehler nicht bearbeitet
    Next Zelle
End Sub

Eine weitere Möglichkeit, der zu bearbeitenden Bereich einzuschränken und Abbrüche wegen Fehlern zu vermeiden, besteht darin, die Auswahl des Benutzers noch einmal auf den Zelltyp mit der Methode SpecialCells() einzuschränken. Wenn nur Werte, aber keine Formeln geändert werden sollen, schränkt man den Bereich mit SpecialCells(xlCellTypeConstants) ein:

Public Sub AlleZellenBearbeiten()
    Dim Zelle As Range, Bereich As Range
    
    On Error GoTo NichtsGefunden
    ' Wenn SpecialCells einen Fehler liefert, gibt es nichts zu bearbeiten
    Set Bereich = Selection.Cells.SpecialCells(xlCellTypeConstants)
    
    On Error GoTo WeiterNächsteZelle
    For Each Zelle In Bereich
        ' Jede Zelle einzeln bearbeiten
        Zelle.Value = ...

WeiterNächsteZelle: ' Sprung hierher bedeutet: Eine Zelle wegen Fehler nicht bearbeitet
    Next Zelle

NichtsGefunden: ' Sprung hierher bedeutet: Gar keine Aktion ausgeführt
End Sub

Durch SpecialCells(xlCellTypeConstants, xlTextValues + xlNumbers) werden nur noch Zahlen und Texte bearbeitet, Zellen mit logischen Werten (WAHR, FALSCH) oder Fehlerwerten bleiben außen vor.

Vorsicht: Wenn SpecialCells() keine Zellen findet, führt dies nicht etwa zu einem leeren Objekt, sondern zu einem Fehler, der nur mit On Error ... abgefangen werden kann.

Neuberechnung der Zellinhalte

Excel unterscheidet zwischen drei Berechnungsmodi:

• Automatisch
• Automatisch außer bei Datentabellen
• Manuell

Wollen Sie beispielsweise eine große Tabelle mit Ergebnissen aus Ihrem VBA-Code befüllen, so wird Excel im automatischen Modus nach jedem Eintrag das gesamte Dokument neu berechnen. Nutzen Sie daher folgenden Code, um die Neuberechnung aller Inhalte nur einmal am Ende auszuführen:

Sub TabelleBefuellen()
      'lokale Variablen deklarieren
      '...
   
   'Berechnungsmodus in manuell ändern
    Application.Calculation = xlCalculationManual
      
      'Ihr VBA Code: Tabelle befüllen
      'cells(x,y)= ...
      
   'Berechnungsmodus in automatisch ändern
   Application.Calculation = xlCalculationAutomatic
   
End Sub

Im manuellen Modus hingegen müssen Sie die Neuberechnung der Zellen manuell ausführen:

ActiveSheet.EnableCalculation = True
ActiveSheet.Calculate

Bemerkung: Die Neuberechnung des gesamten Dokuments nach einem Zelleintrag kann >1 Sekunde dauern, was bei Tabellen mit 1000 Zeilen und 10 Spalten über 10.000 Sekunden, also deutlich über 2 Stunden dauert!

Einzelnachweise

| Hoch zu „Inhaltsverzeichnis“ |