single.php
< Beitrag von Götz Gleitsmann

Dynamisches Excel in der Projektplanung

Dynamisches Excel in der Projektplanung sorgt u. a. dafür, dass Excel-Mappen immer auf dem gleichen Stand sind. Im Folgenden werden durch die Firma ORBIT durchgeführte Änderungen der VBA-Programmierung in einer Excel-basierten Projektplanungsliste vorgestellt. 

Dynamisches Excel in der Projektplanung – die Anforderungen

In einem Kundenprojekt sollte die Excel-Masterliste ergänzt und in ihrer Funktionalität verbessert werden. Dies bezog sich hauptsächlich auf die Generierung der Auswertungslisten (sog. „Auszüge“). Konkret sollte bei der Generierung der Auszüge die Anzahl der Filterungsmöglichkeiten von 2 auf 9 erweitert und die wöchentlichen Änderungen in den Auszügen gegenüber dem letzten Wochenbericht farblich hervorgehoben werden.

Für jede der 3 Applikationen (SiNan, WWI und KW500X) ist in der Masterliste ein Blatt vorhanden, dessen Spalte „Verantwortung“ gefiltert werden muss. Anfänglich waren nur Filterungen auf die Verantwortungsbereiche TDG und GSO vorhanden. Durch die Forderung nach weiteren Ausprägungen der Filterung ergaben sich folgende Anforderungen:

  • CSO (GW/TS/PK) – hier die mit „E-CSO“ beginnenden Felder berücksichtigen
  • TSO – hier die mit „E-TSO“ beginnenden Felder berücksichtigen
  • MCS – hier die mit „E-MCS“ beginnenden Felder berücksichtigen
  • GSO – auf diese Liste bitte einen Qualitätscheck machen – sprich, ob diese ebenso arbeitet
  • WPS – hier die mit „WPS“ beginnenden Felder berücksichtigen
  • TSI – hier die mit „E-TSI“ beginnenden Felder berücksichtigen
  • MU – hier die mit „MU“ bzw. „TSI-MU“ beginnenden Felder berücksichtigen; „Weitere Applikationen“ – dies ist die Liste der Applikationen, die keine der zuvor genannten Filterkriterien erfüllen.

Die Spalte „Verantwortung“ entspricht im Reiter „SiNan“ der Spalte G, in den Reitern „WWI“ und „X500“ hingegen der Spalte F. Ausnahme: Wie in der schon bestehenden Auswertung soll für die Ausprägung „TDG“ die jeweils rechts anschließende Spalte verwendet werden.
Weiterhin sollen sämtliche Datensätze mit dem Abstimmungsstatus „Nicht erforderlich“ herausgefiltert werden.

Die jeweiligen Empfänger der Auswertungslisten (sog. „Auszüge“) sollen auf einen Blick erkennen, welche Änderungen sich von einer Woche auf die nächste ergeben haben. Darum sollen im Auszug die entsprechenden Felder farblich hinterlegt sein. Für diesen Zweck wurde ein Makro erstellt, das den jeweils aktuellen Auszug mit dem vorausgegangenen Wochenbericht vergleicht und die Neuerungen jeweils farblich kennzeichnet. Konkret werden die Spalten H, J, L, N, P und R verglichen, in denen die sog. „Ist-Daten“ von Meilensteinen in den einzelnen Projekten eingetragen sind.

Neues Tabellenblatt „Datei-Info“

Das Blatt „Datei-Info“ dient der Ermittlung des passenden Wochenberichts (Dateiname), um im Auszug die letzten Änderungen farblich hervorheben zu können. Wochenberichte erscheinen jeweils am Freitag, und ihr Dateiname folgt der Konvention „JJJJ_MM_TT_Projektplanung.xlsx“. Er wird bei jeder Auszugsgenerierung dynamisch bestimmt. Dies geschieht in folgenden Schritten:

  1. In Zelle B5 ist eine Formel hinterlegt, die den Wochentag als Ziffer (0 = Sonntag, 1 = Montag etc.) bestimmt. Sie lautet:
    • =WOCHENTAG(DATUM(JAHR(HEUTE());MONAT(HEUTE());TAG(HEUTE()));2)
  2. Das Ergebnis aus Zelle B5 wird in Zelle B6 dazu verwendet, das Datum des letzten Freitags in der Form JJJJ_MM_TT zu bestimmen. Zu diesem Zweck ist in Zelle B6 folgende Formel hinterlegt:
    • =JAHR(HEUTE()-REST(B5+2;7)) & „_“& WENN(MONAT(HEUTE()-REST(B5+2;7)) < 10;“0″;““) & MONAT(HEUTE()-REST(B5+2;7)) & „_“ & WENN(TAG(HEUTE()-REST(B5+2;7)) < 10;“0″;““) & TAG(HEUTE()-REST(B5+2;7))
    • Dabei werden einstellige Monats- und Tageswerte mit einer führenden „0“ aufgefüllt.
  3. Zelle B10 enthält den Verzeichnispfad, wo die Wochenberichte abgelegt sind. Das kann jedes beliebige lokale oder Netzlaufwerk sein.

Letztlich wird in Zelle B2 mittels der Formel „=VERKETTEN(B10;B6;“_Projektplanung.xlsx“)“ der vollständige Dateiname erzeugt.
Diese dynamische Lösung hat gegenüber im Code „fest verdrahteten“ Verzeichnis- und Dateinamen den Vorteil, dass sie schnell an neue Umgebungen angepasst werden kann.

Ergänzung der Auswertungen

Eine Analyse der bestehenden Excel-VBA-Programmierung ergab, dass die Filterung auf TDG und GSO auf den Excel-Freitextfiltern basierte. Für ihre Erzeugung existierten 3 VBA-Prozeduren, nämlich „FilterGSO“, „FilterGSOundTDG“ sowie „FilterTDG“. Da Freitextfilter nur 2 Kriterien miteinander verknüpfen können, musste die Programmierung in diesem Teil komplett erneuert werden. Daher wurden die o. g. 3 Prozeduren entfernt.

Erweiterung der Eingabemaske „UserForm1“

Die Eingabemaske „UserForm1“ für die Festlegung der Filterbedingungen wurde gemäß den Anforderungen erweitert und hat nunmehr 9 Checkboxen, im Einzelnen: TDG, E-GSO, E-CSO, E-TSO, WPS, E-TSI, MCS, MU und Rest. Letzteres filtert auf alle Zeilen, die den erstgenannten 8 Kriterien nicht entsprechen.
Diese Checkboxen ermöglichen eine Mehrfachauswahl, um eine beliebige ODER-Bedingung zu realisieren. Mit der Quittierung der Auswahl (Schaltfläche „OK“) erfolgt die Übergabe der booleschen Werte aus den o. g. 9 Checkboxen an die neu erstellte Prozedur „Filterung“, siehe nächsten Abschnitt.

Dynamisches Excel in der Projektplanung - Auszug Erstellen

Neue Prozedur „Filterung“

Die neue Prozedur „Filterung“ ersetzt die Prozeduren „FilterGSO“, „FilterGSOundTDG“ und „FilterTDG“. Sie nimmt von UserForm1 die booleschen Parameter cb1 bis cb9 entgegen, die den Werten der weiter oben beschriebenen Checkboxen entsprechen. Für jedes Inhaltsblatt (SiNan, WWI und KW500X) der Masterliste führt sie folgende Schritte durch:

  • Erstellung eines temporären Blatts gleichen Namens, aber mit dem Namenszusatz „:gefiltert“;
  • Zuweisung der booleschen Variablen „enthält_TDG“, „enthält_GSO“, „enthält_CSO“, „enthält_TSO“, „enthält_WPS“, „enthält_TSI“, „enthält_MCS“ und „enthält_MU“;
  • Füllen des temporären Blatts mit sämtlichen Zeilen, die der Filterbedingung genügen;
  • Ausblenden der Datensätze im jeweiligen Inhaltsblatt, die nicht der Filterbedingung genügen;
  • Entfernen aller Spalten im temporären Blatt, die für die Auszüge nicht benötigt werden, unter gleichzeitiger Vereinheitlichung der Spaltensignatur für die Auszüge. Eine Ausnahme stellt hierbei die Spalte 2 („Nr.“) dar, die erst am Ende gelöscht wird, siehe nächsten Abschnitt.

Sobald die temporären, gefilterten Blätter erstellt sind, wird die Prozedur „Auszug“ aufgerufen, deren Änderungen gegenüber der Vorversion im nächsten Abschnitt beschrieben sind. Abschließend erfolgt in den 3 Inhaltsblättern die Einblendung sämtlicher zuvor ausgeblendeter Zeilen sowie die Löschung der mit dem Namenszusatz „_gefiltert“ versehenen temporären Blätter. Sollte später eine Archivierung der Filterung unabhängig von den Auszügen erwünscht sein, so ließe sich diese Löschung problemlos auskommentieren.

Dynamisches Excel in der Projektplanung - Code-Ausschnitt_VBA_Filterung

Änderungen an der Prozedur „Auszug“

Um ein störungsfreies Funktionieren bei verschiedenen Excel-Versionen und Betriebssystem-Konfigurationen zu gewährleisten, musste die Prozedur „Auszug“ grundlegend überarbeitet werden.

Zunächst erfolgt die Festlegung folgender Variablen:

  • Masterliste: Dateiname der aktiven Datei;
  • Masterliste_komplett: wie oben, aber zusätzlich mit vorangestelltem Verzeichnispfad;
  • Wochenbericht: Name des letzten Wochenberichts, berechnet im Blatt „Datei-Info“, siehe oben.

Nach wie vor wird in der Prozedur „Auszug“ eine neue Excel-Datei erstellt. Anders als in der alten Version der Projektplanungsliste werden jedoch die Inhalte aus den im letzten Abschnitt beschriebenen temporären Blättern direkt in nur ein Blatt kopiert, weil sie bereits die gleiche Reihenfolge der Spalten haben und daher nicht mehr in der Prozedur „Auszug“ selbst angeglichen werden müssen. Dadurch entfallen komplizierte Sonderbehandlungen bezüglich der Benennung der Blätter in der automatisch erstellten Auszugsdatei, die von der verwendeten Excel-Version abhängt.
Nach Fertigstellung der Auszugs-Formatierung (Programmierung unverändert) erfolgt der Aufruf der Prozedur „MarkiereÄnderungen“, siehe nächsten Abschnitt. Dieser werden die vollständigen Dateinamen der aktiven Masterliste, des soeben generierten Auszugs sowie des als Vergleich herangezogenen Wochenberichts als Parameter mitgegeben.
Erst zum Schluss wird im Auszug die zweite Spalte (Projektnummer) gelöscht, da sie bis zuletzt für die farbliche Markierung der Änderungen gegenüber dem letzten Wochenbericht benötigt wird.

Dynamisches Excel in der Projektplanung - Code-Ausschnitt_VBA_Auszug

Farbliche Hervorhebung der wöchentlichen Änderungen

Für die farbliche Hervorhebung der Änderungen gegenüber dem letzten Wochenbericht wurde die Prozedur „MarkiereÄnderungen“ erstellt. Sie übernimmt die Parameter „Hauptmappe“, „Auszugsmappe“ und „Wochenbericht“. Um die beteiligten Excel-Dateien über das VBA-Objekt „Workbooks“ auch dann korrekt zuordnen zu können, wenn weitere (nicht beteiligte) Excel-Dateien in Benutzung sind, erfolgt zunächst die Zuweisung der Integer-Variablen „GegenSeite“ (für den Wochenbericht) und „DieseSeite“ (Für die aktive Masterliste). Damit sind Fehlfunktionen (Zugriffe auf eine falsche Excel-Datei) ausgeschlossen.
Anschließend erfolgt der Vergleich zwischen Auszug und Wochenbericht. Bis zum Erreichen der letzten nicht leeren Zeile im Auszug wird eine Schleife durchlaufen, die jeweils für SiNan, WWI und KW500X folgende Schritte enthält:

  • Ermittlung der passenden Zeilennummer im Wochenbericht durch Vergleich der Projektnummer (temporäre Spalte 2 im Auszug)
  • Ist die Zeilennummer der Gegenseite ermittelt, so werden sämtliche in den Anforderungen genannten Ist-Spalten nacheinander verglichen. Wird Ungleichheit festgestellt, so wird das entsprechende Feld im Auszug hellgelb hinterlegt.

Da die 3 Blätter im Wochenbericht (je eins für die Applikationen SiNan, WWI und KW500X) verschiedene Spaltenfolgen haben, werden die o. g. Operationen in 3 Stufen nacheinander durchgeführt. Nach Beenden der Schleife wird der Wochenbericht automatisch geschlossen. In der folgenden Abbildung ist exemplarisch gezeigt, wie ein Auszug nach einem Vergleich mit dem Wochenbericht aussieht.

Dynamisches Excel in der Projektplanung - Farbliche Hervorhebung

Folgen
X

Folgen

E-mail : *
Kategorie: SharePoint | Schlagwörter: | Kommentare: 0

Beitrag kommentieren

CAPTCHA * Time limit is exhausted. Please reload CAPTCHA.