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.

Gruppierung reduzieren / erweitern auf gesperrtem Tabellenblatt

Gruppierungen werden in Excel-Berichten eingesetzt, um bestimmte Inhalte transparent anzeigen oder ausblenden zu lassen. Es zeigt deutet dem Leser an, dass noch weitere Informationen in der Tabelle vorhanden sind, die möglicherweise eingesehen werden sollten. Im Unterschied dazu gibt es die Möglichkeit, Zeilen oder Spalten komplett auszublenden. Dort erfordert es vom Leser mehr Aufmerksamkeit, um zu bemerken, dass Inhalte verborgen sind. Man bemerkt möglicherweise einen sprunghaften Anstieg in den Zeilennummerierungen oder den Spalten. Für welche Variante man sich entscheidet, ist natürlich situativ.

Worum es allerdings in diesem Blog im Spezifischen geht: Hast Du einmal versucht, Gruppierungen in einem geschützten und gesperrten Tabellenblatt auf- oder einzuklappen? Das ist nicht möglich und die Rechte können nicht entsprechend vergeben werden. Es sei denn, man wendet zwei Zeilen VBA-Code an. Es wird die nachfolgende Fehlermeldung angezeigt:

ErrorGroupings

Hierfür gibt es zwei Möglichkeiten, die ich nachfolgend vorstelle: Eine Schaltfläche, mit welcher der Benutzer die +/- Symbole "aktiviert" oder dass das Makro direkt beim Öffnen der Datei ausgeführt wird. Für beide Lösungsansätze musst Du zunächst in die Makro-Umgebung wechseln (ALT + F11 oder mittels Rechtsklick auf ein Tabellenblatt → "Code anzeigen"). Auf der linken Seite findest Du Deine Tabelle wieder. Mit einem Rechtsklick auf kannst Du via Einfügen → Modul alles soweit vorbereiten, um Dein Makro einzurichten. Füge dann folgenden Code ein:

Sub GruppierungenErlauben()

Sheets("MeinTabellenblatt").Protect Password:="MeinPasswort", UserInterfaceOnly:=True
Sheets("MeinTabellenblatt").EnableOutlining = True

End Sub

Ersetze bitte "MeinTabellenblatt" mit dem Namen Deines Excel-Sheets und vergebe ein Passwort, mit dem der Schutz aufzuheben ist. Mit "UserInterfaceOnly" wird lediglich die Benutzereingabe eingeschränkt; ein Makro könnte nach wie vor Änderungen in den gesperrten Bereichen vornehmen. Durch die weitere Angabe "EnableOutlining" wird die Berechtigung erteilt, auf die Gruppierungen zuzugreifen - trotz Blattschutz. Um dem Benutzer im Tabellenblatt die Möglichkeit zu geben, die Funktion zu aktivieren, füge beispielsweise ein Textfeld ein. Mit einem Rechtsklick auf dieses Objekt findest Du die Option "Makro zuweisen", woraufhin Du das Makro "GruppierungenErlauben" dem "Button" zuweisen kannst.

Makro beim Öffnen der Datei ausführen

Meiner Meinung nach schöner gelöst ist es, wenn Du die Codezeilen direkt ausführst, wenn die Datei geöffnet wird. Um dies zu bewerkstelligen, füge nicht ein neues Modul ein, sondern navigiere in der VBA-Umgebung auf "DieseArbeitsmappe" (ThisWorkbook in Englisch). Füge dort den nachfolgenden Code ein (achte bitte auf die angepasste 1. Code-Zeile):

Private Sub Workbook_Open()

Sheets("MeinTabellenblatt").Protect Password:="MeinPasswort", UserInterfaceOnly:=True
Sheets("MeinTabellenblatt").EnableOutlining = True

End Sub

So wird der Code entsprechend ausgeführt, wenn das Excel-File geöffnet wird.

Bitte beachte, dass Du die Excel-Datei als Makrodatei (.xlsm) oder Binärdatei (.xlsb) abspeichern musst, damit die Makros enthalten bleiben.

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.