Hallo vba Freaks,
ich habe noch Excel 2010, aber bald Excel 2016 64 bit.
Für beide Versionen möchte ich eine Buchhalternase in mein kleines Kassenbuch (jeden Monat neues Kassenbuch Tabellenblatt in neuem xlxm file) via vba programmieren (ich weiss nicht, ob es für die neue Excel Version einen anderen Code braucht).
Bisher habe ich testweise nur ein Makro aufgezeichnet, was einen waagrechten Strich mit Hilfe der Formen einzeichnet (irgendwo im file). Ich habe keine Ahnung von Linien mit vba :-(
Die Linienlänge (3.Wert im Makro = 213.5) des Makros passt für die beiden waagrechten Linien immerhin schon mal...
Aber das löst mein Problem nicht, da die Buchhalternase in Form und Position abhängig von der letzten bebuchten Buchungszeile ist, und nicht starr von vorselektierten Koordinaten.
Das Kassenbuch.xlxm besteht aus 8 gleichartigen, untereinander positionierten, mit Rahmen gezeichneten Kassenblättern (alle in einem Tabellenblatt). Dazu noch 2 weitere Tabellenblätter und vba Code (aber das nur am Rande).
Jedes Kassenblatt hat je 45 Buchungszeilen und je 7 Kopfzeilen darüber, und jeweils 3 Summenzeilen (Fusszeilen) darunter. 3 Leerzeilen trennen zusätzlich die Blätter voneinander (optisch besser).
Kassenblatt 1 beginnt in Excel Zeile 1, erste Buchungszeile (Blatt 1) ist wegen Kopfzeilen Excel Zeile 8.
Letzte mögliche Buchungszeile Nr. 360 ist Excel Zeile 458. Plus 3 Fusszeilen = bei Excel Zeile 461 ist das File zu Ende.
Kopf- und Fusszeilen sind unterschiedlich hoch, Buchungszeilen alle gleich hoch.
Die (unterschiedlich breiten) Spalten gehen von B bis Q, Spalte A ist Leerspalte.
Die Buchhalternase soll aus 2 gleich langen, waagrechten, blauen Linien (Excel Standard blau) der Dicke "2" bestehen, und einer gleichartigen diagonalen Verbindungslinie (fallweise unterschiedlicher Länge und Steigung natürlich).
Die 1. waagrechte Linie über die Spalten C (Einzahlungen, enthält Werte oder leer) und D (Auszahlungen, enthält Werte oder leer) und E (Bestand, Formel mit Null oder ungleich Null). Nur C ODER D einer Zeile kann Werte enthalten, oder beide leer (falls keine Buchungserfassung in dieser Zeile)... dann ist E auch gleich Null.
Ausrichtung der Linie: linksbündig in C, bis Mitte Spalte E, zellenmittig von der Höhe her.
Diese Linie wird ausschliesslich in die LETZTE VORHANDENE und zugleich LEERE Buchungszeile des LETZTEN mit Buchungen versehenen Blatts positioniert, also immer unten links im Kassenbuchblatt. Frühestens also Zeile 52, falls nur Blatt 1 Buchungen enthält. Oder Zeile 110, falls nur Blatt 1 und Blatt 2 Buchungen enthalten... oder dann jeweils 58 Zeilen (45 Buchungszeilen + 7 Kopfzeilen + 3 Fusszeilen + 3 Leerzeilen) tiefer, bis das erste unbebuchte Blatt gefunden wird.
Die 2. waagrechte Linie (gleich lang, dick und gefärbt) kommt in Spalten N,O,P,Q (rechtsbündig in Q bis Mitte N; Höhe dto. zellenmittig). Und zwar DIREKT UNTER die LETZTE BEBUCHTE Buchungszeile (falls diese nicht zugleich die letzte VORHANDENE Buchungszeile des betreffenden Kassenblatts ist, dann gibts nämlich keine Buchhalternase) des SELBEN Kassenblatts, wobei Spalte Q den USt.-Satz enthält (Formel), O und P in dieser Version leer sind (Kostenträger und Skonto), und N eine Formel für die Kostenstelle enthält.
Also befindet sich die 2. Linie immer relativ weit oben rechts in den Buchungszeilen des Kassenbuchblatts.
Eine 3. Linie (Verbindungslinie; gleiches Blau, Dicke "2") soll nun die beiden waagrechten Linien punktgenau verbinden (einrastern): vom rechten Endpunkt der waagrechten Linie unten links bis zum linken Endpunkt der waagrechten Linie oben rechts.
Diese Diagonale ist natürlich unterschiedlich lang und schräg relativ zur Horizontalen, je nachdem, in welcher Zeile der letzte Buchungssatz steht.
Im 1. Sonderfall, dass die betreffende 44. Buchungszeile (vorletzte vorhandene) des jeweiligen Kassenblatts als letztes bebucht wurde, ist die Diagonale (und mithin die ganze "Nase") eine waagrechte Linie in Excel Zeile 52 (= Buchungszeile 45) oder ggf. Excel Zeile 110 (= Buchungszeile 90) oder Excel Zeile 168 usw., abhängig vom Kassenblatt...
Im 2. Sonderfall, dass die betreffende 45. Buchungszeile (letzte vorhandene) des jeweiligen Kassenblatts (letzte mögliche Buchungszeile des betreffenden Kassenblatts) als letztes bebucht wurde, gibt es keine Buchhalternase, da es kein unvollständig bebuchtes Kassenblatt gibt.
In Pseudo-Code (Computerchinesisch) könnte ich mir die todo Liste für das vba in etwa so vorstellen:
1. Ermittle die LETZTE BEBUCHTE Buchungszeile des gesamten Kassenbuchs (nennen wir sie x, gemessen in Excel Zeilen), z.B. indem du feststellst, ob die Formel in Spalte E (= Bestand) dieser Zeile einen Wert gleich Null enthält. Triffst du auf den ersten Nullwert, so ist die Zeile davor die gesuchte Zeile x (Bestand ungleich Null = bebucht). Gehe hierzu alle Zeilen ab Excel Zeile 8 von Spalte E des Kassenbuchs (= Tabellenblatts) von oben nach unten durch. Triffst du dabei in Spalte E auf keine Formel, sondern auf Zeilen, die Text enthalten (= z.B. auf die Überschrift "Bestand" bzw. die Unterschriften "Summe", "gesamt" etc.), dann ignorierst du das... und suchst weiter im nächsten Kassenblatt, Spalte E.
2. Nach deren Ermittlung gehe eine Zeile tiefer ( = ERSTE UNbebuchte Zeile, nennen wir sie x+1, auch in Excel Zeilen). Triffst du dabei wiederum in Spalte E auf keine Formel, sondern auf den Text "Summe" (= 1. Zeile mit Text unterhalb der letzten vorhandenen Buchungszeile) , brich das vba ab: die letzte BEBUCHTE Zeile ist in diesem Falle zugleich die letzte VORHANDENE Buchungszeile des betreffenden Blatts, und es gibt keine Buchhalternase (Sonderfall 2).
3. Andernfalls gehe in die Spalten N bis Q dieser Zeile (x+1).
Zeichne hier rechtsbündig an Q, in Höhe der Zellenmitte eine waagrechte Linie der Dicke 2 in Excel Standard blau und der Länge (ca. lt. obigem Makro, aber vielleicht gibts auch noch andere Bemassungen?)) von 213.5 ein.
4. Ermittle nun die letzte VORHANDENE Buchungszeile des betreffenden Kassenblattes (die ja nach obiger Prüfung leer ist, nennen wir sie y, in Excel Zeilen), z.B. nach folgendem Algorithmus:
Die nun folgenden Ziffern beziehen sich wie gehabt auf die Excel Zeilen Nr.:
Wenn x kleiner 52, dann y = 52.
Wenn x = 52, dann vba Abbruch (aber wohl redundant mit 2., 2. Satz ;-)
Dann für n = 1 bis 7 (immer um eine Ganzzahl erhöhen, d.h., 7 mal durchrechnen):
Wenn 52 kleiner x kleiner 52 + (n*58), dann y = 52 + (n*58)
Wenn x = 52 + (n*58), dann vba Abbruch (aber wohl redundant mit 2., 2. Satz)
5. Gehe nun in die Spalten C bis E dieser Zeile y.
Zeichne hier linksbündig an C, in Höhe der Zellenmitte eine waagrechte Linie der Dicke 2 in Excel Standard blau und der Länge (ca. lt. obigem Makro, aber vielleicht gibts auch noch andere Bemassungen?) von 213.5 ein.
6. Verbinde die beiden waagrechten Linien mit einer diagonalen Linien gleicher Dicke und Farbe an den Eckpunkten wie folgt:
Rechter Eckpunkt waagrechte Linie unten links mit linkem Eckpunkt waagrechte linie oben rechts (im Sonderfall 1 liegen beide waagrechten Linien in einer Zeile, und die Diagonale wird ebenfalls eine Waagrechte).
Der Code soll in bereits vorhandenen Code in Modul 1 (Private Sub Sperren()) integriert werden, welcher das Kassenbuch am Schluss auch noch sperrt und schützt... und zwar zwischen die Code-Zeilen "MsgBox tralala..." (Sicherheitsabfrage vor dem Sperren) und "Range("A1:w500").Locked = True ' alle Zellen sperren".
Kennt sich da wer aus?
Vielen Dank für Eure Hilfe bei der Suche nach meinem Osterei ;-)
Berni