excel-vba
VBA-Best Practices

Bemerkungen

Verwenden Sie IMMER "Option Explicit"

Wählen Sie im VBA-Editor-Fenster im Menü "Extras" die Option "Optionen":

Stellen Sie dann auf der Registerkarte "Editor" sicher, dass "Variablendeklaration erforderlich" aktiviert ist:

Wenn Sie diese Option auswählen, wird die Option Explicit automatisch an der Spitze jedes VBA-Moduls angezeigt.

Kleiner Hinweis: Dies gilt für die bisher noch nicht geöffneten Module, Klassenmodule usw. Wenn Sie sich beispielsweise bereits den Code von Sheet1 bevor Sie die Option "Deklaration der Variablen erforderlich" aktivieren, wird Option Explicit nicht hinzugefügt!

Option Explicit muss jede Variable vor der Verwendung definiert werden, z. B. mit einer Dim Anweisung. Wenn die Option Explicit aktiviert ist, nimmt der VBA-Compiler an, dass ein nicht erkanntes Wort eine neue Variable des Variant Typs ist. Dies führt zu äußerst schwer zu findenden Fehlern im Zusammenhang mit Tippfehlern. Option Explicit die Option Explicit aktiviert ist, wird bei nicht erkannten Wörtern ein Kompilierungsfehler ausgegeben, der die fehlerhafte Zeile anzeigt.

Beispiel:

Wenn Sie den folgenden Code ausführen:

Sub Test()
  my_variable = 12
  MsgBox "My Variable is : " & myvariable
End Sub

Sie erhalten folgende Nachricht:

Sie haben einen Fehler gemacht, indem Sie myvariable anstelle von my_variable haben. Das Meldungsfeld zeigt dann eine leere Variable an. Wenn Sie die Option Explicit , ist dieser Fehler nicht möglich, da eine Fehlermeldung zum Kompilieren angezeigt wird.

Wenn Sie jetzt die korrekte Deklaration hinzufügen:

Sub Test()
  Dim my_variable As Integer
  my_variable = 12
  MsgBox "My Variable is : " & myvariable
End Sub

Mit myvariable erhalten Sie eine Fehlermeldung, die den Fehler genau myvariable :

Hinweis zu Option Explicit und Arrays ( Deklarieren eines dynamischen Arrays ):

Mit der ReDim-Anweisung können Sie ein Array implizit innerhalb einer Prozedur deklarieren.

• Achten Sie darauf, dass Sie den Namen des Arrays nicht falsch eingeben, wenn Sie die ReDim-Anweisung verwenden

• Auch wenn die Option Explicit-Anweisung im Modul enthalten ist, wird ein neues Array erstellt

  Dim arr() as Long

  ReDim ar() 'creates new array "ar" - "ReDim ar()" acts like "Dim ar()"

Arbeit mit Arrays, nicht mit Ranges

Office-Blog - Best Practices für die Excel VBA-Leistungscodierung

Oft wird die beste Leistung erzielt, indem der Einsatz von Range so weit wie möglich vermieden wird. In diesem Beispiel lesen wir ein gesamtes Range Objekt in ein Array ein, quadrieren jede Zahl im Array und bringen das Array dann wieder zum Range . Dies greift nur zweimal auf Range , wohingegen eine Schleife für das Lesen / Schreiben 20-mal darauf zugreifen würde.

Option Explicit
Sub WorkWithArrayExample()
    
Dim DataRange As Variant
Dim Irow As Long
Dim Icol As Integer
DataRange = ActiveSheet.Range("A1:A10").Value ' read all the values at once from the Excel grid, put into an array

For Irow = LBound(DataRange,1) To UBound(DataRange, 1) ' Get the number of rows.
  For Icol = LBound(DataRange,2) To UBound(DataRange, 2) ' Get the number of columns.
    DataRange(Irow, Icol) = DataRange(Irow, Icol) * DataRange(Irow, Icol) ' cell.value^2
  Next Icol
Next Irow
ActiveSheet.Range("A1:A10").Value = DataRange ' writes all the results back to the range at once
    
End Sub

Weitere Tipps und Informationen zu zeitgesteuerten Beispielen finden Sie in Charles Williams 'VBA-UDFs für Schreiben (Teil 1) und anderen Artikeln der Serie .

Verwenden Sie, falls verfügbar, VB-Konstanten

If MsgBox("Click OK") = vbOK Then

kann anstelle von verwendet werden

If MsgBox("Click OK") = 1 Then

um die Lesbarkeit zu verbessern.

Verwenden Sie den Objektbrowser , um verfügbare VB-Konstanten zu finden. Ansicht → Objektbrowser oder F2 im VB-Editor.

Geben Sie die zu suchende Klasse ein

Mitglieder anzeigen

Benennen Sie beschreibende Variablen

Beschreibende Namen und Strukturen in Ihrem Code helfen, Kommentare zu machen

Dim ductWidth  As Double
Dim ductHeight As Double
Dim ductArea   As Double

ductArea = ductWidth * ductHeight

ist besser als

Dim a, w, h

a = w * h

Dies ist besonders hilfreich, wenn Sie Daten von einem Ort an einen anderen kopieren, egal ob es sich um eine Zelle, einen Bereich, ein Arbeitsblatt oder eine Arbeitsmappe handelt. Helfen Sie sich, indem Sie Namen wie diese verwenden:

Dim myWB As Workbook
Dim srcWS As Worksheet
Dim destWS As Worksheet
Dim srcData As Range
Dim destData As Range

Set myWB = ActiveWorkbook
Set srcWS = myWB.Sheets("Sheet1")
Set destWS = myWB.Sheets("Sheet2")
Set srcData = srcWS.Range("A1:A10")
Set destData = destWS.Range("B11:B20")
destData = srcData

Wenn Sie mehrere Variablen in einer Zeile deklarieren, müssen Sie für jede Variable einen Typ angeben, wie:

Dim ductWidth As Double, ductHeight As Double, ductArea As Double

Im Folgenden wird nur die letzte Variable deklariert, und die erste Variable bleibt Variant :

Dim ductWidth, ductHeight, ductArea As Double

Fehlerbehandlung

Eine gute Fehlerbehandlung verhindert, dass Endbenutzer VBA-Laufzeitfehler erkennen, und hilft dem Entwickler, Fehler leicht zu diagnostizieren und zu beheben.

Es gibt drei Hauptmethoden für die Fehlerbehandlung in VBA, von denen zwei für verteilte Programme vermieden werden sollten, sofern dies nicht ausdrücklich im Code erforderlich ist.

On Error GoTo 0 'Avoid using

oder

On Error Resume Next 'Avoid using

Lieber verwenden:

On Error GoTo <line> 'Prefer using

On Error GoTo 0

Wenn in Ihrem Code keine Fehlerbehandlung festgelegt ist, ist On Error GoTo 0 der Standardfehlerhandler. In diesem Modus wird bei Laufzeitfehlern die typische VBA-Fehlermeldung angezeigt, sodass Sie den Code entweder beenden oder in den debug Modus wechseln können, um die Quelle zu identifizieren. Beim Schreiben von Code ist diese Methode die einfachste und nützlichste Methode. Sie sollte jedoch immer für Code vermieden werden, der an Endbenutzer verteilt wird, da diese Methode für Endbenutzer sehr unansehnlich und schwer verständlich ist.

On Error Resume Next

On Error Resume Next bewirkt, dass VBA alle Fehler ignoriert, die zur Laufzeit für alle Zeilen nach dem Fehleraufruf ausgegeben werden, bis der Fehlerbehandler geändert wurde. In sehr speziellen Fällen kann diese Zeile nützlich sein, sollte aber außerhalb dieser Fälle vermieden werden. Wenn Sie beispielsweise ein separates Programm aus einem Excel-Makro starten, kann der Aufruf von On Error Resume Next hilfreich sein, wenn Sie nicht sicher sind, ob das Programm bereits geöffnet ist oder nicht:

'In this example, we open an instance of Powerpoint using the On Error Resume Next call
Dim PPApp As PowerPoint.Application
Dim PPPres As PowerPoint.Presentation
Dim PPSlide As PowerPoint.Slide

'Open PPT if not running, otherwise select active instance
On Error Resume Next
Set PPApp = GetObject(, "PowerPoint.Application")
On Error GoTo ErrHandler
If PPApp Is Nothing Then
    'Open PowerPoint
    Set PPApp = CreateObject("PowerPoint.Application")
    PPApp.Visible = True
End If

Wenn wir den Aufruf On Error Resume Next nicht verwendet hätten und die Powerpoint-Anwendung nicht bereits geöffnet war, würde die GetObject Methode einen Fehler GetObject . Daher war On Error Resume Next erforderlich, um zu vermeiden, dass zwei Instanzen der Anwendung erstellt werden.

Hinweis: Es empfiehlt sich auch, den Fehlerbehandler sofort zurückzusetzen, sobald Sie den Aufruf On Error Resume Next nicht mehr benötigen

On Error GoTo <Zeile>

Diese Methode zur Fehlerbehandlung wird für den gesamten Code empfohlen, der an andere Benutzer verteilt wird. Dadurch kann der Programmierer genau steuern, wie VBA einen Fehler behandelt, indem er den Code an die angegebene Zeile sendet. Das Tag kann mit einer beliebigen Zeichenfolge (einschließlich numerischer Zeichenfolgen) gefüllt werden und sendet den Code an die entsprechende Zeichenfolge, auf die ein Doppelpunkt folgt. Mehrere Fehlerbehandlungsblöcke können verwendet werden, indem On Error GoTo <line> . Die folgende Subroutine veranschaulicht die Syntax eines On Error GoTo <line> -Aufrufs.

Hinweis: Es ist wichtig, dass die Exit Sub Zeile über dem ersten Error-Handler und vor jedem nachfolgenden Error-Handler platziert wird, um zu verhindern, dass der Code in den Block eintritt, ohne dass ein Fehler aufgerufen wird. Daher ist es für Funktion und Lesbarkeit empfehlenswert, Fehlerbehandlungsroutinen am Ende eines Codeblocks zu platzieren.

Sub YourMethodName()
    On Error GoTo errorHandler
    ' Insert code here
    On Error GoTo secondErrorHandler

    Exit Sub 'The exit sub line is essential, as the code will otherwise
             'continue running into the error handling block, likely causing an error

errorHandler:
    MsgBox "Error " & Err.Number & ": " & Err.Description & " in " & _
        VBE.ActiveCodePane.CodeModule, vbOKOnly, "Error"
    Exit Sub

secondErrorHandler:
    If Err.Number = 424 Then 'Object not found error (purely for illustration)
        Application.ScreenUpdating = True
        Application.EnableEvents = True
        Exit Sub
    Else
        MsgBox "Error " & Err.Number & ": " & Err.Desctription
        Application.ScreenUpdating = True
        Application.EnableEvents = True   
        Exit Sub
    End If      
    Exit Sub

End Sub

Wenn Sie Ihre Methode mit Ihrem Fehlerbehandlungscode beenden, stellen Sie sicher, dass Sie bereinigen:

• Machen Sie alles rückgängig, was teilweise abgeschlossen ist
• Dateien schließen
• Bildschirmaktualisierung zurücksetzen
• Berechnungsmodus zurücksetzen
• Ereignisse zurücksetzen
• Setzen Sie den Mauszeiger zurück
• Ruft die Unload-Methode für Instanzen von Objekten auf, die nach dem End Sub bestehen bleiben
• Statusleiste zurücksetzen

Dokumentieren Sie Ihre Arbeit

Es empfiehlt sich, Ihre Arbeit für die spätere Verwendung zu dokumentieren, insbesondere wenn Sie für eine dynamische Arbeitslast programmieren. Gute Kommentare sollten erklären, warum der Code etwas tut, nicht was er tut.

Function Bonus(EmployeeTitle as String) as Double
    If EmployeeTitle = "Sales" Then
        Bonus = 0    'Sales representatives receive commission instead of a bonus
    Else
        Bonus = .10
    End If
End Function

Wenn Ihr Code so dunkel ist, dass er Kommentare benötigt, um zu erklären, was er tut, sollten Sie ihn umschreiben. Zum Beispiel anstelle von:

Sub CopySalesNumbers
    Dim IncludeWeekends as Boolean
    
    'Boolean values can be evaluated as an integer, -1 for True, 0 for False.
    'This is used here to adjust the range from 5 to 7 rows if including weekends.
    Range("A1:A" & 5 - (IncludeWeekends * 2)).Copy
    Range("B1").PasteSpecial
End Sub

Verdeutlichen Sie den Code, um leichter zu folgen, z.

Sub CopySalesNumbers
    Dim IncludeWeekends as Boolean
    Dim DaysinWeek as Integer
    
    If IncludeWeekends Then
        DaysinWeek = 7
    Else
        DaysinWeek = 5
    End If
    Range("A1:A" & DaysinWeek).Copy
    Range("B1").PasteSpecial
End Sub 

Eigenschaften während der Makroausführung ausschalten

In jeder Programmiersprache wird empfohlen, vorzeitige Optimierung zu vermeiden. Wenn sich jedoch beim Testen herausstellt, dass Ihr Code zu langsam ausgeführt wird, können Sie etwas Geschwindigkeit erreichen, indem Sie einige Eigenschaften der Anwendung deaktivieren, während sie ausgeführt wird. Fügen Sie diesen Code einem Standardmodul hinzu:

Public Sub SpeedUp( _
    SpeedUpOn As Boolean, _
    Optional xlCalc as XlCalculation = xlCalculationAutomatic _
)
    With Application
        If SpeedUpOn Then
            .ScreenUpdating = False
            .Calculation = xlCalculationManual
            .EnableEvents = False
            .DisplayStatusBar = False 'in case you are not showing any messages
            ActiveSheet.DisplayPageBreaks = False 'note this is a sheet-level setting
        Else
            .ScreenUpdating = True
            .Calculation = xlCalc
            .EnableEvents = True
            .DisplayStatusBar = True
            ActiveSheet.DisplayPageBreaks = True
        End If
    End With
End Sub

Weitere Informationen finden Sie im Office Blog - Best Practices für die Excel VBA-Leistungscodierung

Und nennen Sie es einfach am Anfang und Ende von Makros:

Public Sub SomeMacro
    'store the initial "calculation" state
    Dim xlCalc As XlCalculation
    xlCalc = Application.Calculation

    SpeedUp True

    'code here ...

    'by giving the second argument the initial "calculation" state is restored
    'otherwise it is set to 'xlCalculationAutomatic'
    SpeedUp False, xlCalc
End Sub

Während diese weitestgehend als "Verbesserungen" für reguläre Public Sub Prozeduren betrachtet werden können, sollte das Deaktivieren der Ereignisbehandlung mit Application.EnableEvents = False für obligatorische private Ereignismakros Worksheet_Change und Workbook_SheetChange , die Werte in einem oder mehreren Arbeitsblättern ändern. Wenn Sie die Ereignisauslöser nicht deaktivieren, wird das Ereignismakro rekursiv über sich selbst ausgeführt, wenn sich ein Wert ändert. Dies kann zu einer "eingefrorenen" Arbeitsmappe führen. Denken Sie daran, Ereignisse wieder zu aktivieren, bevor Sie das Ereignismakro verlassen, möglicherweise durch einen Fehlerhandler "Safe Exit".

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("A:A")) Is Nothing Then
        On Error GoTo bm_Safe_Exit
        Application.EnableEvents = False
        
        'code that may change a value on the worksheet goes here
        
    End If
bm_Safe_Exit:
    Application.EnableEvents = True
End Sub

Ein Nachteil: Durch das Deaktivieren dieser Einstellungen wird zwar die Laufzeit verbessert, das Debugging der Anwendung kann jedoch schwieriger werden. Wenn Ihr Code nicht ordnungsgemäß funktioniert, kommentieren Sie den SpeedUp True Aufruf aus, bis Sie das Problem gefunden haben.

Dies ist besonders wichtig, wenn Sie in Zellen in einem Arbeitsblatt schreiben und dann die berechneten Ergebnisse von Arbeitsblattfunktionen xlCalculationManual da xlCalculationManual die Berechnung der xlCalculationManual verhindert. Um dies zu SpeedUp , ohne SpeedUp zu deaktivieren, sollten Sie Application.Calculate einschließen, um eine Berechnung an bestimmten Punkten auszuführen.

HINWEIS: Da es sich um Eigenschaften der Application selbst handelt, müssen Sie sicherstellen, dass sie erneut aktiviert werden, bevor Ihr Makro beendet wird. Dies macht es besonders wichtig, Fehlerbehandlungsroutinen zu verwenden und mehrere Ausstiegspunkte (z. B. End oder Unload Me ) zu vermeiden.

Mit Fehlerbehandlung:

Public Sub SomeMacro()
    'store the initial "calculation" state
    Dim xlCalc As XlCalculation
    xlCalc = Application.Calculation

    On Error GoTo Handler
    SpeedUp True
    
    'code here ...
    i = 1 / 0
CleanExit:
    SpeedUp False, xlCalc
    Exit Sub
Handler:
    'handle error
    Resume CleanExit
End Sub

Vermeiden Sie die Verwendung von ActiveCell oder ActiveSheet in Excel

Die Verwendung von ActiveCell oder ActiveSheet kann Fehler verursachen, wenn (aus irgendeinem Grund) der Code an der falschen Stelle ausgeführt wird.

ActiveCell.Value = "Hello" 
'will place "Hello" in the cell that is currently selected
Cells(1, 1).Value = "Hello" 
'will always place "Hello" in A1 of the currently selected sheet

ActiveSheet.Cells(1, 1).Value = "Hello" 
'will place "Hello" in A1 of the currently selected sheet
Sheets("MySheetName").Cells(1, 1).Value = "Hello" 
'will always place "Hello" in A1 of the sheet named "MySheetName"

• Die Verwendung von Active* kann zu Problemen in lang laufenden Makros führen, wenn sich Ihr Benutzer langweilt und auf ein anderes Arbeitsblatt klickt oder eine andere Arbeitsmappe öffnet.
• Es kann Probleme verursachen, wenn Ihr Code eine andere Arbeitsmappe öffnet oder erstellt.
• Wenn Ihr Code Sheets("MyOtherSheet").Select verwendet, kann dies zu Problemen führen Sheets("MyOtherSheet").Select Sie aus, und Sie haben vergessen, auf welchem ​​Blatt Sie sich befanden, bevor Sie mit dem Lesen beginnen oder darauf schreiben.

Nimm niemals das Arbeitsblatt an

Selbst wenn Ihre gesamte Arbeit auf ein einziges Arbeitsblatt gerichtet ist, empfiehlt es sich, das Arbeitsblatt explizit in Ihrem Code anzugeben. Diese Gewohnheit macht es viel einfacher, Ihren Code später zu erweitern oder Teile (oder alle) eines Sub oder einer Function anzuheben, um sie an einem anderen Ort wiederzuverwenden. Viele Entwickler machen es sich zur Gewohnheit, denselben lokalen Variablennamen für ein Arbeitsblatt in ihrem Code (erneut) zu verwenden, wodurch die Wiederverwendung dieses Codes noch einfacher wird.

Der folgende Code ist zum Beispiel mehrdeutig - funktioniert aber! - solange der Entwickler kein anderes Arbeitsblatt aktiviert oder geändert hat:

Option Explicit
Sub ShowTheTime()
    '--- displays the current time and date in cell A1 on the worksheet
    Cells(1, 1).Value = Now()  ' don't refer to Cells without a sheet reference!
End Sub

Wenn Sheet1 aktiv ist, wird Zelle A1 in Sheet1 mit dem aktuellen Datum und der aktuellen Uhrzeit gefüllt. Wenn der Benutzer jedoch Arbeitsblätter aus irgendeinem Grund ändert, wird der Code aktualisiert, unabhängig davon, welches Arbeitsblatt gerade aktiv ist. Das Zielarbeitsblatt ist mehrdeutig.

Es empfiehlt sich, immer zu ermitteln, auf welches Arbeitsblatt sich Ihr Code bezieht:

Option Explicit
Sub ShowTheTime()
    '--- displays the current time and date in cell A1 on the worksheet
    Dim myWB As Workbook
    Set myWB = ThisWorkbook
    Dim timestampSH As Worksheet
    Set timestampSH = myWB.Sheets("Sheet1")
    timestampSH.Cells(1, 1).Value = Now()
End Sub

Der obige Code ist eindeutig bei der Identifizierung der Arbeitsmappe und des Arbeitsblatts. Obwohl es wie ein Overkill klingt, werden Sie sich vor zukünftigen Problemen bewahren, wenn Sie sich eine gute Gewohnheit in Bezug auf die Zielreferenzen schaffen.

Vermeiden Sie SELECT oder ACTIVATE

Es ist sehr selten, dass Sie Select oder Activate in Ihrem Code verwenden möchten. Einige Excel-Methoden erfordern jedoch die Aktivierung eines Arbeitsblatts oder einer Arbeitsmappe, bevor sie wie erwartet funktionieren.

Wenn Sie gerade erst anfangen, VBA zu lernen, wird Ihnen oft empfohlen, Ihre Aktionen mit dem Makro-Recorder aufzuzeichnen, und schauen Sie sich dann den Code an. Ich habe beispielsweise Aktionen aufgezeichnet, die zur Eingabe eines Werts in Zelle D3 in Sheet2 ausgeführt wurden, und der Makrocode sieht folgendermaßen aus:

Option Explicit
Sub Macro1()
'
' Macro1 Macro
'

'
    Sheets("Sheet2").Select
    Range("D3").Select
    ActiveCell.FormulaR1C1 = "3.1415"   '(see **note below)
    Range("D4").Select
End Sub

Denken Sie jedoch daran, dass der Makrorecorder für jede Ihrer (Benutzer-) Aktionen eine Codezeile erstellt. Dazu müssen Sie auf die Registerkarte des Arbeitsblatts klicken, um Sheet2 ( Sheets("Sheet2").Select ) auszuwählen Sheets("Sheet2").Select Klicken Sie auf Zelle D3, bevor Sie den Wert ( Range("D3").Select Sheets("Sheet2").Select Range("D3").Select ) und die Eingabetaste (was effektiv ist). Auswahl "der Zelle unter der aktuell ausgewählten Zelle: Range("D4").Select ).

Es gibt mehrere Probleme bei der Verwendung von .Select hier:

• Das Arbeitsblatt ist nicht immer angegeben. Dies geschieht, wenn Sie während der Aufnahme nicht zwischen Arbeitsblättern wechseln. Dies bedeutet, dass der Code für verschiedene aktive Arbeitsblätter zu unterschiedlichen Ergebnissen führt.
• .Select() ist langsam. Auch wenn Application.ScreenUpdating auf False , handelt es sich um einen nicht mehr zu verarbeitenden Vorgang.
• .Select() ist nicht .Select() . Wenn Application.ScreenUpdating auf True belassen wird, werden in Excel tatsächlich die Zellen, das Arbeitsblatt, das Formular usw. ausgewählt. Das ist anstrengend für die Augen und wirklich unangenehm anzusehen.
• .Select() löst Listener aus. Dies ist bereits ein wenig fortgeschritten, aber wenn dies nicht Worksheet_SelectionChange() wird, werden Funktionen wie Worksheet_SelectionChange() ausgelöst.

Wenn Sie in VBA codieren, sind alle "Typisierungsaktionen" (dh Select Anweisungen) nicht mehr erforderlich. Ihr Code kann auf eine einzige Anweisung reduziert werden, um den Wert in die Zelle einzufügen:

'--- GOOD
ActiveWorkbook.Sheets("Sheet2").Range("D3").Value = 3.1415

'--- BETTER
Dim myWB      As Workbook
Dim myWS      As Worksheet
Dim myCell    As Range

Set myWB = ThisWorkbook             '*** see NOTE2
Set myWS = myWB.Sheets("Sheet2")
Set myCell = myWS.Range("D3")

myCell.Value = 3.1415

(Das BETTER-Beispiel oben zeigt die Verwendung von Zwischenvariablen zum Trennen verschiedener Teile der Zellreferenz. Das GOOD-Beispiel funktioniert immer einwandfrei, kann jedoch bei viel längeren Codemodulen sehr umständlich sein und ist schwieriger zu debuggen, wenn eine der Referenzen falsch eingegeben wird. )

** HINWEIS: Der Makrorekorder nimmt viele Annahmen über den Datentyp an, den Sie eingeben. In diesem Fall geben Sie einen Zeichenfolgenwert als Formel ein, um den Wert zu erstellen. Ihr Code muss dies nicht tun und kann der Zelle einfach einen numerischen Wert wie oben gezeigt direkt zuweisen.

** HINWEIS2: Die empfohlene Vorgehensweise besteht darin, Ihre lokale Arbeitsmappenvariable auf ThisWorkbook statt auf ActiveWorkbook (sofern Sie dies nicht ausdrücklich benötigen). Der Grund dafür ist, dass Ihr Makro im Allgemeinen Ressourcen in der Arbeitsmappe benötigt bzw. verwendet, die der VBA-Code erzeugt, und NICHT außerhalb der Arbeitsmappe aussieht - auch wenn Sie Ihren Code nicht ausdrücklich dazu auffordern, mit einer anderen Arbeitsmappe zu arbeiten. Wenn Sie mehrere Arbeitsmappen in Excel geöffnet haben, hat ActiveWorkbook den Fokus, der sich von der Arbeitsmappe unterscheiden kann, die in Ihrem VBA-Editor angezeigt wird . Sie glauben also, Sie führen eine Arbeitsmappe aus, wenn Sie wirklich auf eine andere Arbeitsmappe verweisen. ThisWorkbook Arbeitsbuch bezieht sich auf die Arbeitsmappe, die den ausgeführten Code enthält.

Definieren und setzen Sie immer Verweise auf alle Arbeitsmappen und Arbeitsblätter

Wenn Sie mit mehreren offenen Arbeitsmappen arbeiten, von denen jede mehrere Arbeitsblätter haben kann, ist es am sichersten, alle Arbeitsmappen und Arbeitsblätter zu definieren und einen Verweis darauf zu setzen.

ActiveWorkbook Sie sich nicht auf ActiveWorkbook oder ActiveSheet da diese möglicherweise vom Benutzer geändert werden.

Das folgende Codebeispiel zeigt , wie eine Reihe von „RAW_DATA“ Blatt in dem „Data.xlsx“ Arbeitsmappe „Refined_Data“ Blatt in der „Results.xlsx“ Arbeitsmappe kopieren.

Das Verfahren veranschaulicht auch, wie Sie ohne die Select Methode kopieren und einfügen.

Option Explicit

Sub CopyRanges_BetweenShts()

    
    Dim wbSrc                           As Workbook
    Dim wbDest                          As Workbook
    Dim shtCopy                         As Worksheet
    Dim shtPaste                        As Worksheet
    
    ' set reference to all workbooks by name, don't rely on ActiveWorkbook
    Set wbSrc = Workbooks("Data.xlsx")
    Set wbDest = Workbooks("Results.xlsx")
    
    ' set reference to all sheets by name, don't rely on ActiveSheet
    Set shtCopy = wbSrc.Sheet1 '// "Raw_Data" sheet
    Set shtPaste = wbDest.Sheet2 '// "Refined_Data") sheet
    
    ' copy range from "Data" workbook to "Results" workbook without using Select
    shtCopy.Range("A1:C10").Copy _
    Destination:=shtPaste.Range("A1")

End Sub

Das WorksheetFunction-Objekt wird schneller als ein UDF-Äquivalent ausgeführt

VBA wird zur Laufzeit kompiliert, was die Leistung erheblich beeinträchtigt. Alles integrierte wird schneller. Versuchen Sie, sie zu verwenden.

Als Beispiel vergleiche ich die SUM- und COUNTIF-Funktionen, aber Sie können sie verwenden, wenn Sie etwas mit WorkSheetFunctions lösen können.

Ein erster Versuch für diese wäre, den Bereich zu durchlaufen und ihn Zelle für Zelle (mit einem Bereich) zu verarbeiten:

Sub UseRange()
    Dim rng as Range
    Dim Total As Double
    Dim CountLessThan01 As Long
    
    Total = 0
    CountLessThan01 = 0
    For Each rng in Sheets(1).Range("A1:A100")
        Total = Total + rng.Value2
        If rng.Value < 0.1 Then
            CountLessThan01 = CountLessThan01 + 1
        End If
    Next rng
    Debug.Print Total & ", " & CountLessThan01
End Sub

Eine Verbesserung kann darin bestehen, die Bereichswerte in einem Array zu speichern und Folgendes zu verarbeiten:

Sub UseArray()
    Dim DataToSummarize As Variant
    Dim i As Long
    Dim Total As Double
    Dim CountLessThan01 As Long
    
    DataToSummarize = Sheets(1).Range("A1:A100").Value2 'faster than .Value
    Total = 0
    CountLessThan01 = 0
    For i = 1 To 100
        Total = Total + DataToSummarize(i, 1)
        If DataToSummarize(i, 1) < 0.1 Then
            CountLessThan01 = CountLessThan01 + 1
        End If
    Next i
    Debug.Print Total & ", " & CountLessThan01
End Sub

Anstatt jedoch eine Schleife zu schreiben, können Sie die Anwendung Application.Worksheetfunction der Sie einfache Formeln ausführen können:

Sub UseWorksheetFunction()
    Dim Total As Double
    Dim CountLessThan01 As Long
    
    With Application.WorksheetFunction
        Total = .Sum(Sheets(1).Range("A1:A100"))
        CountLessThan01 = .CountIf(Sheets(1).Range("A1:A100"), "<0.1")
    End With
    
    Debug.Print Total & ", " & CountLessThan01
End Sub

Bei komplexeren Berechnungen können Sie sogar Application.Evaluate :

Sub UseEvaluate()
    Dim Total As Double
    Dim CountLessThan01 As Long
    
    With Application
        Total = .Evaluate("SUM(" & Sheet1.Range("A1:A100").Address( _
            external:=True) & ")")
        CountLessThan01 = .Evaluate("COUNTIF('Sheet1'!A1:A100,""<0.1"")")
    End With
    
    Debug.Print Total & ", " & CountLessThan01
End Sub

Und schließlich läuft man über 25.000 Mal über Subs. Hier ist die durchschnittliche Zeit (5 Tests) in Millisekunden (natürlich ist dies auf jedem PC anders, aber im Vergleich zueinander verhalten sie sich ähnlich)

1. UseWorksheetFunction: 2156 ms
2. UseArray: 2219 ms (+ 3%)
3. UseEvaluate: 4693 ms (+ 118%)
4. Nutzungsbereich: 6530 ms (+ 203%)

Vermeiden Sie, die Namen von Eigenschaften oder Methoden als Variablen zu verwenden

Es wird im Allgemeinen nicht als "bewährte Methode" betrachtet, die reservierten Namen von Eigenschaften oder Methoden als Namen Ihrer eigenen Prozeduren und Variablen zu verwenden.

Bad Form - Während die folgende ist (streng genommen) legal, Code arbeiten , um die Umwidmung der Suche Methode sowie die Zeile , Spalte und Adress Eigenschaften können Probleme / Konflikte mit Namen Mehrdeutigkeit verursacht und ist einfach nur verwirrend im Allgemeinen.

Option Explicit

Sub find()
    Dim row As Long, column As Long
    Dim find As String, address As Range
    
    find = "something"
    
    With ThisWorkbook.Worksheets("Sheet1").Cells
        Set address = .SpecialCells(xlCellTypeLastCell)
        row = .find(what:=find, after:=address).row        '< note .row not capitalized
        column = .find(what:=find, after:=address).column  '< note .column not capitalized
        
        Debug.Print "The first 'something' is in " & .Cells(row, column).address(0, 0)
    End With
End Sub

Gute Form : Da alle reservierten Wörter in nahe, aber eindeutige Näherungen der Originale umbenannt wurden, wurden mögliche Namenskonflikte vermieden.

Option Explicit

Sub myFind()
    Dim rw As Long, col As Long
    Dim wht As String, lastCell As Range
    
    wht = "something"
    
    With ThisWorkbook.Worksheets("Sheet1").Cells
        Set lastCell = .SpecialCells(xlCellTypeLastCell)
        rw = .Find(What:=wht, After:=lastCell).Row         '◄ note .Find and .Row
        col = .Find(What:=wht, After:=lastCell).Column     '◄ .Find and .Column
        
        Debug.Print "The first 'something' is in " & .Cells(rw, col).Address(0, 0)
    End With
End Sub

Es kann vorkommen, dass Sie eine Standardmethode oder -eigenschaft absichtlich nach Ihren eigenen Vorstellungen neu schreiben möchten, doch es gibt nur wenige Situationen. Bleiben Sie größtenteils fern von reservierten Namen für Ihre eigenen Konstrukte.