Basierend auf der Arbeitszeiterfassung, die komplett von Hand auszuführen ist, war der nächste Ansporn, das ganze zu automatisieren. Gesagt, getan und eine Woche später konnte ich nun mit einer umfangreich programmierten Exceldatei prahlen. Die Funktionen möchte ich hier im einzelnen erklären, zuerst hier aber in einer Textdatei der Code aus allen Worksheets, UserForms und Modulen, damit ihr euch sicher sein könnt, dass ihr euch durch meine Datei keinen Virus auf den Computer ladet 😉

Ein kleiner Disclaimer

Ich bin kein Programmierer, schon gar kein Informatiker. Wer sich unter diesen Aspekten den Code anschaut, wird wahrscheinlich die Hände über dem Kopf zusammenschlagen. Das ist voll okay und mir prinzipiell egal, denn bei mir heiligt der Zweck die Mittel. Solange etwas funktioniert, ist das okay. Wenn nicht, fixe ich es. Natürlich kann man den Code optimieren, aber solange er läuft, bin ich zufrieden.
Nun denn, wollen wir die Datei mal öffnen und uns die einzelnen Fenster betrachten:

Öffnet man die Exceldatei, wird in der Workbook_Open-Prozedur das Ausgangsblatt (Tabellenblatt „Jahresübersicht“) gereinigt und die für das aktuelle Jahr geleisteten Arbeits- und Überstunden in einer Ansicht angezeigt, sowie auch die bisherigen Urlaubs- und Krankheitstage. Die aktuellen Überstunden stehen ebenfalls auf den ersten Blick sichtbar drin. Die Kalenderwochen werden nach der ISO-Norm 8601 (früher DIN 1355-1) berechnet, sodass die Erkennung der 52. und 53. Kalenderwoche ohne Probleme von statten geht. An dieser Stelle, als ich diese Zeilen schrieb, merkte ich dann aber, dass ich die Anzeige der 53. KW im Tabellenblatt gar nicht berücksichtigt habe. Dies ist nun aber der Fall. Sollte ein Jahr eine 53. KW haben, so wird diese angezeigt und auch die Anzahl der Stunden eingetragen. Nachdem dies geschehen ist, schaut die Prozedur nach, ob für den heutigen Tag Eintragungen vorhanden sind. Je nachdem wird eine Meldung angezeigt, auch, wenn in der Vergangenheit Tage ohne Eintrag vorhanden sind. In meinem Fall wird das obenstehende Fenster angezeigt. Auch die gesetzlichen Feiertage werden erkannt, in meiner Datei aber nur die für Sachsen. Man kann sich natürlich auch alle anderen Feiertage einbinden. Wo das geht, zeige ich etwas später.
Nach bestätigen dieser Meldung kommen wir in das eigentliche „Hauptmenü“ der Datei. Die Menüführung mag etwas komisch erscheinen, hat aber den Hintergrund, dass die wichtigsten Funktionen gebündelt im ersten Fenster erscheinen und alle Zusatzfunktionen auf weiteren Fenstern liegen. Die Datei soll ja im Grunde nur die Arbeitszeit erfassen.

Hier haben wir nun das Hauptmenü erreicht. Wie man sieht, sind zwei Felder deaktiviert („ausgegraut“), was der Tatsache geschuldet ist, dass hier in dem Beispiel bereits Daten im aktuellen Tag erfasst wurden. Bei „Erfasste Zeit“ sieht man die aktuelle Uhrzeit. Diese wird auch einmalig beim Öffnen abgefragt und in die Caption des Option Buttons geschrieben, sodass diese fest bleibt. Im Feld „Eigene Zeit“ kann man eine individuelle Erfassung durchführen. Haben wir – wie in diesem Beispiel – bereits Arbeitsbeginn und Feierabend erfasst, können wir nur noch die Pausenzeit eintragen. Falls es für den aktuellen Tag noch gar keine Eintragung gibt, erscheinen nur die Optionen der erfassten und eigenen Zeit.
Nun haben wir hier noch drei Buttons. „Erfassen“ schreibt entweder die erfasste oder selbst eingetragene Zeit in den aktuellen Tag und fragt, ob man die Datei nun schließen möchte. Das ist die eigentliche Hauptfunktion. „Beleg drucken“ bringt ein kleines Fenster mit einem Dropdown-Feld, in dem alle Sheets, die eine Jahresliste enthalten, zur Auswahl stehen und man das jeweilige Jahr als PDF speichern kann. Wählen wir als Option „Urlaub / Krankheit“ und gehen auf „Erfassen“, kommen wir in folgendes Fenster:

In diesem Fenster werden in zwei Listenboxen die Tage des aktuellen Jahres aufgelistet und man kann per Mehrfachauswahl die jeweiligen Tage als Urlaub oder Krank markieren. In meinem Beispiel geht das Jahr 2019 in der Datei erst mit dem 12. August los.

Haben wir im „Hauptmenü“ den Button „Daten editieren / ansehen“ gewählt, bekommen wir das obige Fenster zu sehen, wobei der untere Rahmen beim Start noch aufgeblendet ist. Man kann einerseits sich neue Tabellenblätter generieren lassen, die nach dem letzten Jahr hinzugefügt werden. Als Beispiel: ist das letzte Jahr in der Excel 2020 und man lässt sich für die nächsten drei Jahre Tabellenblätter generieren, so erhält man die Tabellenblätter für 2021, 2022 und 2023. Es werden dabei wieder nur die Arbeitstage (Montag bis Freitag) generiert, gleichzeitig aber in der Spalte „Bemerkung“ eingetragen, ob es sich um einen gesetzlichen Feiertag (in Sachsen) handelt. Wählt man den Button „Arbeitszeiten anpassen / ansehen“, kommt folgendes Fenster:

Hier wird die absurde Menüstruktur ersichtlich, denn wollen wir die Datei öffnen und uns einfach nur die Daten ansehen, müssen wir uns bis zu diesem Fenster durchklicken und die Option „Bearbeiten“ wählen. Alle Fenster, die wir bisher gesehen haben, können wir auch nicht über das Kreuz in der oberen rechten Ecke schließen. Das ist so gewollt, da die Standardprozedur Öffnen Erfassen Schließen sein soll.
Wählen wir im obigen Fenster „Anzeigen“, bekommen wir wieder alle vorhandenen Tabellenblätter mit Jahreslisten beinhaltend aufgelistet und können uns ein Jahr auf der Startseite anzeigen lassen. Nachdem dieses generiert wurde, bleibt die Exceldatei aber offen. Das dient eben nur der Anzeige.
Beim Schließen der Exceldatei wird das erste Blatt ebenfalls gecleant, also alle Werte gelöscht. Das ist einfach ein Sicherheitsmechanismus, der meiner Programmierung geschuldet ist, weil ich die Stunden von Zelle zu Zelle addiere. Ein Blick in den Programmcode erklärt dies.

Feiertage für andere Bundesländer

An dieser Stelle möchte ich noch kurz auf die Funktion der Erkennung von Feiertagen eingehen. Zuerst der Programmcode:

Public Function IstFeiertag(datum) As Boolean
    Select Case Left(datum, 6)
        Case "01.01."
            IstFeiertag = True
        Case "01.05."
            IstFeiertag = True
        Case "03.10."
            IstFeiertag = True
        Case "31.10."
            IstFeiertag = True
        Case "25.12."
            IstFeiertag = True
        Case "26.12."
            IstFeiertag = True
        Case Left(Bettag(Right(datum, 4)), 6)
            IstFeiertag = True
        Case Left(DateAdd("d", Ostersonntag(Right(datum, 4)), "01.03." & Right(datum, 4)), 6)
            IstFeiertag = True
        Case Left(DateAdd("d", Ostersonntag(Right(datum, 4)) - 3, "01.03." & Right(datum, 4)), 6)
            IstFeiertag = True
        Case Left(DateAdd("d", Ostersonntag(Right(datum, 4) + 38), "01.03." & Right(datum, 4)), 6)
            IstFeiertag = True
        Case Left(DateAdd("d", Ostersonntag(Right(datum, 4) + 49), "01.03." & Right(datum, 4)), 6)
            IstFeiertag = True
        Case Else
            IstFeiertag = False
    End Select
End Function

Ich gebe in meine Funktion ein Datum ein und frage ab, ob es ein Feiertag ist (IstFeiertag = True) oder nicht (IstFeiertag = False). Man kann dies natürlich anpassen und den Namen des Feiertags ausgeben, mir reichte aber ein boolscher Wert. Weiterhin lassen sich hier natürlich entsprechend andere Feiertage einstellen. Man ändert den jeweiligen Case, z.B. „01.11.“ für Allerheiligen, das in Baden-Württemberg, Bayern, Nordrhein-Westfalen, Rheinland-Pfalz und dem Saarland ein Feiertag ist. Da dies als Funktion implementiert wurde, muss die Änderung nur einmal erfolgen und ist somit global für die ganze Datei gültig.
Hier fällt übrigens auch eine weitere Funktion auf: „Ostersonntag“. Das ist eine weitere Funktion die ich mir geschrieben habe und beinhaltet nur die Osterformel nach Gauß, die 1997 durch Heiner Lichtenberg für die computergestützte Berechnung angepasst wurde:

Public Function Ostersonntag(jahr)
    k = jahr \ 100
    m = 15 + (3 * k + 3) \ 4 - (8 * k + 13) \ 25
    s = 2 - (3 * k + 3) \ 4
    a = jahr Mod 19
    d = (19 * a + m) Mod 30
    r = (d + a \ 11) \ 29
    OG = 21 + d - r
    SZ = 7 - (jahr + jahr \ 4 + s) Mod 7
    OE = 7 - (OG - SZ) Mod 7
    Ostersonntag = OG + OE
End Function

Hierbei gebe ich lediglich der Funktion das Jahr und die spuckt mir das Datum des Ostermontags aus. Das habe ich erst festgestellt, nachdem ich die Funktion so genannt und überall darauf bezogen habe. Macht aber nichts, da ich ja um diesen Umstand weiß 😉 Die Funktion errechnet ein Märzdatum, also z.B. den 53. März. Den Zahlenwert 53 kann man dann mit der DateAdd-Funktion auf den 01.03. aufaddieren und erhält dann das richtige Aprildatum. Alle anderen Daten der Karwoche ergeben sich dann daraus. Eine dritte Funktion, die ich mir gebaut habe, ist die Errechnung des Buß- und Bettagsdatums, das immer der letzte Mittwoch vor dem 23. November ist:

Public Function Bettag(jahr)
    hilf = "23.11." & jahr
    If Weekday(hilf, vbMonday) = 3 Then
        Bettag = DateAdd("d", -7, hilf)
    Else
        Do
            hilf = DateAdd("d", -1, hilf)
        Loop Until Weekday(hilf, vbMonday) = 3
        Bettag = hilf
    End If
End Function

Falls der 23.11. selbst ein Mittwoch ist, fällt der Buß- und Bettag auf den 16.11., ansonsten entsprechend einen Tag davor, spätestens auf den 22.11.

So, nach allerhand Erklärung soll es nun hier die Datei zum Download geben. Bei Anregungen oder Fehlern freue ich mich über einen Kommentar oder eine E-Mail. 🙂

Das Problem mit der fehlenden Bibliothek

Leider kann es beim ersten Öffnen zu einem Fehler kommt. Dieser hängt mit einem fehlerhaften Bezug zu einer Bibliothek zusammen. Wie genau das passieren konnte, weiß ich nicht, allerdings gibt es ein kleines Workaround, das einmalig auszuführen ist:

• 

• 

• 

• 

•