Excel-Diagramm aus variabler Datenquelle per Makro

klexy · 09.12.2004

Zeile 1 ist Kopfzeile.
In Spalte D läuft eine Nummerierung aufsteigend, sagen wir mal bis 25.
In den Spalten E bis G sind die Werte.

So geht es:

Code:

    Charts.ADD
    ActiveChart.ChartType = xl3DColumn
    ActiveChart.SetSourceData _
    Source:=Sheets(Tabelle2).Range(E1:G25), _
    PlotBy:=xlColumns
    ActiveChart.Location Where:=xlLocationAsNewSheet

Weil es aber nicht immer 25 Zeilen sind, habe ich versucht, die Range variabel anzusteuern. Aber irgendwo ist ein Fehler drin. So gibt es eine Fehlermeldung:

Code:

    Charts.ADD
    ActiveChart.ChartType = xl3DColumn
    ActiveChart.SetSourceData _
    Source:=Sheets(Tabelle2).Range(Cells(1, 5), Cells.Row, 7), _
    PlotBy:=xlColumns
    ActiveChart.Location Where:=xlLocationAsNewSheet

So gibt es keine Fehlermeldung, aber das Diagramm bestehht nur aus einer einzigen dicken Säule:

Code:

    Charts.ADD
    ActiveChart.ChartType = xl3DColumn
    ActiveChart.SetSourceData _
    Source:=Sheets(Tabelle2).Range(Worksheets(Tabelle2).Cells(1, 5), Worksheets(Tabelle2).Cells(Worksheets(Tabelle2).Cells.Row, 7)), _
    PlotBy:=xlColumns
    ActiveChart.Location Where:=xlLocationAsNewSheet

Gibt es einen Unterschied zwischen Sheets und Worksheets?
Ich komm einfach nicht drauf :'( :'( :'(

Matjes · 09.12.2004

Hi klexy,

zunächst mal die Frage:

Gibt es einen Unterschied zwischen Sheets und Worksheets?

Ja!
Mit
- Sheets kannst Du alle Blätter ansprechen (Tabellenblätter (Worksheets) und Diagramme (Charts) (nicht eingebettete))
- Worksheets kannst Du alle Tabellenblätter ansprechen.
- Charts kannst Du alle Diagramme ansprechen, nicht eingebettete

Eingebetteten Charts kannst Du über Worksheet ansprechen.

Unten sind 4 Beispiele dazu.

zu

Weil es aber nicht immer 25 Zeilen sind, habe ich versucht, die Range variabel anzusteuern. Aber irgendwo ist ein Fehler drin. So gibt es eine Fehlermeldung:

'Cells' bezieht sich immer auf das aktive Blatt. Da mit Charts.Add gerade ein Diagramm angelegt wurde, ist das aktive Blatt das Diagramm. Darauf geht->Cells' natürlich schief, weil keine Tabelle ;D Deshalb ist es besser den Bereich (Range) vorher zu definieren. So ungefähr wie im 3 Anlauf gehts natürlich auch, wenn man Cells das Tabellenblatt voranstellt, auf das sich dieser Befehl beziehen soll. Ist aber für meinen Geschmack in der Schreibweise sehr unübersichtlich.

zu

So gibt es keine Fehlermeldung, aber das Diagramm besteht nur aus einer einzigen dicken Säule

Im Prinzip war das schon richtig gedacht.
Cells.Row gibt aber die erste Zeile des Bereichs Cells zurück, und das ist 1.
Die letzte Zeile des benutzten Bereiches ergibt:

Code:

Cells.SpecialCells(xlCellTypeLastCell).Row

(Das entspricht Strg+End, und damit ist nicht unbedingt die letzte Zeile mit Inhalt definiert, wenn man vorher Zeilen gelöscht hat. Dann liegt diese Zelle da, wo ehemals die letzte beschrieben Zelle lag.)

Das definitive Ende einer Spalte findet man (siehe klexys_Chart1 und 2)

Viel Spaß damit und gutes Gelingen

Gruß Matjes

Code:

Option Explicit
Sub klexys_Chart0()
  Dim l_rows
  l_rows = Cells.SpecialCells(xlCellTypeLastCell).Row
   Charts.Add
    ActiveChart.ChartType = xl3DColumn
    ActiveChart.SetSourceData _
    Source:=Sheets(Tabelle1).Range(Worksheets(Tabelle1).Cells(1, 5), _
    Worksheets(Tabelle1).Cells(l_rows, 7)), _
    PlotBy:=xlColumns
    ActiveChart.Location Where:=xlLocationAsNewSheet
End Sub
Sub klexys_Chart1()
  
 ->Definition fester Werte
  Const c_SP_anf = 5->erste Spalte
  Const c_SP_end = 7->letzte Spalte
  Const c_Z_anf = 1 ->Zeile Überschrift
  
  Dim ws As Worksheet, r As Range, l_rows As Long
  
 ->Quelltabelle definieren
  Set ws = ActiveWorkbook.Worksheets(Tabelle1)
  
 ->letzte Zeile der Spalte c_SP_anf
  l_rows = ws.Cells(ws.Rows.Count, c_SP_anf).End(xlUp).Row
  
 ->Range definieren
  Set r = Range(ws.Cells(c_Z_anf, c_SP_anf), _
                ws.Cells(l_rows, c_SP_end))
 ->Chart erzeugen
  Charts.Add
    ActiveChart.ChartType = xl3DColumn
    ActiveChart.SetSourceData _
    Source:=r, _
    PlotBy:=xlColumns
    ActiveChart.Location Where:=xlLocationAsNewSheet

 ->aufräumen
  Set ws = Nothing: Set r = Nothing

End Sub
'**********************************************************
Sub klexys_Chart2()
 ->der Range der Darzustellenden Spalten wird einzeln definiert
 ->und dann zusammengefaßt
 ->Chart wird als eingebettetes Chart auf Tabelle1 angelegt
  
 ->Definition fester Werte
  Const c_SP_1 = 5-> Spalte 5
  Const c_SP_2 = 6-> Spalte 6
  Const c_SP_3 = 7-> Spalte 7
  Const c_Z_anf = 1 ->Zeile Überschrift
  
  Dim ws As Worksheet, r As Range, l_rows As Long
  Dim r1 As Range, r2 As Range, r3 As Range
  
 ->Quelltabelle definieren
  Set ws = ActiveWorkbook.Worksheets(Tabelle1)
  
 ->letzte Zeile der Spalte c_SP_anf
  l_rows = ws.Cells(ws.Rows.Count, c_SP_1).End(xlUp).Row
  
 ->Spalten-Ranges definieren
  Set r1 = Range(ws.Cells(c_Z_anf, c_SP_1), _
                ws.Cells(l_rows, c_SP_1))
  Set r2 = Range(ws.Cells(c_Z_anf, c_SP_2), _
                ws.Cells(l_rows, c_SP_2))
  Set r3 = Range(ws.Cells(c_Z_anf, c_SP_3), _
                ws.Cells(l_rows, c_SP_3))
 ->Gesamtrange mit der Reihenfolge der Spalte definieren
  Set r = Application.Union(r3, r1, r2)
  
 ->Chart erzeugen
  Charts.Add
    ActiveChart.ChartType = xl3DColumn
    ActiveChart.SetSourceData _
    Source:=r, _
    PlotBy:=xlColumns
    ActiveChart.Location Where:=xlLocationAsNewSheet
    ActiveChart.Location Where:=xlLocationAsObject, _
                        Name:=ws.Name

 ->aufräumen
  Set r1 = Nothing: Set r2 = Nothing: Set r3 = Nothing
  Set ws = Nothing: Set r = Nothing
End Sub

'**********************************************************
Sub SheetsAuflisten()
  Dim s_tmp As String, i As Long
  
  s_tmp = 
  For i = 1 To ActiveWorkbook.Sheets.Count
    s_tmp = s_tmp &amp; vbLf &amp; ActiveWorkbook.Sheets(i).Name
  Next i
  MsgBox (Sheets-Auflistung &amp; vbLf &amp; s_tmp)
End Sub
'**********************************************************
Sub WorksheetsAuflisten()
  Dim s_tmp As String, i As Long
  
  s_tmp = 
  For i = 1 To ActiveWorkbook.Worksheets.Count
    s_tmp = s_tmp &amp; vbLf &amp; ActiveWorkbook.Worksheets(i).Name
  Next i
  MsgBox (Worksheets-Auflistung &amp; vbLf &amp; s_tmp)
End Sub
'**********************************************************
Sub ChartsAuflisten()
  Dim s_tmp As String, i As Long
  
  s_tmp = 
  For i = 1 To ActiveWorkbook.Charts.Count
    s_tmp = s_tmp &amp; vbLf &amp; ActiveWorkbook.Charts(i).Name
  Next i
  MsgBox (Charts-Auflistung &amp; vbLf &amp; s_tmp)
End Sub

'**********************************************************
Sub EingebetteteChartsAuflisten()
  Dim s_tmp As String, i As Long
  
  s_tmp = 
  For i = 1 To ActiveWorkbook.Worksheets(Tabelle1).ChartObjects.Count
    s_tmp = s_tmp &amp; vbLf &amp; _
      ActiveWorkbook.Worksheets(Tabelle1).ChartObjects(i).Chart.Name
  Next i
  MsgBox (Auflistung der in Tabelle1 eingebettete Charts &amp; vbLf &amp; s_tmp)
End Sub

klexy · 10.12.2004

Super!
Da hab ich wieder viel gelernt. Und auch fast alles verstanden.
Auch wenn ich die Aufzählungen gar nicht brauchen kann, haben sie doch einige Erhellnis gebracht.

Frage:
1. Wozu aufräumen? und was bedeutet Nothing genau?
2. In klexys_Chart2 habe ich folgende Zeile variiert:

Code:

  Set r = Application.Union(r3, r1, r2)
  Set r = Application.Union(r2, r1, r3)
  Set r = Application.Union(r1, r3, r2)

aber die Anordnung der Säulenreihen war immer gleich, nämlich 1 - 2 - 3
Oder was war der Sinn dieses aufwendigen Herumdefinierens?
3. sind s_tmp & vbLf VBA-Befehle oder wie oder was? Was mich daran besonders irritiert ist, daß s_tmp gleich nichts ist (s_tmp = ). Oder heißt das was ganz anderes?

Matjes · 10.12.2004

Hi klexy,

Auch wenn ich die Aufzählungen gar nicht brauchen kann, haben sie doch einige Erhellnis gebracht.

So war das auch gedacht ;D

zu Frage1:
VBA arbeitet intern mit einer GarbageCollection, d.h. nicht mehr benutzter Speicher wird weitgehend selbstständig im Hintergrund immer wieder aufgeräumt.
Für Variable/Objekte die mit->Set' angelegt werden, gilt das nicht. Die werden erst mit der Beendigung der Application aufgeräumt. Wenn man also mit Set Variablen anlegt und nicht wieder freigibt, wächst der benutzte Speicher immer mehr.
Irgendwann ist Schluß mit Hauptspeicher ...
Beobachten kann man das ganz gut mit dem Taskmonitor/Systemmonitor.
Mit

Code:

Set Variable = Nothing

signalisiert man der GarbageCollection, daß sie diesen Speicher freigeben kann.

zu Frage2:
'Union' hatte ich eigentlich angeführt, um zu zeigen, wie nicht zusammenhängende Bereiche in einem Range zusammengefaßt werden können.
Bei der Reihenfolge der Bereiche hatte ich etwas falsch in Erinnerung :'( Da macht VBA wirklich immer das gleiche.

zu Frage3:
'vbLf' ist das VBA-Synonym für Linefeed, entspricht chr(13)

s_tmp ist eine selbstdefinierte String-Variable (siehe DIM-Anweisung). In dem Beispiel dient sie dazu, den Ausgabe-String aufzunehmen. Zunächst wird sie initialisiert, also leer gesetzt. In der Schleife wird der Variablen ihr Wert , Linefeed und der Name des Blattes zugewiesen. Das ganze solange, bis alle Blattnamen enthalten sind.
nach 1.Schleifendurchlauf enthält s_tmp:
'vbLf & 1.Blattname'
nach 2.Schleifendurchlauf enthält s_tmp:
'vbLf & 1.Blattname & vbLf & 2.Blattname'
....

Gruß Matjes

Excel-Diagramm aus variabler Datenquelle per Makro

klexy

Matjes

klexy

Matjes

Excel-Diagramm aus variabler Datenquelle per Makro

ANGEBOTE & SPONSOREN

Neueste Themen

Statistik des Forums