Cells in Benutzerdefinierter Formel

Hallo Zusammen,
ich habe eine Arbeitsmappe mit mehreren Blättern und folgender benutzerdefinierter Formel:
Function sum_gew(arange As Range, sumcolumn As Range, art As Long) As Double
'1=normal 2=ohne Auftrag
Dim arow As Long, acolumn As Long, i As Long, sumcol As Long
arow = arange.Row
acolumn = arange.Column
sumcol = sumcolumn.Column
i = arow + 1
sum_gew = 0
Do Until Cells(i, acolumn).Value "" Or Cells(i, 1).Value = "Gesamtergebnis"
If art = 1 Then
sum_gew = sum_gew + Cells(i, sumcol).Value
End If
i = i + 1
Loop
'Objekt addieren
sum_gew = sum_gew + Cells(arow, sumcol).Value
'Immer neu berechnen
Application.Volatile
Diese funktioniert auch super solange ich in dem Blatt bewege, wo die Formel verwendet wird.
Wechsle ich in anderes Blatt so läuft und aktualisieren per F9 (bei manueller Berechnung), so läuft diese Formel ewig.
Ursache ich wahrscheinlich die Verwendung von Cells ohne Angabe des Blattes.
Was muss ich tun damit die Formel korrekt funktioniert bzw. gegen was kann ich das cells ersetzen?
Danke Euch schomal
VG SteffenS

Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

Function sum_gew(arange As Range, sumcolumn As Range, art As Long) As Double 'Immer neu berechnen Application.Volatile '1=normal 2=ohne Auftrag Dim ws As Worksheet Dim arow As Long, acolumn As Long, i As Long, sumcol As Long arow = arange.Row acolumn = arange.Column sumcol = sumcolumn.Column Set ws = arange.Parent With ws i = arow + 1 sum_gew = 0 Do Until .Cells(i, acolumn).Value "" Or .Cells(i, 1).Value = "Gesamtergebnis" If art = 1 Then sum_gew = sum_gew + .Cells(i, sumcol).Value End If i = i + 1 Loop 'Objekt addieren sum_gew = sum_gew + .Cells(arow, sumcol).Value End With Set ws = Nothing End Function

Hi Steffen
im Prinzip ist es so:
- ohne Application.Volatile berechnet Excel eine Formel immer dann neu, wenn sich eine der Zellen ändert, die in den Übergabeparametern vorhanden sind.
- mit Application.Volatile berechnet Excel die Formel bei jeder Änderung eines Zellwertes in Excel neu (egal in welcher Zelle diese Änderung passiert
dh. Application.Volatile ist dann erforderlich, wenn deine Funktion Zellen verwendet, die nicht Bestandteil der Übergabeparameter sind und die Formel trotzdem immer das aktuelle Ergebnis anzeigen soll.
Du verwendest mit Cells(i, 1) auf jeden Fall eine Zelle, die du nicht in den Übergabeparametern angegeben hast und auch bei arange und sumcolumn wirst du wahrscheinlich nicht die ganze Spalte, sondern immer nur die Zelle aus der Zeile mit der Überschrift angeben wollen, daher ist das Application.Volatile bei dir erforderlich, wenn die Formel immer aktuell sein soll.
die Lange Laufzeit wenn du auf einem anderen Blatt bist, kommt daher:
1. du referenzierst immer auf das aktuelle Blatt.
2. im aktuellen Blatt ist nicht sicher gestellt, dass die Abbruchbedingung für die Do-Schleife erfüllt wird und dann bekommst du eine Endlosschleife, welche nur dadurch unterbrochen wird, dass du irgendwann auf eine Zelle zugreift, die nicht existiert und dann ein Fehlerabbruch kommt.
Das passiert aber erst nach 1,04 Mio umläufen (denn so viele Zellen hat eine Spalte mittlerweile)
ohne jetzt zu stark in deien Programmierung einzugreifen wären die Abhilfen:
1. referenziere immer das richtige Tabellenblatt (wie das geht, wurde dir ja schon gezeigt)
2. vermeide die Endlosschleife in dem du sie abbrichst, wenn das Ende des benutzen Bereichs der Tabelle erreicht ist:

Function sum_gew(arange As Range, sumcolumn As Range, art As Long) As Double
'1=normal 2=ohne Auftrag
Dim arow As Long, acolumn As Long, i As Long, sumcol As Long
Dim wsA As Worksheet
Dim wsS As Worksheet
Dim Ende As Long
Set wsA = arange.Worksheet
Set wsS = sumcolumn.Worksheet
Ende = wsA.UsedRange.Rows.Count + wsA.UsedRange.Row - 1
arow = arange.Row
acolumn = arange.Column
sumcol = sumcolumn.Column
i = arow + 1
sum_gew = 0
Do Until _
wsA.Cells(i, acolumn).Value  "" _
Or wsA.Cells(i, 1).Value = "Gesamtergebnis" _
Or i >= Ende
If art = 1 Then
sum_gew = sum_gew + wsS.Cells(i, sumcol).Value
End If
i = i + 1
Loop
'Objekt addieren
sum_gew = sum_gew + wsS.Cells(arow, sumcol).Value
'Immer neu berechnen
Application.Volatile
End Function

Gruß Daniel

Beliebteste Forumthreads (12 Monate)

Cells in Benutzerdefinierter Formel

6
Beiträge zum Forumthread

Beiträge zu diesem Forumthread

145 Forumthreads zu ähnlichen Themen

Beliebteste Forumthreads (12 Monate)

Beliebteste Forumthreads (12 Monate)

Cells in Benutzerdefinierter Formel

6 Beiträge zum Forumthread Beiträge zu diesem Forumthread

145 Forumthreads zu ähnlichen Themen

VBA Beispiele zu verwandten Themen

Beliebteste Forumthreads (12 Monate)

Beliebteste Forumthreads (12 Monate)

6
Beiträge zum Forumthread

Beiträge zu diesem Forumthread