excel-vba
Tipps und Tricks zu Excel VBA

Bemerkungen

Verwenden von xlVeryHidden Sheets

Arbeitsblätter in Excel haben drei Optionen für die Eigenschaft Visible . Diese Optionen werden durch Konstanten in der xlSheetVisibility Enumeration dargestellt und lauten wie folgt:

1. xlVisible oder xlSheetVisible Wert: -1 (Standardeinstellung für neue xlSheetVisible )
2. xlHidden oder xlSheetHidden Wert: 0
3. xlVeryHidden xlSheetVeryHidden Wert: 2

Sichtbare Blätter repräsentieren die Standardsichtbarkeit für Blätter. Sie sind in der Tab-Leiste sichtbar und können frei ausgewählt und angezeigt werden. Ausgeblendete Blätter werden aus der Blatt-Registerkartenleiste ausgeblendet und können daher nicht ausgewählt werden. Ausgeblendete Blätter können jedoch aus dem Excel-Fenster ausgeblendet werden, indem Sie mit der rechten Maustaste auf die Blattregister klicken und "Einblenden" auswählen.

Sehr verborgene Tabellen sind dagegen nur über den Visual Basic-Editor zugänglich. Dies macht sie zu einem unglaublich nützlichen Werkzeug zum Speichern von Daten zwischen verschiedenen Instanzen von Excel sowie zum Speichern von Daten, die für Endbenutzer verborgen sein sollten. Der Zugriff auf die Tabellen ist über einen Namen innerhalb des VBA-Codes möglich, sodass die gespeicherten Daten einfach verwendet werden können.

Um die .Visible-Eigenschaft eines Arbeitsblatts manuell in xlSheetVeryHidden zu ändern, öffnen Sie das Eigenschaftenfenster des VBE ( F4 ), wählen Sie das zu ändernde Arbeitsblatt aus und verwenden Sie das Dropdown-Menü in der dreizehnten Zeile, um Ihre Auswahl zu treffen.

Um die .Visible-Eigenschaft eines Arbeitsblatts im Code in xlSheetVeryHidden¹ zu ändern, greifen Sie in ähnlicher Weise auf die .Visible-Eigenschaft zu und weisen Sie einen neuen Wert zu.

with Sheet3
    .Visible = xlSheetVeryHidden
end with

¹ _{Sowohl xlVeryHidden als auch xlSheetVeryHidden geben den numerischen Wert 2 zurück (sie sind austauschbar).}

Arbeitsblatt .Name, .Index oder .CodeName

Wir wissen, dass 'Best Practice' vorschreibt, dass das übergeordnete Arbeitsblatt eines Bereichsobjekts explizit referenziert werden sollte. Ein Arbeitsblatt kann über seine .Name-Eigenschaft, seine numerische .Index-Eigenschaft oder seine .CodeName-Eigenschaft referenziert werden. Ein Benutzer kann jedoch die Arbeitsblattwarteschlange neu ordnen, indem er einfach eine Namensregisterkarte zieht oder das Arbeitsblatt mit einem Doppelklick auf dieselbe Registerkarte und einige umbenennt Eingeben einer ungeschützten Arbeitsmappe.

Betrachten Sie ein Standard-drei-Arbeitsblatt. Sie haben die drei Arbeitsblätter Montag, Dienstag und Mittwoch in dieser Reihenfolge umbenannt und VBA-Unterprozeduren codiert, die auf diese verweisen. Angenommen, ein Benutzer kommt und entscheidet, dass Montag am Ende der Arbeitsblattwarteschlange steht, dann kommt ein anderer und entscheidet, dass die Namen der Arbeitsblätter auf Französisch besser aussehen. Sie haben jetzt eine Arbeitsmappe mit einer Warteschlange für die Registerkarte "Name des Arbeitsblatts", die etwa wie folgt aussieht.

Wenn Sie eine der folgenden Arbeitsblatt-Referenzmethoden verwendet hätten, wäre Ihr Code jetzt beschädigt.

'reference worksheet by .Name
with worksheets("Monday")
    'operation code here; for example:
    .Range(.Cells(2, "A"), .Cells(.Rows.Count, "A").End(xlUp)) = 1
end with

'reference worksheet by ordinal .Index
with worksheets(1)
    'operation code here; for example:
    .Range(.Cells(2, "A"), .Cells(.Rows.Count, "A").End(xlUp)) = 1
end with

Sowohl die ursprüngliche Reihenfolge als auch der Name des ursprünglichen Arbeitsblatts wurden beeinträchtigt. Wenn Sie jedoch die .CodeName-Eigenschaft des Arbeitsblatts verwendet hätten, wäre Ihre Subprozedur weiterhin betriebsbereit

with Sheet1
    'operation code here; for example:
    .Range(.Cells(2, "A"), .Cells(.Rows.Count, "A").End(xlUp)) = 1
end with

Das folgende Bild zeigt das VBA-Projektfenster ([Strg] + R), in dem die Arbeitsblätter nach .CodeName und dann nach .Name (in Klammern) aufgeführt sind. Die Reihenfolge, in der sie angezeigt werden, ändert sich nicht. Der Ordnungsindex .Index wird anhand der Reihenfolge ermittelt, in der sie in der Warteschlange der Registerkarte "Name" im Arbeitsblattfenster angezeigt werden.

Das Umbenennen eines .CodeName ist zwar ungewöhnlich, aber es ist nicht unmöglich. Öffnen Sie einfach das Eigenschaftenfenster der VBE ([F4]).

Das Arbeitsblatt .CodeName befindet sich in der ersten Zeile. Das Arbeitsblatt .Name befindet sich im zehnten. Beide sind editierbar.

Verwenden von Zeichenfolgen mit Trennzeichen anstelle von dynamischen Arrays

Die Verwendung dynamischer Arrays in VBA kann bei sehr großen Datensätzen recht unhandlich und zeitaufwändig sein. Wenn Sie einfache Datentypen in einem dynamischen Array (Strings, Numbers, Booleans usw.) speichern, können Sie die ReDim Preserve Anweisungen vermeiden, die für dynamische Arrays in VBA erforderlich sind, indem Sie die Split() Funktion mit einigen cleveren String-Prozeduren verwenden. Zum Beispiel betrachten wir eine Schleife, die basierend auf einigen Bedingungen eine Reihe von Werten aus einem Bereich zu einer Zeichenfolge hinzufügt, und verwendet dann diese Zeichenfolge, um die Werte einer ListBox aufzufüllen.

Private Sub UserForm_Initialize()

Dim Count As Long, DataString As String, Delimiter As String

For Count = 1 To ActiveSheet.UsedRows.Count
    If ActiveSheet.Range("A" & Count).Value <> "Your Condition" Then
        RowString = RowString & Delimiter & ActiveSheet.Range("A" & Count).Value
        Delimiter = "><" 'By setting the delimiter here in the loop, you prevent an extra occurance of the delimiter within the string
    End If
Next Count

ListBox1.List = Split(DataString, Delimiter)

End Sub

Die Delimiter selbst kann auf einen beliebigen Wert gesetzt werden, es ist jedoch ratsam, einen Wert zu wählen, der in der Menge nicht natürlich vorkommt. Angenommen, Sie haben beispielsweise eine Spalte mit Datumsangaben verarbeitet. In diesem Fall verwenden Sie . , - oder / wäre unklug als Trennzeichen, da die Datumsangaben so formatiert werden könnten, dass eines dieser Werte verwendet wird und mehr Datenpunkte generiert werden, als Sie erwartet hatten.

Hinweis: Die Verwendung dieser Methode unterliegt Einschränkungen (insbesondere der maximalen Länge von Zeichenfolgen). Daher sollte sie bei sehr großen Datensätzen mit Vorsicht verwendet werden. Dies ist nicht unbedingt die schnellste oder effektivste Methode zum Erstellen dynamischer Arrays in VBA, aber es ist eine praktikable Alternative.

Doppelklicken Sie auf Ereignis für Excel-Shapes

Standardmäßig haben Formen in Excel keine bestimmte Methode für die Verarbeitung von Einzel- oder Doppelklicks. Sie enthalten nur die Eigenschaft "OnAction", mit der Sie Klicks verarbeiten können. Es kann jedoch Fälle geben, in denen Ihr Code Sie dazu zwingt, bei einem Doppelklick anders (oder ausschließlich) zu handeln. Die folgende Subroutine kann zu Ihrem VBA-Projekt hinzugefügt werden. Wenn Sie als OnAction Routine für Ihre Form festgelegt ist, können Sie Doppelklicks ausführen.

Public Const DOUBLECLICK_WAIT as Double = 0.25 'Modify to adjust click delay
Public LastClickObj As String, LastClickTime As Date

Sub ShapeDoubleClick()
    
    If LastClickObj = "" Then
        LastClickObj = Application.Caller
        LastClickTime = CDbl(Timer)
    Else
        If CDbl(Timer) - LastClickTime > DOUBLECLICK_WAIT Then
            LastClickObj = Application.Caller
            LastClickTime = CDbl(Timer)
        Else
            If LastClickObj = Application.Caller Then
                'Your desired Double Click code here
                LastClickObj = ""
            Else
                LastClickObj = Application.Caller
                LastClickTime = CDbl(Timer)
            End If
        End If
    End If
    
End Sub

Diese Routine bewirkt, dass die Form den ersten Klick funktionell ignoriert und nur den gewünschten Code innerhalb des angegebenen Zeitraums auf den zweiten Klick ausführt.

Dateidialog öffnen - Mehrere Dateien

Diese Subroutine ist ein schnelles Beispiel, wie einem Benutzer ermöglicht wird, mehrere Dateien auszuwählen und dann mit diesen Dateipfaden etwas zu tun, z.

Option Explicit

Sub OpenMultipleFiles()
    Dim fd As FileDialog
    Dim fileChosen As Integer
    Dim i As Integer
    Dim basename As String
    Dim fso As Variant
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set fd = Application.FileDialog(msoFileDialogFilePicker)
    basename = fso.getBaseName(ActiveWorkbook.Name)
    fd.InitialFileName = ActiveWorkbook.Path ' Set Default Location to the Active Workbook Path
    fd.InitialView = msoFileDialogViewList
    fd.AllowMultiSelect = True
    
    fileChosen = fd.Show
    If fileChosen = -1 Then
        'open each of the files chosen
        For i = 1 To fd.SelectedItems.Count
            Debug.Print (fd.SelectedItems(i))
            Dim fileName As String
            ' do something with the files.
            fileName = fso.getFileName(fd.SelectedItems(i))
            Debug.Print (fileName)
        Next i
    End If

End Sub