AW: fcs versteh die Formel nicht
07.10.2007 18:25:00
fcs
Hallo Julia,
ich hatte zwar schon versucht durch Kommentare im Code die Funktion verständlich zu machen. Dies ist scheinbar nur teileweise gelungen.
Um den Daten-Bereich des Diagramms per Plus-Minus-Buttons zu ändern hab ich den Formeltext im Makro analysiert, um die aktuell letzte Zeile des Diagramms hereauszubekommen.
Üblicherweise sind die Zeilen für die X-Werte (X-Values) und die Y-Werte (Values) der Datenreihen in einem Diagram identisch und müssen auch parallel für alle Reihen geändert werden, wenn man nicht irgendwelchen Datensalat im Diagramm dargestellt bekommen möchte.
Die Formel für die Datenreihe sieht bei einem Säulendiagramm standardmäßig so aus:
Deutsch in A1-Darstellung:
=DATENREIHE(Pivot!$B$61;Tabelle2!$A$62:$A$70;Pivot!$B$62:$B$70;1)
Englisch unter VBA in Zeile-Spalten-Darstellung:
=SERIES(Pivot!R61C2,Pivot!R62C1:R70C1,Pivot!R62C2:R70C2,1)
oder alemein formuliert:
=SERIES(Name_Reihe,X-Werte_Reihe,Y-Werte_Reihe,Nr_Reihe)
Ziel der 1. Aktionen der Prozedur ist es den Wert für die letzte Zeile herauszubekommen.
Ich hatte mich hier für das Suchen im Formeltext entschieden. Herbert ist in seiner Lösung den Weg über die Anzahl der Datenpunkte gegangen, was evtl. die etwas elegantere Lösung ist.
Die Zeilennummer beginnt im Formeltext für die X-Werte_Reihe nach "R62C1:R". Da ich meine Prozedur auf verschiedene Diagramme anwenden wollte, hab ich die Berechnung dieses Suchstrings variabel gemacht.
Pos1 = InStr(InStr(1, strFormel, ","), strFormel, "!", vbTextCompare) + 1
ermittelt im Formeltext ab dem 1. Zeichen die Position des "!" nach dem Komma "," und addiert 1. Hier steht das "R" der 1. Zelle des X-Werte Bereichs
Pos2 = InStr(Pos1, strFormel, ":", vbTextCompare) + 1
ermittelt im Formeltext ab Pos1 die Position des ":" und addiert 1. Hier steht das "R" der letzten Zelle des X-Werte Bereichs
Mit den beiden Position wird dann mit der Mid-FUnktion der Suchstring berechnet.
Mit diesem Suchstring wird dann im nächsten Schritt die Position der 1. Ziffer in der Zeilennummer ermittelt; das ist die nach dem Suchstring. Danach dann die Position der letzten Ziffer der Zeilennummer; ist dann vor dem nachfolgenden "C".
Mit den ermittelten Positionen wird die Zeilen-Nummer bestimmt.
Zum Schluss wird dann für alle Datenreihen in der Formel der Textstring ":R" & Zeile durch ":R" & ZeileNeu ersetzt. Da dieser sowohl im X-Werte_Bereich als Auch im Y-Werte_Bereich vorkommt wird er auch in beiden Bereichen ersetzt..
Und als ich diese Zeilen schreibe, merke ich, dass ich viel zu kompliziert gedacht hatte.
Mein Code läßt sich vereinfachen, wobei das anzupassenden Diagramm gleich als Parameter übergeben wird, woduch das ganze noch flexibler wird, und wird zu dem nachfolgenden Code.
Dein aktuelles Problem mit dem anderen Diagramm bzw. der anderen Datenreihe im Diagramm und der Änderung des Y-Werte-Bereichs für die Datenreihe(n) läßt sich mit mit meinem Code nicht lösen. Das erfordert eine Variante. Diese findest du im Anschluss.
Gruß
Franz
Sub PlusButton()
Call x_Achse_verschieben(Verschiebung:=1, Diagramm:=ActiveSheet.ChartObjects(1).Chart)
End Sub
Sub MinusButton()
Call x_Achse_verschieben(Verschiebung:=-1, Diagramm:=ActiveSheet.ChartObjects(1).Chart, _
ZeileMin:=62)
End Sub
Sub x_Achse_verschieben(Verschiebung As Integer, Diagramm As Chart, Optional ZeileMin As Long)
' x_Achse_verschieben Makro
' Makro am 05.10.2007 von julia aufgezeichnet, modified by fcs
Dim Reihe As Series
Dim Zeile As Long, ZeileNeu As Long, strSuch As String, Pos1%, Pos2%, strFormel$
With Diagramm
'Formelstring für 1. Datenreihe analysieren
'Beispielformel: "=SERIES(Pivot!R61C2,Pivot!R62C1:R70C1,Pivot!R62C2:R70C2,1)"
Set Reihe = .SeriesCollection(1)
strFormel = Reihe.FormulaR1C1 'Aktuelle Formel der 1. Datenreihe
'Bestimmung der Position der 1. Ziffer der Nummer der letzen Zeile des Datenbereichs _
Diese steht nach dem 1. Komma 2 Zeichen hinter dem ":"
Pos1 = InStr(InStr(1, strFormel, ","), strFormel, ":", vbTextCompare) + 2
'Bestimmung der Position der letzen Ziffer der Nummer für die letzte Datenzeile _
Diese steht vor dem nächsten "C"
Pos2 = InStr(Pos1, strFormel, "C", vbTextCompare) - 1
'Text mit Zeilennumer aus dem Formel-Text ausschneiden und in Zahl umwandeln
Zeile = Val(Mid(strFormel, Pos1, Pos2 - Pos1 + 1)) 'Aktuelle Zeilen-Nummer
ZeileNeu = Zeile + Verschiebung 'Neue Zeilen-Nummer
If ZeileNeu