Die hauptsächlich auftretende Aufgabe der VBA-Programmierung ist es, in Excel vorhandene Elemente durch den Programmcode eines Makros zu beeinflussen. So greift man in einem umfangreicheren Makro des häufigeren auf Zellen, Spalten, Tabellen und Diagramme zu. Obwohl sicherlich in vielen Anwendungsfällen eigene Dialoge erstellt werden müssen, nimmt deren Programmierzeit in der VBA-Programmierung allgemein nur einen geringen Teil der Gesamtzeit ein. Dieses Kapitel beschäftigt sich daher mit Zugriffstechniken zur Verwendung der zahlreichen Excel-Objekte und Auflistungen.
Zugriff auf Zellen
Zuerst sollen einige Begriffe erläutert werden: Die aktive Zelle (ActiveCell) ist jene Zelle des Tabellenblattes, die den Zellenzeiger enthält. Wenn man mit der Maus oder den Cursor-Tasten in eine Zelle springt, so wird diese Zelle zur aktiven Zelle, sie wird mit Zahlen oder Text gefüllt, wenn man eine alphanumerische Taste drückt. Ein Bereich (Range) ist eine Gruppe von Zellen. Ein Bereich wird z.B. dazu verwendet, um dem Diagramm-Assistenten die Datenquelle für ein Diagramm mitzuteilen. Eine Auswahl (Selection) liegt vor, wenn ein Bereich ausgewählt (markiert) wurde. Sie ist daran zu erkennen, daß die Zellen der Auswahl dunkel hinterlegt sind.
Einzelne Zellen bearbeiten
Um mit einer Eigenschaft bzw. Methode auf eine einzelne Zelle zuzugreifen, hat man eine Vielzahl von Möglichkeiten, die je nach Aufgabe mal besser und mal schlechter geeignet sind.
Zugriff über das Range-Objekt: Wie bereits erwähnt, handelt es sich bei einem Bereich um eine Gruppe von Zellen. Eine einzelne Zelle ist ein Sonderfall des Bereiches. Um eine einzelne Zelle mit einem Range-Objekt zu erreichen, kann folgende Syntax verwendet werden:
Range("A3").Methode
Als Kurzschreibweise ist auch
[A3].Methode
erlaubt.
Eine zweite Möglichkeit wäre, den Umweg über eine Selektion zu gehen. Dabei wird zuerst die Zelle, deren Zellenbezug im vorangegangenen Beispiel der Range-Methode übergeben wurde, ebenfalls unter Zuhilfenahme von Range, selektiert. Anschließend wird die Methode auf das Objekt der Selektion angewandt:
Range("A3").Select
Selection.Methode
Diese Art des Zellenzugriffs eignet sich besonders, wenn schon eine Selektion vorliegt, etwa weil der Programmanwender aus einer Mehrzahl von Zellen eine Zelle auswählen soll, deren Inhalt dann bearbeitet wird.
In Bezug auf Zellen ist die Activate-Methode mit der Select-Methode gleichbedeutend. Das Beispiel könnte also auch lauten:
Range("A3").Activate
ActiveCell.Methode
Weiterhin kann eine einzelne Zelle über die Cells-Auflistung angesprochen werden:
Cells(3,1).Methode
Wie man sieht, erwartet Cells statt eines Zeichenkettenparameters (wie Range oder Selection) einen Zellenbezug, der aus zwei Zahlen besteht. Der Vorteil dabei ist, daß man Cells sehr einfach in Schleifen verwenden kann, da die Laufvariablen der Schleifen ohne Umwege in den Parameterbereich eingesetzt werden können. So wird das Bearbeiten mehrerer Zellen einer Zeile oder Spalte vereinfacht. Der Nachteil ist, daß das Cells-Objekt die Parameter in der Form {Zeile, Spalte} erwartet, und nicht wie Range in der Form {Spalte, Zeile}, wodurch eine große Fehlergefahr besteht.
Eine einzelne Zelle kann auch angesprochen werden, indem man einen vorher (über den Menübefehl "Einfügen | Namen") vergebenen Zellennamen ausnutzt:
[Zellenname].Methode
Während alle vorangegangenen Zugriffsmöglichkeiten einen absoluten Zellenbezug benötigen, arbeitet die Offset-Methode mit relativen Angaben:
[A1].Offset(3,1)
Hier begegnet uns wieder die Schreibweise {Zeile, Spalte}, anzugeben als zwei positive natürliche Zahlen.
Bereiche bearbeiten
Ähnlich wie der Sonderfall der Zellenauswahl bei einer einzelnen Zelle können auch Bereiche selektiert werden. Dabei werden dem Range-Objekt statt einer einzelnen Zelle die "Eckpunkte", genauer gesagt, die Eckzellen eines rechteckigen Bereiches übergeben, voneinander getrennt durch einen Doppelpunkt:
Range("A5:D8").Select
Eine ganze Spalte wird selektiert durch
Columns("A:A").Select
und ein Spaltenbereich durch
Columns("A:F").Select
Sinngemäß gelten beide Zugriffsarten auch für Zeilen. Dort wird allerdings Rows() statt Columns() verwendet.
Zugriff auf Arbeitsmappen, Fenster und Arbeitsblätter
Wie bereits im Kapitel 5.3.4 erwähnt, wird die Excel-Anwendung durch ein Application-Objekt repräsentiert, dem alle weiteren Objekte, Methoden und Eigenschaften zur Manipulation der Anwendung untergeordnet sind. Um die nächsten Hierarchieebenen ansprechen zu können, bedient man sich in der VBA-Programmierung für Excel im wesentlichen der Arbeitsmappen, Fenster und Arbeitsblätter.
Arbeitsmappen
Mit einem Excel-Makro können nicht nur die Arbeitsmappen (Excel-Dateien) bearbeitet werden, die den gerade abgearbeiteten Code enthalten, der Zugriff ist auch auf andere Arbeitsmappen möglich, sofern diese in die Anwendung geladen wurden. Alle Arbeits-mappen (geöffnet oder nicht) werden dabei durch die Workbooks-Auflistung angesprochen. Zum Beispiel läßt sich eine neue Mappe durch
Workbooks.Add
erzeugen.
Der Zugriff auf die Mappe, deren Code gerade abläuft, ist durch das Objekt ThisWorkbook möglich. Die aktive Arbeitsmappe, d.h. die Mappe, welche momentan den Fokus besitzt, wird durch das ActiveWorkbook-Objekt angesprochen.
Fenster
In Excel können mehrere Fenster von einer Arbeitsmappe nebeneinander existieren. Dadurch lassen sich z.B. bei umfangreichen Tabellen die Spaltenüberschriften und die letzten Zeilen gleichzeitig in verschiedenen Fenstern betrachten, was die Lesbarkeit erhöhen kann.
Jeder Arbeitsmappe ist in Excel mindestens ein Fenster zugeordnet, auch wenn es zum Icon verkleinert oder unsichtbar ist. Angesprochen werden die Fenster über die Windows-Methode. Je nachdem, auf welches Objekt - Application oder ein Workbook-Objekt (z.B. ThisWorkbook) - die Methode angewandt wird, kann man alle Fenster oder nur die Fenster der Mappe bearbeiten.
Äquivalent zum Objekt ActiveWorkbook gibt es auch ein ActiveWindow.
Arbeitsblätter
Mit Sheets hat man Zugriff auf alle Blätter einer Arbeitsmappe, egal, ob es sich um ein Tabellenblatt, ein Diagramm oder um einen Dialog handelt. Sheets kann sowohl auf Application angewandt werden, wenn der Zugriff auf die Blätter der gerade aktiven Arbeitsmappe gewünscht ist, als auch mit einem Workbook-Objekt, wenn eine bestimmte Mappe bearbeitet werden soll.
Eine Verfeinerung von Sheets stellen im wesentlichen die Auflistungen Worksheets (Zugriff nur auf Tabellenblätter), Charts (Diagramme), und DialogSheets (vom Anwender erstellte Dialoge bzw. Formulare) dar. Letzteres ist nur noch aus Kompatibilitätsgründen in Excel 97 vorhanden; wie im Abschnitt 5.2 erwähnt, geschah in Excel 95 der Zugriff auf einen Dialog anders als in Excel 97, heute wird ein Dialog über seinen Namen angesprochen.
Die aktiven Blätter werden, wie üblich, mit einer Eigenschaft angesprochen, deren Name jeweils mit Active beginnt und mit dem Blatt-Typ schließt (ActiveSheet, ActiveChart, ActiveDialog).
Spezielle Objekte, Eigenschaften und Methoden
Soll mit einem Excel-Makro der Inhalt einer Zelle, eines Blattes oder einer Mappe verändert, ausgelesen oder formatiert werden, sind oft zahlreiche Objekte im Spiel, die alle ihre eigenen Methoden und Eigenschaften mitbringen. Bei einfachen Aufgaben kann es daher nützlich sein, die anfallenden Aufgaben zuerst einmal manuell auszuführen und dabei ein Makro aufzunehmen. Das Makro kann dann als Grundlage für den eigenen Programmcode dienen und der Programmierer braucht sich nicht alle Anweisungen zu merken. Besteht Unklarheit über die Funktion eines Begriffes im erzeugten Programmtext, liefert die Excel-Hilfe meistens schnell die Erklärung (Wort markieren oder nur den Cursor darüber setzen, dann die F1-Taste drücken).
Viele Programmzeilen in einem automatisch erzeugten Makro sind allerdings überflüssig für den Programmablauf und verlangsamen somit nur die Ausführungsgeschwindigkeit. Daher ist es häufig sinnvoll, der Programmierer erzeugt den Code von Hand. Die folgenden Abschnitte versuchen, Standardanweisungen für die wichtigsten Anwendungen bei der Excel-Programmierung aufzulisten. Da viele Anweisungen selbsterklärend sind, werde ich die Erläuterungen auf ein minimales Maß begrenzen. Für eine erweiterte Liste siehe z.B. /1/.
Umgang mit Arbeitsmappen, Fenstern und Blättern
Bereits im vorangegangenen Kapitel 5.4.2.1 wurde gezeigt, daß mit
Workbooks.Add
eine neue Arbeitsmappe erstellt wird.
Das Speichern der aktuellen Mappe unter einem neuen Namen im selben Verzeichnis, in dem auch die Excel-Anwendung untergebracht ist, geschieht mit:
ActiveWorkbook.SaveAs FileName:=Application.Path & "\TestMappe.xls"
Aktivieren einer Arbeitsmappe durch Angabe ihres Namens:
Windows("TestMappe.xls").Activate
(Achtung: Select darf nicht angewandt werden!)
Öffnen einer Mappe unter Angabe eines Dateinamens mitsamt Pfad:
Workbooks.Open FileName:="C:\TEMP\AndereMappe.xls"
Aufruf des Excel-Standarddialogs zum Öffnen einer Datei:
Dateiname = Application.GetOpenFilename
(Die Datei wird nicht geöffnet, sondern nur deren Name ermittelt und in eine Variable geschrieben. Zum Öffnen muß die Open-Methode verwendet werden. Achtung: Bei vorzeitigem Abbruch des Dialogs durch Betätigen der entsprechenden Schaltfläche liefert GetOpenFilename den Wert Null. Darum muß die Dateiname-Variable vom Typ Variant sein.)
Speichern der aktiven Mappe:
ActiveWorkbook.Save
Schließen der aktiven Mappe:
ActiveWorkbook.Close
Aktivieren einer Tabelle durch Angabe ihres Namens:
Sheets("Tabelle1").Select
Umbenennen einer Tabelle:
Sheets("Tabelle1").Name = "NeuerName"
Hinzufügen einer neuen Tabelle zur aktiven Arbeitsmappe:
Sheets.Add Type:= xlWorksheet
Einfügen einer Kopie der Tabelle3 an erster Stelle aller Arbeitsblätter:
Sheets("Tabelle3").Copy Before:=Sheets(1)
Löschen der ausgewählten Arbeitsblätter:
ActiveWindow.SelectedSheets.Delete
Aktivieren eines Diagramms, das in einem Tabellenblatt integriert ist:
ActiveSheet.ChartObjects("Diagramm 1").Activate
Aktivieren eines Diagrammblattes:
Sheets("Diagramm1").Select
Fenster Minimieren, Maximieren oder auf Normalgröße verändern:
ActiveWindow.WindowState = xlMinimized
ActiveWindow.WindowState = xlNormal
ActiveWindow.WindowState = xlMaximized
Horizontales und vertikales Teilen eines Fensters (die Zahlen stehen für die Anzahlen von Zeilen oberhalb, bzw. Spalten links neben der Teilungslinien):
ActiveWindow.SplitRow = 10
ActiveWindow.SplitColumn = 2
Erzeugen eines neuen Fensters der aktiven Mappe:
ActiveWindow.NewWindow
Anordnen aller nicht minimierter Fenster:
Windows.Arrange ArrangeStyle:=xlTiled Windows.Arrange ArrangeStyle:=xlHorizontal Windows.Arrange ArrangeStyle:=xlVertical Windows.Arrange ArrangeStyle:=xlCascade
Umgang mit Zellen, Zeilen und Spalten
Einfügen neuer Spalten links neben den selektierten Spalten (die Anzahl der neuen entspricht der Anzahl der ausgewählten Spalten):
Columns("B:C").Select
Selection.Insert Shift:=xlToRight
Einfügen neuer Zeilen oberhalb der selektierten Zeile (die Anzahl der neuen entspricht der Anzahl der ausgewählten Zeilen):
Rows("6:6").Select
Selection.Insert Shift:=xlDown
Einfügen eines Textes (oder einer Zahl) in eine Zelle:
ActiveCell.FormulaR1C1 = "abcd"
Einstellen der Zeilenhöhe und der Spaltenbreite:
Selection.RowHeight = 15
Columns("D:D").ColumnWidth = 20
Formatierung des Zellentextes über das Font-Objekt:
Selection.Font.Name = "SansSerif"
Selection.Font.FontStyle = "Bold"
Selection.Font.Size = 16
Selection.Font.Underline = xlUnderlineStyleDouble
Anwendung der Summenformel:
ActiveCell.FormulaR1C1 = "=SUM(R[-4]C:R[-1]C)"
Eingabe einer einfachen eigenen Formel:
ActiveCell.FormulaR1C1 = "=R[3]C[-2]-R[1]C[-2]"
Zahlenformatierung (zwei Nachkommastellen):
Selection.NumberFormat = "0.00"
Zahlenformatierung (Angabe der Zahl mit Tausender-Trennzeichen):
Selection.Style = "Comma"
Zahlenformatierung:
Selection.Style = "Percent"
Einfügen einer doppelten, dicken Zellenrahmenlinie unten, in der Farbe blau:
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlDouble
.Weight = xlThick
.ColorIndex = 32
End With
Zeilenumbruch in der selektierten Zelle einfügen:
Selection.WrapText = True
Inhalte einer Zelle löschen (Formatierung bleibt erhalten):
Selection.ClearContents
Formatierung einer Zelle auf den Standard zurückstellen:
Selection.ClearFormats
Zelleninhalt kopieren / ausschneiden / einfügen:
Selection.Copy
Selection.Cut
Selection.Paste
Textausrichtung mittig / links / rechts über alle Selektierten:
Selection.HorizontalAlignment = xlCenter
Selection.HorizontalAlignment = xlLeft
Selection.HorizontalAlignment = xlRight
|