[gelöst] mehrere Formeln einer Calc-Zeile per Makro

Antwort erstellen

BBCode ist eingeschaltet
[img] ist ausgeschaltet
[url] ist eingeschaltet
Smileys sind ausgeschaltet
Die letzten Beiträge des Themas
   

Ansicht erweitern Die letzten Beiträge des Themas: [gelöst] mehrere Formeln einer Calc-Zeile per Makro

Re: [gelöst] mehrere Formeln einer Calc-Zeile per Makro

von wega » Mi, 31.12.2008 16:35

Hallo Charly,

meine vorherigen Fragen haben sich erledigt.
Ich habe Deinen Beitrag noch einmal entspannt gelesen und da stand schon alles drin.
Womit sich wieder bestätigt, "Wer lesen kann ist klar im Vorteil"

Wünsche Dir und allen Forenteilnehmern ein frohes neues Jahr.

Gruß
Werner

Re: [gelöst] mehrere Formeln einer Calc-Zeile per Makro

von wega » Mi, 31.12.2008 16:32

Hallo Charly,

mächtig tolle und starke Anleitung, die Du gebracht hast.
Ich wäre nie auf diese Idee gekommen so Formeln und Formate vom Makro aus auf ein Calc-Blatt zu übertragen.
Einfach toll.
Dennoch habe ich zwei Fragen.

1. muss ich die Zelle erst händisch formatieren, bevor ich über Dein Makro die Kennziffer der Zelle abfragen kann?
2. gibt es irgendwie eine Formel bzw. Tabelle mit der man die Kennziffer zu den gewünschten Zellenattributen abfragen/auslesen kann?

Ich wünsche Dir einen guten rutsch in das Neue Jahr und in diesem einen guten Start.
Gruß
Werner

Re: [gelöst] mehrere Formeln einer Calc-Zeile per Makro

von Charly » Mi, 31.12.2008 11:14

Hallo Wega!

Schön dass alles funktioniert.

In einer Fleißarbeit habe ich mich auch mal an die Formatierung per Makro herangewagt.

Das nachfolgende Makro formatiert die letzte Zeile einer Tabelle von Spalte A bis Spalte K und trägt eine Bedingte Formatierung ein. Für die Bedingte Formatierung ist eine Zellvorlage mit dem Namen "Grau" Voraussetzung

Für die übrigen Formatierungen muss die Nummer der Formatierung bekannt sein. Ich habe gesehen, du hast etwas anders formatiert als ich es in meiner Beispieldatei gemacht habe. Möglicherweise gibt es auch Unterschiede zwischen den Versionen und du musst die Nummern deinen Wünschen anpassen.

Folgendes Makro ermittelt das NummernFormat der aktuellen Zelle. Also eine Zelle entsprechend formatieren und über das Makro die Nummer herausbringen.

Code: Alles auswählen

Sub NummerFormat

Doc = ThisComponent
Zelle = Doc.CurrentSelection
Nr = Zelle.NumberFormat

MSGBox(Nr)

End Sub
Die ermittelte Nummer kann dann per Makro den gewünschten Zelle zugewiesen werden.

Hier nun das versprochene Makro:

Code: Alles auswählen

Sub Formatierung()

Dim Doc as Object
Dim Controller as Object
Dim Blatt as object
Dim Cursor as object
Dim Zeile as long
Dim Add as object
Dim Bereich as Object
Dim Format as Object
Dim Bedingungen(2) As new com.sun.star.beans.PropertyValue
Dim Formel as string


Doc = ThisComponent
Controller = Doc.CurrentController
Blatt = Controller.ActiveSheet

Cursor = Blatt.createCursor()
Cursor.gotoEndOfUsedArea(true)
Add = Cursor.getRangeAddress()
Zeile = Add.Endrow

'Bedingte Formatierung

Z1 = cstr(Zeile+1)
Z2 = Cstr(Zeile+2)


Bereich = Blatt.getCellRangeByPosition(0,Zeile,10,Zeile)



Bedingungen(0).name = "Operator"
Bedingungen(0).value = com.sun.star.sheet.ConditionOperator.FORMULA

Bedingungen(1).name = "Formula1"
Formel = "$B" & Z1 & "<>" & "$B" & Z2
Bedingungen(1).value = Formel

'Hier muss eine Zellvorlage mit dem Namen "Grau" vorhanden sein
Bedingungen(2).name = "StyleName"
Bedingungen(2).value = "Grau"

Format = Bereich.ConditionalFormat
Format.addNew(Bedingungen())
Bereich.ConditionalFormat = Format

'Zellen Formatieren

Blatt.getCellByPosition(0,Zeile).NumberFormat = 108
Blatt.getCellByPosition(1,Zeile).NumberFormat = 37
Blatt.getCellByPosition(2,Zeile).NumberFormat = 40
Blatt.getCellByPosition(3,Zeile).NumberFormat = 40
Blatt.getCellByPosition(4,Zeile).NumberFormat = 40
Blatt.getCellByPosition(5,Zeile).NumberFormat = 40
Blatt.getCellByPosition(8,Zeile).NumberFormat = 109
Blatt.getCellByPosition(9,Zeile).NumberFormat = 109
Blatt.getCellByPosition(10,Zeile).NumberFormat = 109


End Sub
Gruß
Charly

Re: mehrere Formeln einer Calc-Zeile per Makro übergeben.

von wega » Di, 30.12.2008 23:09

Hallo Charly,

nochmals besten Dank für Deine Mühen. du hast mir sehr geholfen und mein Wissen um die Programmierung mit und um Calc ein gutes Stück vorangebracht.
Die Zeilenummer 125 hatte ich bei meiner ersten ausführung ins Spiel gebracht um einen weiten Eingabebereich abzufragen, den der Anwender vielleicht nie ereichen würde.
Diesen Krampf habe ich aber nur angewandt, weil ich nicht wußte, wie man eine Formel vom Makro ins Calc-Arbeitsblatt bringt.
Dank Deiner Hilfe weiß ich das jetzt.
Sicher kann man die Formatierung der Spalten und auch die "bedingte Formation" vorher in Clac bestimmen, habe ich so auch gemacht.
Dennoch wäre es für mich interessant zu erfahren wie dies aus dem Makro geschieht.
Wenn Du da auch eine Lösung hast, ich bin ganz Ohr.

Aus Dank für Deine Hilfe veröffentliche ich hier den jetzigen Stand meiner Zeiterfassung.
mit Gruß
Werner
Dateianhänge
Zeiterfassung_Formel.ods
(18.63 KiB) 74-mal heruntergeladen

Re: mehrere Formeln einer Calc-Zeile per Makro übergeben.

von Charly » Di, 30.12.2008 17:40

Hallo Wega!

Ich habe deine Formel mal in ein Makro integriert. Da ich mangels Daten nicht überprüfen kann, ob ich die Formeln alle richtig geschrieben haben, müsstest du das überprüfen. Mit deiner Funktion in den Spalten D bis E bin ich nicht zurecht gekommen. Die Syntax von "Und" lautet doch: "Und(Bedingungs1;Bedingung2)". Das "Und" steht nicht zwischen den Bedingungen.

Die Formatierungen habe ich noch nicht in das Makro integriert. Kann mann diese nicht im Voraus über die ganzen Spalten machen?

Deine Formeln decken einen Bereich bis Zeile 125 ab. Stehen nach der Zeile, in der die Formeln eingetragen werden sollen noch Daten? Dann trägt mein Makro die Formel in die falsche Zeile. Der Befehl getEndOfUsedArea findet nur die allerletzte der benutzten Zeilen.

Code: Alles auswählen

Sub Formel_uebertragen

Dim Doc as Object
Dim Controller as object
Dim Blatt as object
Dim Cursor as object
Dim Zeile as long
Dim Add as Object

dim Quelle as object
dim Ziel as Object

Doc = ThisComponent
Controller = Doc.CurrentController
Blatt = Controller.ActiveSheet

Cursor = Blatt.createCursor()
Cursor.gotoEndOfUsedArea(true)
Add = Cursor.getRangeAddress()
Zeile = Add.Endrow+1

Zelle = Blatt.getCellByPosition(0,Zeile)

Z1= cstr(Zeile+1)
Z2 =cstr(Zeile)
Z3 = cstr(Zeile+2)

Formel = "=WENN(B"&Z1 & "=B" & Z2 & ";"""";B"& Z1 & ")"

Zelle.formulaLocal = Formel

Zelle = Blatt.getCellByPosition(1,Zeile)

Formel = "=WENN($G" & Z1 & "<>"""";$B" & Z2 & ";"""")"
Zelle.formulaLocal = Formel

Zelle = Blatt.getCellByPosition(2,Zeile)
Formel = "=WENN(Und($B" & Z1 & "=$B" & Z2 &  ";C" & Z2 & "<>"""");"""";C" & Z2 & ")"
Zelle.formulaLocal = Formel

Zelle = Blatt.getCellByPosition(3,Zeile)
Formel = "=WENN(Und($B" & Z1 & "=$B" & Z2 &  ";D" & Z2 & "<>"""");"""";D" & Z2 & ")"
Zelle.formulaLocal = Formel

Zelle = Blatt.getCellByPosition(4,Zeile)
Formel = "=WENN(Und($B" & Z1 & "=$B" & Z2 &  ";E" & Z2 & "<>"""");"""";E" & Z2 & ")"
Zelle.formulaLocal = Formel

Zelle = Blatt.getCellByPosition(5,Zeile)
Formel = "=WENN($C" & Z1 & "="""";"""";D" & Z1 & "-D" & Z1 &  "-E" & Z1 & ")"
Zelle.formulaLocal = Formel

Zelle = Blatt.getCellByPosition(8,Zeile)
Formel = "=Wenn(B" & Z1 & "= B" & Z3 & ";"""";Summenprodukt(Tag($B$4:$B$125)=TAG(B" + Z1 & ";($F$4:$F$125)*24))"
Zelle.formulaLocal = Formel

Zelle = Blatt.getCellByPosition(9,Zeile)
Formel = "=Wenn(B" & Z1 & "= B" & Z3 & ";"""";Summenprodukt(Tag($B$4:$B$125)=TAG(B" + Z1 & ";($H$4:$H$125)*24))"
Zelle.formulaLocal = Formel

Zelle = Blatt.getCellByPosition(10,Zeile)
Formel = "=Wenn(B" & Z1 & "=B" & Z3 &";"""";J" & Z1 & "-I" & Z1 & ")"
Zelle.formulaLocal = Formel


End Sub
Gruß
Charly

Re: mehrere Formeln einer Calc-Zeile per Makro übergeben.

von wega » Di, 30.12.2008 14:46

Hallo Charly,

erst mal Danke für Deine Hilfsbereitschaft.

Also,
in A5 steht: =WENN(B5=B4;"";B5)
in B5 steht: =WENN($G5<>"";$B4;"")
in C5 steht: =WENN($B5=$B4 UND(C4<>"");"";C4)
in D5 steht: =WENN($B5=$B4 UND(D4<>"");"";D4)
in E5 steht: =WENN($B5=$B4 UND(E4<>"");"";E4)
in F5 steht: =WENN($C5="";"";D5-C5-E5)
in I5 steht: =WENN(B5=B6;"";SUMMENPRODUKT(TAG($B$4:$B$125)=TAG(B5);($F$4:$F$125)*24))
in J5 steht: =WENN(B5=B6;"";SUMMENPRODUKT(TAG($B$4:$B$125)=TAG(B5);($H$4:$H$125)*24))
in K5 steht: =WENN(B5=B6;"";J5-I5)


zudem gilt für die Spalten folgende Formate
A: Datum NNN
B: Datum TT.MM.JJ
C: Zeit HH:MM
D: Zeit HH:MM
E: Zeit HH:MM
F: Zeit HH:MM
I: Zahl #.##0,00;[ROT]-#.##0,00
J: Zahl #.##0,00;[ROT]-#.##0,00
K: Zahl #.##0,00;[ROT]-#.##0,00

Und für die Zeilen die bedingte Formatierung

Formel ist: $B5<>$B4 leichtgrau

Das versuche ich vergebens vom Makro aus zu realisieren.

mit Gruß
Werner

Re: mehrere Formeln einer Calc-Zeile per Makro übergeben.

von Charly » Di, 30.12.2008 14:42

Hallo Wega!

Nachstehend habe ich mal einen Beispielcode verfasst ohne Berücksichtigung von relativen Bezügen.

Code: Alles auswählen

Sub Formel_uebertragen()

Dim Doc as Object
Dim Controller as object
Dim Blatt as object
Dim Cursor as object
Dim Zeile as long
Dim Add as Object

Doc = ThisComponent
Controller = Doc.CurrentController
Blatt = Controller.ActiveSheet

Cursor = Blatt.createCursor()
Cursor.gotoEndOfUsedArea(true)
Add = Cursor.getRangeAddress()
Zeile = Add.Endrow+1

Zelle = Blatt.getCellByPosition(0,Zeile)
Formel = "=WENN(B5=B4;"""";B5)"

Zelle.formulaLocal = Formel

Zelle = Blatt.getCellByPosition(1,Zeile)
Formel = "=WENN($G7<>"""";$B6;"""")"
Zelle.formulaLocal = Formel

Zelle = Blatt.getCellByPosition(2,Zeile)
Formel = "=WENN(Und($B5= $B5=$B4;C4<>"""");"""";C4)"
Zelle.formulaLocal = Formel

End Sub
Hinweis:Da das Anführungszeichen auch ein Steuerzeichen ist, müssen hier immer vier Zeichen stehen, um im Ergebnis zwei zu erhalten.

Gruß
Charly

Re: mehrere Formeln einer Calc-Zeile per Makro übergeben.

von Charly » Di, 30.12.2008 13:57

Hallo Wega!

Wie ich in meinem vorigen Beitrag erwähnt habe, können die Formel auch direkt vom Makro in die Zelle eingetragen werden.

In deinen Beispielformeln verwendest du sowohl einen absoluten Bezug mit "$"-Zeichen als auch einen relativen Bezug ohne diese Zeichen. Bei der Kopiermethode passt sich automatisch der Zellenbezug an die neuen Gegebenheiten an. Bei direkter Formeleingabe muss das Makro den Bezug anhand der aktuellen Zeile ermitteln.

Ich könnte deine Beispiele in ein Makro umsetzen, wenn du mir noch mitteilst, auf welche Zeile sich dein aktuelles Beispiel bezieht. Daraus kann ich dann den relativen Bezug ermitteln.


mfg

Charly

Re: mehrere Formeln einer Calc-Zeile per Makro übergeben.

von wega » Di, 30.12.2008 13:30

Hallo Charly,

Danke für die Verfeinerung des Makros.
Das sieht nicht nur professionell aus, es ist es jetzt auch durch Deine Zugabe.

In etwa ist es das was ich suche.
Aber auch bei diesem Makro müssen in den jeweiligen Zellen schon formeln stehen, damit sie in die nächst folgende kopiert werden können.

Da es aber hin und wieder passiert, das ein Benutzer hingeht, den Tabellenschutz entfernt und Zeilen löscht, können dann auch die Informationen der Formeln verloren gehen.
Das ist der Grund, weshalb ich die Formeln aus dem Makro heraus in die Calczelle schreiben möchte.

so sollte dann zum Beispiel in
$A + zeile -> =WENN(B5=B4;"";B5)
$B + zeile -> =WENN($G7<>"";$B6;"")
$C + zeile -> =WENN($B5=$B4 UND(C4<>"");"";C4)

usw. stehen und die etsprechende Formatierung übergeben werden.

mit Gruß
Werner

Re: mehrere Formeln einer Calc-Zeile per Makro übergeben.

von Charly » Di, 30.12.2008 09:44

Hallo Wega!

Ich habe dein aufgezeichnetes Makro etwas erweitert. Damit wird die jeweils letzte gefüllte Zeile von Spalte A bis Spalte K kopiert und in die nächste Zeile werden die Formeln eingefügt. Ein manuelles Eintragen der Zeilennummer entfällt.

Code: Alles auswählen

Sub Formel_uebertragen

Dim Doc as Object
Dim Controller as object
Dim Blatt as object
Dim Cursor as object
Dim Zeile as long
Dim Add as Object
dim document   as object
dim dispatcher as object
dim Quelle as object
dim Ziel as Object

Doc = ThisComponent
Controller = Doc.CurrentController
Blatt = Controller.ActiveSheet
document   = Controller.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

Cursor = Blatt.createCursor()
Cursor.gotoEndOfUsedArea(true)
Add = Cursor.getRangeAddress()
Zeile = Add.Endrow
Quelle = Blatt.getCellRangeByPosition(0,Zeile,10,Zeile)
Controller.select(Quelle)

dispatcher.executeDispatch(document, ".uno:Copy", "", 0, Array())

Ziel = Blatt.getCellByPosition(0,Zeile+1)
Controller.select(Ziel)

dim args1(5) as new com.sun.star.beans.PropertyValue
args1(0).Name = "Flags"
args1(0).Value = "F"
args1(1).Name = "FormulaCommand"
args1(1).Value = 0
args1(2).Name = "SkipEmptyCells"
args1(2).Value = false
args1(3).Name = "Transpose"
args1(3).Value = false
args1(4).Name = "AsLink"
args1(4).Value = false
args1(5).Name = "MoveMode"
args1(5).Value = 4

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

End Sub
Ich weiß nicht, ob das deinen Anforderungen genügt. Du kannst natürlich die Formeln auch direkt im Makrohinterlegen und von dort in eine Zelle Schreiben. Aber dazu müsste ich mehr wissen.
Gruß
Charly

Re: mehrere Formeln einer Calc-Zeile per Makro übergeben.

von wega » Di, 30.12.2008 00:04

Hallo zusammen,

ich noch einmal.

die einfachste Art eine Zeile, gespickt mit Formeln, zu übergeben ist die, dass man ein Makro aufzeichnet.
So, wie in dieser From.

Code: Alles auswählen

sub Formel_uebertragen (Formel_Row)
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 ----------------------------------------------------------------------
dim args1(0) as new com.sun.star.beans.PropertyValue
args1(0).Name = "ToPoint"
args1(0).Value = "$A" & Formel_Row

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

rem ----------------------------------------------------------------------
dim args2(0) as new com.sun.star.beans.PropertyValue
args2(0).Name = "ToPoint"
args2(0).Value = args1(0).value & ":$K" & Formel_Row
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args2())

rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:Copy", "", 0, Array())

rem ----------------------------------------------------------------------
dim args4(0) as new com.sun.star.beans.PropertyValue
args4(0).Name = "ToPoint"
args4(0).Value = "$A" & Formel_Row + 1

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

rem ----------------------------------------------------------------------
dim args5(5) as new com.sun.star.beans.PropertyValue
args5(0).Name = "Flags"
args5(0).Value = "F"
args5(1).Name = "FormulaCommand"
args5(1).Value = 0
args5(2).Name = "SkipEmptyCells"
args5(2).Value = false
args5(3).Name = "Transpose"
args5(3).Value = false
args5(4).Name = "AsLink"
args5(4).Value = false
args5(5).Name = "MoveMode"
args5(5).Value = 4

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


end sub
Die aktuelle Zeile >> " Formel_Row" übergebe ich wie folgt, indem ich in "A3" vorher die aktuelle Zeile hineinschreibe.
Z,B., nachdem ich die Formel kopiert habe. -> Kopierte Zeile +1.

[

Code: Alles auswählen

Formel_Row = mySheet.getCellRangeByName("A3").value
Aber das ist eigentlich nicht das, was ich suche.
Ich suche eine Möglichkeit frei definierte Funktionen vom Makro aus in die Calc-Zeile zu schreiben und nicht Calc-Zeilen von einer Zeile in die andere zu kopieren.

Hat sich da schon jemand versucht?

Gruß
Werner

[gelöst] mehrere Formeln einer Calc-Zeile per Makro

von wega » Sa, 27.12.2008 21:33

Hallo zusammen,

bei einem zeitfeld kann man die mit:

TimeSerial(H,M,S) und ...ForumalLocal

Zeitformate an Calc übergeben.

Wie sieht das aber bei einer Calc-Zeile aus, die in den Spalten von z.B. A bis H kompelexe Formeln je Zelle beinhaltet?
Wie kann man solche formeln vom Makro aus an Calc übergeben?

Hintergrund der Frage ist folgende:

Man hat ein Datenblatt, das mit den unterschiedlichsten Tageseintragungen nach unten wächst.
Jetzt könnte man beliebiege Zeilen mit der Formelansammlung belegen, was aber Speicherplatz bedeutet.
Es gibt ja Monate, da kommt man vielleicht mit 100 Einträgen aus, aber es gibt auch Monate mit viel mehr einträgen.

Da man den letzten Zeileneintrag verwalten kann, indem man die letzte aktuelle Zeilenmummer in einer speziellen Zelle ablegt, weiß man also in welche Zeile die nächste Buchung folgen soll.
Wenn man dann vorher die Zeile mit den pro Spalte zugehörigen Formeln füllen könnte, wäre die Buchungsdatei auch wirklich nur so groß wie die aktuelle buchung es benötigt.

Hat das schon mal jemand gemacht?

Gruß
Werner

Nach oben