5
Excel VBA Diagramm Datenbereich unterschiedlich, wenn Makro durchläuft oder gestoppt wird
Moin!
Ich komme diesmal mit einem Schönheitsfehler, der technisch aber trotzdem ein Problem ist:
Ich fahre in Excel eine Auswertung über Umsätze in verschiedenen Geschäftsbereichen. Es entsteht dabei eine Tabelle, die etwa so aussieht:
Daraus soll ein Diagramm entstehen, das nur die Monatswerte, nicht aber die Spalten mit den Jahressummen anzeigt.
Der Code dazu sieht wie folgt aus:
Ich befinde mich in einer If-Abfrage innerhalb eines with-Block und die Variablen sind korrekt gefüllt.
Wenn das Makro nun über diese Zeile geht, gibt es die Meldung "Methode Range für Objekt _global fehlgeschlagen". Drücke ich F5, wird der Code anstandslos ausgeführt und das Diagramm sieht aus, wie ich es haben will und das durchgehend für zehn Tabellenblätter (for-Schleife)
Wenn ich "on error resume next" einsetze, erscheint zwar die Fehlermeldung nicht, aber die Anweisung wird auch nicht ausgeführt.
Wenn ich zum Testen einen Stop-Punkt setze und beim Stop F5 drücke, macht er wieder, was er soll.
Kann mir jemand erklären, warum der Code unterschiedlich ausgeführt wird, je nachdem, ob der durchläuft oder zwischendurch pausiert?
Viel wichtiger wäre aber: es gibt zu der "Methode Range für Objekt _global fehlgeschlagen"-Meldung zig Antworten, die unbefriedigendste ist von MS, die sagen, dass das ein gewünschtes Verhalten ist. Demzufolge soll ich den Code zuerst beenden und dann wieder neu aufnehmen. Kann mir das jemand übersetzen?
Interessant ist auch: wenn ich den Code anhalte und mit der Maus über das erste ".Name" gehe, erscheint dort ebenfalls die Fehlermeldung, die Adresse bzw. das Worksheet kann anscheinend nicht aufgelöst werden. Gehe ich mit der Maus über eins der weiteren ".Name", erscheint wie erwartet der Tabellenblattname.
Wenn ich die Source vorher in eine Variable schreibe, passiert genau das Gleiche. Fülle ich allerdings String-Variablen, wird alles korrekt aufgelöst. Leider kann ich damit dann nicht die Source zusammensetzen.
Hat da jemand eine brauchbare Idee zu?
Ich komme diesmal mit einem Schönheitsfehler, der technisch aber trotzdem ein Problem ist:
Ich fahre in Excel eine Auswertung über Umsätze in verschiedenen Geschäftsbereichen. Es entsteht dabei eine Tabelle, die etwa so aussieht:
Daraus soll ein Diagramm entstehen, das nur die Monatswerte, nicht aber die Spalten mit den Jahressummen anzeigt.
Der Code dazu sieht wie folgt aus:
ActiveChart.SetSourceData Source:=.Range(.Name & "!" & DiaBlock_1_Start.Address & ":" & DiaBlock_1_Ende.Address & "," & .Name & "!" & DiaBlock_2_Start.Address & ":" & DiaBlock_2_Ende.Address & "," & .Name & "!" & DiaBlock_3_Start.Address & ":" & DiaBlock_3_Ende.Address & "," & .Name & "!" & DiaBlock_4_Start.Address & ":" & DiaBlock_4_Ende.Address), PlotBy:=xlRows Ich befinde mich in einer If-Abfrage innerhalb eines with-Block und die Variablen sind korrekt gefüllt.
Wenn das Makro nun über diese Zeile geht, gibt es die Meldung "Methode Range für Objekt _global fehlgeschlagen". Drücke ich F5, wird der Code anstandslos ausgeführt und das Diagramm sieht aus, wie ich es haben will und das durchgehend für zehn Tabellenblätter (for-Schleife)
Wenn ich "on error resume next" einsetze, erscheint zwar die Fehlermeldung nicht, aber die Anweisung wird auch nicht ausgeführt.
Wenn ich zum Testen einen Stop-Punkt setze und beim Stop F5 drücke, macht er wieder, was er soll.
Kann mir jemand erklären, warum der Code unterschiedlich ausgeführt wird, je nachdem, ob der durchläuft oder zwischendurch pausiert?
Viel wichtiger wäre aber: es gibt zu der "Methode Range für Objekt _global fehlgeschlagen"-Meldung zig Antworten, die unbefriedigendste ist von MS, die sagen, dass das ein gewünschtes Verhalten ist. Demzufolge soll ich den Code zuerst beenden und dann wieder neu aufnehmen. Kann mir das jemand übersetzen?
Interessant ist auch: wenn ich den Code anhalte und mit der Maus über das erste ".Name" gehe, erscheint dort ebenfalls die Fehlermeldung, die Adresse bzw. das Worksheet kann anscheinend nicht aufgelöst werden. Gehe ich mit der Maus über eins der weiteren ".Name", erscheint wie erwartet der Tabellenblattname.
Wenn ich die Source vorher in eine Variable schreibe, passiert genau das Gleiche. Fülle ich allerdings String-Variablen, wird alles korrekt aufgelöst. Leider kann ich damit dann nicht die Source zusammensetzen.
Hat da jemand eine brauchbare Idee zu?
Share on Facebook
Share on Twitter
Share on Reddit
Share on LinkedinPlease also mark the comments that contributed to the solution of the article
Content-Key: 459089
Url: https://administrator.de/contentid/459089
Printed on: April 27, 2024 at 14:04 o'clock
5 Comments
Latest comment
Hi,
vielleicht würde es uns helfen, wenn Du hier den kompletten With-Block zeigst. Dann könnten wir auch nachvollziehen, auf was sich ".Range" und ".Name" beziehen.
E.
vielleicht würde es uns helfen, wenn Du hier den kompletten With-Block zeigst. Dann könnten wir auch nachvollziehen, auf was sich ".Range" und ".Name" beziehen.
Wenn ich die Source vorher in eine Variable schreibe, passiert genau das Gleiche. Fülle ich allerdings String-Variablen, wird alles korrekt aufgelöst.
Diese Aussage kann ich jetzt nicht ganz nachvollziehen. Könntest Du das mal an konkreten Code-Zeilen verdeutlichen?E.
Einen Urlaub später...
Den ganzen with-Block? Das sprengt die Datenbank auf dieser Seite
Ich nehme mal einen Teil daraus, der das verdeutlicht:
Im Code-Block befindet sich "###Hier gehört der Text von unterhalb des Code-Blocks dazu###". Das ist schon unübersichtlich genug, daher hier der Rest der Info, der sich auf Deine zweite Frage bezieht:
Die Sache mit den Variablen für die Source muss ich erst wieder nachstellen, das habe ich wieder entfernt, weil es mich nicht weiterbringt. Die Zeile fängt an mit "ActiveChart.SetSourceData Source:=.Range(.Name... " und hier werden Range und Namen zur Laufzeit zusammengesetzt.
Ich hatte testweise das, was nach "Source:=" steht, vorher in eine Variable geschrieben und hier nur die Variable eingesetzt, weil ich wissen wollte, ob die hier verwendeten Variablen speziell in diesem Zusammenhang ein Problem machen oder ob das auch außerhalb der Active.Chart.SetSourceData-Anweisung ein Problem ist. Bisher scheitere ich nur daran, die Variable so zusammenzusetzen, dass es am Ende funktioniert. Irgendwas mache ich vermutlich bei der Variablendefinition falsch, das weiß ich noch nicht.
Ich werde mich im Laufe der Woche wieder mit dem Ding befassen und mal sehen, was sich findet. Für Änderungsvorschläge bin ich gerne zu haben, allerdings sitze ich nicht Vollzeit davor, daher bitte etwas Geduld, wenn ich was testen soll.
VG, Jörg
Den ganzen with-Block? Das sprengt die Datenbank auf dieser Seite
Ich nehme mal einen Teil daraus, der das verdeutlicht:Sub UserDiagrammeErstellen()
Application.ScreenUpdating = False
Dim DiagrammEnde As Boolean 'Alle anderen Variablen werden oben im Modul definiert. "Option Explicit" ist eingestellt.
DiagrammEnde = False
With ws 'ws = worksheet, dies wird für jedes der Tabellenblätter neu definiert, damit ich den Code hier nur einmal brauche. Das alles befindet sich also in einer größeren Schleife.
.Activate
Set DiaBlock_1_Start = .Range("O4") ' Die Zelle für Startblock 1 ist bekannt. "DiaBlock" und "StartBlock" bezieht sich auf die Datentabelle, in der mehrere Jahre vorkommen können, die jeweils durch Summenspalten unterbrochen sind.
'Ende der ersten Diagramm-Range suchen
For lngSpalte = 15 To 28 ' Durchlaufen der Spalten. Die bekannte Zelle O5 hat die Spaltennummer 15, die letzte Spalte kann maximal 13 Spalten weiter sein, da die Summenzeile ausgespart bleibt
If Left(.Cells(4, lngSpalte), 5) = "Summe" Then ' Wenn die ersten fünf Zeichen der Zelle "Summe" heißen
Exit For
End If
Next lngSpalte
lngSpalte = lngSpalte - 1 ' Eine Spalte vor der JahresSummenSpalte
Call Spaltenbuchstabe_aus_Spaltennmmer_ermitteln 'Ich arbeite lieber mit Range als mit Cells. Das ist mir bei Fehlersuchen und Formatierungen übersichtlicher.
ZeileRngMax = .Range("P" & Rows.Count).End(xlUp).Row ' In der Spalte O befindet sich weiter unten noch die Belegzeilentabelle. P ist die erste Spalte, in der nur die Datentabelle steht
Set DiaBlock_1_Ende = .Range(strSpalte & ZeileRngMax - 1)
If .Cells(4, lngSpalte + 2) = "Gesamtsumme" Then
.Range(DiaBlock_1_Start.Address & ":" & DiaBlock_1_Ende.Address).Select
.Shapes.AddChart.Select
On Error Resume Next
ActiveChart.SetSourceData Source:=ws.Range(.Name & "!" & DiaBlock_1_Start.Address & ":" & DiaBlock_1_Ende.Address), PlotBy:=xlRows
DiagrammEnde = True
ElseIf .Cells(4, lngSpalte + 2) <> "Gesamtsumme" Then
lngSpalte = lngSpalte + 2
Call Spaltenbuchstabe_aus_Spaltennmmer_ermitteln
Set DiaBlock_2_Start = .Range(strSpalte & 4) ' Die Spalte für Startblock 2 wurde errechnet, die Zeile ist bekannt
For lngSpalte = lngSpalte To lngSpalte + 12 ' Durchlaufen der Spalten. Die Zelle fragliche Zelle hat die eine errechnete Spaltennummer, die letzte Spalte kann maximal 12 Spalten weiter sein, da die Summenzeile ausgespart bleibt
If Left(.Cells(4, lngSpalte), 5) = "Summe" Then ' Wenn die ersten fünf Zeichen der Zelle "Summe" heißen
Exit For
End If
Next lngSpalte
'Ende der zweiten Diagramm-Range suchen
lngSpalte = lngSpalte - 1 ' Eine Spalte vor der JahresSummenSpalte
Call Spaltenbuchstabe_aus_Spaltennmmer_ermitteln
ZeileRngMax = .Range("P" & Rows.Count).End(xlUp).Row ' In der Spalte O befindet sich weiter unten noch die Belegzeilentabelle. P ist die erste Spalte, in der nur die Datentabelle steht
Set DiaBlock_2_Ende = .Range(strSpalte & ZeileRngMax - 1)
.Range(DiaBlock_1_Start.Address & ":" & DiaBlock_2_Ende.Address).Select
.Range(DiaBlock_1_Start.Address).Activate
.Shapes.AddChart.Select
On Error Resume Next
'Wenn ich hier ein Stop einbaue und dann sofort nach dem Stop mit F5 weiterlaufen lasse, passiert der Fehler nicht.
'Den Fehler gibt es also nur, wenn der Code vollständig durchläuft, nicht, wenn er unterbrochen wird.
ActiveChart.SetSourceData Source:=.Range(.Name ///***Gehe ich mit der Maus über dieses ".Name", wird mir die Fehlermeldung angezeigt (siehe mein Eröffnungspost). ###Hier gehört der Text von unterhalb des Code-Blocks dazu### ***/// & "!" & DiaBlock_1_Start.Address & ":" & DiaBlock_1_Ende.Address & ";" & .Name ///***Gehe ich mit der Maus über dieses ".Name", wird mir wie erwartet der Name des Tabellenblattes angezeigt***/// & "!" & DiaBlock_2_Start.Address & ":" & DiaBlock_2_Ende.Address), PlotBy:=xlRows
DiagrammEnde = False
End If
If DiagrammEnde = True Then GoTo DiagrammFormatieren
If .Cells(4, lngSpalte + 2) <> "Gesamtsumme" Then
.ChartObjects().Delete
lngSpalte = lngSpalte + 2
Call Spaltenbuchstabe_aus_Spaltennmmer_ermitteln
Set DiaBlock_3_Start = .Range(strSpalte & 4) ' Die Spalte für Startblock 3 wurde errechnet, die Zeile ist bekannt
For lngSpalte = lngSpalte To lngSpalte + 12 ' Durchlaufen der Spalten. Die Zelle fragliche Zelle hat die eine errechnete Spaltennummer, die letzte Spalte kann maximal 12 Spalten weiter sein, da die Summenzeile ausgespart bleibt
If Left(.Cells(4, lngSpalte), 5) = "Summe" Then ' Wenn die ersten fünf Zeichen der Zelle "Summe" heißen
Exit For
End If
Next lngSpalte
'Ende der dritten Diagramm-Range suchen
lngSpalte = lngSpalte - 1 ' Eine Spalte vor der JahresSummenSpalte
Call Spaltenbuchstabe_aus_Spaltennmmer_ermitteln
ZeileRngMax = .Range("P" & Rows.Count).End(xlUp).Row ' In der Spalte O befindet sich weiter unten noch die Belegzeilentabelle. P ist die erste Spalte, in der nur die Datentabelle steht
Set DiaBlock_3_Ende = .Range(strSpalte & ZeileRngMax - 1)
.Range(DiaBlock_1_Start.Address & ":" & DiaBlock_3_Ende.Address).Select
.Range(DiaBlock_3_Start.Address).Activate
.Shapes.AddChart.Select
'On Error Resume Next
ActiveChart.SetSourceData Source:=.Range(.Name & "!" & DiaBlock_1_Start.Address & ":" & DiaBlock_1_Ende.Address & ";" & .Name & "!" & DiaBlock_2_Start.Address & ":" & DiaBlock_2_Ende.Address & ";" & .Name & "!" & DiaBlock_3_Start.Address & ":" & DiaBlock_3_Ende.Address), PlotBy:=xlRows
DiagrammEnde = False
Else
DiagrammEnde = True
End If
...
...
...
Irgendwann werden auch alle Ifs und Fors und Withs wieder beendet ;-)Im Code-Block befindet sich "###Hier gehört der Text von unterhalb des Code-Blocks dazu###". Das ist schon unübersichtlich genug, daher hier der Rest der Info, der sich auf Deine zweite Frage bezieht:
Die Sache mit den Variablen für die Source muss ich erst wieder nachstellen, das habe ich wieder entfernt, weil es mich nicht weiterbringt. Die Zeile fängt an mit "ActiveChart.SetSourceData Source:=.Range(.Name... " und hier werden Range und Namen zur Laufzeit zusammengesetzt.
Ich hatte testweise das, was nach "Source:=" steht, vorher in eine Variable geschrieben und hier nur die Variable eingesetzt, weil ich wissen wollte, ob die hier verwendeten Variablen speziell in diesem Zusammenhang ein Problem machen oder ob das auch außerhalb der Active.Chart.SetSourceData-Anweisung ein Problem ist. Bisher scheitere ich nur daran, die Variable so zusammenzusetzen, dass es am Ende funktioniert. Irgendwas mache ich vermutlich bei der Variablendefinition falsch, das weiß ich noch nicht.
Ich werde mich im Laufe der Woche wieder mit dem Ding befassen und mal sehen, was sich findet. Für Änderungsvorschläge bin ich gerne zu haben, allerdings sitze ich nicht Vollzeit davor, daher bitte etwas Geduld, wenn ich was testen soll.
VG, Jörg
Ich habe das jetzt nicht auf inhaltliche Richtigkeit geprüft.
Aber rein von der Syntax her scheint mir das schon falsch.
Parameter für "Range" soll also sein:
.Name & "!" & DiaBlock_1_Start.Address & ":" & DiaBlock_1_Ende.Address & ";" & .Name & "!" & DiaBlock_2_Start.Address & ":" & DiaBlock_2_Ende.Address
Ich würde sagen, statt ";" muss das ein Komma sein. Dann käme z.B. heraus
"Blatt1!$A$1:$D$10,Blatt1!$H$1:$J$10"
Aber rein von der Syntax her scheint mir das schon falsch.
ActiveChart.SetSourceData Source:=.Range(.Name & "!" & DiaBlock_1_Start.Address & ":" & DiaBlock_1_Ende.Address & ";" & .Name & "!" & DiaBlock_2_Start.Address & ":" & DiaBlock_2_Ende.Address), PlotBy:=xlRows Parameter für "Range" soll also sein:
.Name & "!" & DiaBlock_1_Start.Address & ":" & DiaBlock_1_Ende.Address & ";" & .Name & "!" & DiaBlock_2_Start.Address & ":" & DiaBlock_2_Ende.Address
Ich würde sagen, statt ";" muss das ein Komma sein. Dann käme z.B. heraus
"Blatt1!$A$1:$D$10,Blatt1!$H$1:$J$10"
Das Ergebnis ist in beiden Fällen das Gleiche. Aktuell ist es das Semikolon, weil es so auch in der grafischen Einrichtung steht. Wenn ich mir anschließend die Einrichtung grafisch anschaue, wird auch das Komma dort wieder in ein Semikolon übersetzt. Das habe ich schon ein paarmal hin und her probiert.
Richtig ist, dass "Blatt1!$A$1:$D$10,Blatt1!$H$1:$J$10" das Ergebnis ist bzw. in der Grafik dann "Blatt1!$A$1:$D$10;Blatt1!$H$1:$J$10". In beiden Fällen werden die Daten auf die gleiche Weise übernommen und in beiden Fällen ist das Fehlerbild das Gleiche. Wenn dann das Script durchgelaufen ist, stimmt das Diagramm soweit auch.
Ich bin im Moment auf einer anderen Spur: ich habe den Code komplett mal in eine neue, leere Tabelle eingefügt und da ist das Fehlerverhalten etwas anders. Bedeutet für mich: das liegt nicht am Code, sondern es hat irgendwas mit der Tabelle selbst zu tun oder mit der Datenmenge, dem Speicherort (Netzlaufwerk, das ich über die Windows-Bibliotheken anspreche) oder dergleichen.
Wenn ich da mehr weiß, melde ich mich. Vielleicht liegt das Problem ja völlig außerhalb dieser Reichweite hier.
Richtig ist, dass "Blatt1!$A$1:$D$10,Blatt1!$H$1:$J$10" das Ergebnis ist bzw. in der Grafik dann "Blatt1!$A$1:$D$10;Blatt1!$H$1:$J$10". In beiden Fällen werden die Daten auf die gleiche Weise übernommen und in beiden Fällen ist das Fehlerbild das Gleiche. Wenn dann das Script durchgelaufen ist, stimmt das Diagramm soweit auch.
Ich bin im Moment auf einer anderen Spur: ich habe den Code komplett mal in eine neue, leere Tabelle eingefügt und da ist das Fehlerverhalten etwas anders. Bedeutet für mich: das liegt nicht am Code, sondern es hat irgendwas mit der Tabelle selbst zu tun oder mit der Datenmenge, dem Speicherort (Netzlaufwerk, das ich über die Windows-Bibliotheken anspreche) oder dergleichen.
Wenn ich da mehr weiß, melde ich mich. Vielleicht liegt das Problem ja völlig außerhalb dieser Reichweite hier.
Sooo, es gibt ein Lösung 
Das Problem war anscheinend ein Leerzeichen in einem der Tabellenblattnamen. Die Tabellenblätter werden nach den Nachnamen der User benannt und einer hat eben ein Leerzeichen mit am Start. Nachdem ich das jetzt auch noch ausgebaut habe, ist Ruhe und alles läuft, wie es soll. Jetzt ist auch ein weiteres Problem weg: bei dem User mit dem Leerzeichen wurden beim Diagramm die Spalten, die nicht ausgeblendet werden sollten, mit angezeigt und die Beschriftung der X-Achse sah anders aus, als bei den anderen.
Warum das eigentliche Problem auch aufgetreten ist, obwohl der aktuelle User kein Leerzeichen im Namen hatte, weiß ich nicht. Schade eigentlich.
Was lernen wir also daraus? Finger weg von Leerzeichen
Das Problem war anscheinend ein Leerzeichen in einem der Tabellenblattnamen. Die Tabellenblätter werden nach den Nachnamen der User benannt und einer hat eben ein Leerzeichen mit am Start. Nachdem ich das jetzt auch noch ausgebaut habe, ist Ruhe und alles läuft, wie es soll. Jetzt ist auch ein weiteres Problem weg: bei dem User mit dem Leerzeichen wurden beim Diagramm die Spalten, die nicht ausgeblendet werden sollten, mit angezeigt und die Beschriftung der X-Achse sah anders aus, als bei den anderen.
Warum das eigentliche Problem auch aufgetreten ist, obwohl der aktuelle User kein Leerzeichen im Namen hatte, weiß ich nicht. Schade eigentlich.
Was lernen wir also daraus? Finger weg von Leerzeichen