Raspberry Pi | Nextcloud | LaTeX | Linux | macOS | openmediavault | Synology | YubiKey | Docker | Ansible | OpenWRT
Git sieht eigentlich keine Versionierung von Binär-Dateien, wie Microsoft Word oder Microsoft Excel, im Standard vor. Das Tool war eigentlich mal dazu gedacht mit Flat Files zu versionieren, die in der Programmierung zu anfallen.
Nachdem mich immer wieder mal Anfragen mit der Bitte die Vorlage zu aktualisieren erreichen, habe ich das Arbeitszeiterfassung-Template angepasst. Ihr könnt die neue Version des Template nach wie vor kostenlos hier im Blog herunterladen.
Es sind nun die Jahreszahlen von 2021 bis 2030 auszuwählen und die Feiertage bis 2023 eingetragen.
Zudem wurde noch ein Fehler bei der Überstundenberechnung korrigiert. (Vielen Dank Herzog, der den Fehler gefunden hat ➡ Arbeitszeiterfassungsvorlage für Microsoft Excel.
Das Template könnt ihr direkt von codeberg.org herunterladen.
➡ https://codeberg.org/strobelstefan.org/Arbeitszeiterfassungs-Template
Wenn dir meine Beiträge gefallen und geholfen haben, dann kannst du mir gerne einen Kaffee ausgeben.
Wenn ihr kein Microsoft Excel habt oder das Programm nicht verwenden möchtet, dann nutzt die Vorlage doch mit einem freien Tabellenkalkulationsprogramm, wie z.B. LibreOffice.
➡ https://de.libreoffice.org/.
Im Template werden keine Makros oder Microsoft Excel spezifischen Sachen verwendet, weshalb es mit den kostenlosen Programmen auch funktioniert.
Die alte Vorlage gibt es auch noch hier im Blog herunterladen.
➡ Arbeitszeiterfassungsvorlage für Microsoft Excel
Das Makro schreibt die Benutzerinformationen samt Datum und aktueller Uhrzeit in eine angegebene Zelle.
Sub Benutzer()
'
' Schreibt die Benutzerdaten und Datum & Uhrzeit in die definierte Zelle
'
Worksheets("Tabelle1").Activate
Range("B1").Value = "User " & Environ("username") & " - " & Date & " " & Time
End Sub Zeilenumbrüche bei MsgBox, InputBox, TextBox, etc. lassen sich mit vbCrLf einfügen.
Im nachfolgenden Code wird das beispielhaft an einer TextBox aufgezeigt.
UserForm1.TextBox11.Value = "Das ist ein sehr langer Text" & vbCrLf & _
"Das ist ein sehr langer Text" & vbCrLf & _
"Das ist ein sehr langer Text" & vbCrLf & _
"Das ist ein sehr langer Text" & vbCrLf & _
"Das ist ein sehr langer Text" & vbCrLf & _
"Das ist ein sehr langer Text" & vbCrLf & _
"Das ist ein sehr langer Text" & vbCrLf & _
"Das ist ein sehr langer Text" & vbCrLf & _
"Das ist ein sehr langer Text" & vbCrLf & _
"Das ist ein sehr langer Text" & vbCrLf & _
"Das ist ein sehr langer Text" & vbCrLf & _
"Das ist ein sehr langer Text" & vbCrLf & _
"Das ist ein sehr langer Text" Manchmal ist es ganz hilfreich, wenn man alle Tabellen einer Arbeitsmappe in einer Listbox in einem UserForm anzeigen lässt.
Das erste Makro befüllt die ListBox mit den Namen aller Tabellen einer Arbeitsmappe.
Da es bei der Bearbeitung dazu kommen kann, dass Tabellenblätter hinzugefügt oder gelöscht werden, wird die Liste bei jedem Aufruf neu eingelesen.
Sub UserForm1Anzeigen()
'
'Ruft UserForm1 auf
'
call navigation_loeschen
Call ListBox2_Initialize
UserForm1.Show
End Sub
Private Sub ListBox2_Initialize()
'
' Befüllt die ListBox2 mit allen in der Arbeitsmappe vorhandenen Tabellen
'
Dim sht As Worksheet
For Each sht In ActiveWorkbook.Worksheets
UserForm1.ListBox2.AddItem sht.Name
Next sht
End Sub
Private Sub navigation_loeschen()
'
' Löscht die Einträge im Navigationsfeld
'
UserForm1.ListBox2.Clear
End Sub Mit diesem Makro lassen sich sehr leicht Werte aus einer Quelle in eine andere Ziel-Tabelle kopieren.
Sub KopiereABASUsersOrg()
'
' Kopiert die Originalwerte aus Quelle in Ziel
'
Application.ScreenUpdating = False
' Tabelle: Quelle ' Source
' Tabelle Ziel: Ziel ' Ziel
' Kopiere das Austrittsdatum aus den PMS-Daten-Rohdaten
'
' Ermittelt die letzte befüllte Zelle der Tabelle Ziel
Dim Letzte As Long
Worksheets("Ziel").Activate
Letzte = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row
' Ermittelt die letzte befüllte Zelle der Tabelle Ziel
' Ermittelt die letzte befüllte Zelle der Tabelle Quelle
Dim quelle_letzte As Long
Worksheets("Quelle").Activate
quelle_letzte = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row
' Ermittelt die letzte befüllte Zelle der Tabelle Quelle
' B3 - Schreibt die Formel in die Zelle
Worksheets("Ziel").Range("B3").FormulaLocal = _
"=WENN('Quelle'!B3="""";"""";'Quelle'!B3)"
' Kopiert die Formel ans Ende der Spalte
Worksheets("Ziel").Activate
Range("B3").AutoFill Destination:=Range("B3:B" & quelle_letzte), Type:=xlFillDefault
' D3 - Schreibt die Formel in die Zelle
Worksheets("Ziel").Range("C3").FormulaLocal = _
"=WENN('Quelle'!C3="""";"""";'Quelle'!C3)"
' Kopiert die Formel ans Ende der Spalte
Worksheets("Ziel").Activate
Range("C3").AutoFill Destination:=Range("C3:C" & quelle_letzte), Type:=xlFillDefault
Letzte = 0
quelle_letzte = 0
Call FormelnEntfernen
Application.ScreenUpdating = True
End SubMit diesem Makro können die gesetzten Formeln in der Tabelle „Ziel“ entfernt werden.
Private Sub FormelnEntfernen()
'
' Kopiert die Werte in Tabelle "Ziel", um die Formeln zu ersetzen
'
Application.ScreenUpdating = False
Worksheets("Ziel").Select
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub In meinem letzten Beitrag „Dokumenteneigenschaften“ habe ich gezeigt, wie man die Dokumenteneigenschaften per VBA manipulieren kann.
Ich persönlich finde es auch immer ganz schön, wenn man dem Benutzer diese Informationen auch über ein UserForm (falls vorhanden) ausgibt.
Es muss pro Textfeld ein Wert eingetragen werden. Der Textname lässt sich einfach identifizieren, einfach in das Feld klicken.
Der Code, der das Feld mit einem Wert füllt sieht so aus:
UserForm1.TextBox1.Value = "Das ist Text"In meinem Beitrag „Dokumenteneigenschaften“ habe ich globale Konstanten definiert, die man auch für diesen Zweck verwenden kann.
Dazu erstellen wir ein neues Makro mit dem Namen „CreditNotesEinfügen“
Sub CreditNotesEinfügen()
'
' Credit Notes werden in das Userform geladen
'
UserForm1.TextBox1.Value = "Title: " & Title
UserForm1.TextBox2.Value = "Company: " & Company
UserForm1.TextBox3.Value = "Department: " & Department
UserForm1.TextBox4.Value = "Author: " & Author
UserForm1.TextBox5.Value = "Version: " & Version & " / " & Month & " " & Year
UserForm1.TextBox6.Value = Date
UserForm1.TextBox7.Value = Time
UserForm1.TextBox9.Value = ThisWorkbook.Path
UserForm1.TextBox10.Value = ThisWorkbook.Name
UserForm1.TextBox13.Value = "Contact: " & EMail
End SubZu beachten ist, dass vor einem Aufruf des UserForm die Felder befüllt werden müssen, ansonsten sieht der Benutzer nur ein leeres Feld.
Sub UserForm()
'
' Startet das UserForm
'
Call CreditNotesEinfügen
UserForm.Show
End Sub
Mit Hilfe dieses Makros können die Dokumenteneigenschaften einer Excel-Datei manipuliert werden.
Die Konstanten habe ich global definiert, so können die auch für andere Zwecke in anderen Modulen angesprochen werden.
Im eigentlichen Makro „Dokumenteneigenschaften“ wird auf diese Konstanten zurückgegriffen und die Werte dort abgeholt.
Global Const Title As String = "Dokumententitel"
Global Const Company As String = "Firma"
Global Const Department As String = "Abteilung"
Global Const Author As String = "Stefan Strobel"
Global Const Version As String = "1.0"
Global Const Year As String = "2017"
Global Const Month As String = "June"
Global Const EMail As String = "meine@e-mail.de"
Sub Dokumenteneigenschaften()
'
' Schreibt die Dokumenteneigenschaften
'
ActiveWorkbook.BuiltinDocumentProperties("Title") = Title
ActiveWorkbook.BuiltinDocumentProperties("Author") = Author
ActiveWorkbook.BuiltinDocumentProperties("Company") = Company
ActiveWorkbook.BuiltinDocumentProperties("Subject") = "Meine bestes Dokument"
ActiveWorkbook.BuiltinDocumentProperties("KeyWords") = ""
ActiveWorkbook.BuiltinDocumentProperties("Comments") = ""
End Sub
Damit die Dokumenteneigenschaften sofort beim Öffnen der Excel-Datei eingetragen werden kann man das Makro über die Funktion AutoOpen aufrufen lassen.
Sub Auto_Open()
Call Dokumenteneigenschaften
End Sub
Die Index-Funktion in Excel kann verwendet werden, wenn der SVERWEIS an seine Grenzen stößt. Mit eine Kombination aus INDEX- und VERGLEICH-Funktion kann sowohl von links nach rechts, als auch von oben nach unten und in umgekehrter Reihenfolge gesucht werden.
Mit der INDEX-Funktion haben wir die Möglichkeit innerhalb eines definierten Bereichs, einer sog. Matrix, nach einer Übereinstimmung zu suchen.
Im Beispiel lassen wir die Formel nach dem Wert 2 suchen und bei einem Treffer den Wert aus der Spalte 3 ausgeben.
Formel
=INDEX(B3:E12;2;3)
Zusammensetzung der Formel
Diese Formel erlaubt es uns nach einem Wert zu suchen und die relative Zeilennummer ausgeben zu lassen.
Der ermittelte Wert resultiert daher, dass der Wert 2 in der zweiten Zeile unserer definierten Matrix steht.
Formel
=VERGLEICH(2;B3:B12;0)
Zusammensetzung der Formel
Nun kombinieren wir die beiden Formeln, jedoch wird das Ergebnis aus Spalte 4 als gesuchter Wert ausgegeben.
Formel
=INDEX(B3:E12;VERGLEICH(2;B3:B12);4)
Ich hatte heute ein sehr merkwürdiges Problem bei Excel.
Ich wollte zwei Datum miteinander mit einer WENN-Formel vergleichen, was jedoch nicht funktionierte. Aus irgendwelchen Gründen war das Datum in einer Zelle kein wirkliches Datum. Korrigieren konnte ich das ganze indem ich den Cursor in die Zelle gesetzt habe und wieder mit Enter verlassen habe. Da dieses Vorgehen aber bei mehreren 1.000 Zeilen sehr mühsam ist, habe ich diesen einfachen Weg gewählt.
In der Spalte C steht das Datum in seiner Ursprungsform.
In Spalte D habe ich nur den Wert 1 geschrieben
In Spalte E multipliziere ich den Wert aus Spalte C mit Spalte D. Dadurch habe ich das richtige Datum und kann damit weiterarbeiten.
