Microsoft Excel Archive - Stefan's Weblog

1. April 202127. Dezember 2021

Bedingte Formatierung in LibreOffice Calc kopieren

Seit längerer Zeit habe ich mal wieder mit LibreOffice Calc gearbeitet. In meiner Tabelle hatte ich in mehreren Zellen eine Bedingte Formatierung erstellt, die ich in eine andere Zelle in der Tabelle kopieren wollte. Mit einem schlichten Strg+C und Strg+V, also einem Copy+Paste, bin ich nicht zum Ziel gekommen.

In LibreOffice Calc funktioniert das Kopieren der Bedingten Formatierung anders als in einem Microsoft Excel.

Bedingte Formatierung kopieren

In diesem Beispiel wird in der Zelle „B7“ eine einfache Bedingte Formatierung erstellt, die anschließend in die Zelle „F7“ kopiert werden soll.

Die Bedingte Formatierung erstellen wir zuerst in der Zelle „B7„, wie im Screenshot dargestellt.

Die Bedingte Formatierung ist fertig erstellt und soll nun in die Zelle „F7“ kopiert werden. Mit einem einfachen Copy-Paste ist das in LibreOffice Calc nicht möglich.

Setzt den Cursor in die Zelle B7
Drückt die Tastenkombination Strg+V
Setzt den Cursor in die Zelle F7
Drückt die Tastenkombination Strg+Shift+V
Wählt in dem neuen Fenster nur „Formatierung“ aus

Das erfolgreiche Kopieren der Bedingten Formatierung könnt ihr gleich prüfen.

Fehler, Gruppe existiert nicht! Überprüfen Sie Ihre Syntax! (ID: 47)

Bedingte Formatierung mit Zellbezug kopieren

Eine kleine Besonderheit gilt es beim Erstellen und beim Kopieren von Bedingten Formatierungen in LibreOffice Calc zu beachten.
Habt ihr eine Bedingte Formatierung, die ihr auf eine andere Zelle referenziert, dann wird ein Dollar-Zeichen ($) in die Formel automatisch eingefügt.
Damit referenziert die Bedingte Formatierung immer auf diese Zelle, egal, was ihr in der Tabelle macht oder die Bedingte Formatierung kopiert.

Ihr müsst also schon beim Erstellen der Bedingten Formatierung darauf achten, dass Dollar-Zeichen zu entfernen, damit ihr dann ohne Probleme kopieren könnt.

In diesem Beispiel verweist die Bedingung 1 auf die Zelle B8 mit Dollarzeichen:

$Tabelle1.$B$8>0

Damit wir die Bedingte Formatierung auch innerhalb der Tabelle 1 in die gleiche Zeile 8 kopieren können, ist das Dollarzeichen vor dem B zu entfernen:

$Tabelle1.$B8>0

Nun könnt ihr wie oben beschrieben die definierte Bedingung in die Zelle F10 kopieren.

Möchtet ihr die Bedingte Formatierung auch in eine andere Zeile oder in ein anderes Tabellen-Blatt kopieren, müsst ihr die Dollar-Zeichen entsprechend entfernen.

Photo by David Pisnoy on Unsplash

24. März 202115. Juni 2022

Git – diff für Excel- und LibreOffice Calc-Dateien

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.

7. Januar 202120. November 2021

Arbeitszeiterfassungsvorlage für Microsoft Excel – Aktualisierte Version

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.

Download der Vorlage

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.

Kein Microsoft Excel – nutzt LibreOffice!

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

20. August 201725. November 2022

Benutzerdaten, Datum & Uhrzeit in eine definierte Zelle schreiben

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

19. August 201725. November 2022

Zeilenumbrüche bei MsgBox, InputBox, TextBox, …

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"

1. August 20173. Dezember 2020

Alle Tabellen einer Arbeitsmappe in ListBox auflisten

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

27. Juli 201725. November 2022

Werte Kopieren von Quell- in Ziel-Tabelle

Mit diesem Makro lassen sich sehr leicht Werte aus einer Quelle in eine andere Ziel-Tabelle kopieren.

Es wird für jede definierte Rage (Spalte) die letze beschriebene Zeile ermittelt.
In die Tabelle Ziel wird eine Formel für den Übertrag der Werte aus Ziel eingetragen.
Die Formeln werden nach erfolgreichem übertrag überschrieben.

Quelle

Ziel

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 Sub

Mit 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

23. Juli 201725. November 2022

Credits in UserForm

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 Sub

Zu 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

22. Juli 201725. November 2022

Dokumenteneigenschaften manipulieren

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

1. Dezember 201525. November 2022

INDEX & VERGLEICH – Alternative zum SVERWEIS

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.

INDEX

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

Definition der Matrix = B3:E12
Suchkriterium = 2
Ausgabe = Spalte 3

VERGLEICH

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.