{"text_format":"1","workbook":"xlsx","n_sheets":5,"sheets":["Funktionen","Subroutinen","Val","Kalender","Code"],"not_rendered":{"charts":0,"images":1,"macros":1,"external_links":0,"defined_names":7,"comments":0,"pivot_tables":0},"unopened":[],"bounds":{"document_bytes":16777216,"sheet_cells":100000,"rows_first":2000,"rows_last":500,"rows_between":2000,"halvings":0,"cell_chars":16384,"cell_head_chars":4096},"cells_cut":0,"cells_raw":0,"read":"whole","date_system":"1900"}

{"sheet":"Funktionen","index":0,"n_rows":43,"n_cols":3,"rows_kept":43,"cut":false,"hidden":false,"merged":[]}
["Formular","Funktions-Name","Beschreibung"]
[null,"Function FindFirst_in_Col(ColNr As Long, SuchTxt As String) As Long","Sucht den ersten TextWert vertikal der übergebenen Kollone 'Rückgabe= ReihenNr als Long"]
[null,"Function FindFirst_in_Row(RowNr As Long, SuchTxt As String) As Long"," Sucht den ersten TextWert horizontal der übergebenen Reihe 'Rückgabe= SpaltenNr als Long"]
["UF_DatZeit","Public Function Alter(GebDatum) As Long","Aus dem Geburtsdatum wird das Alter exakt berechnet"]
["UF_DateiFuncSub","Public Function Benutzer_Bereich(Tabelle As String) As String","Sucht die letzten Zelle Horizontal und Vertikal und retoutniert diesen Bereich im Adressformat"]
[null,"Public Function ColNr_in_Char(Koll As Long) As String","Ermittelt die Buchstaben der Spaltenbeschriftung der übergebenen Reihe; Rückwert= 1 oder 2 Buchstaben der Spalte als String"]
["UF_DateiFuncSub","Public Function DateinameClean(sDateiname As String) As String","verbotene Zeichen in sDateiname \\ / : * ? \" < > |  löschen"]
["Kalender","Public Function DaysInMonth(Jh As Integer, Mt As Integer) As Integer","retourniert die Anzahl Tage im angegebenen Monat als Integer"]
["Kalender","Public Function FeiertagText(DatumX As Date, OsternJh As Date) As String","Die Liste beinhaltet die Schweizer Feiertage"]
[null,"Public Function FirstEmptyCell(Kollone As Long) As Long","Ermittelt die erste freie Reihe in der übergebenen Spalte; Rückgabe = Reihe als Long"]
[null,"Public Function FirstEmptyCol(Reihe As Long) As Long"," Ermittelt die erste freie Spalte in der übergebenen Reihe; Rückgabe = Spalte als Long"]
["UF_DatZeit","Public Function FistDayKW(Wo As Integer) As Date","Startdatum einer Kalenderwoche  des aktuellen Jahres berechnen"]
[null,"Public Function HoursAndMinutes(datetime As Date) As String","Wandelt ein Date (Zeitwert) in Stunden und Minuten (Format ss:mm)"]
["UF_DatZeit","Public Function IstSchaltjahr(JahrIn As Long) As Boolean","Ist das Datum in einem Schaltjahr"]
["Kalender","Public Function KWX(d As Date) As Integer","retourniert die Kalenderwoche als Integer"]
[null,"Public Function lastColChar(Reihe As Long) As String"," Ermittelt die Buchstaben der letzen benutzten Spaltenbeschriftung; Rückwert= 1 oder 2 Buchstben der Spalte als String"]
[null,"Public Function lastUsedCell(Kollone As Long) As Long"," Ermittelt die letzte benutzte Reihe in der übergebenen Spalte; Rückgabe = Reihe als Long"]
[null,"Public Function lastUsedCol(Reihe As Long) As Long","Ermittelt die letzte benutzte Spalte in der übergebenen Reihe; Rückgabe = Spalte als Long"]
[null,"Public Function lng_HEX(H1 As Long) As String","Zahl in einen Hex-String umwandeln"]
[null,"Public Function LongFarbe_zu_RGB(Farbe As Long) As String","Der Longwert einer Farbe wird in die Anteile Rot,Grün,Blau zerlegt"]
["UF_DatZeit","Public Function MoDatum(XDatum As Date) As Date","Gibt Datum des Montags der aktuellen Woche zurück"]
["UF_DateiFuncSub","Public Function new_TabIndx() As Integer","Sucht den nächstfreine Tabellenindex in einem Worksheet"]
["Kalender","Public Function OsterSonntag(Jahr As Integer) As Date","Retoutniert das Datum für den Ostersonntag"]
["UF_DateiFuncSub","Public Function Pick_Sel_Datei() As String","Der selektierte Pfad aus dem Dialog Filepicker wird retourniert"]
[null,"Public Function RGB_zu_Hex(R As Long, G As Long, B As Long) As String","Hexwert aus den Werten r, g, b ermitteln"]
[null,"Public Function RGB_zu_HexBGR(R As Long, G As Long, B As Long) As String","HEX Darstellung wie sie bei Userform Backcolor und Forecolor benötigt wird"]
[null,"Public Function RGB_zu_LongFarbe(txtRGB As String) As Long","Der Eingabetext hat das Format \"RGB(rrr,ggg,bbb)\""]
["UF_DatZeit","Public Function SchaltJh(PJahr As Integer) As Boolean","Gibt True zurück wenn Schaltjahr"]
["UF_DatZeit","Public Function StartDatKW(KaWo As Integer) As Date","Berechent das Startdatum einer 1. Kalenderwoche"]
["Val_Stunden","Public Function StdMininDez(Std As Integer, Min As Integer) As Single","StdMininDec wandelt Stunden und Minuten in Dezimalstunden um"]
[null,"Public Function Str_in_Colx(Spalte As Long, SuchWert As String) As Long","Sucht sequentiell einen Wert vertikal der übergebenen Spalte 'Rückgabe= AnzGefunden als Long"]
[null,"Public Function Str_in_Rowx(Reihe As Long, SuchWert As String) As Long"," Sucht sequentiell einen Wert horizontal der übergebenen Reihe 'Rückgabe= AnzGefunden als Long"]
["Kalender","Public Function TageMonat(Datin As Date) As Integer","Anzahl Tage im Monat des übergebenen Datum"]
[null,"Public Function ValEMail(ByVal strEmailAddress As String) As Boolean","Email Adresse prüfen"]
[null,"Public Function Wait(MilliSekunden As Double)","Systemzeit anhalte für Millisekunden"]
["Kalender","Public Function WorkDaysInMonth(Jh As Integer, Mt As Integer) As Integer","Retourniert die Anzahl Werktage (ohne Sa,So) im angegebenen Monat als Integer"]
["Kalender","Public Function WoTgName(Kurz As Boolean, WoTg As Long) As String","Kurz = true >> Son,Mon ... Kurz = False >>> Sonntag , Montag .."]
[null,"Public Function Zahl_Double_Komma(ByVal Z As Integer, FehlMsg As Boolean) As Boolean","Nur Zahlen(48-57) und Komma (44) zulassen (Single)"]
[null,"Public Function Zahl_Double_Punkt(ByVal Z As Integer, FehlMsg As Boolean) As Boolean","Nur Zahlen(48-57) und den Punkt (46) zulassen (prüft Singel-Wert)"]
[null,"Public Function Zahl_Ganze(ByVal Z As Integer, FehlMsg As Boolean) As Boolean","Nur Zahlen(48-57)zulassen (prüft Kardinalzahl)"]
["Val_Stunden","Public Function Zahl_hhmmss(ByVal Z As Integer, FehlMsg As Boolean) As Boolean","Nur Zahlen(48-57) und den DoppelPunkt (58) zulassen (Single) für Zeitangaben"]
["Val_Zahlen","Public Function IstPrim(Zahl) As Boolean","Ist eine Zahl Primzahl"]
[null,"Public Function Clipboard(Optional StoreText As String) As String","ermöghlicht sreibe und lesen in die Zwischenablage"]

{"sheet":"Subroutinen","index":1,"n_rows":35,"n_cols":3,"rows_kept":35,"cut":false,"hidden":false,"merged":[]}
["Test-Formular","Subroutine-Name","Beschreibung"]
[null,"Public Sub Col_x_to_y_hide(StartKoll As Long, EndKoll As Long, JaNein As Boolean)","versteckt oder zeigt die angegeben Kollonen von x bis Y (zusammenhängender Bereich erforderlich)"]
["UF_DateiFuncSub","Public Sub Datei_Exist(DateiPfad As String)","Prüfen ob Datei existiert / wenn Datei nicht vorhanden wird Err.Number = 53 ezeugt"]
["UF_DateiFuncSub","Public Sub Datei_Speichern_per_Dlg(Datei As String, Titeltext As String)","FileDialogSaveAs_Dialog öffenen mit Vorgaben"]
[null,"Public Sub Datenliste_Zuweisen_Bereich(BereichZu As String, Quelle As String)","Einem Bereich wird eine DatenListe zugewiesen"]
[null,"Public Sub Datenliste_Zuweisen_Spalte(Spalte As String, Quelle As String)","Einer Spalte wird eine DatenListe zugewiesen"]
["Kalender","Public Sub Druck_Vorbereiten(Druckbereich As String, TitelReihen As String, AnzSeitenBreite As Integer, HochQuer As Integer)","Druckvorschau aufbauen und zeigen."]
["Kalender","Public Sub Kalender(bolFeiertage As Boolean)","Kalender zeichnen"]
[null,"Public Sub Kollx_hide(KollNr As Long, JaNein As Boolean)","Versteckt oder zeigt die angegeben Kollone"]
["UF_DateiFuncSub","Public Sub Laufwerke()","Die Laufwerke werden in eine Combobox Namens CBDir eingelesen"]
[null,"Public Sub LinienBereich(StartRow As Long, EndRow As Long, StartCol As String, EndCol As String)","jede 2, Linie grau Zeilenbereich bestimmen und letzte Zeile hellgrün färben."]
[null,"Public Sub LinienUnten_Bereich(Bereich As String)","Linien unten den Zellen eintrage im übergebenen Bereich"]
["UF_DateiFuncSub","Public Sub Ordner_vorhanden(OrdnerName As String)","Es wird geprüft ob der Ordner vorhanden ist."]
["UF_DateiFuncSub","Public Sub OrdnerAuflisten(Dir As String)","Ordener des Dir auflisten und in Taballe Val Spalte O abspeichern"]
[null,"Public Sub PDF_per_EMail()","Eine Tabelle als Pfd-Dataie versenden"]
[null,"Public Sub PDFDateiausTabelle(BlattName As String)","erstellt eine PDF-Datei der übergebenen Tabelle"]
["UF_DateiFuncSub","Public Sub Pfad_Vorhanden(OrdnerName As String)"," Prüft Pfad vorhanden wenn nicht erstellen"]
[null,"Public Sub PivotAufdatieren()","Pivottabelle aufdatieren"]
[null,"Public Sub RegisterFarbe(Blatt As String, Farbe As Single)","Registerfarbe einer Tabelle festlegen"]
["UF_DateiFuncSub","Public Sub Save_to_Desktop()","Save Datei auf Desktop erstellen / überschreibt allenfalss bestehende Datei"]
[null,"Public Sub ScrollBereich()","Der Vertikalscroll bereich wird auf den benutzten B"]
["Val_Listen","Public Sub SortBereich(Bereich As String, Schlüssel As String)","Sortiert einen Bereich mit einem Schlüssel"]
[null,"Public Sub SortTwoKey(Bereich As String, Schlüssel1, Schlüssel2 As String)","Zweistuffiges Sortieren einen Bereich mit zwei Schlüssel"]
[null,"Public Sub SpalteAutoFit()","Die Zellen der Tabell den Inhalt anpassen"]
[null,"Public Sub SpaltenTextWrapen(Spalte As Long, VonReihe As Long, AnzReihen As Long)","Zeilenumbruch ausführen"]
[null,"Public Sub Sprung_zuTabelle(BlattName_Zelle)","Beispiel für Blattname und Zelle  >>> \"Control!A1\""]
["UF_DatZeit","Public Sub StdDezinStdMin(StdDez As Single)","Wandelt Dezimalstunden in Stunden und Minuten als Single um"]
["Val_Listen","Public Sub Tab_in_CB_einlesen(KomboBox As ComboBox)","Alle Tabellennamen des Workbook in Combobox einlesen"]
[null,"Public Sub Tabellenblatt_Copy(WBook As String, WBlatt As String)","Selektiert ein Worksheet und kopiert das ganze Tabellenblatt"]
[null,"Public Sub Tabellenblatt_Paste(WBook As String, SWBlatt As String)","Einfügen von Zwischenablage in das ganze gewählte Tabellenblatt des abgegeben Workbook"]
["Val_Listen","Public Sub TabName_auflisten(Blatt As String, Spalte As Long, Zeile As Long)","Auflisten aller Tabellennamen in die übergebene Spalte des übergebenen Tabellenname"]
[null,"Public Sub Titelspalten_in_CB_Add(Tabelle As String, Kombobox As ComboBox, Titelspalte As Long)","Titelspalte in eine Combobox einlesen"]
[null,"Public Sub Zeile_löschen(Reihe As Long)","Löscht die übergeben Reihe und Schiebt den Rest der Tabelle nach oben"]
[null,"Public Sub Zeilen_fixieren(Zelle As String)","Fixiert das Fester je nach Zelle als Eckpunkt,so dass sie immer sichtbar sind"]
[null,"Public Sub ZellenRand_dünn(VonBis As String)","Die Zellen des angegebene Berieches erhalten eine Umrnduneg mit dünner Linie"]

{"sheet":"Val","index":2,"n_rows":49,"n_cols":20,"rows_kept":49,"cut":false,"hidden":false,"merged":[]}
["Tect","Listen","Zahlen","Texte",null,null,"Stundenrechner",null,null,10,null,null,null,null,null,"Pfade",null,null,null,"Text"]
["Land","Fläche (km²)","Einwohner","Hauptstadt","Gemüse","Tabelle","Rückgabe","Land","m2/Person","Schrift","Grösse",null,null,"FirmaXY","AE_IconTasten","C:\\Users\\mkuet\\",null,null,null,"Freude, schöner Götterfunken, Tochter aus Elysium,\r\nWir betreten feuertrunken, Himmlische, dein Heiligtum.\r\nDeine Zauber binden wieder,\r\nWas die Mode streng geteilt,\r\nAlle Menschen werden Brüder,\r\nWo dein sanfter Flügel weilt.\r\n\r\nWem der große Wurf gelungen, eines Freundes Freund zu sein wer ein holdes Weib errungen, mische seinen Jubel ein!\r\nJa--wer auch nur eine Seele sein nennt auf dem Erdenrund!\r\nUnd wer's nie gekonnt, der stehle weinend sich aus diesem Bund!\r\n\r\nFreude heißt die starke Feder in der ewigen Natur.\r\nFreude, Freude treibt die Räder in der großen Weltenuhr.\r\nBlumen lockt sie aus den Keimen, Sonnen aus dem Firmament,\r\nSphären rollt sie in den Räumen, die des Sehers Rohr nicht kennt."]
["Albanien",28748,3560000,"Tirana","Brokkoli","Funktionen","Beginn = ","Albanien",{"$f":"(B3*1000)/C3","v":8.0752808988764038},"Arial",8,null,null,null,"Chaos","E:\\AA_Excel"]
["Andorra",464,72300,"Andorra la Vella","Chicorée","Subroutinen","Ende = ","Andorra",6.4177040110650072,"Aharoni",10,null,null,null,"Gutscheine","G:\\.Bilder\\2_Business"]
["Belgien",30518,1030000,"Brüssel","Chinakohl","Val","aktiveZeit = ","Belgien",29.629126213592233,"Algerian",11,null,null,null,"Icon","E:\\AA_Excel\\AE_APlan_C\\Bilder\\FirmaX.bmp"]
["Bosnien-Herzegowina",51129,3800000,"Sarajevo","Karotten","Kalender","Unterbruch = ","Bosnien-Herzegowina",13.455,"AcmeFont",12,null,null,null,"Kurioses"]
["Bulgarien",110994,7810000,"Sofia","Mangold","Code",null,"Bulgarien",14.211779769526249,"Archicoco",14,null,null,null,"Lachskizzen"]
["Dänemark",43093,5410000,"Kopenhagen","Microgreens",null,null,"Dänemark",7.9654343807763404,"Baltahazar",16,null,null,null,"LOGOS"]
["Deutschland",357114,82220000,"Berlin","Petersilie",null,null,"Deutschland",4.343395767453174,"Bastion",null,null,null,null,"Musik"]
["Estland",45227,1350000,"Tallinn","Rote Bete",null,null,"Estland",33.501481481481484,"Bernadette",null,null,null,null,"Satierskizzen"]
["Finnland",338145,5220000,"Helsinki","Spinat",null,null,"Finnland",64.77873563218391,"Calibri",null,null,null,null,"Signale"]
["Frankreich",547026,60660000,"Paris",null,null,null,"Frankreich",9.0179030662710193,"Calvin",null,null,null,null,"Signaturen"]
["Griechenland",131957,10670000,"Athen",null,null,null,"Griechenland",12.367104029990628,"Emmett",null,null,null,null,"Softwarlizenzen"]
["Großbritannien und Nordirland",244820,60440000,"London",null,null,null,"Großbritannien und Nordirland",4.0506287227001989,"Garamond",null,null,null,null,"Neuer Ordner"]
["Irland",70284,4040000,"Dublin",null,null,null,"Irland",17.397029702970297,"Harvest",null,null,null,null,"Button"]
["Island",103000,293500,"Reykjavik",null,null,null,"Island",350.93696763202723,"Pirate"]
["Italien",301230,57890000,"Rom",null,null,null,"Italien",5.2034893764035237,"Roland"]
["Kosovo",10887,2130000,"Pristina",null,null,null,"Kosovo",5.1112676056338024]
["Kroatien",56600,4490000,"Zagreb",null,null,null,"Kroatien",12.605790645879733]
["Lettland",64600,2350000,"Riga",null,null,null,"Lettland",27.48936170212766]
["Liechtenstein",160,34500,"Vaduz",null,null,null,"Liechtenstein",4.63768115942029]
["Litauen",65200,3440000,"Wilna (Vilnius)",null,null,null,"Litauen",18.953488372093023]
["Luxemburg",2586,462500,"Luxemburg",null,null,null,"Luxemburg",5.5913513513513511]
["Mazedonien",25714,2050000,"Skopje",null,null,null,"Mazedonien",12.543414634146341]
["Malta",316,399000,"Valletta",null,null,null,"Malta",0.79197994987468667]
["Moldawien",33843,4460000,"Chisinau",null,null,null,"Moldawien",7.5881165919282507]
["Monaco",1.95,32400,"Monaco-Ville",null,null,null,"Monaco",6.0185185185185182E-2]
["Montenegro",13812,680000,"Podgorica",null,null,null,"Montenegro",20.311764705882354]
["Niederlande",41864,16290000,"Amsterdam",null,null,null,"Niederlande",2.5699201964395333]
["Norwegen",323759,4580000,"Oslo",null,null,null,"Norwegen",70.689737991266369]
["Österreich",83870,9190000,"Wien",null,null,null,"Österreich",9.1262241566920572]
["Polen",312684,38610000,"Warschau",null,null,null,"Polen",8.0985236985236977]
["Portugal",92289,10570000,"Lissabon",null,null,null,"Portugal",8.7312204351939453]
["Rumänien",238931,22310000,"Bukarest",null,null,null,"Rumänien",10.709592111160914]
["Russland",17075300,145180000,"Moskau",null,null,null,"Russland",117.61468521834963]
["San Marino",61,29000,"San Marino",null,null,null,"San Marino",2.103448275862069]
["Schweden",449964,9010000,"Stockholm",null,null,null,"Schweden",49.940510543840176]
["Schweiz",41290,7450000,"Bern",null,null,null,"Schweiz",5.5422818791946309]
["Serbien",88361,10000000,"Belgrad",null,null,null,"Serbien",8.8361000000000001]
["Slowakei",49036,5430000,"Bratislava",null,null,null,"Slowakei",9.030570902394107]
["Slowenien",20152,2010000,"Ljubljana",null,null,null,"Slowenien",10.025870646766169]
["Spanien",504982,44110000,"Madrid",null,null,null,"Spanien",11.448243028791657]
["Tschechische Republik",78864,10300000,"Prag",null,null,null,"Tschechische Republik",7.656699029126214]
["Türkei",779452,70300000,"Ankara",null,null,null,"Türkei",11.087510668563301]
["Ukraine",6037,47620000,"Kiew",null,null,null,"Ukraine",0.12677446451070978]
["Ungarn",93032,10910000,"Budapest",null,null,null,"Ungarn",8.5272227314390463]
["Vatikan",0.4,800,"Vatikanstadt",null,null,null,"Vatikan",0.5]
["Weißrussland",207600,10300000,"Minsk",null,null,null,"Weißrussland",20.155339805825243]
["Zypern",9251,780000,"Nikosia",null,null,null,"Zypern",11.86025641025641]

{"sheet":"Kalender","index":3,"n_rows":373,"n_cols":6,"rows_kept":373,"cut":false,"hidden":false,"merged":[]}
[null,"zu Datum",null,null,"Kalender zeichnen","Monat drucken"]
["Datum","Tag","KW","Mt","Termin","Notiz"]
["27.12.2021","Montag",52,"Dez"]
["28.12.2021","Dienstag",52,"Dez"]
["29.12.2021","Mittwoch",52,"Dez"]
["30.12.2021","Donnerstag",52,"Dez"]
["31.12.2021","Freitag",52,"Dez"]
["01.01.2022","Samstag",52,"Jan"]
["02.01.2022","Sonntag",52,"Jan"]
["03.01.2022","Montag",1,"Jan"]
["04.01.2022","Dienstag",1,"Jan"]
["05.01.2022","Mittwoch",1,"Jan"]
["06.01.2022","Donnerstag",1,"Jan"]
["07.01.2022","Freitag",1,"Jan"]
["08.01.2022","Samstag",1,"Jan"]
["09.01.2022","Sonntag",1,"Jan"]
["10.01.2022","Montag",2,"Jan"]
["11.01.2022","Dienstag",2,"Jan"]
["12.01.2022","Mittwoch",2,"Jan"]
["13.01.2022","Donnerstag",2,"Jan"]
["14.01.2022","Freitag",2,"Jan"]
["15.01.2022","Samstag",2,"Jan"]
["16.01.2022","Sonntag",2,"Jan"]
["17.01.2022","Montag",3,"Jan"]
["18.01.2022","Dienstag",3,"Jan"]
["19.01.2022","Mittwoch",3,"Jan"]
["20.01.2022","Donnerstag",3,"Jan"]
["21.01.2022","Freitag",3,"Jan"]
["22.01.2022","Samstag",3,"Jan"]
["23.01.2022","Sonntag",3,"Jan"]
["24.01.2022","Montag",4,"Jan"]
["25.01.2022","Dienstag",4,"Jan"]
["26.01.2022","Mittwoch",4,"Jan"]
["27.01.2022","Donnerstag",4,"Jan"]
["28.01.2022","Freitag",4,"Jan"]
["29.01.2022","Samstag",4,"Jan"]
["30.01.2022","Sonntag",4,"Jan"]
["31.01.2022","Montag",5,"Jan"]
["01.02.2022","Dienstag",5,"Feb"]
["02.02.2022","Mittwoch",5,"Feb"]
["03.02.2022","Donnerstag",5,"Feb"]
["04.02.2022","Freitag",5,"Feb"]
["05.02.2022","Samstag",5,"Feb"]
["06.02.2022","Sonntag",5,"Feb"]
["07.02.2022","Montag",6,"Feb"]
["08.02.2022","Dienstag",6,"Feb"]
["09.02.2022","Mittwoch",6,"Feb"]
["10.02.2022","Donnerstag",6,"Feb"]
["11.02.2022","Freitag",6,"Feb"]
["12.02.2022","Samstag",6,"Feb"]
["13.02.2022","Sonntag",6,"Feb"]
["14.02.2022","Montag",7,"Feb"]
["15.02.2022","Dienstag",7,"Feb"]
["16.02.2022","Mittwoch",7,"Feb"]
["17.02.2022","Donnerstag",7,"Feb"]
["18.02.2022","Freitag",7,"Feb"]
["19.02.2022","Samstag",7,"Feb"]
["20.02.2022","Sonntag",7,"Feb"]
["21.02.2022","Montag",8,"Feb"]
["22.02.2022","Dienstag",8,"Feb"]
["23.02.2022","Mittwoch",8,"Feb"]
["24.02.2022","Donnerstag",8,"Feb"]
["25.02.2022","Freitag",8,"Feb"]
["26.02.2022","Samstag",8,"Feb"]
["27.02.2022","Sonntag",8,"Feb"]
["28.02.2022","Montag",9,"Feb"]
["01.03.2022","Dienstag",9,"Mär"]
["02.03.2022","Mittwoch",9,"Mär"]
["03.03.2022","Donnerstag",9,"Mär"]
["04.03.2022","Freitag",9,"Mär"]
["05.03.2022","Samstag",9,"Mär"]
["06.03.2022","Sonntag",9,"Mär"]
["07.03.2022","Montag",10,"Mär"]
["08.03.2022","Dienstag",10,"Mär"]
["09.03.2022","Mittwoch",10,"Mär"]
["10.03.2022","Donnerstag",10,"Mär"]
["11.03.2022","Freitag",10,"Mär"]
["12.03.2022","Samstag",10,"Mär"]
["13.03.2022","Sonntag",10,"Mär"]
["14.03.2022","Montag",11,"Mär"]
["15.03.2022","Dienstag",11,"Mär"]
["16.03.2022","Mittwoch",11,"Mär"]
["17.03.2022","Donnerstag",11,"Mär"]
["18.03.2022","Freitag",11,"Mär"]
["19.03.2022","Samstag",11,"Mär"]
["20.03.2022","Sonntag",11,"Mär"]
["21.03.2022","Montag",12,"Mär"]
["22.03.2022","Dienstag",12,"Mär"]
["23.03.2022","Mittwoch",12,"Mär"]
["24.03.2022","Donnerstag",12,"Mär"]
["25.03.2022","Freitag",12,"Mär"]
["26.03.2022","Samstag",12,"Mär"]
["27.03.2022","Sonntag",12,"Mär"]
["28.03.2022","Montag",13,"Mär"]
["29.03.2022","Dienstag",13,"Mär"]
["30.03.2022","Mittwoch",13,"Mär"]
["31.03.2022","Donnerstag",13,"Mär"]
["01.04.2022","Freitag",13,"Apr"]
["02.04.2022","Samstag",13,"Apr"]
["03.04.2022","Sonntag",13,"Apr"]
["04.04.2022","Montag",14,"Apr"]
["05.04.2022","Dienstag",14,"Apr"]
["06.04.2022","Mittwoch",14,"Apr"]
["07.04.2022","Donnerstag",14,"Apr"]
["08.04.2022","Freitag",14,"Apr"]
["09.04.2022","Samstag",14,"Apr"]
["10.04.2022","Sonntag",14,"Apr"]
["11.04.2022","Montag",15,"Apr"]
["12.04.2022","Dienstag",15,"Apr"]
["13.04.2022","Mittwoch",15,"Apr"]
["14.04.2022","Donnerstag",15,"Apr"]
["15.04.2022","Freitag",15,"Apr"]
["16.04.2022","Samstag",15,"Apr"]
["17.04.2022","Sonntag",15,"Apr"]
["18.04.2022","Montag",16,"Apr"]
["19.04.2022","Dienstag",16,"Apr"]
["20.04.2022","Mittwoch",16,"Apr"]
["21.04.2022","Donnerstag",16,"Apr"]
["22.04.2022","Freitag",16,"Apr"]
["23.04.2022","Samstag",16,"Apr"]
["24.04.2022","Sonntag",16,"Apr"]
["25.04.2022","Montag",17,"Apr"]
["26.04.2022","Dienstag",17,"Apr"]
["27.04.2022","Mittwoch",17,"Apr"]
["28.04.2022","Donnerstag",17,"Apr"]
["29.04.2022","Freitag",17,"Apr"]
["30.04.2022","Samstag",17,"Apr"]
["01.05.2022","Sonntag",17,"Mai"]
["02.05.2022","Montag",18,"Mai"]
["03.05.2022","Dienstag",18,"Mai"]
["04.05.2022","Mittwoch",18,"Mai"]
["05.05.2022","Donnerstag",18,"Mai"]
["06.05.2022","Freitag",18,"Mai"]
["07.05.2022","Samstag",18,"Mai"]
["08.05.2022","Sonntag",18,"Mai"]
["09.05.2022","Montag",19,"Mai"]
["10.05.2022","Dienstag",19,"Mai"]
["11.05.2022","Mittwoch",19,"Mai"]
["12.05.2022","Donnerstag",19,"Mai"]
["13.05.2022","Freitag",19,"Mai"]
["14.05.2022","Samstag",19,"Mai"]
["15.05.2022","Sonntag",19,"Mai"]
["16.05.2022","Montag",20,"Mai"]
["17.05.2022","Dienstag",20,"Mai"]
["18.05.2022","Mittwoch",20,"Mai"]
["19.05.2022","Donnerstag",20,"Mai"]
["20.05.2022","Freitag",20,"Mai"]
["21.05.2022","Samstag",20,"Mai"]
["22.05.2022","Sonntag",20,"Mai"]
["23.05.2022","Montag",21,"Mai"]
["24.05.2022","Dienstag",21,"Mai"]
["25.05.2022","Mittwoch",21,"Mai"]
["26.05.2022","Donnerstag",21,"Mai"]
["27.05.2022","Freitag",21,"Mai"]
["28.05.2022","Samstag",21,"Mai"]
["29.05.2022","Sonntag",21,"Mai"]
["30.05.2022","Montag",22,"Mai"]
["31.05.2022","Dienstag",22,"Mai"]
["01.06.2022","Mittwoch",22,"Jun"]
["02.06.2022","Donnerstag",22,"Jun"]
["03.06.2022","Freitag",22,"Jun"]
["04.06.2022","Samstag",22,"Jun"]
["05.06.2022","Sonntag",22,"Jun"]
["06.06.2022","Montag",23,"Jun"]
["07.06.2022","Dienstag",23,"Jun"]
["08.06.2022","Mittwoch",23,"Jun"]
["09.06.2022","Donnerstag",23,"Jun"]
["10.06.2022","Freitag",23,"Jun"]
["11.06.2022","Samstag",23,"Jun"]
["12.06.2022","Sonntag",23,"Jun"]
["13.06.2022","Montag",24,"Jun"]
["14.06.2022","Dienstag",24,"Jun"]
["15.06.2022","Mittwoch",24,"Jun"]
["16.06.2022","Donnerstag",24,"Jun"]
["17.06.2022","Freitag",24,"Jun"]
["18.06.2022","Samstag",24,"Jun"]
["19.06.2022","Sonntag",24,"Jun"]
["20.06.2022","Montag",25,"Jun"]
["21.06.2022","Dienstag",25,"Jun"]
["22.06.2022","Mittwoch",25,"Jun"]
["23.06.2022","Donnerstag",25,"Jun"]
["24.06.2022","Freitag",25,"Jun"]
["25.06.2022","Samstag",25,"Jun"]
["26.06.2022","Sonntag",25,"Jun"]
["27.06.2022","Montag",26,"Jun"]
["28.06.2022","Dienstag",26,"Jun"]
["29.06.2022","Mittwoch",26,"Jun"]
["30.06.2022","Donnerstag",26,"Jun"]
["01.07.2022","Freitag",26,"Jul"]
["02.07.2022","Samstag",26,"Jul"]
["03.07.2022","Sonntag",26,"Jul"]
["04.07.2022","Montag",27,"Jul"]
["05.07.2022","Dienstag",27,"Jul"]
["06.07.2022","Mittwoch",27,"Jul"]
["07.07.2022","Donnerstag",27,"Jul"]
["08.07.2022","Freitag",27,"Jul"]
["09.07.2022","Samstag",27,"Jul"]
["10.07.2022","Sonntag",27,"Jul"]
["11.07.2022","Montag",28,"Jul"]
["12.07.2022","Dienstag",28,"Jul"]
["13.07.2022","Mittwoch",28,"Jul"]
["14.07.2022","Donnerstag",28,"Jul"]
["15.07.2022","Freitag",28,"Jul"]
["16.07.2022","Samstag",28,"Jul"]
["17.07.2022","Sonntag",28,"Jul"]
["18.07.2022","Montag",29,"Jul"]
["19.07.2022","Dienstag",29,"Jul"]
["20.07.2022","Mittwoch",29,"Jul"]
["21.07.2022","Donnerstag",29,"Jul"]
["22.07.2022","Freitag",29,"Jul"]
["23.07.2022","Samstag",29,"Jul"]
["24.07.2022","Sonntag",29,"Jul"]
["25.07.2022","Montag",30,"Jul"]
["26.07.2022","Dienstag",30,"Jul"]
["27.07.2022","Mittwoch",30,"Jul"]
["28.07.2022","Donnerstag",30,"Jul"]
["29.07.2022","Freitag",30,"Jul"]
["30.07.2022","Samstag",30,"Jul"]
["31.07.2022","Sonntag",30,"Jul"]
["01.08.2022","Montag",31,"Aug"]
["02.08.2022","Dienstag",31,"Aug"]
["03.08.2022","Mittwoch",31,"Aug"]
["04.08.2022","Donnerstag",31,"Aug"]
["05.08.2022","Freitag",31,"Aug"]
["06.08.2022","Samstag",31,"Aug"]
["07.08.2022","Sonntag",31,"Aug"]
["08.08.2022","Montag",32,"Aug"]
["09.08.2022","Dienstag",32,"Aug"]
["10.08.2022","Mittwoch",32,"Aug"]
["11.08.2022","Donnerstag",32,"Aug"]
["12.08.2022","Freitag",32,"Aug"]
["13.08.2022","Samstag",32,"Aug"]
["14.08.2022","Sonntag",32,"Aug"]
["15.08.2022","Montag",33,"Aug"]
["16.08.2022","Dienstag",33,"Aug"]
["17.08.2022","Mittwoch",33,"Aug"]
["18.08.2022","Donnerstag",33,"Aug"]
["19.08.2022","Freitag",33,"Aug"]
["20.08.2022","Samstag",33,"Aug"]
["21.08.2022","Sonntag",33,"Aug"]
["22.08.2022","Montag",34,"Aug"]
["23.08.2022","Dienstag",34,"Aug"]
["24.08.2022","Mittwoch",34,"Aug"]
["25.08.2022","Donnerstag",34,"Aug"]
["26.08.2022","Freitag",34,"Aug"]
["27.08.2022","Samstag",34,"Aug"]
["28.08.2022","Sonntag",34,"Aug"]
["29.08.2022","Montag",35,"Aug"]
["30.08.2022","Dienstag",35,"Aug"]
["31.08.2022","Mittwoch",35,"Aug"]
["01.09.2022","Donnerstag",35,"Sep"]
["02.09.2022","Freitag",35,"Sep"]
["03.09.2022","Samstag",35,"Sep"]
["04.09.2022","Sonntag",35,"Sep"]
["05.09.2022","Montag",36,"Sep"]
["06.09.2022","Dienstag",36,"Sep"]
["07.09.2022","Mittwoch",36,"Sep"]
["08.09.2022","Donnerstag",36,"Sep"]
["09.09.2022","Freitag",36,"Sep"]
["10.09.2022","Samstag",36,"Sep"]
["11.09.2022","Sonntag",36,"Sep","New York Twintower zusammenbruch","Notiz"]
["12.09.2022","Montag",37,"Sep"]
["13.09.2022","Dienstag",37,"Sep"]
["14.09.2022","Mittwoch",37,"Sep"]
["15.09.2022","Donnerstag",37,"Sep"]
["16.09.2022","Freitag",37,"Sep"]
["17.09.2022","Samstag",37,"Sep"]
["18.09.2022","Sonntag",37,"Sep"]
["19.09.2022","Montag",38,"Sep"]
["20.09.2022","Dienstag",38,"Sep"]
["21.09.2022","Mittwoch",38,"Sep"]
["22.09.2022","Donnerstag",38,"Sep"]
["23.09.2022","Freitag",38,"Sep"]
["24.09.2022","Samstag",38,"Sep"]
["25.09.2022","Sonntag",38,"Sep"]
["26.09.2022","Montag",39,"Sep"]
["27.09.2022","Dienstag",39,"Sep"]
["28.09.2022","Mittwoch",39,"Sep"]
["29.09.2022","Donnerstag",39,"Sep"]
["30.09.2022","Freitag",39,"Sep"]
["01.10.2022","Samstag",39,"Okt"]
["02.10.2022","Sonntag",39,"Okt"]
["03.10.2022","Montag",40,"Okt"]
["04.10.2022","Dienstag",40,"Okt"]
["05.10.2022","Mittwoch",40,"Okt"]
["06.10.2022","Donnerstag",40,"Okt"]
["07.10.2022","Freitag",40,"Okt"]
["08.10.2022","Samstag",40,"Okt"]
["09.10.2022","Sonntag",40,"Okt"]
["10.10.2022","Montag",41,"Okt"]
["11.10.2022","Dienstag",41,"Okt"]
["12.10.2022","Mittwoch",41,"Okt"]
["13.10.2022","Donnerstag",41,"Okt"]
["14.10.2022","Freitag",41,"Okt"]
["15.10.2022","Samstag",41,"Okt"]
["16.10.2022","Sonntag",41,"Okt"]
["17.10.2022","Montag",42,"Okt"]
["18.10.2022","Dienstag",42,"Okt"]
["19.10.2022","Mittwoch",42,"Okt"]
["20.10.2022","Donnerstag",42,"Okt"]
["21.10.2022","Freitag",42,"Okt"]
["22.10.2022","Samstag",42,"Okt"]
["23.10.2022","Sonntag",42,"Okt"]
["24.10.2022","Montag",43,"Okt"]
["25.10.2022","Dienstag",43,"Okt"]
["26.10.2022","Mittwoch",43,"Okt"]
["27.10.2022","Donnerstag",43,"Okt"]
["28.10.2022","Freitag",43,"Okt"]
["29.10.2022","Samstag",43,"Okt"]
["30.10.2022","Sonntag",43,"Okt"]
["31.10.2022","Montag",44,"Okt"]
["01.11.2022","Dienstag",44,"Nov"]
["02.11.2022","Mittwoch",44,"Nov"]
["03.11.2022","Donnerstag",44,"Nov"]
["04.11.2022","Freitag",44,"Nov"]
["05.11.2022","Samstag",44,"Nov"]
["06.11.2022","Sonntag",44,"Nov"]
["07.11.2022","Montag",45,"Nov"]
["08.11.2022","Dienstag",45,"Nov"]
["09.11.2022","Mittwoch",45,"Nov"]
["10.11.2022","Donnerstag",45,"Nov"]
["11.11.2022","Freitag",45,"Nov"]
["12.11.2022","Samstag",45,"Nov"]
["13.11.2022","Sonntag",45,"Nov"]
["14.11.2022","Montag",46,"Nov"]
["15.11.2022","Dienstag",46,"Nov"]
["16.11.2022","Mittwoch",46,"Nov"]
["17.11.2022","Donnerstag",46,"Nov"]
["18.11.2022","Freitag",46,"Nov"]
["19.11.2022","Samstag",46,"Nov"]
["20.11.2022","Sonntag",46,"Nov"]
["21.11.2022","Montag",47,"Nov"]
["22.11.2022","Dienstag",47,"Nov"]
["23.11.2022","Mittwoch",47,"Nov"]
["24.11.2022","Donnerstag",47,"Nov"]
["25.11.2022","Freitag",47,"Nov"]
["26.11.2022","Samstag",47,"Nov"]
["27.11.2022","Sonntag",47,"Nov"]
["28.11.2022","Montag",48,"Nov"]
["29.11.2022","Dienstag",48,"Nov"]
["30.11.2022","Mittwoch",48,"Nov"]
["01.12.2022","Donnerstag",48,"Dez"]
["02.12.2022","Freitag",48,"Dez"]
["03.12.2022","Samstag",48,"Dez"]
["04.12.2022","Sonntag",48,"Dez"]
["05.12.2022","Montag",49,"Dez"]
["06.12.2022","Dienstag",49,"Dez"]
["07.12.2022","Mittwoch",49,"Dez"]
["08.12.2022","Donnerstag",49,"Dez"]
["09.12.2022","Freitag",49,"Dez"]
["10.12.2022","Samstag",49,"Dez"]
["11.12.2022","Sonntag",49,"Dez"]
["12.12.2022","Montag",50,"Dez"]
["13.12.2022","Dienstag",50,"Dez"]
["14.12.2022","Mittwoch",50,"Dez"]
["15.12.2022","Donnerstag",50,"Dez"]
["16.12.2022","Freitag",50,"Dez"]
["17.12.2022","Samstag",50,"Dez"]
["18.12.2022","Sonntag",50,"Dez"]
["19.12.2022","Montag",51,"Dez"]
["20.12.2022","Dienstag",51,"Dez"]
["21.12.2022","Mittwoch",51,"Dez"]
["22.12.2022","Donnerstag",51,"Dez"]
["23.12.2022","Freitag",51,"Dez"]
["24.12.2022","Samstag",51,"Dez"]
["25.12.2022","Sonntag",51,"Dez"]
["26.12.2022","Montag",52,"Dez"]
["27.12.2022","Dienstag",52,"Dez"]
["28.12.2022","Mittwoch",52,"Dez"]
["29.12.2022","Donnerstag",52,"Dez"]
["30.12.2022","Freitag",52,"Dez"]
["31.12.2022","Samstag",52,"Dez"]
["01.01.2023","Sonntag",52,"Jan"]

{"sheet":"Code","index":4,"n_rows":1216,"n_cols":1,"rows_kept":1216,"cut":false,"hidden":true,"merged":[]}
["Public Sub Datei_Exist(DateiPfad As String)"]
["    ' prüfen ob Datei existiert"]
["    On Error GoTo melden"]
["    Dim Fs, f"]
["    Set Fs = CreateObject(\"Scripting.FileSystemObject\")"]
["    Set f = Fs.GetFile(DateiPfad)"]
["    MsgBox \" Datei existiert!\", vbInformation, \"Datei gefunden\""]
["    Exit Sub"]
["melden:"]
["    If Err.Number = 53 Then"]
["        MsgBox \" Datei existiert nicht!\", vbInformation, \"Datei suche\""]
["        'wenn Datei nicht vorhanden wird Err.Number = 53 ezeugt"]
["    End If"]
["End Sub"]
[]
["Public Function Pick_Sel_Datei() As String"]
["    'der lezte selektierte Pfad aus dem Dialog Filepicker wird retourniert"]
["    'strDateiName in Mod_Var definiert"]
["    Dim fd As FileDialog"]
["    Set fd = Application.FileDialog(msoFileDialogFilePicker)"]
["    Dim vrtSelectedItem As Variant"]
["    With fd"]
["        If .Show = -1 Then"]
["            For Each vrtSelectedItem In .SelectedItems"]
["                Pick_Sel_Datei = vrtSelectedItem"]
["                strDateiName = vrtSelectedItem"]
["            Next vrtSelectedItem"]
["        Else 'The user pressed Cancel."]
["            MsgBox \"keine Auswahl getroffen!\" & vbLf & \"Pfad wird leer retoutniert!\", vbInformation, \"FilePicker\""]
["            Pick_Sel_Datei = \"\""]
["        End If"]
["    End With"]
["    Set fd = Nothing"]
["End Function"]
[]
["Public Function new_TabIndx() As Integer"]
["    Dim tempTabIdx As Integer"]
["    Dim intTabIdx As Integer"]
["    Dim WS As Worksheet"]
["    tempTabIdx = 0"]
["    intTabIdx = 0"]
["    For Each WS In Worksheets"]
["        tempTabIdx = WS.Index"]
["        If intTabIdx < tempTabIdx Then intTabIdx = tempTabIdx"]
["    Next WS"]
["    new_TabIndx = intTabIdx + 1"]
["End Function"]
[]
["Public Sub Datei_Speichern_per_Dlg(Datei As String, Titeltext As String)"]
["    ' FileDialogSaveAs_Dialog öffenen mit Vorgaben"]
["    Dim oFileDialog As FileDialog"]
["    Set oFileDialog = Application.FileDialog(msoFileDialogSaveAs)"]
["    With oFileDialog"]
["        .InitialFileName = Datei"]
["        .Title = Titeltext"]
["        .ButtonName = \"speichern\""]
["        If .Show = True Then .Execute"]
["    End With"]
["    Set oFileDialog = Nothing"]
["End Sub"]
[]
["Public Sub Tabellenblatt_Copy(WBook As String, WBlatt As String)"]
["    'Aktiviert ein Worksbook und kopiert das ganze gewählte Tabellenblatt in die Zwischenablage"]
["    'Das zu kopierenden Workbook muss geöffnet sein"]
["    Windows(WBook).Activate"]
["    Sheets(WBlatt).Select"]
["    Cells.Select"]
["    Selection.Copy"]
["End Sub"]
[]
["Public Sub Tabellenblatt_Paste(WBook As String, SWBlatt As String)"]
["    'einfügen von Zwischenablage in das ganze gewählte Tabellenblatt des abgegeben Workbook"]
["    'Das Workbook in dem eingefügt werden soll muss geöffnet sein."]
["    'Die selektiert Tabell muss vorhanden sein."]
["    Windows(WBook).Activate"]
["    Sheets(Sh).Select"]
["    Cells.Select"]
["    ActiveSheet.Paste"]
["    Cells(1, 1).Select"]
["End Sub"]
[]
["Public Sub PDF_per_EMail()"]
["    '** Dimensionierung der Variablen"]
["    Dim strPDF As String"]
["    Dim OutlookApp As Object, strEmail As Object"]
["    Dim Wohin As String"]
["    Dim DateiName As String"]
["    '** Vorgaben definieren"]
["    Set OutlookApp = CreateObject(\"Outlook.Application\")"]
["    Set strEmail = OutlookApp.CreateItem(0)"]
["    'Adresse eingeben"]
["    Wohin = InputBox(\"geben Sie die Mailadresse nach untenstehendem Muster ein!\" & vbCrLf & vbCrLf & \"Name@Domain.ch\", \"Mailadresse\", \"info@tiggi.ch\")"]
["    If Wohin = \"\" Then Exit Sub"]
["    'Dateiname eingeben"]
["    DateiName = InputBox(\"geben Sie einen Dateiname ein!\" & Chr(13) & \" Das muss nicht zwingend der Tabellennamen sein.\", \"Dateiname\", BlattName)"]
["    If DateiName = \"\" Then Exit Sub"]
["    '** PDF erzeugen"]
["    ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _"]
["    ThisWorkbook.Path & \"\\\" & DateiName & \".pdf\", Quality:=xlQualityStandard _"]
["    , IncludeDocProperties:=False, IgnorePrintAreas:=False, OpenAfterPublish _"]
["    :=False"]
["    '** E-Mail versenden"]
["    strPDF = ThisWorkbook.Path & \"\\\" & DateiName & \".pdf\""]
["    With strEmail"]
["        .To = Wohin"]
["        .Subject = \"PDF als Anlage\" 'Betreffzeile"]
["        .body = \"Als Anlage die gewünschte PDF-Datei. Vielen Dank für Ihr Interesse.\""]
["        .Attachments.Add strPDF"]
["        .Display ' oder direkt senden"]
["        '.Send 'Damit wir die E-Mail sofort versendet"]
["        Kill strPDF"]
["    End With"]
["'** Objektvariablen wieder löschen"]
["    Set OutlookApp = Nothing"]
["    Set strEmail = Nothing"]
["End Sub"]
[]
["Public Sub PDFDateiausTabelle(BlattName As String)"]
["    'erstellt eine PDF-Datei der übergebenen Tabelle"]
["    On Error GoTo erstellenPDFErr"]
["    MyWorkbookName = ThisWorkbook.Path & \"\\\"    'Vorgabe"]
["    strAntwort = InputBox(\"Pfadname eingeben oder bestätigen\", \"Speicherort des PDF\", MyWorkbookName)"]
["    If strAntwort = \"\" Then ActiveSheet.Range(\"A1\").Select: Exit Sub"]
["    MyWorkbookName = strAntwort 'Eingegebener Pfad"]
["    DateiName = TabBlatt & Zusatz  'Vorgabe"]
["    strAntwort = InputBox(\"Dateiname eingeben oder bestätigen\" & vbCrLf & vbCrLf & \" ! Warnung !\" & vbCrLf & \"Fals Datei bereits existiert, wird sie überschrieben!\", \"Dateiname\", DateiName)"]
["    DateiName = strAntwort  'Eingegebene Datei"]
["    If strAntwort = \"\" Then ActiveSheet.Range(\"A1\").Select: Exit Sub"]
["    ChDir MyWorkbookName"]
["    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=MyWorkbookName & DateiName _"]
["        , Quality:=xlQualityStandard, IncludeDocProperties:=True _"]
["        , IgnorePrintAreas:=False, OpenAfterPublish:=True"]
["    MsgBox \"Pdf-Datei \" & DateiName & \".pdf wurde erstellt in \" & MyWorkbookName, vbOKOnly, \"Pdf erstellt\""]
["    ActiveSheet.Range(\"A1\").Select"]
["    Exit Sub"]
["erstellenPDFErr:"]
["    strErrTitel = \"PDF erstellen\""]
["    strErrMeldung = \"FehlerNummer \" & Err.Number & \" erkannt!\" & vbLf & \"Beschreibung : \" & Err.Description & vbLf & \"in \" & Err.Source"]
["    MsgBox strErrMeldung, vbCritical, strErrTitel"]
["    ActiveSheet.Range(\"A1\").Select"]
["End Sub"]
[]
["Public Sub Save_to_Desktop()"]
["    'Save Datei auf Desktop erstellen / überschreibt allenfalss bestehende Datei"]
["    'dem DateiNamen wird das aktuelle datHeute angehängt"]
["    Dim myPath As String"]
["    Dim Dateiname_alt As String"]
["    Dim DateiTyp As String"]
["    strTitel = Format(Now, \"dd.mm.yyyy\")"]
["    DateiTyp = Right(ActiveWorkbook.Name, 5)"]
["    'DateiTyp prüfen"]
["    If DateiTyp <> \".xlsm\" Then"]
["        MsgBox \"Funktioniert nur mit Dateityp [.xlsm]!\", vbInformation, \"Saven nicht möglich\""]
["        Exit Sub"]
["    End If"]
["    Dateiname_alt = ActiveWorkbook.Path & \"\\\" & ActiveWorkbook.Name"]
["    MsgBox Dateiname_alt"]
["    strDateiName = Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - 5)"]
["    strDateiName = strDateiName & \"_\" & strTitel & DateiTyp 'Dateiname mit Datum angehängt"]
["    strDateiName = InputBox(\"DateiName eingeben.\", \"DateiName\", strDateiName)"]
["    If strDateiName = \"\" Then Exit Sub"]
["    myPath = Environ(\"userprofile\") & \"\\Desktop\\\""]
["    Application.DisplayAlerts = False"]
["    With ActiveWorkbook"]
["        .SaveAs myPath & strDateiName"]
["        .Close savechanges:=False 'Excel schliessen"]
["    End With"]
["    Application.DisplayAlerts = True"]
["End Sub"]
[]
["Public Function DateinameClean(sDateiname As String) As String"]
["    Dim sErgebnis As String"]
["    sErgebnis = \"\""]
["    'verbotene Zeichen in sDateiname \\ / : * ? \" < > |  löschen"]
["    For lngI = 1 To Len(sDateiname)"]
["        Select Case Mid(sDateiname, lngI, 1)"]
["            Case \"\\\""]
["            Case \"\"\"\""]
["            Case \"/\""]
["            Case \":\""]
["            Case \"*\""]
["            Case \"?\""]
["            Case \"<\""]
["            Case \">\""]
["            Case \"|\""]
["            Case Else"]
["            sErgebnis = sErgebnis + Mid(sDateiname, lngI, 1)"]
["        End Select"]
["    Next lngI"]
["    DateinameClean = sErgebnis"]
["End Function"]
[]
["Public Sub Ordner_vorhanden(OrdnerName As String)"]
["    'Ordner vorhanden oder gleich erstellen"]
["    Dim Fso As Object"]
["    Set Fso = CreateObject(\"Scripting.FileSystemObject\")"]
["    If Fso.FolderExists(OrdnerName) = False Then"]
["        intAntwort = MsgBox(\" Dieser Ordner ist nicnt Vorrhanden!\" & vbLf & \"Möchten Sie den Ordner \" & OrdnerName & \" erstellen ? \", _"]
["        vbQuestion + vbYesNo + vbDefaultButton2, \"Ordner nicht vorhanden, erstellen ?\")"]
["        If intAntwort = vbYes Then MkDir OrdnerName"]
["    Else"]
["        MsgBox \" Der Ordner  \" & OrdnerName & \" existiert.\", vbInformation, \"Ordner Vorhanden\""]
["    End If"]
["    Set Fso = Nothing"]
["End Sub"]
[]
["Public Sub Pfad_Vorhanden(OrdnerName As String)"]
["    ' Pfad vorhanden wenn nicht erstellen"]
["    If Dir(OrdnerName) <> \"\" Then"]
["        MsgBox \"vorhanden\""]
["    Else"]
["        MkDir OrdnerName"]
["        MsgBox \"nicht vorhanden\""]
["    End If"]
["End Sub"]
[]
["Public Function Wait(MilliSekunden As Double)"]
["    'Aufruf >> z.B Wait 10000 ' 10 Sekunden warten"]
["    Dim A As Double, E As Double"]
["    E = Timer + (MilliSekunden / 1000)"]
["    Do While A < E"]
["      DoEvents"]
["      A = Timer"]
["    Loop"]
["End Function"]
[]
["Public Function Zahl_Ganze(ByVal Z As Integer, FehlMsg As Boolean) As Boolean"]
["    'Nur Zahlen(48-57)zulassen (Integer)"]
["    'Einsatzbeispiel"]
["    'Private Sub Textbox_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)"]
["        'If Zahl_Ganze(KeyAscii, True) = False Then KeyAscii = 8"]
["    'End Sub"]
["    If Z > 47 And Z < 58 Then"]
["        Zahl_Ganze = True"]
["    Else"]
["        If FehlMsg = True Then MsgBox \"... nur ganze Zahlen erlaubt!\", vbInformation, \"Eingabefehler\""]
["        Zahl_Ganze = False"]
["    End If"]
["End Function"]
[]
["Public Function Zahl_Double_Punkt(ByVal Z As Integer, FehlMsg As Boolean) As Boolean"]
["    'Nur Zahlen(48-57) und den Punkt (46) zulassen (Single)"]
["    'Einsatzbeispiel"]
["    'Private Sub Textbox_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)"]
["        'If Zahl_Double_Punkt(KeyAscii, True) = False Then KeyAscii = 8"]
["    'End Sub"]
["    If Z = 46 Or (Z > 46 And Z < 58) Then"]
["        Zahl_Double_Punkt = True"]
["    Else"]
["        If FehlMsg = True Then MsgBox \"... nur Zahlen und 'Punkt' erlaubt!\", vbInformation, \"Eingabefehler\""]
["        Zahl_Double_Punkt = False"]
["    End If"]
["End Function"]
[]
["Public Function Zahl_Double_Komma(ByVal Z As Integer, FehlMsg As Boolean) As Boolean"]
["    'Nur Zahlen(48-57) und Komma (44) zulassen (Single)"]
["    'Einsatzbeispiel"]
["    'Private Sub Textbox_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)"]
["        'If Zahl_Double_Komma(KeyAscii, True) = False Then KeyAscii = 8"]
["    'End Sub"]
["    If Z = 44 Or (Z > 47 And Z < 58) Then"]
["        Zahl_Double_Komma = True"]
["    Else"]
["        If FehlMsg = True Then MsgBox \"... nur Zahlen und 'Komma' erlaubt!\", vbInformation, \"Eingabefehler\""]
["        Zahl_Double_Komma = False"]
["    End If"]
["End Function"]
[]
["Public Function Zahl_hhmmss(ByVal Z As Integer, FehlMsg As Boolean) As Boolean"]
["    'Nur Zahlen(48-57) und den DoppelPunkt (58) zulassen (Text)"]
["    'Einsatzbeispiel"]
["    'Private Sub Textbox_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)"]
["        'If Zahl_hhmmss(KeyAscii, True) = False Then KeyAscii = 8"]
["    'End Sub"]
["    'Zeichensatz prüfen bei Exit Textbox"]
["    If Z = (Z > 46 And Z < 59) Then 'Doppelpunkzt ist mit 58 hier integriert"]
["        Zahl_hhmmss = True"]
["    Else"]
["        If FehlMsg = True Then MsgBox \"... nur Zahlen und ':' erlaubt!\", vbInformation, \"Eingabefehler\""]
["        Zahl_hhmmss = False"]
["    End If"]
["End Function"]
[]
["Public Function lastColChar(Reihe As Long) As String"]
["    ' --> ermittelt die Buchstaben der letzen benutzten Spaltenbeschriftung; Rückwert= 1 oder 2 Buchstben der Spalte als String"]
["    Dim Koll As Long"]
["    Dim Bu As String"]
["    Koll = Cells(Reihe, Columns.Count).End(xlToLeft).Column"]
["    Bu = Application.Cells(Reihe, Koll).Address(ColumnAbsolute:=False)"]
["    If Mid(Bu, 2, 1) = \"$\" Then lastColChar = Left(Bu, 1)"]
["    If Mid(Bu, 3, 1) = \"$\" Then lastColChar = Left(Bu, 2)"]
["End Function"]
[]
["Public Function lng_HEX(H1 As Long) As String"]
["    If H1 < 16 Then"]
["        lng_HEX = \"0\" & Hex(H1)"]
["    Else"]
["        lng_HEX = Hex(H1)"]
["    End If"]
["End Function"]
[]
["Public Function SchaltJh(PJahr As Integer) As Boolean"]
["    ' gibt True zurück wenn Schaltjahr"]
["    If PJahr Mod 4 = 0 And PJahr Mod 100 <> 0 Or PJahr Mod 400 = 0 Then SchaltJh = True Else SchaltJh = False"]
["End Function"]
[]
["Public Function Alter(GebDatum) As Long"]
["   Alter = Year(Date) - Year(GebDatum)"]
["   If DateSerial(Year(Date), Month(GebDatum), Day(GebDatum)) > Date Then"]
["     Alter = Alter - 1"]
["   End If"]
["End Function"]
[]
["Public Function KWX(d As Date) As Integer"]
["    ' --> berechnet die Kalenderwoche"]
["Dim T As Date"]
["    T = DateSerial(Year(d + (8 - Weekday(d)) Mod 7 - 3), 1, 1)"]
["    KWX = (d - T - 3 + (Weekday(T) + 1) Mod 7) \\ 7 + 1"]
["End Function"]
["Public Function FistDayKW(Wo As Integer) As Date"]
["    ' Startdatum einer Kalenderwoche  des aktuellen Jahres berechnen"]
["    FistDayKW = DateSerial(Year(Now), 1, 4) + Wo * 7 - 7 - (DateSerial(Year(Now), 1, 2) Mod 7)"]
["End Function"]
[]
["Public Function MoDatum(XDatum As Date) As Date"]
["    ' --> gibt Datum des Montags der aktuellen Woche zurück"]
["    If Weekday(XDatum) > 2 Then"]
["        For i = 7 To 0 Step -1"]
["            XDatum = DateAdd(\"d\", -1, XDatum)"]
["            If Weekday(XDatum) = 2 Then Exit For"]
["        Next i"]
["    End If"]
["    If Weekday(XDatum) = 0 Then XDatum = DateAdd(\"d\", 2, XDatum)"]
["    If Weekday(XDatum) = 1 Then XDatum = DateAdd(\"d\", 1, XDatum)"]
["    MoDatum = XDatum"]
["End Function"]
[]
["Public Function DaysInMonth(Jh As Integer, Mt As Integer) As Integer"]
["    ' --> retourniert die Anzahl Tage im angegebenen Monat als Integer"]
["    DaysInMonth = DateSerial(Jh, Mt + 1, 1) - DateSerial(Jh, Mt, 1)"]
["End Function"]
[]
["Public Function WorkDaysInMonth(Jh As Integer, Mt As Integer) As Integer"]
["    ' --> retourniert die Anzahl Werktage (ohne Sa,So) im angegebenen Monat als Integer Jh=Jahr, Mt= Monat"]
["    Dim tmpDate As Date"]
["    Dim Anz, Wday As Integer"]
["    tmpDate = \"1.\" & Mt & \".\" & Jh"]
["    Anz = DateSerial(Jh, Mt + 1, 1) - DateSerial(Jh, Mt, 1)"]
["    For i = 1 To Anz"]
["        tmpDate = DateAdd(\"d\", 1, tmpDate)"]
["        If Weekday(tmpDate) <> 1 And Weekday(tmpDate) <> 7 Then Wday = Wday + 1"]
["    Next i"]
["    WorkDaysInMonth = Wday"]
["End Function"]
[]
["Public Function StdMininDez(Std As Integer, Min As Integer) As Single"]
["    '   Function: StdMininDec wandelt Stunden und Minuten in Dezimalstunden um"]
["    sngStdDez = Std + (Min / 60)"]
["    sngStdDez = Round(sngStdDez, 2)"]
["    If sngStdDez > 24 Then sngStdDez = 24"]
["    StdMininDez = sngStdDez"]
["End Function"]
[]
["Public Sub StdDezinStdMin(StdDez As Single)"]
["    ' wandelt Dezimalstunden in Stunden und Minuten als Single um"]
["    ' speichert die Stunden in der Public Variabe intStd zurück"]
["    ' speichert die Minuten in der Public Variabe intMin zurück"]
["    Dim KommaPos As Integer"]
["    If StdDez > 24 Then StdDez = 24"]
["    If StdDez < 0 Then StdDez = 0"]
["    strDatum = CStr(StdDez)"]
["    KommaPos = InStr(1, strDatum, \",\", vbTextCompare)"]
["    Select Case KommaPos"]
["        Case Is = 0 'kein Komma vorhanden"]
["            intMin = 0"]
["            intStd = strDatum"]
["        Case Is = 2"]
["            intMin = Mid(strDatum, 2, Len(strDatum)) * 60"]
["            intStd = Left(strDatum, 1)"]
["        Case Is = 3"]
["            intMin = Mid(strDatum, 3, Len(strDatum)) * 60"]
["            intStd = Left(strDatum, 2)"]
["    End Select"]
["End Sub"]
[]
["Public Function IstSchaltjahr(JahrIn As Long) As Boolean"]
["   If (JahrIn Mod 4 = 0 And JahrIn Mod 100 <> 0) Or _"]
["    (JahrIn Mod 400 = 0) Then IstSchaltjahr = True"]
["End Function"]
[]
["Public Function FeiertagText(DatumX As Date, OsternJh As Date) As String"]
["    ' gibt entweder einen Feiertag zurück oder den Namen des Tages z.B. Montag"]
["    ' Funktion DateSerial(intJahr,intMonat,intTag)"]
["    ' Ostern mit der Funktion Ostersonntag ermitteln"]
["    ' Muttertage immer zweiter Sonntag im Mai"]
["    ' Vatertag immer  1. Sonntag im Juni landesweit"]
["    ' BussBettag immer 3 So im September"]
["    ' Reformtionstag immer am 1 Son im November"]
["    Dim Muttertag As Date"]
["    Dim Vatertag As Date"]
["    Dim BussBettag As Date"]
["    Dim Reformtag As Date"]
["    Dim Zibelemärit As Date"]
["    Dim Advent As Date"]
["    Dim SoZä As Integer"]
["    intJahr = Year(DatumX)"]
["    intMonat = Month(DatumX)"]
["    'Die Speziellen Feiertage"]
["    Muttertag = DateSerial(intJahr, 5, 1)"]
["    SoZä = 0"]
["    For lngI = 1 To 20"]
["        If Weekday(Muttertag) = 1 Then SoZä = SoZä + 1"]
["        If SoZä = 2 Then Exit For"]
["        Muttertag = Muttertag + 1"]
["    Next lngI"]
["    SoZä = 0"]
["    Vatertag = DateSerial(intJahr, 6, 1)"]
["    For lngI = 1 To 13"]
["        If Weekday(Vatertag) = 1 Then SoZä = SoZä + 1"]
["        If SoZä = 1 Then Exit For"]
["        Vatertag = Vatertag + 1"]
["    Next lngI"]
["    SoZä = 0"]
["    BussBettag = DateSerial(intJahr, 9, 1)"]
["    SoZä = 0"]
["    For lngI = 1 To 27"]
["        If Weekday(BussBettag) = 1 Then SoZä = SoZä + 1"]
["        If SoZä = 3 Then Exit For"]
["        BussBettag = BussBettag + 1"]
["    Next lngI"]
["    SoZä = 0"]
["    Zibelemärit = DateSerial(intJahr, 11, 1)"]
["    For lngI = 1 To 27"]
["        If Weekday(Zibelemärit) = 2 Then SoZä = SoZä + 1"]
["        If SoZä = 4 Then Exit For"]
["        Zibelemärit = Zibelemärit + 1"]
["    Next lngI"]
["    SoZä = 0"]
["    Reformtag = DateSerial(intJahr, 11, 1)"]
["    For lngI = 1 To 13"]
["        If Weekday(Reformtag) = 1 Then SoZä = SoZä + 1"]
["        If SoZä = 1 Then Exit For"]
["        Reformtag = Reformtag + 1"]
["    Next lngI"]
["    SoZä = 0"]
["    Advent = DateSerial(intJahr, 12, 25)"]
["    For lngI = 1 To 28"]
["        If Weekday(Advent) = 1 Then SoZä = SoZä + 1"]
["        If SoZä = 4 Then Exit For"]
["        Advent = Advent - 1"]
["    Next lngI"]
["    ' Feiertag als Text ermitteln"]
["    Select Case DatumX"]
["        Case Is = DateSerial(intJahr, 1, 1)"]
["            FeiertagText = \"Neujahr\""]
["        Case Is = DateSerial(intJahr, 1, 2)"]
["            FeiertagText = \"Berchtoldstag\""]
["        Case Is = DateSerial(intJahr, 1, 6)"]
["            FeiertagText = \"Hl.3 Koenige\""]
["        Case Is = DateSerial(intJahr, 2, 14)"]
["            FeiertagText = \"Valentintag\""]
["        Case Is = OsternJh - 52"]
["            FeiertagText = \"Altweiber\""]
["        Case Is = OsternJh - 48"]
["            FeiertagText = \"Rosenmontag\""]
["        Case Is = OsternJh - 2"]
["            FeiertagText = \"Karfreitag\""]
["        Case Is = OsternJh"]
["            FeiertagText = \"Ostersonntag\""]
["        Case Is = OsternJh + 1"]
["            FeiertagText = \"Ostermontag\""]
["        Case Is = DateSerial(intJahr, 5, 1)"]
["            FeiertagText = \"Tag der Arbeit\""]
["        Case Is = Muttertag"]
["            FeiertagText = \"Muttertag\""]
["        Case Is = Vatertag"]
["            FeiertagText = \"Vatertag\""]
["        Case Is = OsternJh + 39"]
["            FeiertagText = \"Auffahrt\""]
["        Case Is = OsternJh + 49"]
["            FeiertagText = \"Pfingsten\""]
["        Case Is = OsternJh + 50"]
["            FeiertagText = \"Pfingstmontag\""]
["        Case Is = OsternJh + 60"]
["            FeiertagText = \"Fronleichnam\""]
["        Case Is = DateSerial(intJahr, 8, 1)"]
["            FeiertagText = \"Nationalfeiertag\""]
["        Case Is = BussBettag"]
["            FeiertagText = \"Buss & Bettag\""]
["        Case Is = DateSerial(intJahr, 11, 1)"]
["            FeiertagText = \"Allerheiligen\""]
["        Case Is = Zibelemärit"]
["            FeiertagText = \"Zibelemärit\""]
["        Case Is = Reformtag"]
["            FeiertagText = \"Reformationstag\""]
["        Case Is = Advent"]
["            FeiertagText = \"1. Advent\""]
["        Case Is = Advent + 7"]
["            FeiertagText = \"2. Advent\""]
["        Case Is = Advent + 14"]
["            FeiertagText = \"3. Advent\""]
["        Case Is = Advent + 21"]
["            If Advent + 21 = DateSerial(intJahr, 12, 25) Then 'ist bereits Weihnachten"]
["                FeiertagText = \"Weihnachten\""]
["            ElseIf Advent + 21 = DateSerial(intJahr, 12, 24) Then ' es gibt einen 4. Advent"]
["                FeiertagText = \"heilig Abend\""]
["            Else"]
["                FeiertagText = \"4. Advent\""]
["            End If"]
["        Case Is = DateSerial(intJahr, 12, 6)"]
["            FeiertagText = \"St. Niklaus\""]
["        Case Is = DateSerial(intJahr, 12, 24)"]
["            FeiertagText = \"heilig Abend\""]
["        Case Is = DateSerial(intJahr, 12, 25)"]
["            FeiertagText = \"Weihnachten\""]
["        Case Is = DateSerial(intJahr, 12, 26)"]
["            FeiertagText = \"Stephantag\""]
["        Case Is = DateSerial(intJahr, 12, 31)"]
["            FeiertagText = \"Sylvester\""]
["        Case Else"]
["            FeiertagText = Format(DatumX, \"dddd\")"]
["    End Select"]
["End Function"]
[]
["Public Function OsterSonntag(Jahr As Integer) As Date"]
["   Dim d As Integer"]
["    d = (((255 - 11 * (Jahr Mod 19)) - 21) Mod 30) + 21"]
["    OsterSonntag = DateSerial(Jahr, 3, 1) + d + (d > 48) + 6 - ((Jahr + Jahr \\ 4 + d + (d > 48) + 1) Mod 7)"]
["End Function"]
[]
["Public Function TageMonat(Datin As Date) As Integer"]
["    TageMonat = Day(DateSerial(Year(Datin), Month(Datin) + 1, 0))"]
["End Function"]
[]
["Public Function StartDatKW(KaWo As Integer) As Date"]
["    'Berechent das Startdatum einer 1. Kalenderwoche"]
["    'Annahme: Das aktuelle Jahr // KaWo 1 hat mind 4 Tage im aktuellen Jahr // 1. Tag der KaWo ist der Montage"]
["    StartDatKW = DateSerial(Year(Now), 1, 4) + KW * 7 - 7 - (DateSerial(Year(Now), 1, 2) Mod 7)"]
["End Function"]
[]
["Public Function WoTgName(Kurz As Boolean, WoTg As Long) As String"]
["    'Kurz = true >> Son,Mon ... Kurz = False >>> Sonntag , Montag .."]
["    WoTgName = WeekdayName(WoTg, True, Kurz)"]
["End Function"]
[]
["Public Function HoursAndMinutes(datetime As Date) As String"]
["    'Wandelt ein Date (Zeitwert) in Stunden und Minuten (Format ss:mm)"]
["    Dim hours As Integer"]
["    Dim minutes As Integer"]
["    hours = Int(datetime) * 24 + Hour(datetime) ' the unit of Date type is 1 day"]
["    minutes = Round((datetime * 24 - hours) * 60)"]
["    HoursAndMinutes = hours & \":\" & Format(minutes, \"00\")"]
["End Function"]
[]
["Public Sub Druck_Vorbereiten(Druckbereich As String, TitelReihen As String, AnzSeitenBreite As Integer, HochQuer As Integer)"]
["    With ActiveSheet.PageSetup"]
["        .PrintArea = Druckbereich"]
["        .PrintTitleRows = TitelReihen"]
["        ' LeftHeader wird vorgängig definiert deshalb hier auskommentiert"]
["        '.LeftHeader = \"&12\" & ThisWorkbook.BuiltinDocumentProperties(\"company\").Value"]
["        .CenterHeader = \"&14\" & ThisWorkbook.BuiltinDocumentProperties(\"title\").Value"]
["        .RightHeader = \"gedruckt am; \" & \"&D \"  'Periode"]
["        .LeftFooter = Application.ActiveWorkbook.Name"]
["        .CenterFooter = \"&P\" & \" von \" & \"&N\""]
["        .RightFooter = Environ(\"Username\")"]
["        .Orientation = HochQuer 'xlPortrait"]
["        .FitToPagesWide = AnzSeitenBreite"]
["        .FitToPagesTall = False"]
["    End With"]
["    ActiveSheet.PrintPreview"]
["End Sub"]
[]
["Public Sub DruckPrepare(BlattX As String, Druckbereich As String)"]
["    Sheets(BlattX).Activate"]
["    With ActiveSheet"]
["        .PageSetup.PrintArea = Druckbereich"]
["        .PageSetup.LeftHeader = \"&12\" & Sheets(\"Val\").Range(\"KopfLinks\")"]
["        .PageSetup.CenterHeader = \"&14\" & Sheets(\"Val\").Range(\"KopfMitte\")"]
["        .PageSetup.RightHeader = \"gedruckt am; \" & \"&D \"  'Periode"]
["        .PageSetup.LeftFooter = strTitel"]
["        .PageSetup.CenterFooter = \"&P\" & \" von \" & \"&N\""]
["        .PageSetup.RightFooter = Sheets(\"Val\").Range(\"FussRechts\")"]
["        .PrintPreview"]
["    End With"]
["End Sub"]
[]
["Public Sub Tab_in_CB_einlesen(Kombobox As ComboBox)"]
["    Dim wks As Worksheet"]
["    'Alte Einträge löschen ... geht nur mit Remove"]
["    For i = 1 To Kombobox.ListCount 'Remove alle Einträge der ComboBox"]
["        Kombobox.RemoveItem 0"]
["    Next i"]
["    'alle Tabellennamen des Workbook in Combo einlesen"]
["    For Each wks In ThisWorkbook.Worksheets"]
["        If wks.Name = \"Val\" Then GoTo weiter  'nicht in Liste aufnehmen"]
["        Kombobox.AddItem wks.Name"]
["weiter:"]
["    Next wks"]
["End Sub"]
[]
["Public Sub TabName_auflisten(Blatt As String, Spalte As Long, Zeile As Long)"]
["    'Auflisten aller Tabellennamen in die übergebene Spalte des übergebenen Tabellenname"]
["    Dim wks As Worksheet"]
["    Sheets(Blatt).Activate"]
["    'Alte Daten löschen"]
["    With ActiveSheet"]
["        .Columns(Spalte).Clear"]
["    End With"]
["    'alle Tabellennamen des Workbook in BlattName bei Spalte eintragen"]
["    For Each wks In ThisWorkbook.Worksheets"]
["        ''If wks.Name = \"Val\" Then GoTo weiter  'nicht in Lieste aufnehmen"]
["        With ThisWorkbook.Worksheets(Blatt)"]
["            .Cells(Zeile, Spalte).Value = wks.Name"]
["        End With"]
["        Zeile = Zeile + 1"]
["weiter:"]
["    Next wks"]
["End Sub"]
[]
["Public Sub ZellenRand_dünn(VonBis As String)"]
["    ' Die Zellen des angegebene Berieches erhalten eine Umrnduneg mit dünner Linie"]
["    ' Der Bereich ist im aktuell gewählten Tabellenblatt"]
["    With ActiveSheet"]
["        .Range(VonBis).Select"]
["        Application.CutCopyMode = False"]
["        Selection.Borders(xlDiagonalDown).LineStyle = xlNone"]
["        Selection.Borders(xlDiagonalUp).LineStyle = xlNone"]
["        With Selection.Borders(xlEdgeLeft)"]
["            .LineStyle = xlContinuous"]
["            .ColorIndex = 0"]
["            .TintAndShade = 0"]
["            .Weight = xlThin"]
["        End With"]
["        With Selection.Borders(xlEdgeTop)"]
["            .LineStyle = xlContinuous"]
["            .ColorIndex = 0"]
["            .TintAndShade = 0"]
["            .Weight = xlThin"]
["        End With"]
["        With Selection.Borders(xlEdgeBottom)"]
["            .LineStyle = xlContinuous"]
["            .ColorIndex = 0"]
["            .TintAndShade = 0"]
["            .Weight = xlThin"]
["        End With"]
["        With Selection.Borders(xlEdgeRight)"]
["            .LineStyle = xlContinuous"]
["            .ColorIndex = 0"]
["            .TintAndShade = 0"]
["            .Weight = xlThin"]
["        End With"]
["        With Selection.Borders(xlInsideVertical)"]
["            .LineStyle = xlContinuous"]
["            .ColorIndex = 0"]
["            .TintAndShade = 0"]
["            .Weight = xlThin"]
["        End With"]
["        With Selection.Borders(xlInsideHorizontal)"]
["            .LineStyle = xlContinuous"]
["            .ColorIndex = 0"]
["            .TintAndShade = 0"]
["            .Weight = xlThin"]
["        End With"]
["    End With"]
["End Sub"]
[]
["Public Sub RegisterFarbe(Blatt As String, Farbe As Single)"]
["    ' hellgrün >0.399975585192419> bezieht sich immer auf das aktuelle Tabellenblatt"]
["    With ActiveWorkbook.Sheets(Blatt).Tab"]
["        .ThemeColor = xlThemeColorAccent6"]
["        .TintAndShade = Farbe"]
["    End With"]
["End Sub"]
[]
["Public Sub LinienBereich(StartRow As Long, EndRow As Long, StartCol As String, EndCol As String)"]
["    With ActiveSheet"]
["        .Range(StartCol & StartRow & \":\" & EndCol & EndRow + 10).Interior.Pattern = xlNone    'Keine Füllungsfarbe"]
["        strBereich = StartCol & StartRow & \":\" & EndCol & EndRow"]
["        For lngI = StartRow + 1 To EndRow Step 2   'jede 2, Linie grau"]
["            'Zeilenbereich bestimmen"]
["            .Range(StartCol & lngI & \":\" & EndCol & lngI).Interior.Color = &HE0E0E0     'grau"]
["        Next i"]
["        'letzte Zeile hellgrün färben"]
["        .Range(\"A\" & EndRow + 1 & \":\" & EndCol & EndRow + 1).Interior.ColorIndex = iciHellgrün"]
["        .Cells(1, 1).Select ' Zelle A1 Selectieren"]
["    End With"]
["End Sub"]
[]
["Public Sub LinienVertikal(VertBereich As String)"]
["    ' LinienVertikal zeichnen"]
["    bolIgSelChx = True"]
["    Range(VertBereich).Select"]
["    With Selection.Borders(xlEdgeLeft)"]
["        .LineStyle = xlContinuous"]
["        .ColorIndex = 0"]
["        .TintAndShade = 0"]
["        .Weight = xlThin"]
["    End With"]
["    With Selection.Borders(xlEdgeRight)"]
["        .LineStyle = xlContinuous"]
["        .ColorIndex = 0"]
["        .TintAndShade = 0"]
["        .Weight = xlThin"]
["    End With"]
["    bolIgSelChx = False"]
["End Sub"]
[]
["Public Sub LinienUnten_Bereich(Bereich As String)"]
["    'Linien unten den Zellen eintrage im übergebenen Bereich"]
["    Range(Bereich).Select"]
["    Selection.Borders(xlDiagonalDown).LineStyle = xlNone"]
["    Selection.Borders(xlDiagonalUp).LineStyle = xlNone"]
["    Selection.Borders(xlEdgeLeft).LineStyle = xlNone"]
["    Selection.Borders(xlEdgeTop).LineStyle = xlNone"]
["    Selection.Borders(xlEdgeBottom).LineStyle = xlNone"]
["    Selection.Borders(xlEdgeRight).LineStyle = xlNone"]
["    Selection.Borders(xlInsideVertical).LineStyle = xlNone"]
["    With Selection.Borders(xlInsideHorizontal)"]
["        .LineStyle = xlContinuous"]
["        .ColorIndex = 16"]
["        .TintAndShade = 0"]
["        .Weight = xlThin"]
["    End With"]
["End Sub"]
[]
["Public Sub Datenliste_Zuweisen_Spalte(Spalte As String, Quelle As String)"]
["    ' Einer Spalte wird eine DatenListe zugewiesen"]
["    ' Das Blatt wo eingefügt werden soll muss ausgewählt sein(ActiveSheet)"]
["    ' Die Spalte muss wie folgt definiert werden z.B. \"A:A\""]
["    ' Die Quelle muss wie folgt definiert werden \"=BlattName!Bereich\" z.B. \"=Val!$A$2:$A$4\""]
["    ' Die Quelle (Blatt) muss im gleichen Workbook sein"]
["    Columns(Spalte).Select"]
["    With Selection.Validation"]
["        .Delete"]
["        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=Quelle"]
["        .IgnoreBlank = True"]
["        .InCellDropdown = True"]
["        .InputTitle = \"\""]
["        .ErrorTitle = \"info tiggi\""]
["        .InputMessage = \"\""]
["        .ErrorMessage = \"nur Elemente der Dropdownliste erlaubt\""]
["        .ShowInput = False"]
["        .ShowError = True"]
["    End With"]
["End Sub"]
[]
["Public Sub Datenliste_Zuweisen_Bereich(BereichZu As String, Quelle As String)"]
["    ' Einem Bereich wird eine DatenListe zugewiesen"]
["    ' Das Blatt wo eingefügt werden soll muss ausgewählt sein(ActiveSheet)"]
["    ' Die Spalte muss wie folgt definiert werden z.B. \"A1:A200\""]
["    ' Die Quelle muss wie folgt definiert werden \"=BlattName!Bereich\" z.B. \"=Val!$A$2:$A$4\""]
["    ' Die Quelle (Blatt) muss im gleichen Workbook sein"]
["    Range(BereichZu).Select"]
["    With Selection.Validation"]
["        .Delete"]
["        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _"]
["        xlBetween, Formula1:=Quelle"]
["        .IgnoreBlank = True"]
["        .InCellDropdown = True"]
["        .InputTitle = \"\""]
["        .ErrorTitle = \"\""]
["        .InputMessage = \"\""]
["        .ErrorMessage = \"\""]
["        .ShowInput = True"]
["        .ShowError = True"]
["    End With"]
["End Sub"]
[]
["Public Sub Zeilen_fixieren(Zelle As String)"]
["    'fixiert das Fester je nach Zelle als Eckpunkt,so dass sie immer sichtbar sind"]
["    Range(Zelle).Select"]
["    ActiveWindow.FreezePanes = True"]
["End Sub"]
["Public Sub Sprung_zuTabelle(BlattName_Zelle)"]
["    'Beispiel für Blattname und Zelle  >>> \"Control!A1\""]
["    ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:=\"\", SubAddress:= _"]
["        BlattName_Zelle, TextToDisplay:=\"Zu Control\""]
["End Sub"]
[]
["Public Sub SpaltenTextWrapen(Spalte As Long, VonReihe As Long, AnzReihen As Long)"]
["    'Zeilenumbruch ausführen"]
["    For i = VonReihe To AnzReihen"]
["        ActiveSheet.Cells(i, Spalte).WrapText = True"]
["    Next i"]
["End Sub"]
[]
["Public Sub SpaltenFixWrapen(Spalte As String, Breite As Single)"]
["    ActiveSheet.Columns(Spalte).ColumnWidth = Breite"]
["End Sub"]
[]
["Public Sub Titelspalten_in_CB_Add(Tabelle As String, Kombobox As ComboBox, Titelspalte As Long)"]
["    Application.ScreenUpdating = False"]
["    Sheets(Tabelle).Activate"]
["    'Alte Einträge löschen ... geht nur mit Remove"]
["    For i = 1 To Kombobox.ListCount 'Remove alle Einträge der ComboBox"]
["        Kombobox.RemoveItem 0"]
["    Next i"]
["    lngC = lastUsedCol(Titelspalte)"]
["    For i = 1 To lngC"]
["        Kombobox.AddItem ActiveSheet.Cells(Titelspalte, i).Value"]
["    Next i"]
["    "]
["End Sub"]
[]
["Public Function Benutzer_Bereich(Tabelle As String) As String"]
["    Dim lngLastRow, lngLastCol As Long"]
["    Sheets(Tabelle).Activate"]
["    'zu durchsuchender Bereich finden"]
["    With ActiveSheet"]
["        lngLastRow = Cells.Find(what:=\"*\", LookIn:=xlValues, SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row"]
["        lngLastCol = Cells.Find(what:=\"*\", LookIn:=xlValues, SearchDirection:=xlPrevious, SearchOrder:=xlByColumns).Column"]
["        Benutzer_Bereich = .Range(.Cells(1, 1), .Cells(lngLastRow, lngLastCol)).Address(RowAbsolute:=False, ColumnAbsolute:=False)"]
["    End With"]
["End Function"]
[]
["Public Sub BlattSpeichern()"]
["    ' Das aktuelle gewählte Blatt wird abgespeichert"]
["    ' Der Dateinamhe ist gleich wir der Blattname"]
["    ' gespeichert wird im selben Dir wo auch das Workbook ist"]
["    myPath = ThisWorkbook.Path"]
["    Sheets(BlattName).Copy"]
["    ActiveWorkbook.SaveAs Filename:= _"]
["        myPath & \"\\\" & BlattName, FileFormat:=xlNormal, _"]
["        Password:=\"\", WriteResPassword:=\"\", ReadOnlyRecommended:=False, _"]
["        CreateBackup:=False"]
["    ActiveWorkbook.Close"]
["    MsgBox \"Blatt ist abgespeichert in \" & myPath, vbInformation + vbOKOnly, \"Blatt speichern beendet\""]
["End Sub"]
[]
["Public Sub SpalteAutoFit()"]
["    'Im Datenbereich Spaltenbreite anpassen"]
["    bolGumpe = True"]
["    With ActiveSheet"]
["        .Cells.Select"]
["        Selection.Columns.AutoFit"]
["        .Range(\"A1\").Select"]
["    End With"]
["    bolGumpe = False"]
["End Sub"]
[]
["Public Sub ScrollBereich()"]
["    'Der Vertikalscroll bereich wird auf den benutzten Bereich eingeschränkt oder erweitert."]
["    strB = lastColChar(3)"]
["    strAntwort = InputBox(\"Vertikal-Scrollbereich auf Arbeitsbereich einschrängen.\" _"]
["    & vbCrLf & \"Gilt für die aktive Tabelle.\", \"Scrollbereich eingeben/bestätigen\", \"A1:\" & strB & lngLastR + 2)"]
["    ActiveSheet.ScrollArea = strAntwort"]
["End Sub"]
[]
["Public Function Str_in_Rowx(Reihe As Long, SuchWert As String) As Long"]
["        'Sucht  sequentiell einen Wert horizontal der übergebenen Reihe 'Rückgabe= AnzGefunden"]
["        Dim Zä, EndKoll As Long"]
["        Zä = 0"]
["        EndKoll = Cells(Reihe, Columns.Count).End(xlToLeft).Column"]
["        For i = 1 To EndKoll"]
["            If SuchWert = Application.Cells(Reihe, i).Value Then Zä = Zä + 1 'gefunden"]
["        Next i"]
["        If Zä > EndKoll Then Str_in_Rowx = 0 Else Str_in_Rowx = Zä"]
["End Function"]
[]
["Public Function Str_in_Colx(Spalte As Long, SuchWert As String) As Long"]
["        'Sucht  sequentiell einen Wert vertikal der übergebenen Spalte 'Rückgabe= AnzGefunden"]
["        Dim Zä, EndR As Long"]
["        Zä = 0"]
["        EndR = Cells(Rows.Count, Spalte).End(xlUp).Row"]
["        For i = 1 To EndR"]
["            If SuchWert = Application.Cells(i, Spalte).Value Then Zä = Zä + 1 'gefunden"]
["        Next i"]
["        If Zä > EndR Then Str_in_Colx = o Else Str_in_Colx = Zä ' found"]
["End Function"]
[]
["Public Sub Kollx_hide(KollNr As Long, JaNein As Boolean)"]
["    'versteckt oder zeigt die angegeben Kollone"]
["    Columns(KollNr).Select"]
["    Selection.EntireColumn.Hidden = JaNein"]
["End Sub"]
[]
["Public Sub Col_x_to_y_hide(StartKoll As Long, EndKoll As Long, JaNein As Boolean)"]
["    ' --> versteckt oder zeigt die angegeben Kollonen von x bis Y (zusammenhängender Bereich erforderlich)"]
["    Columns.Range(Cells(1, StartKoll), Cells(1, EndKoll)).Select"]
["    Selection.EntireColumn.Hidden = JaNein"]
["End Sub"]
[]
["Public Sub Zeile_löschen(Reihe As Long)"]
["    ' --> löscht die übergeben Reihe und Schiebt den Rest der Tabelle nach oben"]
["    Rows(Reihe).Delete Shift:=xlUp"]
["End Sub"]
[]
["Public Function lastUsedCell(Kollone As Long) As Long"]
["    ' --> ermittelt die letzte benutzte Reihe in der übergebenen Spalte; Rückgabe = Reihe als Long"]
["    lastUsedCell = Cells(Rows.Count, Kollone).End(xlUp).Row"]
["End Function"]
[]
["Public Function FirstEmptyCell(Kollone As Long) As Long"]
["    ' --> ermittelt die erste freie Reihe in der übergebenen Spalte; Rückgabe = Reihe als Long"]
["    FirstEmptyCell = (Cells(Rows.Count, Kollone).End(xlUp).Row) + 1"]
["End Function"]
[]
["Public Function lastUsedCol(Reihe As Long) As Long"]
["    ' --> ermittelt die letzte benutzte Spalte in der übergebenen Reihe; Rückgabe = Spalte als Long"]
["    lastUsedCol = Cells(Reihe, Columns.Count).End(xlToLeft).Column"]
["End Function"]
[]
["Public Function FirstEmptyCol(Reihe As Long) As Long"]
["    ' --> ermittelt die erste freie Spalte in der übergebenen Reihe; Rückgabe = Spalte als Long"]
["    FirstEmptyCol = Cells(Reihe, Columns.Count).End(xlToLeft).Column + 1"]
["End Function"]
[]
["Public Function ColNr_in_Char(Koll As Long) As String"]
["    ' --> ermittelt die Buchstaben der Spaltenbeschriftung der übergebenen Spalte Rückwert= 1 oder 2 Buchstaben der Spalte als String"]
["    Dim Bu As String"]
["    Bu = Application.Cells(1, Koll).Address(ColumnAbsolute:=False)"]
["    If Mid(Bu, 2, 1) = \"$\" Then ColNr_in_Char = Left(Bu, 1)"]
["    If Mid(Bu, 3, 1) = \"$\" Then ColNr_in_Char = Left(Bu, 2)"]
["End Function"]
[]
["Function FindFirst_in_Col(ColNr As Long, SuchTxt As String) As Long"]
["    ' --> Sucht den ersten Wert vertikal der übergebenen Kollone 'Rückgabe= SpaltenNr als Long"]
["    Dim R As Variant 'Return RowNr if exist else 0"]
["    R = Application.Match(SuchTxt, Application.Columns(ColNr), 0)"]
["    If Not IsError(R) Then FindFirst_in_Col = CLng(R) Else FindFirst_in_Col = 0"]
["End Function"]
[]
["Function FindFirst_in_Row(RowNr As Long, SuchTxt As String) As Long"]
["    ' --> Sucht den ersten Wert horizontal der übergebenen Reihe 'Rückgabe= ReihenNr als Long"]
["    Dim R As Variant 'Return ColNr if exist else 0"]
["    R = Application.Match(SuchTxt, Application.Rows(RowNr), 0)"]
["    If Not IsError(R) Then FindFirst_in_Row = CLng(R) Else FindFirst_in_Row = 0"]
["End Function"]
[]
["Public Sub SortBereich(Bereich As String, Schlüssel As String)"]
["    With ActiveSheet.Sort"]
["        .SortFields.Clear"]
["        .SortFields.Add Key:=Range(Schlüssel), _"]
["            SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal"]
["        .SetRange Range(Bereich)"]
["        .Header = xlGuess"]
["        .MatchCase = False  'Groß-/Kleinschreibung nicht berücksichtigt"]
["        .Orientation = xlTopToBottom"]
["        .SortMethod = xlPinYin"]
["        .Apply"]
["    End With"]
["End Sub"]
[]
["Public Sub SortTwoKey(Bereich As String, Schlüssel1, Schlüssel2 As String)"]
["    With ActiveSheet.Sort"]
["        .SortFields.Clear"]
["        .SortFields.Add Key:=Range(Schlüssel1), _"]
["            SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal"]
["        .SortFields.Add Key:=Range(Schlüssel2), _"]
["            SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal"]
["        .SetRange Range(Bereich)"]
["        .Header = xlGuess"]
["        .MatchCase = False  'Groß-/Kleinschreibung nicht berücksichtigt"]
["        .Orientation = xlTopToBottom"]
["        ''.SortMethod = xlPinYin"]
["        .Apply"]
["    End With"]
["End Sub"]
[]
["Public Sub OrdnerAuflisten(Dir As String)"]
["    Dim objFSO As Object"]
["    Dim folder As Object"]
["    Dim strPfad As String"]
["    Dim subFolder As Object, colSubfolders As Object"]
["    On Error GoTo OrdnerListe"]
["    bolGesperrt = False"]
["    'Pfad wird als Dir übergeben z-: C:"]
["    Set objFSO = CreateObject(\"Scripting.FileSystemObject\")"]
["    Set folder = objFSO.GetFolder(Dir)"]
["    Set colSubfolders = folder.Subfolders"]
["    'Prüfen ob Ordner vorhanden sind"]
["    lngK = 0"]
["    For Each subFolder In colSubfolders"]
["        lngK = lngK + 1"]
["    Next subFolder"]
["    If lngK = 0 Then"]
["        bolGesperrt = True"]
["        Exit Sub"]
["    End If"]
["    ' Ordner-Listen bilden"]
["    lngI = 2 'Startzeile festlegen"]
["    With Sheets(\"Val\")"]
["        'evtl bestehen Ordnerauflistung löschen"]
["        lngAnzR = .Cells(Rows.Count, 15).End(xlUp).Row  'Anzahl verwendet Reihen in Spalte X"]
["        strBereich = \"O2:O\" & lngAnzR"]
["        .Range(strBereich).Value = \"\""]
["        'Neu Liste aufbauen"]
["        lngI = 2"]
["        For Each subFolder In colSubfolders"]
["            .Range(\"O\" & lngI).Value = subFolder.Name"]
["            lngI = lngI + 1"]
["        Next subFolder"]
["    End With"]
["    Set folder = Nothing"]
["    Set colSubfolders = Nothing"]
["    Set objFSO = Nothing"]
["    Application.ScreenUpdating = True"]
["    Exit Sub"]
["OrdnerListe:"]
["    MsgBox \"Fehler # \" & Str(Err.Number) & \" wurde ausgelöst von \" _"]
["            & Err.Source & Chr(13) & Err.Description, vbCritical + vbOKOnly, \"Fehler Ordner auflisten  ... Ktm\""]
["    Set folder = Nothing"]
["    Set colSubfolders = Nothing"]
["    Set objFSO = Nothing"]
["    Application.ScreenUpdating = True"]
["End Sub"]
[]
["Public Sub Laufwerke()"]
["    'Die Laufwerke werden in eine Combobox Namens ComboDir eingelesen"]
["    'Diese muss im aufrufemden Formular vorhanden sein!"]
["    'Alternativ kann die aktiveb Dir auch in eine messagebox ausgegeben werden."]
["    Dim Fs"]
["    Dim lw"]
["    Set Fs = CreateObject(\"Scripting.filesystemobject\")"]
["    UF_DateiFuncSub.ComboDir.Clear"]
["    For Each lw In Fs.drives"]
["        If lw.isready Then UF_DateiFuncSub.ComboDir.AddItem lw"]
["        ''If lw.isready Then MsgBox lw"]
["    Next"]
["End Sub"]
[]
["Public Sub Kalender(bolFeiertage As Boolean)"]
["    ' Kalender zeichnen"]
["    ' vordefiniert sind; Jahr(intJahr), Startreihe (lngR), AnzahlSpalten(lngAnzC), Farbe Sonntag (lngFarbe)"]
["    ' Variablen aus Mod_Var"]
["    On Error GoTo KalErr"]
["    Dim datKalender As Date      'Kalenderstart"]
["    Dim datOstern As Date"]
["    Dim strMonat As String"]
["    Application.ScreenUpdating = False"]
["    datKalender = \"1.01.\" & intJahr"]
["    intWochentag = -Weekday(datKalender) + 2"]
["    datKalender = DateAdd(\"d\", intWochentag, datKalender) '1. Montag im Jahr"]
["    With ActiveSheet"]
["        For lngI = lngR To 373  'Kalenderloop"]
["            intWochentag = Weekday(datKalender)"]
["            strWoTag = Format(datKalender, \"dddd\")"]
["            intKW = KWX(datKalender)"]
["            strMonat = Format(datKalender, \"mmm\")"]
["            'Spalten 1-4 eintragen"]
["            .Cells(lngI, 1).Value = Format(datKalender, \"dd.MM.YYYY\")   'Datum"]
["            .Cells(lngI, 2).Value = strWoTag    'Wochentag oder Feiertag"]
["            .Cells(lngI, 3).Value = intKW   'Kalenderwoche"]
["            '.Cells(lngi, 3).Interior.Color = 14120960 'blau    'Kalenderwoche"]
["            '.Cells(lngi, 3).Font.Color = 16777215     'weiss   'Kalenderwoche"]
["            .Cells(lngI, 4).Value = strMonat  'Monat (Kurzformat)"]
["            '"]
["            If intWochentag = vbSunday Then 'Sonntag"]
["                For lngK = 1 To lngAnzC    'Anzahl Kollonnen fest"]
["                    .Cells(lngI, lngK).Interior.Color = lngFarbe"]
["                Next lngK"]
["            End If"]
["            datKalender = datKalender + 1"]
["        Next lngI"]
["    End With"]
["    ' Feiertage eintragen"]
["    If bolFeiertage = False Then GoTo OhneFeiertage"]
["    datOstern = OsterSonntag(intJahr)"]
["    For lngK = lngR To 373"]
["        With ActiveSheet"]
["            datJetzt = Format(.Cells(lngK, 1).Value, \"dd.mm.yyyy\")"]
["            strDatum = FeiertagText(datJetzt, datOstern) 'Sub Local"]
["            .Cells(lngK, 2).Value = strDatum"]
["            If Left(strDatum, 1) = \"!\" Then 'Feiertag"]
["                .Cells(lngK, 2).Font.Bold = True"]
["            Else"]
["                .Cells(lngK, 2).Font.Bold = False  'Datum"]
["            End If"]
["        End With"]
["    Next lngK"]
["    GoTo Abschliessen"]
["OhneFeiertage:"]
["    For lngK = lngR To 373  '"]
["        With ActiveSheet"]
["            .Cells(lngK, 2).Font.Bold = False"]
["        End With"]
["    Next lngK"]
["Abschliessen:"]
["    ActiveSheet.Range(\"A1\").Activate"]
["    Application.ScreenUpdating = True"]
["    Exit Sub"]
["KalErr:"]
["    MsgBox \"Fehler # \" & Str(Err.Number) & \" wurde ausgelöst von \" _"]
["            & Err.Source & Chr(13) & Err.Description, vbCritical + vbOKOnly, \"Fehler Kalender generieren  ... Ktm\""]
["    Application.ScreenUpdating = True"]
["End Sub"]
[]
["Public Sub PivotAufdatieren()"]
["' PivotAufdatieren Makro"]
["lngLastR = Sheets(\"Journal\").Cells(Rows.Count, 1).End(xlUp).Row ' Letzte Reihe im Journal"]
["strBereich = \"R4C1:R\" & lngLastR & \"C17\"    'Datebereich der Pivottabelle"]
["'"]
["    Sheets(\"Erfolg\").Select"]
["    Range(\"A4\").Select"]
["    ActiveSheet.PivotTables(\"Journal\").ChangePivotCache ActiveWorkbook.PivotCaches. _"]
["        Create(SourceType:=xlDatabase, SourceData:= _"]
["        \"C:\\Users\\Martin\\Documents\\AA_Vebu\\[VeBu2020.xlsm]Journal!R4C1:R100C17\", _"]
["        Version:=6)"]
["    ActiveSheet.PivotTables(\"Journal\").PivotCache.Refresh"]
["End Sub"]
[]
["Public Function ValEMail(ByVal strEmailAddress As String) As Boolean"]
["    On Error GoTo Catch"]
["    'Email Adresse prüfen"]
["    Dim objRegExp As New RegExp"]
["    Dim bolEMail As Boolean"]
["    objRegExp.IgnoreCase = True"]
["    objRegExp.Global = True"]
["    objRegExp.Pattern = \"^([a-zA-Z0-9_\\-\\.]+)@[a-z0-9-]+(\\.[a-z0-9-]+)*(\\.[a-z]{2,3})$\""]
["    bolEMail = objRegExp.test(strEmailAddress)"]
["    ValEMail = bolEMail"]
["    Exit Function"]
["Catch:"]
["    ValEMail = False"]
["    MsgBox \"Module: \" & MODULE_NAME & \" - ValEMail Funktion\" & vbCrLf & vbCrLf _"]
["        & \"Error#:  \" & Err.Number & vbCrLf & vbCrLf & Err.Description"]
["End Function"]
[]
["Public Function LongFarbe_zu_RGB(Farbe As Long) As String"]
["    ' Der Longwert einer Farbe wird in die Anteile Rot,Grün,Blau zerlegt"]
["    ' und als Text z.B. RGB(255,0,0) zurückgegeben (Beispiel: Farbe Rot)"]
["    lngRot = Farbe Mod 256"]
["    lngGrün = (Farbe \\ 256) Mod 256"]
["    lngBlau = (Farbe \\ 65536) Mod 256"]
["    LongFarbe_zu_RGB = \"RGB(\" & lngRot & \",\" & lngGrün & \",\" & lngBlau & \")\""]
["End Function"]
[]
["Public Function RGB_zu_LongFarbe(txtRGB As String) As Long"]
["    'Der Eingabetext hat das Format \"RGB(rrr,ggg,bbb)\""]
["    Dim strTemp As String"]
["    Dim i, R, G, B As Long"]
["    'zerlegen in RGB"]
["    strTemp = Right(txtRGB, Len(txtRGB) - 4)    'RGB( entfernen"]
["    strTemp = Left(strTemp, Len(strTemp) - 1)   'Klammer  rechts entfernen"]
["    i = InStr(1, strTemp, \",\", vbTextCompare)   'Kommaposition suchen"]
["    R = Val(Left(strTemp, i - 1))               'Rotwert"]
["    strTemp = Right(strTemp, Len(strTemp) - i)  'um Rot rediziert"]
["    i = InStr(1, strTemp, \",\", vbTextCompare)   'Kommaposition suchen"]
["    G = Val(Left(strTemp, i - 1))               'Grünwert"]
["    strTemp = Right(strTemp, Len(strTemp) - i)  'um Grün rediziert"]
["    B = Val(strTemp)                            'Blauwert"]
["    RGB_zu_LongFarbe = R + G * 256 + B * 65536"]
["End Function"]
[]
["Public Function RGB_zu_HexBGR(R As Long, G As Long, B As Long) As String"]
["'Hexwert aus den Werten r, g, b ermitteln"]
["'HEX Darstellung wie sie bei Userform Backcolor und Forecolor benötigt wird"]
["Dim HexRot, HexGrün, HexBlau As String"]
["    HexRot = Hex(R)"]
["    If Len(HexRot) = 1 Then HexRot = \"00\"    'falls 1-stellig auf 2-stellig erweitern"]
["    HexGrün = Hex(G)"]
["    If Len(HexGrün) = 1 Then HexGrün = \"00\"     'falls 1-stellig auf 2-stellig erweitern"]
["    HexBlau = Hex(B)"]
["    If Len(HexBlau) = 1 Then HexBlau = \"00\"     'falls 1-stellig auf 2-stellig erweitern"]
["    'Hex RGB mit &H zusammenstellen"]
["    RGB_zu_Hex = \"&H\" & lblHBlau & lblHGrün & lblHRot & \"&\""]
["End Function"]
[]
["Public Sub Box1Sort(BoxName As Object)"]
["    'sortieren einer 1-spaltigen Combobox oder Listbox"]
["    Dim A, B As Long, C As Variant"]
["    For A = 0 To BoxName.ListCount - 1"]
["        For B = A To BoxName.ListCount - 1"]
["            If BoxName.List(B) < BoxName.List(A) Then"]
["                C = BoxName.List(A)"]
["                BoxName.List(A) = BoxName.List(B)"]
["                BoxName.List(B) = C"]
["            End If"]
["        Next"]
["    Next"]
["End Sub"]
[]
["Public Function RGB_zu_Hex(R As Long, G As Long, B As Long) As String"]
["'Hexwert aus den Werten r, g, b ermitteln"]
["'HEX Darstellung wie sie bei Userform Backcolor und Forecolor benötigt wird"]
["Dim HexRot, HexGrün, HexBlau As String"]
["    HexRot = Hex(R)"]
["    If Len(HexRot) = 1 Then HexRot = \"00\"    'falls 1-stellig auf 2-stellig erweitern"]
["    HexGrün = Hex(G)"]
["    If Len(HexGrün) = 1 Then HexGrün = \"00\"     'falls 1-stellig auf 2-stellig erweitern"]
["    HexBlau = Hex(B)"]
["    If Len(HexBlau) = 1 Then HexBlau = \"00\"     'falls 1-stellig auf 2-stellig erweitern"]
["    'Hex RGB mit &H zusammenstellen"]
["    RGB_zu_Hex = \"&H\" & lblHRot & lblHGrün & lblHBlau & \"&\""]
["End Function"]
[]
["Public Function IstPrim(Zahl) As Boolean"]
["   For dblSumm = 2 To Int(Sqr(Zahl))"]
["      If Zahl / dblSumm = Int(Zahl / dblSumm) Then"]
["         IstPrim = False"]
["         Exit Function"]
["      End If"]
["   Next dblSumm"]
["   IstPrim = True"]
["End Function"]
[]
["Public Function Clipboard(Optional StoreText As String) As String"]
["    'PURPOSE: Read/Write to Clipboard"]
["    'Source: ExcelHero.com (Daniel Ferry)"]
["    Dim X As Variant"]
["    'Store as variant for 64-bit VBA support"]
["    X = StoreText"]
["    'Create HTMLFile Object"]
["    With CreateObject(\"htmlfile\")"]
["        With .ParentWindow.ClipboardData"]
["            Select Case True"]
["                Case Len(StoreText)"]
["                'Write to the clipboard"]
["                .SetData \"text\", X"]
["            Case Else"]
["                'Read from the clipboard (no variable passed through)"]
["                Clipboard = .GetData(\"text\")"]
["            End Select"]
["        End With"]
["    End With"]
["End Function"]