Leere Zellen mit Formel befüllen

Programmierung unter AOO/LO (StarBasic, Python, Java, ...)

Moderator: Moderatoren

Gerald Peterson
Beiträge: 6
Registriert: Mi, 03.06.2020 16:51

Leere Zellen mit Formel befüllen

Beitrag von Gerald Peterson »

Guten Tag,

ich benutze die Calc-Version 6.0.7.3 (Build-ID: 1:6.0.7-0ubuntu0.18.04.10) unter Linux.

Ich arbeite gerade an einer Kalkulation zum Zwecke der Haushaltsplanung. Ein Dokument erfasst alle Kostenpunkte, verteilt auf mehrere Tabellen. Ein anderes Dokument greift das Budget, also die in den Tabellen erfassten Kostenpunkte, wieder auf und enthält Tabellen für jeden Monat des Jahres. Dort werden beispielsweise Abweichungen vom Budget erfasst. Der Folgemonat greift dann das Ergebnis des alten Monats wieder auf. So besteht immer ein Überblick darüber, wie viel Geld für was aktuell vorhanden ist.
Da manchmal Kostenpunkte wegfallen und leere Zeilen unschön sind, müssen die Tabellen, die die Kostenpunkte erfassen, sortiert werden. Dabei verschiebt sich die Position eines Kostenpunkts. Damit in dem 2. Dokument der Folgemonat dann noch den richtigen Wert zum richtigen Kostenpunkt in der Tabellen des alten Monats findet, müsste man per Formel nach dem Namen des Kostenpunkts suchen und dann in dessen Zeile die richtige Zahl holen. Dadurch wäre aber der Name des Kostenpunkts nicht problemlos umbenennbar.
Um den Namen also auch umbenennen zu können, hätte ich gerne einen einzigartigen ID-Code zu jedem Kostenpunkt, anhand dem er immer identifizierbar bleibt. Er soll automatisch erstellt werden mit der Formel "=JETZT()&"a"&ZUFALLSBEREICH(1;99999)". Allerdings ändert sich ja dieser Wert. Und damit er es nicht macht, wird die Formel anschließend in einen Wert umbenannt.

Wie die Tabelle aussieht, zeigt das Bild im Anhang.

Ich habe mich in der Makroprogrammierung versucht und bin an einer Stelle hängengeblieben. Das Makro entfernt alte Kostenpunkte aus den vorher angewählten Zeilen. Anschließend wird automatisch sortiert. Und eben danach fehlt der Code, der in den leeren Zeilen unterhalb der Kostenpunkte (leere Zeilen sind die, wo gelöscht wurden) meine Formel "=JETZT()&"a"&ZUFALLSBEREICH(1;99999)" einfügt. Alle leere Zeilen stehen nach dem Sortieren untereinander.

Ich habe das Problem versucht manuell zu lösen: Ich habe den Dialog Suchen & Ersetzen geöffnet. Nichts als Suchwort eingetragen, aber meine Formel als Ersetzungswort. In dem angewählten Bereich steht in den leeren Zellen nachher meine Formel, aber die Formel wird nicht als solche erkannt, trotz des Gleichheitszeichens davor. Sie wird wie Text behandelt.

Mein Makro:

Code: Alles auswählen

sub Kostenpunktentferner
rem ----------------------------------------------------------------------
rem define variables
dim document   as object
dim dispatcher as object
rem ----------------------------------------------------------------------
rem get access to the document
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

rem ----Jetzt werden aus allen angewaehlten Zeilen alles außer Formeln und Formate entfernt-----
dim args1(0) as new com.sun.star.beans.PropertyValue
args1(0).Name = "Flags"
args1(0).Value = "SVDN"

dispatcher.executeDispatch(document, ".uno:Delete", "", 0, args1())

rem ------Jetzt wird der Sortierbereich angewaehlt---------
dim args2(0) as new com.sun.star.beans.PropertyValue
args2(0).Name = "ToPoint"
args2(0).Value = "$A$3:$AZ$31"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args2())

rem -----------Jetzt wird sortiert-------------
dim args3(8) as new com.sun.star.beans.PropertyValue
args3(0).Name = "ByRows"
args3(0).Value = true
args3(1).Name = "HasHeader"
args3(1).Value = false
args3(2).Name = "CaseSensitive"
args3(2).Value = false
args3(3).Name = "NaturalSort"
args3(3).Value = false
args3(4).Name = "IncludeAttribs"
args3(4).Value = true
args3(5).Name = "UserDefIndex"
args3(5).Value = 0
args3(6).Name = "Col1"
args3(6).Value = 1
args3(7).Name = "Ascending1"
args3(7).Value = true
args3(8).Name = "IncludeComments"
args3(8).Value = false

dispatcher.executeDispatch(document, ".uno:DataSort", "", 0, args3())

rem --Hier muesste zuerst Code eingefuegt werden, der im Bereich B3:B31 LEERE Zellen mit der Formel "=JETZT()&"a"&ZUFALLSBEREICH(1;99999)" befuellt.--

rem --Jetzt wird der Bereich angewaehlt, wo Formeln in Werte verwandelt werden sollen--
dim args4(0) as new com.sun.star.beans.PropertyValue
args4(0).Name = "ToPoint"
args4(0).Value = "$B$3:$B$31"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args4())

rem ----------Jetzt wird in Werte konvertiert---------
dispatcher.executeDispatch(document, ".uno:ConvertFormulaToValue", "", 0, Array())

rem ----------------Wir kehren zur ersten Zelle in der Tabelle zurueck----------------------
dim args5(0) as new com.sun.star.beans.PropertyValue
args5(0).Name = "ToPoint"
args5(0).Value = "$A$1"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args5())


end sub
Vielen Dank allen Helfenden
Tabelle.png
Tabelle.png (31.45 KiB) 4116 mal betrachtet
F3K Total
********
Beiträge: 3704
Registriert: Mo, 28.02.2011 17:49

Re: Leere Zellen mit Formel befüllen

Beitrag von F3K Total »

Moin,
warum einfach, wenn es auch kompliziert geht?
Deine Formel stellt nicht sicher, das es zu jedem Kostenpunkt eine eindeutige ID gibt, denn Zufallsbereich() kann zufällig auch zweimal oder mehrmals die gleiche Zahl ermitteln, und die Jetzt() Funktion hat zumindest bei mir, bei einer Neuberechnung den gleichen Wert für alle Zeilen.
Warum fängst du nicht einfach bei 1 an und zählst die ID je Zeile um eins hoch?

Gruß R
Gerald Peterson
Beiträge: 6
Registriert: Mi, 03.06.2020 16:51

Re: Leere Zellen mit Formel befüllen

Beitrag von Gerald Peterson »

Danke, ein guter Hinweis. Allerdings gibt es ja mehrere solche Tabellen und da käme es dann zu Wiederholungen, wenn überall 1, 2, 3, 4 gezählt wird. Außerdem darf die ID eines gelöschten Kostenpunkts nicht einem neuen Kostenpunkt erneut zugeteilt werden, sonst wird es Fehler geben.
Um Wiederholungen bei den IDs garantiert zu vermeiden hätte ich folgende Lösung:

=JETZT()&"a"&ZUFALLSBEREICH(1;99999)&"a"&VERSCHIEBUNG(INDIREKT(ADRESSE(ZEILE();SPALTE()));32;55)

In der Zelle, auf die die Verschiebung verweist, steht eine einzigartige Zahl, um die die ID nochmals erweitert wird. Dann kann nichts passieren.

Es bleibt die Frage, wie ich per Makro in der Spalte B leere Zellen mit der Formel oben befüllen kann. Ich hatte versucht, etwas mit IF … THEN … END IF zu schreiben, aber es hat nicht funktioniert. Das Programm sollte sich durch die Spalte B von Zelle 3 bis Zelle 31 durcharbeiten und jedes Mal abfragen, ob die Zelle leer ist, und wenn ja, dann die Formel einsetzen.
F3K Total
********
Beiträge: 3704
Registriert: Mo, 28.02.2011 17:49

Re: Leere Zellen mit Formel befüllen

Beitrag von F3K Total »

:roll: Wenn du nun unbedingt willst:

Code: Alles auswählen

Sub Set_Formula
    oSheet = Thiscomponent.currentController.ActiveSheet
    for i = 2 to 30
        oCell = oSheet.getCellbyposition(1,i)
        if oCell.string = "" then
            oCell.FormulaLocal = "=JETZT()&""a""&ZUFALLSBEREICH(1;99999)&""a""&VERSCHIEBUNG(INDIREKT(ADRESSE(ZEILE();SPALTE()));32;55)"
        endif
    next i
End Sub
... wirkt auf das aktuell sichtbare Tabellenblatt.
Gruß R
Gerald Peterson
Beiträge: 6
Registriert: Mi, 03.06.2020 16:51

Re: Leere Zellen mit Formel befüllen

Beitrag von Gerald Peterson »

Vielen Dank!

Es funktioniert. So sieht das fertige Makro aus (Dein Makro zum Formelneinsetzen habe ich integriert in das eigene):

Code: Alles auswählen

sub Kostenpunktentferner
rem ----------------------------------------------------------------------
rem define variables
dim document   as object
dim dispatcher as object
rem ----------------------------------------------------------------------
rem get access to the document
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

rem ----Jetzt werden aus allen angewaehlten Zeilen alles außer Formeln und Formate entfernt-----
dim args1(0) as new com.sun.star.beans.PropertyValue
args1(0).Name = "Flags"
args1(0).Value = "SVDN"

dispatcher.executeDispatch(document, ".uno:Delete", "", 0, args1())

rem ------Jetzt wird der Sortierbereich angewaehlt---------
dim args2(0) as new com.sun.star.beans.PropertyValue
args2(0).Name = "ToPoint"
args2(0).Value = "$A$3:$AZ$31"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args2())

rem -----------Jetzt wird sortiert-------------
dim args3(8) as new com.sun.star.beans.PropertyValue
args3(0).Name = "ByRows"
args3(0).Value = true
args3(1).Name = "HasHeader"
args3(1).Value = false
args3(2).Name = "CaseSensitive"
args3(2).Value = false
args3(3).Name = "NaturalSort"
args3(3).Value = false
args3(4).Name = "IncludeAttribs"
args3(4).Value = true
args3(5).Name = "UserDefIndex"
args3(5).Value = 0
args3(6).Name = "Col1"
args3(6).Value = 1
args3(7).Name = "Ascending1"
args3(7).Value = true
args3(8).Name = "IncludeComments"
args3(8).Value = false

dispatcher.executeDispatch(document, ".uno:DataSort", "", 0, args3())

rem --Im Bereich B3:B31 werden LEERE Zellen mit einer Formel befuellt.--

    oSheet = Thiscomponent.currentController.ActiveSheet
    for i = 2 to 30
        oCell = oSheet.getCellbyposition(1,i)
        if oCell.string = "" then
            oCell.FormulaLocal = "=JETZT()&""a""&ZUFALLSBEREICH(1;99999)&""a""&VERSCHIEBUNG(INDIREKT(ADRESSE(ZEILE();SPALTE()));32;55)"
        endif
    next i

rem --Jetzt wird der Bereich angewaehlt, wo Formeln in Werte verwandelt werden sollen--
dim args4(0) as new com.sun.star.beans.PropertyValue
args4(0).Name = "ToPoint"
args4(0).Value = "$B$3:$B$31"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args4())

rem ----------Jetzt wird in Werte konvertiert---------
dispatcher.executeDispatch(document, ".uno:ConvertFormulaToValue", "", 0, Array())

rem ----------------Wir kehren zur ersten Zelle in der Tabelle zurueck----------------------
dim args5(0) as new com.sun.star.beans.PropertyValue
args5(0).Name = "ToPoint"
args5(0).Value = "$A$1"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args5())


end sub
Merkwürdigerweise wird die Spalte B meistens nur bis einschließlich Zelle 28 befüllt, nicht bis 31. Wenn ich das Makro auf Spalte A, C oder D ansetze, wird immer bis Zelle 31 befüllt. Vielleicht ein Bug? Das gilt, wenn Dein Makro in meinem integriert ist, nicht aber, wenn es für sich allein als eigenständiges Makro eingesetzt wird. Dann funktioniert es immer bis Zeile 31.
mikeleb
*******
Beiträge: 1315
Registriert: Fr, 09.12.2011 16:50

Re: Leere Zellen mit Formel befüllen

Beitrag von mikeleb »

Hallo,
Und damit er es nicht macht, wird die Formel anschließend in einen Wert umbenannt.
Wäre es dann nicht sinnvoll, anstelle einer Formel gleich den berechneten Wert in die Zelle zu schreiben?
Gruß,
mikeleb
Stephan
********
Beiträge: 12369
Registriert: Mi, 30.06.2004 19:36
Wohnort: nahe Berlin

Re: Leere Zellen mit Formel befüllen

Beitrag von Stephan »

Vielleicht ein Bug?
Warum überprüfst Du es nicht?

z.B.:

Code: Alles auswählen

'...
if oCell.string = "" then
oCell.FormulaLocal = "=JETZT()&""a""&ZUFALLSBEREICH(1;99999)&""a""&VERSCHIEBUNG(INDIREKT(ADRESSE(ZEILE();SPALTE()));32;55)"
Else
If LEN(oCell.string) <> 0 Then Msgbox "Zelle B" & i+1 & " ist nicht nicht leer."
Endif
'...

Gruß
Stephan
Gerald Peterson
Beiträge: 6
Registriert: Mi, 03.06.2020 16:51

Re: Leere Zellen mit Formel befüllen

Beitrag von Gerald Peterson »

Es muss tatsächlich ein Bug sein, der sich auf Spalte B beschränkt. Auf diese Spalte kann ich natürlich verzichten. Hier der Befund:

Die Infobox, dass die Zellen schon mit irgendwas befüllt wären, erscheint nicht. Wenn ich den Bereich, der zu füllen ist, über Zeile 31 hinaus erweitere in dem Makro, arbeitet er sich ab Zeile 32 normal weiter durch. Die Zellen 29–31 bleiben leer.
Ich bin auf einen anderen Benutzer gegangen, um zu überprüfen, ob es nicht mit Einstellungen zu tun hat, die für LO Calc im Homeordner gespeichert sind. Diese These konnte ich falsifizieren. Aber es gibt einen Unterschied: Auf dem anderen Benutzer bleibt eine Zelle mehr leer: 28–31. Auf beiden Benutzern funktioniert es in jeder Spalte einwandfrei außer in Spalte B. Es ist egal, in welchem Tabellendokument man es probiert.
Interessant ist Folgendes: Ich habe das Makro testweise noch so angepasst, dass nicht leere Zellen das Kriterium sind, sondern solche, die eine 0 als Wert enthalten. Ich habe die ersten 50 Zellen von Spalte B mit Nullen gefüllt, und siehe da: Die Nullen in den Zellen 29–31 bzw. 28–31 verschwinden und stehen nachher leer da. In anderen Spalten funktioniert alles normal:
Bild.png
Bild.png (24.38 KiB) 3957 mal betrachtet
F3K Total
********
Beiträge: 3704
Registriert: Mo, 28.02.2011 17:49

Re: Leere Zellen mit Formel befüllen

Beitrag von F3K Total »

Versuche es mal hiermit, aber nicht mit Nullen, sondern mit leeren Zellen:

Code: Alles auswählen

Sub Set_Formula
    oSheet = Thiscomponent.currentController.ActiveSheet
    for i = 2 to 30
        oCell = oSheet.getCellbyposition(1,i)
        if oCell.string = "" then
            oCell.FormulaLocal = "=JETZT()&""a""&ZUFALLSBEREICH(1;99999)&""a""&VERSCHIEBUNG(INDIREKT(ADRESSE(ZEILE();SPALTE()));32;55)"
            s = oCell.String
            oCell.FormulaLocal = ""
            oCell.String = s
        endif
    next i
End Sub
Gruß R
Gerald Peterson
Beiträge: 6
Registriert: Mi, 03.06.2020 16:51

Re: Leere Zellen mit Formel befüllen

Beitrag von Gerald Peterson »

Dankeschön! Jetzt werden die Zellen 29, 30, 31 jedenfalls auch mit der Formel befüllt.

Trotzdem, wie ich zuvor festgestellt habe, scheint das Makro zum Befüllen der Zellen mit der Formel nicht der Grund gewesen zu sein, warum 3 Zellen leer geblieben sind. Es lag vielleicht an dem Code im Makro, der die Formeln in Werte umwandelt:

Code: Alles auswählen

rem --Jetzt wird der Bereich angewaehlt, wo Formeln in Werte verwandelt werden sollen--
dim args4(0) as new com.sun.star.beans.PropertyValue
args4(0).Name = "ToPoint"
args4(0).Value = "$B$3:$B$31"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args4())

rem ----------Jetzt wird in Werte konvertiert---------
dispatcher.executeDispatch(document, ".uno:ConvertFormulaToValue", "", 0, Array())
Als ich nämlich den dort angegebenen Bereich $B$3:$B$31 auf eine andere Spalte anwendete, während weiterhin die Formeln in Spalte B eingesetzt waren, verschwand der Inhalt in Zelle 29, 30 und 31 nicht.
Bei dieser Gelegenheit hatte ich auch ein anderes Phänomen festgestellt, das, KORREKTUR, NICHT mit dem Code zum Formelnumwandeln oben zu tun hat (wenn ich den Code oben aus dem vollständigen Makro entferne, passiert es nämlich weiterhin). Experimentell enthielten alle Zellen im Bereich A1:O75 Nullen, waren also befüllt. Wenn ich das vollständige Makro ausführe, wird an irgendeiner Stelle, von der im Makro gar nicht die Rede ist, der Inhalt einer einzigen Zelle gelöscht. Nachdem ich Dein neues Makro in das große eingebettet habe und das Ganze ausgeführt habe, war die Zelle D10 plötzlich leer (bei einem 2. Test traf es D14, bei einem 3. Test nach Programmneustart J15). Wenn man mit Strg+Z zurückgeht, wird sie auch irgendwann wieder hergestellt.

Aktualisierung: Das Phänomen mit der einzelnen leeren Zelle hängt nur damit zusammen, dass ich entgegen einem der Zwecke des Makros, ausgewählte Zeilen zu löschen, keine Zeilen zum Löschen angewählt hatte, als ich die Tests machte.
Gerald Peterson
Beiträge: 6
Registriert: Mi, 03.06.2020 16:51

Re: Leere Zellen mit Formel befüllen

Beitrag von Gerald Peterson »

Könntet Ihr mir vielleicht noch helfen, das Makro, siehe unten, so einzurichten, dass ganz zu Anfang geprüft wird, ob (mit Klick auf die Zeilennummern) eine oder mehrere vollständige Zeilen angewählt sind? Wenn nicht, sollte abgebrochen werden und mit msgbox ein Fenster erscheinen, das mitteilt, dass nichts zum Löschen angewählt ist.

Code: Alles auswählen

sub Kostenpunktentferner
rem ----------------------------------------------------------------------
rem define variables
dim document   as object
dim dispatcher as object
rem ----------------------------------------------------------------------
rem get access to the document
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

rem ----Jetzt werden aus allen angewaehlten Zeilen alles außer Formeln und Formate entfernt-----
dim args1(0) as new com.sun.star.beans.PropertyValue
args1(0).Name = "Flags"
args1(0).Value = "SVDN"

dispatcher.executeDispatch(document, ".uno:Delete", "", 0, args1())

rem ------Jetzt wird der Sortierbereich angewaehlt---------
dim args2(0) as new com.sun.star.beans.PropertyValue
args2(0).Name = "ToPoint"
args2(0).Value = "$A$3:$AZ$31"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args2())

rem -----------Jetzt wird sortiert-------------
dim args3(8) as new com.sun.star.beans.PropertyValue
args3(0).Name = "ByRows"
args3(0).Value = true
args3(1).Name = "HasHeader"
args3(1).Value = false
args3(2).Name = "CaseSensitive"
args3(2).Value = false
args3(3).Name = "NaturalSort"
args3(3).Value = false
args3(4).Name = "IncludeAttribs"
args3(4).Value = true
args3(5).Name = "UserDefIndex"
args3(5).Value = 0
args3(6).Name = "Col1"
args3(6).Value = 1
args3(7).Name = "Ascending1"
args3(7).Value = true
args3(8).Name = "IncludeComments"
args3(8).Value = false

dispatcher.executeDispatch(document, ".uno:DataSort", "", 0, args3())

rem --Jetzt werden Formeln eingefuegt--

    oSheet = Thiscomponent.currentController.ActiveSheet
    for i = 2 to 30
        oCell = oSheet.getCellbyposition(1,i)
        if oCell.string = "" then
            oCell.FormulaLocal = "=JETZT()&""a""&ZUFALLSBEREICH(1;99999)&""a""&VERSCHIEBUNG(INDIREKT(ADRESSE(ZEILE();SPALTE()));32;55)"
            s = oCell.String
            oCell.FormulaLocal = ""
            oCell.String = s
        endif
    next i

rem --Jetzt wird der Bereich angewaehlt, wo Formeln in Werte verwandelt werden sollen--
dim args4(0) as new com.sun.star.beans.PropertyValue
args4(0).Name = "ToPoint"
args4(0).Value = "$B$3:$B$31"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args4())

rem ----------Jetzt wird in Werte konvertiert---------
dispatcher.executeDispatch(document, ".uno:ConvertFormulaToValue", "", 0, Array())

rem ----------------Wir kehren zur ersten Zelle in der Tabelle zurueck----------------------
dim args5(0) as new com.sun.star.beans.PropertyValue
args5(0).Name = "ToPoint"
args5(0).Value = "$A$1"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args5())


end sub
F3K Total
********
Beiträge: 3704
Registriert: Mo, 28.02.2011 17:49

Re: Leere Zellen mit Formel befüllen

Beitrag von F3K Total »

Hallo Gerald,
du vermischst aufgezeichneten mit programmiertem Code, ohne zu wissen, was jeweils passiert, mein Code wandelt bereits die Formeln in Text um, dein Code versucht dies im Nachgang dann nochmal.
Aufgezeichnete Makros funktionieren in Calc nicht allzu gut, wie allgemein bekannt, besser ist es, den Code selbst zu programmieren.
Es gibt hier im Forum ausreichend zu lesen und lernen.
Z.B. mit Dannenhöfer, auch auf o.a. Seite verlinkt, kommt man als Einsteiger recht schnell zum Ziel.

Gruß Rik
Antworten