Berechnungen in Zellen aktualisieren (eigene functions)

[Thomas Hoffmann]

moin,

habe in einer Tabellenzeile immer gleich werte wie:
Zeit,KM usw., die eingeben werden. weiter rechts stehen dann in den Zellen Formeln wie
=durchschnitt(zeile()).
'durchschnitt' ist eine selbstgeschriebene function in einem MOdul. (Berechnung steht auf
automatisch)
Wenn ich die Formeln in den 'rechten' Zellen alle durch 'Markierung erweitern' und STRG+U
in eine freie Zeile drunter ziehe und dann in den 'linken' Zellen manuell meine Werte
eingebe, so werden die Zellen mit den Formeln nicht automatisch aktualisiert.
Woran liegt das denn?
Trage ich erst 'links' die WErte ein und zieh dann die Formeln mit strg+U runter, werden
die Zellen berechnet.
Hab auch schon versucht mit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rr As Range
Set rr = Range(Cells(Target.Row, 7), Cells(Target.Row, 13))
rr.Calculate
End Sub

Die Formelspalten 7 bis 13 zur Neuberechnung zu zwingen, wenn 'links' was
eingegeben/geändert wurde, aber da tut sich auch nix.

Woran liegt das denn?
Wie kann ich per code diese Neuberechnung erreichen?

Gruß und Dank!
TH

[Thomas Ramel]

Grüezi Thomas

Thomas Hoffmann schrieb am 04.05.2009

Post by Thomas Hoffmann
Zeit,KM usw., die eingeben werden. weiter rechts stehen dann in den Zellen Formeln wie
=durchschnitt(zeile()).
'durchschnitt' ist eine selbstgeschriebene function in einem MOdul. (Berechnung steht auf
automatisch)

...und wie lautet der Code deiner Funktion...?

Post by Thomas Hoffmann
Wenn ich die Formeln in den 'rechten' Zellen alle durch 'Markierung erweitern' und STRG+U
in eine freie Zeile drunter ziehe und dann in den 'linken' Zellen manuell meine Werte
eingebe, so werden die Zellen mit den Formeln nicht automatisch aktualisiert.
Woran liegt das denn?

Daran wie Du die Funktion geschrieben hast und diese ihre
Berechnungs-Parameter übergeben bekommt.

Post by Thomas Hoffmann
Trage ich erst 'links' die WErte ein und zieh dann die Formeln mit strg+U runter, werden
die Zellen berechnet.
Hab auch schon versucht mit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rr As Range
Set rr = Range(Cells(Target.Row, 7), Cells(Target.Row, 13))
rr.Calculate
End Sub

Das ist IMO nur Symptombekämpfung und löst dein Problem nicht wirklich.

Post by Thomas Hoffmann
Woran liegt das denn?
Wie kann ich per code diese Neuberechnung erreichen?

...indem Du deine Funktion korrekt schreibst... ;-)

Den wichtigsten Teil zur erfolgreichen Lösung deiner Frage hast Du leider
'unterschlagen', den Code deiner Funktion nämlich.

Grundsätzlich daher soviel:
Du musst den Bereich auf den sich die Funktion bezieht sauber als Parameter
übergeben und darfst sich innerhalb der Funktion auf *keine* Zellen
ausserhalb dieses übergebenen Bereiches beziehen.
Dann klappt das mit der Aktualisierung und Neuberchnung korrekt.


Mit freundlichen Grüssen
Thomas Ramel

[Thomas Hoffmann]

Post by Thomas Ramel
...und wie lautet der Code deiner Funktion...?

OK: ich berechne insg 7 Werte, und mir ist es lästig, jedesmal in jeder Funktion bis zu 6
Parameter(Zellwerte) zu übergeben. Die Funktionen mit get... nehmen mir das ab. Ich
übergebe lediglich die Zeile und hole die Werte aus der Tabelle wie ich sie brauche dann
mit einem Range (oder auch direkt). (Sowas liegt mir mehr, da ich aus der VB-Ecke komme).

Auszug:

Private Function getKm(zeile)
Dim rr As Range
Set rr = ActiveSheet.Rows(zeile)
getKm = CDbl(rr.Cells(1, 3))
End Function
Private Function getBirth() As Date
getBirth = CDate(ActiveSheet.Cells(2, 2))
End Function
Private Function getGender() as Boolean
getGender = UCase(ActiveSheet.Cells(3, 2)) = "M"
End Function
Private Function getDatum(zeile) As Date
Dim rr As Range
Set rr = ActiveSheet.Rows(zeile)
getDatum = CDate(rr.Cells(1, 2))
End Function
Private Function getZeit(zeile)
Dim zz
Dim rr As Range
Set rr = ActiveSheet.Rows(zeile)
getZeit = Val(rr.Cells(1, 4)) / 24 + Val(rr.Cells(1, 5)) / 24 / 60 + Val(rr.Cells(1,
6)) / 24 / 3600
End Function


'1. Wert:
Public Function durchschnitt(zeile)
Dim erg, zeit
zeit = getZeit(zeile) * 24
If zeit = 0 Then durchschnitt = "": Exit Function
erg = getKm(zeile) / zeit
durchschnitt = Format(erg, "##0.00")
End Function

... usw.

Gruß TH

[Thomas Ramel]

Grüezi Thomas

Thomas Hoffmann schrieb am 04.05.2009

Post by Thomas Hoffmann

Post by Thomas Ramel
...und wie lautet der Code deiner Funktion...?

OK: ich berechne insg 7 Werte, und mir ist es lästig, jedesmal in jeder Funktion bis zu 6
Parameter(Zellwerte) zu übergeben. Die Funktionen mit get... nehmen mir das ab. Ich
übergebe lediglich die Zeile und hole die Werte aus der Tabelle wie ich sie brauche dann
mit einem Range (oder auch direkt). (Sowas liegt mir mehr, da ich aus der VB-Ecke komme).

...und damit schiesst Du einen kapitalen Hirsch... ;-)

Post by Thomas Hoffmann
Private Function getKm(zeile)
Dim rr As Range
Set rr = ActiveSheet.Rows(zeile)
getKm = CDbl(rr.Cells(1, 3))
End Function

...denn was geschieht wenn der Wert in jener Zeile in Spalte 3 sich ändert?

Deine Funktion wird nicht aktualisiert!!

...aber das hast Du ja schon selbst festgestellt - das liegt genau an
diesem 'internen' Bezug auf einen Bereich der nicht explizit als Parameter
übergeben wird.

Ob dir das nun liegt oder eher weniger, tut nichts zur Sache - in
Excel-Funktionen, die aus Zellen heraus aufgerufen werden sollen ist das
ein No-No.

Mit einfachen direkten Zellbezügen kommst Du hier viel besser zum Ziel und
brauchst auch das Rad nicht neu zu erfinden ;-)

Post by Thomas Hoffmann
Public Function durchschnitt(zeile)
Dim erg, zeit
zeit = getZeit(zeile) * 24
If zeit = 0 Then durchschnitt = "": Exit Function
erg = getKm(zeile) / zeit
durchschnitt = Format(erg, "##0.00")
End Function

Hier noch eine Anmerkung - diese Funktion liefert einen String als Ergebnis
- damit kannst Du im Tabellenblatt keine weiteren Berechnungen mehr
anstellen.
Auch das würde ich über konventionelle Excel-Formeln lösen und das
Zellformat korrekt einstellen.


Mit freundlichen Grüssen
Thomas Ramel

[Alexander Wolff]

Partiell kannst Du auch

=EineWichtigeFunktion()+JETZT()*0

verwenden. JETZT() ist flüchtig (volatil) und rechnet auch neu, wenn
IRGENDWO, nicht beschränkt nur auf ZELLVORGÄNGER, geändert wurde.

[Thomas Hoffmann]

Post by Alexander Wolff
=EineWichtigeFunktion()+JETZT()*0

Danke, das funktioniert tatsächlich, Wolff! Eigenartiger workaround...

kannst Du mir sagen, warum das mit dem range.calculate nicht funktioniert?
Hatte nochmal zur Sicherheit die If-Zeile eingebaut, stelle aber über das debug.print
fest, das die Zellen, die durch den code geändert werden, gar nicht das Change-Ereignis
auslösen.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rr As Range
'Debug.Print "WS_Change " & Target.Address
If Target.Column > 6 Then Exit Sub 'die Spalten, die berechnet werden.
Set rr = Range(Cells(Target.Row, 7), Cells(Target.Row, 13))
rr.Calculate
End Sub

Gruß und Dank!
TH

[Thomas Hoffmann]

Post by Thomas Hoffmann

Post by Alexander Wolff
=EineWichtigeFunktion()+JETZT()*0

Danke, das funktioniert tatsächlich, Wolff! Eigenartiger workaround...

(allerdings 'zerschießt' das die Formatierung des Wertes den die Funktion mit zB
Format(erg, "nn:ss") zurückgibt. Lässt sich natürlich durch Einstellen des Zellformates
und Abändern der Werteberechnung wieder kompensieren.)

Gruß T.

[Andreas Killer]

Post by Thomas Hoffmann

Post by Alexander Wolff
=EineWichtigeFunktion()+JETZT()*0

Danke, das funktioniert tatsächlich, Wolff! Eigenartiger workaround...

Allerdings. Wenn Du eine benutzerdefinierte function hast, die sich
auf andere Bereiche als die via Parameter übergebenen bezieht, kannst
Du sie auch direkt kennzeichnen.

In diesem Beispiel wird die benutzerdefinierte Funktion "My_Func" als
veränderlich gekennzeichnet. Die Funktion wird jedes Mal neu
berechnet, wenn in einer beliebigen Zelle des Arbeitsblattes eine
Berechnung durchgeführt wird.

Function My_Func()
Application.Volatile
'Remainder of the function
End Function

Allerdings möchte ich Dir dringends raten Dein Konzept zu überdenken,
Deine "function getBirth" z.B. ist durch die Formel =$B$2 zu ersetzen,
ohne das Du dabei Kompromisse machen müsstest.

Auch Deine anderen function's sehen nicht gerade "sinnvoll" aus und
lassen sich alle durch einfache Formeln ersetzen.

Post by Thomas Hoffmann
das debug.print fest, das die Zellen, die durch den code geändert
werden, gar nicht das Change-Ereignis auslösen.

Ist korrekt, das Change-Ereignis ist nur für Zellen da die wirklich
geändert wurden, was für Zellen mit Formeln nicht der Fall ist, das
sich hier die Formel nicht verändert hat. Das die Formel etwas anderes
berechnet hat ist unerheblich.

Andreas.

[Thomas Hoffmann]

Hallo, Andreas

Post by Andreas Killer
In diesem Beispiel wird die benutzerdefinierte Funktion "My_Func" als
veränderlich gekennzeichnet. Die Funktion wird jedes Mal neu berechnet,
wenn in einer beliebigen Zelle des Arbeitsblattes eine Berechnung
durchgeführt wird.
Function My_Func()
Application.Volatile
'Remainder of the function
End Function

Hab ich das nun nicht kapiert? wenn ich das mit dem .volatile einfüge, wird die Zelle mit
der Funktion (als Beispiel) =durchschnitt(zeile()) trotzdem nicht aktualisiert, wenn ich
bei irgendeiner Eingabezelle was ändere.

Public Function durchschnitt(zeile)
Dim erg, zeit
Application.Volatile
zeit = getZeit(zeile) * 24
If zeit = 0 Then durchschnitt = "": Exit Function
erg = getKm(zeile) / zeit
durchschnitt = Format(erg, "##0.00")
End Function

Post by Andreas Killer
Allerdings möchte ich Dir dringends raten Dein Konzept zu überdenken,

OK, das hab ich verstanden ;-) Vielen Dank.

Sowas wie
=KorrFaktor($B$2;B15;C15;D15;E15;F15)
und dann in der Funktion die Parameter alle richtig zuzuordnen ist mir halt ein Graus...
(welcher Parameter waren nun nochmal die Sekunden, der 4. oder der 5.?)

Auch wenn Ihr denkt, dass ich wohl einen nassen Hut aufhab ;-)

Habs nun so gelöst:

=MyFunc($B16:$F16;SPALTE())

und die Funktion sieht dann halt so aus:

Public Function MyFunc(rr As Range, spalte)
Dim zeile
zeile = rr.Row
Select Case spalte
Case 7
MyFunc = durchschnitt(zeile)
Case 8
MyFunc = MinProKm(zeile)
Case 9
MyFunc = Alter(zeile)
Case 10
MyFunc = Korrfaktor(zeile)
Case 11
MyFunc = KorrZeit(zeile)
Case 12
MyFunc = KorrKM(zeile)
Case 13
MyFunc = KorrMin(zeile)
Case Else
MyFunc = ""
End Select
End Function

Hätte auch bei jedem einzelnen Funktionsaufruf das Range einfügen können,
=Durchschnitt($B16:$F16;Zeile())
und das Range in der function dann einfach ignorieren.

Dank an Euch Alle!
TH

[Thomas Ramel]

Grüezi Thomas

Thomas Hoffmann schrieb am 04.05.2009

Post by Thomas Hoffmann

Post by Andreas Killer
Allerdings möchte ich Dir dringends raten Dein Konzept zu überdenken,

OK, das hab ich verstanden ;-) Vielen Dank.
Sowas wie
=KorrFaktor($B$2;B15;C15;D15;E15;F15)
und dann in der Funktion die Parameter alle richtig zuzuordnen ist mir halt ein Graus...

Aber programmiertechnisch und für den Dependency-Tree von Excel das einzig
Richtige. ;-)

Post by Thomas Hoffmann
(welcher Parameter waren nun nochmal die Sekunden, der 4. oder der 5.?)

Wenn Du die Funktionen über den Assistenten eingibst und bearbeitest und
deinen Parametern sprechende Namen verpasst ist das wirklich halb so wild.

Post by Thomas Hoffmann
Auch wenn Ihr denkt, dass ich wohl einen nassen Hut aufhab ;-)

Genau so, oder anders... ;-)

Jedem Tierchen sein Pläsierchen, da ist wirklich jeder frei.

Post by Thomas Hoffmann
=MyFunc($B16:$F16;SPALTE())

OK, und welchen Wert holst Du dir denn da auch gleich...? ;-)

Das ist IMO auch kaum übersichtlicher als die konsequente
Parameter-Übergabe, aber wie gesagt, da ist jeder frei. Je nach 'Herkunft'
und Hintergrund mag einem das eine oder das andere logisch(er) erscheinen
oder zum gewohnten Umfeld gehören.

Post by Thomas Hoffmann
Dank an Euch Alle!

Aber immer gerne doch :-)


Mit freundlichen Grüssen
Thomas Ramel

[Alexander Wolff]

Post by Thomas Hoffmann

Post by Alexander Wolff
=EineWichtigeFunktion()+JETZT()*0

Danke, das funktioniert tatsächlich, Wolff! Eigenartiger workaround...

B1: =EineWichtigeFunktion()+A1*0

hätte bei tausenden Einsätzen darüberhinaus den Vorteil, dass nur B1, nicht
jedesmal B1:B5000, neu rechnet, wenn A1 geändert wird. Vermute ich.