Konfigurationsdatei in Excel (Config)

In einem älteren Beitrag habe ich einst aufgeführt, dass ein Blatt "Parameter" zum professionellen Präsentieren von Excel-Tabellen und -Reports hilfreich ist (siehe diesen Blogeintrag). Wichtige Angaben oder Inhalte von Drop-Down-Listen werden als Einträge auf einem (ausgeblendeten) Tabellenblatt hinterlegt. Eine Konfigurationsdatei (Config) ist dem sehr ähnlich, die hier vorgestellten Ansätze könntest Du durchaus auch in einem "Parameter"-Sheet hinterlegen.

Eine Konfigurationsdatei dient dazu, an einem zentralen Ort bestimmte Einstellungen vorzunehmen, es ist aus Wartungssicht deshalb deutlich komfortabler, als Anpassungen in diversen Formeln, Tabellenblättern oder VBA-Codezeilen vorzunehmen. Insbesondere bei grösseren Dateien oder Skripten ist es je länger je unübersichtlicher, eine Config-Datei ist dort empfohlen. Man bindet also möglichst viele übergreifenden Angaben, die allenfalls ändern könnten (manuell geändert werden müssen) in eine Konfig-Datei ein. Ein weitergehendes Beispiel könnten Dateipfade sein - respektive dynamische Pfade und Ordnerverzeichnisse. Stelle Dir vor, Du teilst Deine Datei mit einigen Arbeitskollegen. Je nach dem, wenn Ihr das File lokal öffnet, befindet sich die Datei jedoch in einem anderen Verzeichnis. Wenn man neben dem aktuellen File auch eine zweite Datei im Stammverzeichnis ansprechen möchte (z.B. eine Input- oder Vormonats-Datei), dann führen starre Pfade zu Fehlern. Im Beitrag "Dateipfad mit einer Formel darstellen" sind die Grundlagen aufgeführt, wie ein dynamischer Pfad hinterlegt werden kann.

Hier führe ich Dir eine Beispieldatei auf, die ein Blatt "Config" enthält:

Config- Konfigurations-Datei.xlsx

Es werden Variablen definiert, die allerdings im Rahmen dieses Blogs nicht weiter verwendet werden. Du könntest diese entsprechend einem Diktionär (Variable des Typs "Dictionary") einlesen - insbesondere im VBA-Kontext. Du kannst aber auch selber simplere Definitionen mit Zellbezügen anstellen. Relevant für den Excel-Teil an und für sich ist der Inhalt in der Spalte B: Dort sind unterschiedliche Varianten der Datei oder des Stammverzeichnisses (Root Path) direkt ausgelesen. Je nach dem, wo die Datei abgespeichert ist, sind andere Werte (und dort relevante Gegebenheiten) eingetragen.

Collections in VBA

Wer Daten von einer Quelle in eine Zieltabelle überträgt, kann mit den .Copy und .Paste-Methoden ein gewünschtes Resultat erzielen. Ist man jedoch häufig dazu gezwungen, zwischen Tabellenblättern oder Excel-Dateien hin und her zu wechseln, hat das Implikationen auf die Laufzeit des Makros. Eine performante Variante, um mehrere Datensätze zu übertragen, bietet der Einsatz von Collections. Beim einleitend beschriebenen Fall werden zuerst alle Datensätze der Collection hinzugefügt und daraufhin auf der Zieltabelle eingetragen. In diesem Blog wird dieses Beispiel illustriert und abschliessend eine weitere Möglichkeit aufgezeigt, wie Collections zusätzlich genutzt werden können.

Die nachfolgenden Tagesumsätze sollen auf eine zweite Tabelle übertragen werden:

CollectionUmsaetze

Natürlich könnte man alle Daten selektieren und mittels .Copy und .Paste fortschreiben, wir verwenden aber zur Übung und Illustration eine Collection dazu. Wir definieren demnach die Variable "UmsatzColl". Zudem ist die Collection zu initiieren. Verwende hierzu diese beiden Codezeilen:

Dim UmsatzColl As Collection
Set UmsatzColl = New Collection

Um beispielsweise den Wert in Zelle "C4" Deiner Collection hinzuzufügen, verwende:

UmsatzColl.Add Range("C4").Value

Man führt hinter der Collection-Variable also die Methode "Add" hinzu, gefolgt vom Bezug auf den Wert, den man zwischenspeichern möchte. Zusätzlich zum Wert könnte ein "Key" angegeben werden mit dem jederzeit auf den effektiven Wert referenziert werden kann. Hier könnte beispielsweise das Datum des Umsatzes hinzugefügt werden. Desweiteren kann man den Wert sortieren oder einordnen lassen, indem man die fakultativen "Before" oder "After"-Angaben mitgibt.

CollectionProperties

Neben "Add" stehen weitere Methoden zur Verfügung, sie werden Dir angezeigt, sobald Du "UmsatzColl." schreibst:

CollectionMethods

Man fügt Werte zur Sammlung hinzu, kann mit "Count" ausgeben, wie viele Werte in der Collection sind und mit "Remove" spezifische Einträge entfernen. Mit "Item" kann ein Wert ausgegeben werden (es ist jedoch die Standardeigenschaft und muss nicht zwingend angegeben werden) - ich werde das weiter unten aufführen.

Wir haben oben einen spezifischen Wert (Zelle "C4") hinzugefügt. Mit einer For-Schleife werden wir alle Umsätze durchgehen (Zeile 2 bis 24), die Integer-Variable "i" unterstützt diesen Loop:

For i = 2 To 24
UmsatzColl.Add Range("C" & i).Value
Next

 Um nun die Daten auf einer anderen Tabelle abzurufen, verwende ich wiederum einen For-Loop. Dabei wird wieder "i" verwendet, beginnend bei 1 bis hin zur Anzahl Items, die in der Collection sind (UmsatzColl.Count). Die Werte werden in eine Spalte A geschrieben:

For i = 1 To UmsatzColl.Count
Range("A" & i) = UmsatzColl(i)
Next

Alternativ könntest Du auch UmsatzColl.Item(i) verwenden, wie oben erwähnt - das Resultat ist dasselbe.

In der Praxis verwende ich teilweise auch mehrere Collections in Kombination. Eine "StartCopyRowColl" und "EndCopyRowColl" beispielsweise, wenn mehrere auseinanderliegende und unterschiedlich grosse Bereiche zu kopieren sind. Zusammen gibt es spannende und sehr performante Lösungen, da der Index ("i") in beiden Collections korreliert.

Ein weiterer Tipp: Du musst nicht alle Items mit "Remove" entfernen, wenn Du die Collection in der gleichen Prozedur erneut verwenden möchtest. Führe einfach die Codezeile "Set UmsatzColl = New Collection" erneut auf, dann beginnst Du mit Deiner Sammlung wieder von vorne.