Blog

Collection und Dictionary

Inhaltsverzeichnis

Collection

Eine Collection (deutsch Sammlung) umfasst eine Gruppe von Elementen.

Beispiel: Worksheets Collection
Spezialisierte Arten von Collections werden in Excel häufig verwendet. In VBA wird ein Tabellenblatt (engl. Worksheet) in einer Excel Datei über ein Worksheet Objekt angesprochen. Ein Worksheet Objekt verfügt dabei über Eigenschaften und Methoden. Falls eine Excel Datei mehrere Tabellenblätter hat, gibt es für jedes Tabellenblatt ein eigenes Worksheet Objekt. Die Worksheet Objekte sind Elemente (engl. Items) der Worksheets-Collection. In der Abbildung ist eine Excel Datei mit drei Tabellenblättern „Tabelle1“, „Tabelle2“ und „Tabelle3“ gezeigt.

Für die gezeigte Excel Beispieldatei kann man sich die Worksheets Collection wie folgt vorstellen:

Mit folgendem Code kann man die Anzahl der Tabellenblätter in der Worksheets-Collection im Direktfenster ausgeben:

Sub AnzahlDerTabellenblaetterAusgeben()
Debug.Print Worksheets.Count
End Sub

Wie im Beispiel Code gezeigt läßt sich ein Worksheet Objekt über den Tabellenblattnamen referenzieren. Der Tabellenblattname ist der Schlüssel (engl. Key) für das Tabellenblattobjekt in der Worksheets Collection.

Sub TabellenblattUeberNamenReferenzieren()
Debug.Print Worksheets("Tabelle1").Name
End Sub

In folgendem Beispiel Code ist gezeigt wie ein Worksheet Objekt über seine Nummer referenziert werden kann. Die Worksheet Objekt Elemente (gnl. Items) der Worksheets Collection werden entsprechend der Reihenfolge der Tabellenblätter in der Excel Datei durchnummeriert.

Sub TabellenblattUeberNummerReferenzieren()
Debug.Print Worksheets.Item(1).Name
End Sub

Mit folgendem VBA Code lassen sich die Namen der Tabellenblätter (engl. Worksheets) in einer Excel Datei ausgeben.

Sub TabellenblattNamenAusgeben()
Dim Tabellenblatt As Worksheet
For Each Tabellenblatt In ThisWorkbook.Worksheets
    Debug.Print Tabellenblatt.Name
Next Tabellenblatt
End Sub

Benutzerdefinierte Collections sind sehr nützlich, da es einfach ist, Elemente hinzuzufügen und die Elemente in der Collection zu durchlaufen.

Deklaration und Instanziierung einer Collection

Sub SammlungErstellen()
' Deklaration
Dim Sammlung As Collection
' Instanziierung
Set Sammlung = New Collection
End Sub

Eigenschaften des Collection Objects

Methoden des Collection Objects

Mit der „Add“ Methode ein Element zu einer Collection hinzufügen

Mit der „Add“ Methode kann ein Element (engl. Item) zu einer Collection hinzugefügt werden. Die Syntax ist
CollectionName.Add Item, Key, Before, After
wobei:

  • Item (deutsch Element): Ein Ausdruck eines beliebigen Datentyps.
  • Key: Optional, Eindeutiger Zeichenfolgenausdruck.
  • Before, After: Optional, Relative Positionierung der Zufügung.

Beispiel:

Sub SammlungErstellen()
' Deklaration
Dim Sammlung As Collection
' Instanziierung
Set Sammlung = New Collection
Sammlung.Add "Item1"
Sammlung.Add "Item2"
Sammlung.Add "Item3"
End Sub

Die mit dem VBA Code erstellte Collection „Sammlung“ mit den Elementen „Item1“, „Item2“ und „Item3“ kann man sich wie folgt vorstellen:

Wichtig:
Wenn ein Element einer Collection hinzugefügt wurde, kann man es nicht mehr nachträglich ändern. Man kann aber das zu ändernde Element mit der „Remove“ Methode aus der Collection entfernen. Das geänderte Element kann man dann mit der „Add“ Methode wieder zur Collection hinzufügen.

Elemente einer Collection im Direktfenster ausgeben

Mit folgendem VBA Code lassen sich die Elemente der Collection im Direktfenster ausgeben:

Sub SammlungErstellenUndAusgeben()
Dim item As Variant
' Deklaration
Dim Sammlung As Collection
' Instanziierung
Set Sammlung = New Collection
Sammlung.Add "Item1"
Sammlung.Add "Item2"
Sammlung.Add "Item3"
For Each item In Sammlung
    Debug.Print item
Next item
End Sub

Elemente einer Collection in einem Tabellenblatt ausgeben

Mit dem folgenden VBA Code lassen sich die Elemente einer Collection in einem Tabellenblatt ausgeben.

Sub SammlungErstellenUndInTabellenblattAusgeben()
Dim item As Variant
Dim ws As Worksheet, iZeile As Integer
Set ws = Worksheets("Tabelle1")
' Deklaration
Dim Sammlung As Collection
' Instanziierung
Set Sammlung = New Collection
Sammlung.Add "Item1"
Sammlung.Add "Item2"
Sammlung.Add "Item3"
'
ws.Cells(1, 1).Value = "Inhalt der Collection"
iZeile = 2
For Each item In Sammlung
    ws.Cells(iZeile, 1).Value = item
    iZeile = iZeile + 1
Next item
Set ws = Nothing
End Sub

Parameter „Key“ der „Add“ Methode

Mit dem Parameter ‚Key‘ kann man einen Schlüssel (engl. Key) hinzufügen, den man zur Identifizierung eines Elements in der Collection verwenden kann. Der Schlüssel (engl. Key) muss vom Typ String sein und muss innerhalb der Collection eindeutig sein.

Beispiel:

Sub SammlungErstellenUndAusgeben2()
Dim item As Variant
' Deklaration
Dim Sammlung As Collection
' Instanziierung
Set Sammlung = New Collection
Sammlung.Add item:="Item1", Key:="Schluessel1"
Sammlung.Add item:="Item2", Key:="Schluessel2"
Sammlung.Add item:="Item3", Key:="Schluessel3"
For Each item In Sammlung
    Debug.Print item
Next item
Debug.Print Sammlung("Schluessel1")
End Sub

Beispiel für die Verwendung von Before und After in der „Add“ Methode

Sub Test_Collection()
Dim collFrucht As Collection, item As Variant
'
Set collFrucht = New Collection
'
collFrucht.Add "Mandarine"
collFrucht.Add "Orange"
' Add lemon before first item
collFrucht.Add "Zitrone", Before:=1
collFrucht.Add "Apfel", After:=2
For Each item In collFrucht
    Debug.Print item
Next
'
Set collFrucht = Nothing
End Sub

Die Elemente der Collection haben mit dem ‚Key‘-Wert einen Schlüssel erhalten. Man kann nun über den Schlüssel anstelle der
Indexnummer auf ein Element der Collection zugreifen.

Mit der „Remove“ Methode ein Element aus einer Collection entfernen

Mit der Methode ‚Remove‘ kann ein Element (engl. Item) aus einer Collection entfernt werden. Die Syntax ist
CollectionName.Remove(index)
wobei:
Index: Ein Ausdruck (Key oder Zahl in der Liste), der die Position eines Elements innerhalb einer Collection angibt.

Beispiel:

 

Sammlung.Remove(3)

Bei einer großen Anzahl von Elementen in einer Collection ist es nicht einfach, den Index des zu löschenden Elements herauszufinden. In einem solchen Fall ist es sehr hilfreich den Parameter ‚Key‘ bei der Erstellung der Collection zu verwenden.

Sammlung.Remove("Schlüssel2")

Alle Elemente aus einer Collection entfernen

Mit folgender Anweisung lassen sich alle Elemente aus einer Collection entfernen
Set collectionName = New Collection

Mit der „item“ Methode ein Element einer Collection zurückgeben

Elemente einer Collection können mit der Methode „Item“ zurückgegeben werden. Die Syntax ist
CollectionName.Item(index)
wobei:
Index – Ein Ausdruck (Key oder Zahl in der Liste), der die Position eines Elements innerhalb einer Collection angibt.

Beispiel:

 

Sub SammlungErstellenUndAusgeben3()
Dim varItem As Variant, i As Integer
' Deklaration
Dim Sammlung As Collection
' Instanziierung
Set Sammlung = New Collection
Sammlung.Add item:="Item1", Key:="Schluessel1"
Sammlung.Add item:="Item2", Key:="Schluessel2"
Sammlung.Add item:="Item3", Key:="Schluessel3"
For i = 1 To Sammlung.Count
    Debug.Print Sammlung.item(i), Sammlung.item("Schluessel" & CStr(i))
Next i
End Sub

Mit der „Count“ Eigenschaft die Anzahl der Elemente in einer Collection zurückgeben

Mit der Count Eigenschaft kann die Anzahl der Elemente in einer Collection zurückgegeben werden. Die Syntax ist
CollectionName.Count

Beispiel:

Debug.Print Sammlung.Count

Überprüfen, ob ein bestimmtes Element in einer Collection enthalten ist

Sub SammlungDurchsuchen()
Dim item As Variant
' Deklaration
Dim Sammlung As New Collection
' Instanziierung
Set Sammlung = New Collection
Sammlung.Add "Item1"
Sammlung.Add "Item2"
Sammlung.Add "Item3"
'
For Each item In Sammlung
    If item = "Item2" Then
       Debug.Print item & " ist in der Collection enthalten"
    End If
Next
End Sub

Dictionary

Ein Dictionary speichert Paare von Schlüsseln (engl. keys) und zugehörigen Elementen (engl. items) bzw. Werten (engl. values). Sie sind so implementiert, dass man sehr effizient das Element bzw. den Wert zu einem gegebenen Schlüssel bestimmen kann.

Merkmale von Dictionaries

  • Ein Dictionary ändert seine Größe automatisch. Sie können praktisch beliebig viele Schlüssel-Wert-Paare zu einem Dictionary hinzufügen. Im Gegensatz zu VBA-Arrays dimensioniert sich das Dictionary automatisch selbst.
  • Eigenschaften von Schlüsseln:
    Standardmäßig wird bei den Schlüsseln zwischen Groß- und Kleinschreibung unterschieden.
    Die am häufigsten verwendeten Datentypen für Schlüssel sind Zeichenfolgen und Zahlen.
  • Eigenschaften von Elementen (engl. items) bzw. Werten (engl. values)
    Elemente oder Werte können nahezu jeden Typ haben, z. B. Zeichenfolgen oder Zahlen aber auch Objekte, Collections und Arrays. Sogar ein Dictionary mit Dictionaries ist möglich!

Ein Dictionary Objekt ähnelt einer Collection, bietet aber wesentliche Vorteile:
– man kann leicht überprüfen, ob ein Schlüssel bereits existiert.
– man kann den Wert eines vorhandenen Elements ändern.
Wegen dieser Vorteile habe ich Dictionary Objekte sehr häufig verwendet.

Deklaration und Instanziierung eines Dictionarys

Das Dictionary-Objekt ist nicht in Excel VBA enthalten sondern in der Bibliothek „Microsoft Scripting Runtime“. Bei der Nutzung von Dictionary Ojekten gibt es zwei Möglichkeiten

  • Early Binding:
    Vor der vollständigen Verwendung des Objekts Dictionary wird im Visual Basic Editor ein Verweis auf die Bibliothek Microsoft Scripting Runtime gesetzt. Hierzu wird im Visual Basic Editor auf den Menüpunkt „Extra“ und dann auf das Untermenü „Verweise“ geklickt. In der Liste der verfügtbaren Verweise scrollt man bis zur „Microsoft Scripting Runtime“ und setzt dort das Häkchen.

     Die Deklaration und Instanziierung eines Dictionary Objektes ist wie folgt

Sub TestDictionary()
' Early Binding
' Deklaration
Dim dict As Scripting.Dictionary
' Instanziierung
Set dict = New Scripting.Dictionary
End Sub
  • Late Binding:
    Die Deklaration und Instanziierung eines Dictionary Objektes ist wie folgenden Beispiel gezeigt. „Late Binding“ hat Vorteile, wenn man eine Excel Datie mit dem Code weiter gibt. Der Nutzer muss dann nicht mühsam den Verweise auf die „Microsoft Scripting Runtime“ setzen.
Sub TestDictionary2()
' Late Binding
' Deklaration
Dim dict As Object
' Instanziierung
Set dict = CreateObject("Scripting.Dictionary")
End Sub

Eigenschaften des Dictionary Objekts

Methoden des Dictionary Objekts

Mit der „Add“ Methode ein Element zu einem Dictionary hinzufügen

Mit der „Add“ Mthode kann ein Element (engl. Item) zu einem Dictionary hinzugefügt werden. Die Syntax ist
DictionaryName.Add Key, Item
wobei
key – der Schlüssel des hinzuzufügenden Elements.
Item – das Element, das unter dem Schlüssel Key hinzugefügt werden soll.

Beispiel

Sub TestDictionary3()
' Late Binding
' Deklaration
Dim dict As Object
' Instanziierung
Set dict = CreateObject("Scripting.Dictionary")
' Dictionary fuellen
dict.Add Key:="Michael", item:=21
dict.Add Key:="Melanie", item:=45
dict.Add Key:="Josef", item:=51
dict.Add Key:="Katarina", item:=17
' Wert des Eintrags "Melanie" ausgeben
Debug.Print dict("Melanie")
End Sub

Der Schlüssel (engl. Key) muß eindeutig sein: er darf nicht mehrfach verwendet werden. Der Schlüssel kann ein beliebiger Datentyp sein. Das Element (engl. Item) kann ebenfalls ein beliebiger Datentyp sein, beispielsweise ein Wert, eine Zeichenfolge, ein Objekt, eine Sammlung (engl. Collection) oder sogar ein Dictionary.

Mit der „Item“ Eigenschaft den Wert eines Dictionary Elements ändern

Mit der Eigenschaft „Item“ kann der Wert eines Dictionary Elements geändert werden. Die Syntax ist
DictionaryName.Item (Key) = newitem
wobei
Key – Schlüssel (engl. Key) des Elements, das geändert werden soll.
newitem – neuer Wert, der dem Element mit dem Schlüssel „Key“ zuwiesen werden soll.

Beispiel:
Im Beispiel werden die Werte zweier Dictionary Elemente geändert. Wie gezeigt kann das Schlüsselwort „.Item“ auch weggelassen werden.

Sub WertEinesElementsMitItemAendern()
' Deklaration (Late Binding)
Dim dict As Object
' Instanziierung
Set dict = CreateObject("Scripting.Dictionary")
' Dictionary fuellen
dict.Add Key:="Michael", Item:=21
dict.Add Key:="Melanie", Item:=45
dict.Add Key:="Josef", Item:=51
' Man kann die Schlueselwoerter Key:= und item:= auch weglassen
dict.Add "Katarina", 17
' Wert des Eintrags "Katarina" ändern
dict.Item("Katarina") = 30
' Wert des Eintrags "Melanie" ändern
dict("Melanie") = 40
' Wert des Eintrags "Melanie" ausgeben
Debug.Print dict.Item("Katarina")
Debug.Print dict("Melanie")
End Sub

Mit der „Item“ Eigenschaft ein Element eines Dictionarys zurückgeben

Mit der Eigenschaft „Item“ kann ein Element eines Dictionarys zurückgegeben werden. Die Syntax ist
DictionaryName.Item(Key)
wobei
Key – Schlüssel (engl. Key) des Elements, das zurückgegeben werden soll

Beispiel:
Im Beispiel werden alle Schlüssel und Werte im Direktfenster ausgeben. Wie im Beispiel gezeigt kann das Schlüsselwort „.Item“ auch weggelassen werden.

Sub ElementMitItemZurueckgeben()
Dim varItem As Variant
' Deklaration (Late Binding)
Dim dict As Object
' Instanziierung
Set dict = CreateObject("Scripting.Dictionary")
' Dictionary fuellen
dict.Add Key:="Michael", Item:=21
dict.Add Key:="Melanie", Item:=45
dict.Add Key:="Josef", Item:=51
dict.Add Key:="Katarina", Item:=17
' Alle Schlüssel und Werte im Direktfenster ausgeben
For Each varItem In dict
    Debug.Print varItem, dict.Item(varItem), dict(varItem)
Next
End Sub

Mit der „Exists“ Methode überprüfen, ob ein Schlüssel (engl. Key) bereits existiert

Mit der „Exsist“ Methode kannn überprüft werden, ob ein Schlüssel (engl. Key) bereits existiert. Die Syntax ist
DictionaryName.Exists(Key)
wobei:
Key = Schlüssel (engl. Key), nach dem im Dictionary-Objekt gesucht wird.

Beispiel:

Sub TestDictionary5()
' Late Binding
' Deklaration
Dim dict As Object
' Instanziierung
Set dict = CreateObject("Scripting.Dictionary")
' Dictionary fuellen
dict.Add Key:="Michael", item:=21
dict.Add Key:="Melanie", item:=45
dict.Add Key:="Josef", item:=51
dict.Add Key:="Katarina", item:=17
' Überpruefen, ob der Schlüssel "Melanie" existiert
Debug.Print dict.exists("Melanie")
' Überpruefen, ob der Schlüssel "Klaus" existiert
Debug.Print dict.exists("Klaus")
End Sub

Mit der „Count“ Eigenschaft die Anzahl der Elemente in einem Dictionary zurückgeben

Mit der „Count“ Eigenschaft kann die Anzahl der Elemente (engl. Items) in einem Dictionary zurückgegeben werden. Die Syntax ist
DictionaryName.Count

 

Beispiel

Sub TestDictionary6()
' Late Binding
' Deklaration
Dim dict As Object
' Instanziierung
Set dict = CreateObject("Scripting.Dictionary")
' Dictionary fuellen
dict.Add Key:="Michael", item:=21
dict.Add Key:="Melanie", item:=45
dict.Add Key:="Josef", item:=51
dict.Add Key:="Katarina", item:=17
' Anzahl der Elemente in einem Dictionary zaehlen
Debug.Print dict.Count
End Sub

Mit der Methode „Remove“ ein Element aus einem Dictionary entfernen

Mit der Methode Remove kann ein Element (engl. Item) aus einem Dictionary entfernt werden. Die Syntax ist
DictionaryName.Remove(Key)
wobei:
Key – Schlüssel (engl. Key) des Elements, das aus dem Dictionary entfernt werden soll

Beispiel:

Sub ElementMitRemoveAusDictionaryEntfernen()
'
' Deklaration (Late Binding)
Dim dict As Object, varItem As Variant
' Instanziierung
Set dict = CreateObject("Scripting.Dictionary")
' Dictionary fuellen
dict.Add Key:="Michael", Item:=21
dict.Add Key:="Melanie", Item:=45
dict.Add Key:="Josef", Item:=51
' Man kann die Schlueselwoerter Key:= und item:= auch weglassen
dict.Add "Katarina", 17
dict.Remove ("Josef")
For Each varItem In dict
    Debug.Print varItem
Next
End Sub

Mit der Methode „RemoveAll“ alle Elemente aus einem Dictionary entfernen

Mit der Methode „RemoveAll“ können alle Elemente aus einem Dictionary entfernt werden. Die Syntax ist
DictionaryName.Removeall

Beispiel:

Sub AlleElementMitRemoveAllAusDictionaryEntfernen()
'
' Deklaration (Late Binding)
Dim dict As Object, varItem As Variant
' Instanziierung
Set dict = CreateObject("Scripting.Dictionary")
' Dictionary fuellen
dict.Add Key:="Michael", Item:=21
dict.Add Key:="Melanie", Item:=45
dict.Add Key:="Josef", Item:=51
' Man kann die Schlueselwoerter Key:= und item:= auch weglassen
dict.Add "Katarina", 17
dict.RemoveAll
dict.Add "Mathias", 20
For Each varItem In dict
    Debug.Print varItem
Next
End Sub

Dictionary Elemente aus einem Excel Tabellenblatt einlesen

In folgendem Beispiel werden Dictionary Elemente aus dem Excel Tabellenblatt „Tabelle1“ eingelesen. Einige Zeilen sind doppelt vorhanden. Das Tabellenblatt sieht wie folgt aus:

Mit dem folgenden VBA Code werden Name in der Spalte 1 des Tabellenblatts „Tabelle1“ und Alter in der Spalte 2 des Tabellenblatts eingelesen und als Elemente zu einem Dictionary hinzugefügt. Der Name wird dabei als Schlüssel (engl. Key) verwendet, das Alter ist der Inhalt. Wichtig ist nun, dass bei einem Dictionary der Schlüssel immer eindeutig sein muss: Duplikate sind nicht erlaubt. Daher lassen sich mit einem Dictionary sehr einfach konsolidierte Daten erstellen. Um das zu veranschaulichen gibt der Code die konsolidierte Liste von Namen und Alter in den Spalten 4 und 5 aus.

Sub DictionaryInteraktionMitTabellenblatt()
Dim varItem As Variant, varKey As Variant, iZeile As Integer
Dim strName As String, iAlter As Integer, iLetzteZeile As Integer, i As Integer
Dim ws As Worksheet
Set ws = Worksheets("Tabelle1")
iLetzteZeile = 7
' Deklaration (Late Binding)
Dim dict As Object
' Instanziierung
Set dict = CreateObject("Scripting.Dictionary")
' Dictionary fuellen
For i = 2 To iLetzteZeile
    strName = ws.Cells(i, 1).Value
    iAlter = ws.Cells(i, 2).Value
    ' Überprüfen, ob der Schlüssel bereits existiert
    If dict.exists(strName) = False Then
       ' Element zum Dictionary hinzufügen
       dict.Add Key:=strName, Item:=iAlter
    End If
Next i
' Konsolidierte Liste von Namen und Alter in die Spalten 4 und 5 des Tabellenblatts schreiben
iZeile = 2
For Each varItem In dict
    ws.Cells(iZeile, 4).Value = varItem
    ws.Cells(iZeile, 5).Value = dict(varItem)
    iZeile = iZeile + 1
Next
Set ws = Nothing
End Sub

Das Ergebnis sieht wie folgt aus

Nun kann es passieren, dass in der Tabelle der Name Michael zwei Mal vorkommt aber mit unterschiedlichem Alter. In einem solchen Fall kann man einen Schlüssel (engl. Key) aus Namen und Alter bilden, z. B. mit der Anweisung
strKey = strName & Chr(0) & iAlter
Chr(0) ist dabei das ASCII Zeichen „0“, das normalerweise nicht vorkommt. Dieses ASCII Zeichen ist als Trennzeichen gedacht. Mit einer Split Anweisung können damit die Bestandteile des Schlüssels strKey wieder getrennt werden:
varFeld = Split(strKey, Chr(0))
Der Rückgabewert varFeld ist ein Datenfeld bestehend aus den zwei Elementen varFeld(0) (enthält den Namen) und varFeld(1) (enthält das Alter).
Das wird in folgendem Beispiel Code gezeigt. Die Excel Datei sieht wie folgt aus

Sub DictionaryInteraktionMitTabellenblatt_2()
Dim varItem As Variant, varKey As Variant, iZeile As Integer, strKey As String
Dim strName As String, iAlter As Integer, iLetzteZeile As Integer, i As Integer
Dim varFeld As Variant
Dim ws As Worksheet
Set ws = Worksheets("Tabelle1")
iLetzteZeile = 7
' Deklaration (Late Binding)
Dim dict As Object
' Instanziierung
Set dict = CreateObject("Scripting.Dictionary")
' Dictionary fuellen
For i = 2 To iLetzteZeile
    strName = ws.Cells(i, 1).Value
    iAlter = ws.Cells(i, 2).Value
    strKey = strName & Chr(0) & iAlter
    ' Überprüfen, ob der Schlüssel bereits existiert
    If dict.exists(strKey) = False Then
       ' Element zum Dictionary hinzufügen
       dict.Add Key:=strKey, Item:=iAlter
    End If
Next i
' Konsolidierte Liste von Namen und Alter in die Spalten 4 und 5 des Tabellenblatts schreiben
ws.Cells(1, 4).Value = "Name konsolidiert"
ws.Cells(1, 5).Value = "Alter"
iZeile = 2
For Each varItem In dict
    strKey = varItem
    varFeld = Split(strKey, Chr(0))
    ws.Cells(iZeile, 4).Value = varFeld(0)
    ws.Cells(iZeile, 5).Value = dict(varItem)
    iZeile = iZeile + 1
Next
Set ws = Nothing
End Sub

Das Ergebnis sieht nun wie folgt aus

Mit einem Dictionary Daten aus einem anderen Tabellenblatt einfügen

Ein Dictionary eignet sich sehr gut für das Einfügen von Daten aus einem anderen Tabellenblatt. Das soll in folgendem Beispiel gezeigt werden. Die Excel Arbeitsmappe enthält zwei Tabellenblätter „Tabelle1“ und „Tabelle2“. Der Inhalt der Tabellenblätter sieht wie folgt aus:

Mit folgendem VBA Code werden die Nachnamen aus dem Tabellenblatt „Tabelle 2“ in das Tabellenblatt „Tabelle 1“ eingefügt:

Sub DictionaryInteraktionMitTabellenblatt_2()
Dim varItem As Variant, varKey As Variant, iZeile As Integer
Dim strVorname As String, strNachname As String, iAlter As Integer, i As Integer
Dim iLetzteZeileInput As Integer, iLetzteZeileOutput As Integer
Dim wsInput As Worksheet, wsOutput As Worksheet
'
Set wsInput = Worksheets("Tabelle2")
iLetzteZeileInput = 5
Set wsOutput = Worksheets("Tabelle1")
iLetzteZeileOutput = 7
'
' Deklaration (Late Binding)
Dim dict As Object
' Instanziierung
Set dict = CreateObject("Scripting.Dictionary")
' Dictionary fuellen, die Variable strVorname wird als Schluessel verwendet
For i = 2 To iLetzteZeileInput
    strVorname = wsInput.Cells(i, 1).Value
    strNachname = wsInput.Cells(i, 2).Value
    ' Überprüfen, ob der Schlüssel bereits existiert
    If dict.exists(strVorname) = False Then
       ' Element zum Dictionary hinzufügen
       dict.Add Key:=strVorname, Item:=strNachname
    End If
Next i
' Nachnamen in das Tabellenblatt Tabelle1 schreiben
wsOutput.Cells(1, 3).Value = "Nachname"
For i = 2 To iLetzteZeileOutput
    strVorname = wsOutput.Cells(i, 1).Value
    If dict.exists(strVorname) = True Then
       wsOutput.Cells(i, 3).Value = dict(strVorname)
    End If
Next i
'
Set wsInput = Nothing
Set wsOutput = Nothing
End Sub

Das Ergebnis sieht wie folgt aus

Dieser Beitrag hat 12 Kommentare

  1. Alois Oktober 14, 2022 Antworten

    Super interesantes thema und wie alles bei dir super erklärt aber die Ausgabe über debag print bringt nicht viel es wäre Super wenn du das einlesen und die Ausgabe über excel arbeitsblätter erklären würdest.
    einem Anfänger wie mir bringt debag print nichts

    Aber sonst 10 mal Super

    1. admin Oktober 14, 2022 Antworten

      Hallo Alois,
      vielen Dank für Deinen Kommentar. Mit Deiner Anmerkung, dass für Dich die Ausgabe in einem Excel Tabellenblatt wichtig ist, hast Du vollkommen recht. Das sehe ich genau so. Ich habe nun ein Beispiel mit einer Ausgabe in einem Tabellenblatt unter der Überschrift „Elemente einer Collection in einem Tabellenblatt ausgeben“ eingefügt. Würde Dir das helfen? Im Beitrag „Excel Objekte“ habe mehr ich über die Interaktion zwischen einem VBA-Code und Tabellenblättern geschrieben. Ich werde mir diesen Beitrag noch einmal ansehen und wo nötig verbessern.

  2. Alois Oktober 14, 2022 Antworten

    Hallo
    Alles Super so und blitzartig reagiert man merkt das du Spaß an VBA hast vielen vielen Dank, bin fleißig am lernen.
    Wenn du jetzt noch etwas zum Befüllen eines Dictionary mit Werten aus einer Excel Tabelle bringen würdest wäre ich noch mehr von dir begeistert.
    Schaffe das einfach nicht selber hab schon alles mit meinen Fähigkeiten mögliche probiert

    Gruß und bitte weiter so hab noch so viel zu lernen

    1. admin Oktober 15, 2022 Antworten

      Hallo Alois,
      wie gewünscht habe ich Beispiele für das Befüllen eines Dictionarys mit Werten aus einer Excel Tabelle beigefügt. Die Beispiele sehen zwar etwas konstruiert aus. Ich habe aber in der Vergangenheit Dictionarys sehr häufig für vergleichbare Anwendungen verwendet und war oft überrascht, was man alles mit Dictionarys machen kann.
      Viel Spaß beim Ausprobieren

  3. Alois Oktober 15, 2022 Antworten

    Hallo
    Wie immer Top
    Schon wieder ei bisschen schlauer.

  4. Alois Februar 15, 2023 Antworten

    Hallo
    Ich bin es mal wieder mit einer Frage zum Thema ArrayList.
    Will mich mal mit den Array Listen beschäftigen aber da funktioniert bei mir nichts. (Habe Windows 10 und MS Office 2010 installiert. Auch mscorlib.dll habe ich unter Verweise aktiviert.

    Habe folgende 2 Prozeduren angefangen

    Sub Array_Liste()
    Dim Liste As Object
    Set Liste = CreateObject(„System.Collections.ArrayList“)
    End Sub

    Sub Array_Liste_2()
    Dim Liste As New ArrayList
    Liste.Add „Huber“
    End Sub
    Beim ersten meutert der Debuger Set Liste = CreateObject(„System.Collections.ArrayList“) und beim zweiten erstellt er zwar eine liste läst sich aber nicht befüllen.

    Was kann da sein das das nicht hinhaut.

    Danke Gruß und Gesundheit
    Alois

    1. admin Februar 16, 2023 Antworten

      Hallo Alois

      erscheint beim ersten Makro die Fehlermeldung „-2146232576 (80131700) Automatisierungsfehler“ auf (ich arbeite mit Windows 11 und Microsoft 365)? Auch beim zweiten Makro erhalte ich diesselbe Fehlermeldung obwohl bei mir auch ein Verweis auf die mscorlib aktiviert ist.

      Wenn ja hätte ich im Internet folgende Erklärung gefunden:
      Die Fehlermeldung wird dadurch verursacht, dass nicht die richtige .Net Version installiert ist. Für die ArrayList wird diie Version 3.5 benötigt. Es spielt keine Rolle, ob eine neuere Version wie 4.7 installiert ist, Version 3.5 muss installiert sein, siehe
      https://excelmacromastery.com/vba-arraylist/

      Unter folgendem Link ist beschrieben, wie sich .Net Version 3.5 installieren lässt:
      https://learn.microsoft.com/de-de/dotnet/framework/install/dotnet-35-windows

      Da ich mir nicht sicher bin, ob die gleichzeitige Installation verschiedener .Net Versionen Probleme verursachen kann, habe ich .Net Version 3.5 nicht auf meinem PC installiert und auch nichts über ArrayLists in meinem Blog geschrieben. Es könnte ein Hinweis darauf sein, dass Micros ArrayList in Zukunft nicht mehr unterstützt werden.

      Beantwortet dies Deine Frage?

      Viele Grüße und einen schönen Abend

      Stefan

  5. Alois Februar 16, 2023 Antworten

    Hallo Stefan
    Alles Klar vielen Dank
    Bist du nur in vba so ein Super Ass oder auch mit Windows Access und so weiter ein Profi habe nämlich auch da einige Probleme die im Laufe der Zeit aufgetaucht sind wenn ja würde ich mich sehr freuen wenn ich mich an dich wenden dürfte
    Gruß und schönes WE
    Alois

    1. admin Februar 18, 2023 Antworten

      Hallo Alois,
      mit Microsoft Access kenne ich mich leider nicht so gut aus, hätte aber Literatur dazu, in der ich nachlesen könnte. Ich könnte versuchen, Fragen dazu zu beantworten. Ich habe mich auch mit der Interaktion zwischen Excel VBA und Word beschäftigt, z. B. für das kopieren von Tabellen aus einem Word Dokument. Dazu werde ich demnächst einen Blog verfassen.
      Viele Grüße und ein schönes Wochenende
      Stefan

  6. Alois Februar 18, 2023 Antworten

    Hallo Stefan
    Ich weiß nicht ob das überhaupt ein Access Problem ist aber seit mein alter Laptop den Geist aufgegeben hat läuft auf dem neuem Rechner einiges ganz anders als das war.
    Ich habe in einer Access Datei ein Formular mit Bildern und Daten bei denen habe ich bisher immer mit dem Mausrad von Datensatz zu Datensatz scrollen können jetzt muß ich immer die Pfeile am unteren Rand des Formulars anklicken.
    Ich weiß nicht ob du mein Geschwafel verstehst. Wenn nicht könnte ich dir ja die Datei senden wenn du mir deine Mailadresse sendest.

    Gruß und ebenfalls schönes närrischen Wochenende (Bei uns ist Faschingszug morgen)

    1. admin Februar 20, 2023 Antworten

      Hallo Alois,

      Du hattest mir im Oktober 2022 zwei E-Mails geschickt. An diese Adresse könntest Du Dir Datei schicken.

      Viele Grüße

      Stefan

Schreibe einen Kommentar