cmp-online.de
Startseite
Neu...
Yamaha XT600Z
Meine Tenere
XT-Technik
VB/VBA-Grundlagen
Grundelemente
Prozeduren
Operatoren
Variablen
Methoden
Schleifen
Vordefinierte Funktionen
Excel-Objekte
Impressum
private homepage von christian mario preussler
Excel-Objekte und -Programmiertechniken

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 'Unterteilt
Windows.Arrange ArrangeStyle:=xlHorizontal 'Untereinander
Windows.Arrange ArrangeStyle:=xlVertical 'Nebeneinander
Windows.Arrange ArrangeStyle:=xlCascade 'Überlappend

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 '(Text doppelt 
'unterstrichen)

Anwendung der Summenformel:

ActiveCell.FormulaR1C1 = "=SUM(R[-4]C:R[-1]C)"
'(Summe der vier Zellen oberhalb der aktiven Zelle)

Eingabe einer einfachen eigenen Formel:

ActiveCell.FormulaR1C1 = "=R[3]C[-2]-R[1]C[-2]"
'(Inhalt der Zelle, die drei Zeilen unterhalb und zwei Spalten links neben der 
'aktiven Zelle liegt, minus dem Inhalt der Zelle an Position [+1,-2])

Zahlenformatierung (zwei Nachkommastellen):

Selection.NumberFormat = "0.00"

Zahlenformatierung (Angabe der Zahl mit Tausender-Trennzeichen):

Selection.Style = "Comma"

Zahlenformatierung:

Selection.Style = "Percent"
'(Macht aus einer Zahl eine Prozentzahl)

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