HERBERS Excel-Forum - VBA-Basics

Thema: Code-Optimierung

Inhaltsverzeichnis
  • 1 Konstanten
  • 2 Objektindex
  • 3 Direkte Objektzuweisungen
  • 4 Selektieren
  • 5 Keine eckigen Klammern
  • 6 Direkte Referenzierung
  • 7 Dimensionierung
  • 8 With-Rahmen
  • 9 Excel-Funktionen
  • 10 Array-Formeln
  • 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

    
    wksData
    

    ist schneller als

    
    Worksheets(intCounter)
    


    Direkte Objektzuweisungen

    Verwenden Sie keine allgemeinen Objektzuweisungen wie:

    
    Dim wksData As Objekt
    

    Deklarieren Sie korrekt:

    
    Dim wksData As Worksheet
    

    Selektieren

    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

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

    
    [b3] = [d4]
    

    Schreiben Sie stattdessen:

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

    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 größer als dies erforderlich ist:

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

    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
    

    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 Zellbereich 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
    

    Array-Formeln

    Setzen Sie temporäre Excel-Array-Formeln zur Matrixberechnung ein. Wenn Sie in VBA zwei Zellbereich 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