VBA in Excel/ Code-Optimierung

Aus Wikibooks
Zur Navigation springen Zur Suche springen
| One wikibook.svg Hoch zu „Inhaltsverzeichnis“ |


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

Konstanten[Bearbeiten]

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

Objektindex[Bearbeiten]

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:Next

ist schneller als

Worksheets(intCounter)

Direkte Objektzuweisungen[Bearbeiten]

Verwenden Sie nach Möglichkeit keine allgemeinen Objektzuweisungen wie:

Dim wksData As Object

Deklarieren Sie so genau wie möglich:

Dim wksData As Worksheet

Die zweite Art der Deklaration ist nicht immer möglich, denn sie setzt voraus, dass die Bibliothek über die Verweise (References) eingebunden ist. Die zweite Art der Deklaration hat auch 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.

Selektieren[Bearbeiten]

Wählen Sie keine Arbeitsmappen, Blätter, Bereiche oder andere Objekte aus:

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

Keine eckigen Klammern[Bearbeiten]

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[Bearbeiten]

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[Bearbeiten]

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[Bearbeiten]

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

Excel-Funktionen[Bearbeiten]

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:

Sub Berechne3()
    dim a
    dim i as long, j as long, sum as long
    a = me.Range("A1:H800").value
    for i=1 to 8
       for j=1 to 800
           sum=sum+a(j,i) ' a(ZeilenNr, SpaltenNr)
       next j
    next i
    debug.print sum    
End Sub

Array-Formeln[Bearbeiten]

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[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[Bearbeiten]

Excel unterscheidet zwischen drei Berechnungsmodi: [1]

  • 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[Bearbeiten]

  1. [1] MSDN VBA Excel-Neuberechnung
| One wikibook.svg Hoch zu „Inhaltsverzeichnis“ |