Meldung nach Einfügen oder Löschen von Zeilen

[Walter Griesser]

Hallo Newsgroup-Helden,

Excels 2007 - VBA - Ich suche nach einem "workaround" um festzustellen, ob
in einer Tabelle (eine) Zeilen gelöscht bzw. eingefügt wurde(n)
um Formeln zu aktualisieren bei denen sich durch. Löschen von Zeilen
#Bezug-Fehler einstellen.

Ich habe gesucht und hier Code gefunden:
http://www.herber.de/mailing/137701h.htm

Beim Start der Datei erscheint die Fehlermeldung: Laufzeitfehler '1004': Die
Methode Range für das Objekt '_Global' ist fehlgeschlagen.

Bitte um Hilfe.

Gruß
Walter Griesser

[Alexander Wolff]

a) Hast Du die beiden verschiedenen Klassenmodule berücksichtigt?

b) Der Code liefert Dir nur die Info, dass seit dem Öffnen Zeilen PER SALDO
gelöscht oder eingefügt wurden. #BEZUG! musst Du dann schon selbst lösen.

[Walter Griesser]

Hallo Alexander,

Post by Alexander Wolff
a) Hast Du die beiden verschiedenen Klassenmodule berücksichtigt?

Nein. Was mache ich da falsch:
In der Entwicklungsumgebung klicke ich doppelt auf "Diese Arbeitsmappe" -
rechte Maustaste - Einfügen - Klassenmodul - Klasse 1: Code einfügen:

Private Sub Workbook_Open()
gintRow = Range("Last").Row
End Sub


Doppelklick auf "Tabelle (Tabelle1)" - rechte Maustaste - Einfügen -
Klassenmodul - Klasse 2 : Code einfügen:

Public gintRow As Integer

Private Sub Worksheet_Calculate()
If Range("Last").Row < gintRow Then
MsgBox "Es wurde eine Zeile gelöscht!"
ElseIf Range("Last").Row > gintRow Then
MsgBox "Es wurde eine Zeile eingefügt!"
End If
gintRow = Range("Last").Row
End Sub

Nach dem Neustart gibt es beim Löschen aber keine Meldung.

Post by Alexander Wolff
b) Der Code liefert Dir nur die Info, dass seit dem Öffnen Zeilen PER
SALDO gelöscht oder eingefügt wurden. #BEZUG! musst Du dann schon selbst
lösen.

Das ist mit schon klar gewesen.

Gruß
Walter Griesser

[Alexander Wolff]

Post by Walter Griesser

Post by Alexander Wolff
a) Hast Du die beiden verschiedenen Klassenmodule berücksichtigt?

In der Entwicklungsumgebung klicke ich doppelt auf "Diese Arbeitsmappe" -

- Klasse 1: Code einfügen:
ist nicht einzufügen!

Post by Walter Griesser
Doppelklick auf "Tabelle (Tabelle1)" - rechte Maustaste - Einfügen -

- Klasse 2 : Code einfügen:
ist nicht einzufügen!

Post by Walter Griesser

Post by Alexander Wolff
b) Der Code liefert Dir nur die Info, dass seit dem Öffnen Zeilen PER
SALDO gelöscht oder eingefügt wurden. #BEZUG! musst Du dann schon selbst
lösen.

Das ist mit schon klar gewesen.

Wirklich? Wenn Du 6 Zeilen einfügst und 6 löschst, zeigt der Code Dir eben
dies nicht an.

[Ullrich Schwarz]

Hallo Walter,

die Prozedur erwartet einen benannten Bereich namens "Last", welcher in der
letzte Zeile liegen muss. Du kannst das manuell erledigen (mindestens eine
Zelle der letzten Zeile markieren, EINFÜGEN | NAMEN | DEFINIEREN Name in der
Arbeitsmappe: Last) oder natürlich auch per VBA. Du bekommst aber ein
Problem, wenn der User die letzte Zeile löscht.

MfG Ullrich
- Excel 2003 -

Post by Walter Griesser
Hallo Newsgroup-Helden,
Excels 2007 - VBA - Ich suche nach einem "workaround" um festzustellen, ob
in einer Tabelle (eine) Zeilen gelöscht bzw. eingefügt wurde(n)
um Formeln zu aktualisieren bei denen sich durch. Löschen von Zeilen
#Bezug-Fehler einstellen.
http://www.herber.de/mailing/137701h.htm
Die Methode Range für das Objekt '_Global' ist fehlgeschlagen.
Bitte um Hilfe.
Gruß
Walter Griesser

[Walter Griesser]

Hallo Ullrich,

Hallo Walter, die Prozedur erwartet einen benannten Bereich namens
"Last", welcher in der letzte Zeile liegen muss. Du kannst das manuell
erledigen (mindestens eine Zelle der letzten Zeile markieren, EINFÜGEN |
NAMEN | DEFINIEREN Name in der Arbeitsmappe: Last) oder natürlich auch per
VBA. Du bekommst aber ein Problem, wenn der User die letzte Zeile löscht.

Danke! Das war es. Das Problem "Löschen letzte Zeile" kann ich aber
abfangen, indem
ich im Fehlerfall den Namen "Last" wieder setze.

Gruß
Walter Griesser

[Peter Schleif]

Post by Walter Griesser
Danke! Das war es. Das Problem "Löschen letzte Zeile" kann ich aber
abfangen, indem
ich im Fehlerfall den Namen "Last" wieder setze.

Es geht auch eleganter. Statt dauernd drauf zu achten, dass der
benannte Bereich "Last" nicht verschwindet, könntest Du auch die
UsedRange Eigenschaft benutzen.

Wenn sich "Last" also zum Beispiel zu "Tabelle1" gehört, benutzt Du
statt Range("Last").Row einfach

Tabelle1.UsedRange.Rows.Count + Tabelle1.UsedRange.Row - 1

Außerdem verwende ich das Ereignis Worksheet_Change und nicht
Worksheet_Calculate. Hier der Complette Code in Anlehnung an das
Original von der Website.

Peter


'DieseArbeitsmappe
'------------------
Private Sub Workbook_Open()
Tabelle1.gintRow = Tabelle1.UsedRange.Rows.Count _
+ Tabelle1.UsedRange.Row - 1
End Sub


'Tabelle1
'---------
Public gintRow As Integer

Private Sub Worksheet_Change(ByVal Target As Range)

If UsedRange.Rows.Count + UsedRange.Row - 1 < gintRow Then
MsgBox "Es wurde eine Zeile gelöscht!"
ElseIf UsedRange.Rows.Count + UsedRange.Row - 1 > gintRow Then
MsgBox "Es wurde eine Zeile eingefügt!"
End If

gintRow = UsedRange.Rows.Count + UsedRange.Row - 1

End Sub

[Walter Griesser]

Hallo Peter,

Post by Peter Schleif

Danke! Das war es. Das Problem "Löschen letzte Zeile" kann ich >>
abfangen, indem ich im Fehlerfall den Namen "Last" wieder setze.

Es geht auch eleganter. Statt dauernd drauf zu achten, dass der
benannte Bereich "Last" nicht verschwindet, könntest Du auch die
UsedRange Eigenschaft benutzen.
Wenn sich "Last" also zum Beispiel zu "Tabelle1" gehört, benutzt Du
statt Range("Last").Row einfach
Tabelle1.UsedRange.Rows.Count + Tabelle1.UsedRange.Row - 1
Außerdem verwende ich das Ereignis Worksheet_Change und nicht
Worksheet_Calculate. Hier der Complette Code in Anlehnung an das
Original von der Website.
'DieseArbeitsmappe
'------------------
Private Sub Workbook_Open()
Tabelle1.gintRow = Tabelle1.UsedRange.Rows.Count _
+ Tabelle1.UsedRange.Row - 1
End Sub
'Tabelle1
'---------
Public gintRow As Integer
Private Sub Worksheet_Change(ByVal Target As Range)
If UsedRange.Rows.Count + UsedRange.Row - 1 < gintRow Then
MsgBox "Es wurde eine Zeile gelöscht!"
ElseIf UsedRange.Rows.Count + UsedRange.Row - 1 > gintRow Then
MsgBox "Es wurde eine Zeile eingefügt!"
End If
gintRow = UsedRange.Rows.Count + UsedRange.Row - 1
End Sub

Danke! Das ist einfacher.
Bei der Verwendung von Worksheet_Change kommt halt die Meldung auch wenn ich
nur eine Zelle verändere und wie Du um 9 : 25 richtig beschrieben hast gibts
bei _Calculate überhaupt keine Meldung.
Ich werde also Deinen Code nehmen.

Gruß
Walter Griesser

[Peter Schleif]

Post by Walter Griesser
http://www.herber.de/mailing/137701h.htm

Funktioniert bei mir nicht.

Im Workbook_Open kommt der Fehler, dass gintRow nicht definiert ist.
Wenn ich es durch Tabelle1.gintRow ersetzte klappt es. Allerdings
reagiert das Sheet (auch nach einem Neustart) nicht auf das
Einfügen/Löschen einer Zeile.

Kann mir jemand erklären, wieso _Calculate benutzt wurde statt _Change?

Ich habe einen (für mich) funktionieren Code an anderen Stelle in
diesem Thread gepostet (9:20 Uhr). Aber auch auch der funktioniert nur
mit Worksheet_Change? Wieso also _Calculate?

Peter

[Alexander Wolff]

Post by Peter Schleif
Ich habe einen (für mich) funktionieren Code an anderen Stelle in
diesem Thread gepostet (9:20 Uhr). Aber auch auch der funktioniert nur
mit Worksheet_Change? Wieso also _Calculate?

Weil _Change das Arbeiten mit Blättern ganz schön verlangsamen kann.
_Calculate (bei manuellem Neuberechnen!) ist ein Hinweis darauf, dass es um
große Blätter geht, die manuell neuberechnet werden sollen, wobei diese
Frage eben miterledigt wird.

[Peter Schleif]

Post by Alexander Wolff
Weil _Change das Arbeiten mit Blättern ganz schön verlangsamen kann.
_Calculate (bei manuellem Neuberechnen!) ist ein Hinweis darauf, dass es um
große Blätter geht, die manuell neuberechnet werden sollen, wobei diese
Frage eben miterledigt wird.

Das _Change so langsam ist, war mir nicht bewusst. Gut das zu wissen.
Danke.


Den Rest hab' ich nicht kapiert.

Wieso ist _Calculate ein Hinweis auf goße Blätter?

Welche Frage wurde eben miterledigt?

Peter

[Alexander Wolff]

Post by Peter Schleif
Wieso ist _Calculate ein Hinweis auf goße Blätter?

Weil _Calculate nur bei manuellem Neuberechnen ausgelöst wird (sonst wäre es
ja dem _Change gleich) und manuelles Neuberechnen meist nur bei großen oder
rechnenintensiven Modellen gewählt wird.

Post by Peter Schleif
Welche Frage wurde eben miterledigt?

Die des OP. Warum soll nach jeder Eingabe Einfügen/Löschen geprüft werden,
wenn es erst durch ein Neuberechnen sinnvoll wird?

[Peter Schleif]

Post by Alexander Wolff
Weil _Calculate nur bei manuellem Neuberechnen ausgelöst wird

Ist bei mir nicht so. (Excel 2002)

Wobei ich unter "manuellem Neuberechnen" die <F9>-Familie verstehe.
Also eine explizit durch den Benutzer angestoßene Neubrechnung.
Vielleicht reden wir hier aneinander vorbei.

Wenn ich in C3 eine Formel habe die sich auf A1 bezieht, wird
Worksheet_Calculate zum Beispiel abgefeuert, wenn ich ...

- den Wert in A1 ändere
- Zeile 2 lösche
- Spalte B lösche

IMHO sind das alles keine manuellen Neuberechnungen.

Peter

[Alexander Wolff]

Post by Peter Schleif

Post by Alexander Wolff
Weil _Calculate nur bei manuellem Neuberechnen ausgelöst wird

Ist bei mir nicht so. (Excel 2002)

Ich habs nicht ausprobiert und in den Wind hinein vermutet. Anscheinend
falsch.

Dann verstehe ich das Ereignis _Calculate nicht. Was unterscheidet es dann
noch wesentlich von _Change? (Klar, wenn ein nicht volatiler Eintrag ohne
Dependenzen geändert wird).

[Peter Schleif]

Post by Alexander Wolff
Dann verstehe ich das Ereignis _Calculate nicht. Was unterscheidet es dann
noch wesentlich von _Change? (Klar, wenn ein nicht volatiler Eintrag ohne
Dependenzen geändert wird).

Es gibt noch ein weiteres Beispiel bei dem _Change ausgelöst wird
_Calc aber nicht. Zudem zeigt es ein Verhalten das zumindest
inkonsequent ist:

Wert in Zeile 1, Bezug darauf in Zeile 3.

Löschen von Zeile 2 feuert _Calc ab. Das Einfügen einer Zeile zwischen
Zeile 1 und 3 hingegen nicht, obwohl ja in beiden Fällen der Bezug in
der ehemaligen Zeile 3 angepasst werden musste.

IMHO ist in dem Fall das Nicht-Abfeuern korrekt, weil sich ja der Wert
in Zeile 1 nicht geändert hat und somit eine wirkliche Neuberechnung
so oder so unnötig war. Es sollte aber zumindest einheitlich sein.

Peter

[Alexander Wolff]

Post by Peter Schleif
Es gibt noch ein weiteres Beispiel bei dem _Change ausgelöst wird
_Calc aber nicht. Zudem zeigt es ein Verhalten das zumindest
Wert in Zeile 1, Bezug darauf in Zeile 3.
Löschen von Zeile 2 feuert _Calc ab. Das Einfügen einer Zeile zwischen
Zeile 1 und 3 hingegen nicht, obwohl ja in beiden Fällen der Bezug in
der ehemaligen Zeile 3 angepasst werden musste.
IMHO ist in dem Fall das Nicht-Abfeuern korrekt, weil sich ja der Wert
in Zeile 1 nicht geändert hat und somit eine wirkliche Neuberechnung
so oder so unnötig war. Es sollte aber zumindest einheitlich sein.

Ich widerspreche anhand des Beispiels =SUMME():
Zeile Einfügen ändert SUMME nie, Löschen aber möglicherweise schon.

[Peter Schleif]

Post by Alexander Wolff
Löschen aber möglicherweise schon.

Richtig: *möglicherweise*

Trotzdem wird _Calc beim Löschen offentsichtlich *immer* abgefeuert.
Auch wenn es definitiv unnötig ist. Zum Beispiel bei einem einfachen
Bezug auf einem ansonsten leeren Blatt.

[A1]=1
[A3]=A1*2

Eine Prüfung, ob eine Neuberechnung wirklich nötig ist, findet hier
nicht statt, obwohl es diese Möglichkeit ja grundsätzlich zu geben
scheint, wie die folgenden Beispiel zeigen. Aber sie wird wohl nur
angewandt, wenn die zu löschende/einzufügende Zeile im Bereich des
Bezugs - wie in deinem Beispiel - und die Formel selbst dafür spricht.

[A3] = SUMME(A1:A2)

Löschen: ja, WS_Calc wird abgefeuert
Einfügen: nein


[A3] = ZEILEN(A1:A2)

Löschen: ja
Einfügen: ja


Alles korrekt soweit. Sieht so aus, als würde Excel anhand der Art der
Formel entscheiden, ob eine Neberechnung notwendig ist (oder zumindest
sein könnte). Aber warum wird _Calc dann in meinem Beispiel ganz oben
(beim Löschen) abgefeuert, obwohl es ja definitiv unnötig ist, wie
eine solche Prüfung ergeben würde?

Nun ja. Die Frage ist eher rhetorisch. So sehr interessiert es mich
dann auch wieder nicht. Von mir aus können wir auch hier abbrechen.


Peter

[Alexander Wolff]

Post by Peter Schleif

Post by Alexander Wolff
Löschen aber möglicherweise schon.

Richtig: *möglicherweise*
Trotzdem wird _Calc beim Löschen offentsichtlich *immer* abgefeuert.
Auch wenn es definitiv unnötig ist. Zum Beispiel bei einem einfachen
Bezug auf einem ansonsten leeren Blatt.

Stell Dir vor, irgendwo gilt BEREICH.VERSCHIEBEN() oder INDIREKT(). Schon
wegen dieser Möglichkeit braucht es immer ein Calc nach Löschen. Denn Excel
kann nicht auch noch solche volatilen Formeln unter Kontrolle halten.

Post by Peter Schleif
Nun ja. Die Frage ist eher rhetorisch. So sehr interessiert es mich
dann auch wieder nicht. Von mir aus können wir auch hier abbrechen.

Gute Idee.