VBA-Sammlungen (Visual Basic for Applications) sind ein leistungsfähiges Tool in Excel, mit dem Sie große Datenmengen mit relativ wenig Code verwalten können. Sammlungen sind Gruppen von Objekten, die zum Speichern, Aktualisieren und Abrufen von Daten verwendet werden können. In diesem Artikel erfahren Sie, wie Sie eine VBA-Auflistung in einem Arbeitsblatt erstellen und verwenden, um die Arbeit mit Daten zu vereinfachen.
Um eine VBA-Auflistung in einem Arbeitsblatt zu erstellen, benötigen Sie ein VBA-Modul. Um das VBA-Modul zu öffnen, klicken Sie mit der rechten Maustaste auf das Arbeitsblatt, wählen Sie Code anzeigen aus dem Kontextmenü und wählen Sie im angezeigten Fenster das gewünschte Arbeitsblatt aus. Geben Sie dann den folgenden Code ein:
Dim collectionName As New Collection
Hier ist "collectionName" der Name Ihrer Sammlung. Sie können einen beliebigen Namen auswählen, der Ihren Bedürfnissen entspricht.
Nachdem Sie die Sammlung erstellt haben, können Sie Elemente mit folgendem Code hinzufügen:
Dim item As Variant
item = [значение элемента]
collectionName.Add item
Anstatt "[elementwert]" Sie müssen den Wert des Elements angeben, das Sie der Sammlung hinzufügen möchten. Der Wert eines Elements kann eine Zahl, eine Zeichenfolge oder ein anderes Objekt sein.
Sobald Sie der Sammlung Elemente hinzugefügt haben, können Sie verschiedene Methoden und Eigenschaften der Sammlung verwenden, um sie weiter zu verarbeiten.
Vorbereiten der Erstellung einer Excel-VBA-Auflistung
Bevor Sie mit dem Erstellen einer Excel-VBA-Auflistung in einem Arbeitsblatt beginnen, müssen Sie einige vorbereitende Schritte ausführen:
- Öffnen Sie die Excel-Datei, in der die Sammlung erstellt werden soll.
- Drücken Sie Alt+F11, um das Visual Basic-Editor-Fenster zu öffnen.
- Wählen Sie im Visual Basic-Editor das gewünschte Projekt (Arbeitsmappe, Modul oder Arbeitsblatt) aus, in dem die Sammlung erstellt werden soll.
- Klicken Sie mit der rechten Maustaste auf die gewünschte Registerkarte (z. B. Tabelle1, um eine Sammlung im Arbeitsblatt zu erstellen) und wählen Sie Einfügen -> Modulklasse aus dem Kontextmenü aus.
- Geben Sie im geöffneten Fenster der Modulklasse einen Klassennamen ein (z. B. "CollectionDemo").
- Drücken Sie die Eingabetaste, um eine neue Modulklasse zu erstellen.
- Erstellen Sie in der Modulklasse eine Variable vom Typ Sammlung, z. B.:
Dim myCollection As New CollectionDaher haben wir eine Variable MyCollection vom Typ Collection erstellt. Jetzt können wir diese Variable verwenden, um der Sammlung Elemente hinzuzufügen und andere Vorgänge auszuführen.
Nachdem Sie diese Schritte ausgeführt haben, können Sie eine Excel-VBA-Sammlung in einem Arbeitsblatt erstellen und verwenden.
Erstellen eines neuen Arbeitsblatts
Verwenden Sie ein Objekt, um ein neues Arbeitsblatt in VBA Excel zu erstellen Worksheet. Mit dem folgenden Code können Sie ein neues Arbeitsblatt in einer Arbeitsmappe erstellen:
Sub СоздатьНовыйЛист()Dim НовыйЛист As WorksheetSet НовыйЛист = ThisWorkbook.Sheets.AddНовыйЛист.Name = "Новый лист"' Добавьте свой код для работы с новым листом здесьEnd Sub- Ein neues Objekt wird erstellt Worksheet und wird einer Variablen zugewiesen Neue Liste.
- Die Methode wird verwendet Add fügt der aktuellen Arbeitsmappe ein neues Arbeitsblatt hinzu.
- Das neue Arbeitsblatt wird mit einer Eigenschaft benannt Name.
Nachdem Sie ein neues Arbeitsblatt erstellt haben, können Sie verschiedene Operationen mit einer Variablen ausführen Neue Liste. Sie können beispielsweise Zellen Werte hinzufügen, die Formatierung ändern oder andere für Ihre Aufgabe erforderliche Aktionen ausführen.
Es ist wichtig zu beachten, dass das neue Arbeitsblatt nach dem aktuell aktiven Arbeitsblatt hinzugefügt wird. Wenn Sie die Reihenfolge der Kacheln steuern müssen, können Sie andere Methoden und Eigenschaften des Objekts verwenden Workbook, wie Sheets(index) oder Sheets("name").
Vorbereiten der Datentabelle
Bevor wir die VBA-Sammlung von Excel in einem Arbeitsblatt verwenden, müssen Sie eine Datentabelle vorbereiten. Die Tabelle muss Spalten und Zeilen definieren, die die benötigten Daten enthalten. In diesem Artikel betrachten wir ein Beispiel für eine Tabelle, die Daten über die Mitarbeiter eines Unternehmens enthält.
Lassen Sie uns eine Tabelle mit den folgenden Spalten erstellen:
- Name - spalte, die die Namen der Mitarbeiter enthält;
- Nachname - spalte, die die Nachnamen der Mitarbeiter enthält;
- Alter - spalte, die das Alter der Mitarbeiter enthält;
- Berufsbezeichnung - spalte, die die Positionen der Mitarbeiter enthält;
Nachdem Sie eine Tabelle erstellt haben, können Sie sie mit Mitarbeiterdaten füllen, z. B.:
| Name | Nachname | Alter | Berufsbezeichnung |
|---|---|---|---|
| Iwan | Ivanov | 30 | Manager |
| Peter | Petrov | 25 | Entwickler |
| Anna | Sidorova | 35 | Buchhalter |
Daher haben wir eine Datentabelle vorbereitet, mit der wir die Excel-VBA-Sammlung in einem Arbeitsblatt verwenden werden.
Installieren der VBA-Entwicklungsumgebung
VBA-Entwicklungsumgebung (Visual Basic for Applications) - ein Werkzeug, mit dem Sie Makros und benutzerdefinierte Funktionen in Microsoft Office-Anwendungen, einschließlich Excel, erstellen können.
Befolgen Sie die folgenden Anweisungen, um die VBA-Entwicklungsumgebung in Excel zu installieren:
- Öffnen Sie Excel.
- Klicken Sie auf die Schaltfläche "Datei" in der oberen linken Ecke des Bildschirms und wählen Sie dann "Optionen".
- Wählen Sie im angezeigten Fenster auf der Seite "Einstellungen" die Registerkarte "Excel-Optionen" aus.
- Suchen Sie auf dieser Seite in der Liste der verfügbaren Optionen nach "Entwickler" und aktivieren Sie das Kontrollkästchen.
- Klicken Sie auf "OK", um die Änderungen zu speichern.
Nachdem Sie diese Schritte ausgeführt haben, wird im Hauptfenster von Excel eine neue Registerkarte "Entwickler" angezeigt. Dies bedeutet, dass die VBA-Entwicklungsumgebung jetzt für die Verwendung verfügbar ist.
Hinweis: Wenn die Registerkarte "Entwickler" weiterhin nicht angezeigt wird, überprüfen Sie die Sicherheitseinstellungen von Excel. Sie müssen wahrscheinlich vertrauenswürdige Speicherorte aktivieren und Makros aktivieren.
Importieren der benötigten Bibliotheken
Bevor Sie mit der VBA-Sammlung von Excel in einem Arbeitsblatt arbeiten, müssen Sie die erforderlichen Bibliotheken importieren, die Zugriff auf die gewünschten Objekte und Funktionen ermöglichen.
Um mit einer Zellensammlung und Excel-Arbeitsblättern zu arbeiten, müssen Sie die Microsoft Excel-Bibliothek importieren. Dies ermöglicht die Verwendung von Objekten und Methoden wie Range , Worksheet , Cells und vielen anderen.
Führen Sie die folgenden Schritte aus, um die Microsoft Excel-Bibliothek zu importieren:
- Öffnen Sie den VBA-Editor, indem Sie ALT + F11 drücken.
- Wählen Sie im Editor-Menü Extras und dann Links aus. ".
- Suchen Sie im angezeigten Fenster "Links" nach "Microsoft Excel XX.X Object Library", wobei XX.X ist die Version von Excel, die auf Ihrem Computer installiert ist. Wenn Sie die Version von Excel nicht kennen, aktivieren Sie die neueste Version.
- Klicken Sie auf "OK".
Nachdem Sie diese Schritte ausgeführt haben, ist die Bibliothek in Ihrem VBA-Projekt verfügbar. Sie können Objekte und Methoden aus dieser Bibliothek verwenden, um mit der Excel-VBA-Auflistung in einem Arbeitsblatt zu arbeiten.
Wenn Sie mit anderen Objekten oder Bibliotheken arbeiten, müssen Sie die entsprechenden Bibliotheken wie oben beschrieben importieren.
Erstellen einer Excel-VBA-Auflistung
Führen Sie die folgenden Schritte aus, um eine Auflistung in VBA Excel zu erstellen:
| Schritt | Die Beschreibung |
| 1 | Deklarieren Sie eine Variable, die die Sammlung enthält. Verwenden Sie beispielsweise die folgende Syntax: |
Dim collectionName As New Collection
collectionName.Add Range("A1").Value
Die integrierte VBA-Sammlung von Excel, Worksheets genannt, enthält jedoch bereits Arbeitsblätter in der Arbeitsmappe, und die Objekte in dieser Sammlung können direkt verwendet werden, ohne dass eine eigene Sammlung erstellt werden muss. Der folgende Code fügt beispielsweise ein neues Arbeitsmappenblatt hinzu:
Dim newWorksheet As WorksheetSet newWorksheet = ThisWorkbook.Worksheets.Add
Sie können auch einen anderen VBA-Dictionary-Auflistungstyp verwenden, um die Arbeit mit Sammlungen flexibler zu gestalten, mit dem Schlüssel-Wert-Paare gespeichert werden können. Dies ist nützlich, wenn Sie Werte für einen bestimmten Schlüssel speichern und abrufen möchten. Um eine Dictionary-Auflistung zu verwenden, müssen Sie einen Verweis auf Microsoft Scripting Runtime über das Menü Tools > References. Anschließend können Sie Instanzen der Dictionary-Auflistung erstellen und mit ihnen ähnlich wie bei einer normalen Auflistung arbeiten.
Daher ist das Erstellen und Verwenden einer Excel-VBA-Auflistung eine effektive Möglichkeit, Objekte zu organisieren und zu verwalten, während Prozesse in Excel automatisiert werden.
Deklarieren und Initialisieren der Sammlung
Verwenden Sie die Objektansicht der Auflistung, um mit Sammlungen in VBA Excel zu arbeiten. Sie müssen das Schlüsselwort verwenden, um die Auflistung zu deklarieren und zu initialisieren Dim und geben Sie den Namen der Variablen an, die die Sammlung darstellen soll.
Als nächstes wird das Schlüsselwort verwendet, um die Sammlung selbst zu erstellen New und der Name der zu verwendenden Auflistungsklasse wird angegeben. Um beispielsweise eine Auflistung von String-Werten zu erstellen, müssen Sie eine Klasse verwenden Collection.
Beispiel für die Deklaration und Initialisierung einer Sammlung:
| Dim | myCollection | As | New | Collection |
|---|
Nachdem Sie die Auflistung deklariert und initialisiert haben, können Sie mit der Methode beginnen, der Auflistung Elemente hinzuzufügen Add. Ein Beispiel:
| myCollection.Add | "Element1" |
|---|
In diesem Beispiel wird der Auflistung die Zeichenfolge "Element1" hinzugefügt. Sie können beliebige Werte hinzufügen, einschließlich Zahlen, Zeilen, Objekte usw.
Eine Auflistung ist eine geordnete Liste von Elementen, auf die mithilfe einer Methode über ihren Index zugegriffen werden kann Item. Ein Beispiel:
| myVariable = | myCollection.Item | (1) |
|---|
In diesem Beispiel ist die Variable myVariable der Wert des ersten Elements der Auflistung wird zugewiesen.
