Makros im Praxiseinsatz: Die VBA-Beispiel-Datenbank

Einführung

Wer Excel automatisieren und erweitern möchte, dem bietet es eine eigene Entwicklungsumgebung, in der Sie Makros und Funktionen anlegen und speichern können. Mit VBA (Visual Basic for Application) können Sie die Tabellenkalkulation Excel beliebig ausbauen und noch besser machen. Dieser Artikel gibt Ihnen anhand von Praxisbeispielen einen guten Start in die Programmierung von Excel. Dabei werden die wichtigsten Objekte in Excel erläutert. Die hier vorgestellten Beispielmakros sind Teil unserer VBA.Beispiel-Datenbank mit über hundert fertigen Makro-Lösungen. In dieser Datenbank können Sie gezielt nach Lösungen suchen und diese nach Belieben anwenden und Ihren Bedürfnissen anpassen.

 

Hinweis

Anhand einer Musterlösungsdatei können Sie die Arbeitsschritte der Praxisbeispiele nachvollziehen.

Beachten Sie, dass unter Extras → Makro → Sicherheit die Sicherheitsstufe im gleichnamigen Register auf Mittel oder Niedrig eingestellt ist, da ansonsten die VBA-Beispiel-Datenbank nicht gestartet werden kann. Unter Excel XP und Excel 2003 muss zusätzlich auf der Registerkarte Vertrauenswürdige Herausgeber das Kontrollkästchen Zugriff auf Visual Basic Projekt vertrauen gesetzt sein.

1 Zellenprogrammierung

Das wohl am häufigsten verwendete Objekt ist die Zelle. Diese Zelle wird in Excel als Range bezeichnet. Damit können Sie eine Zelle, mehrere Zellen und sogar ganze Bereiche ansprechen. Für die Programmierung von Zellen stehen Ihnen pro Tabelle als Obergrenze die Zellen aus 256 Spalten und insgesamt 65536 Zeilen zur Verfügung. In den allermeisten Fällen werden Sie diese Kapazität nie ausnutzen.

1.1 Umlaute ersetzen

Im ersten Praxisbeispiel sollen in einer Tabelle in allen markierten Zellen die Umlaute ersetzt werden. Um diese Aufgabe zu erledigen, fügen Sie ein neues Modul in der Entwicklungsumgebung ein, indem Sie über die Tastenkombination Alt + F11 in die Entwicklungsumgebung wechseln und dort aus dem Menü Einfügen den Befehl Modul wählen. Erfassen Sie danach das folgende Listing:

Sub ZeichenTauschen()
Dim zelle As Range
For Each zelle In Selection
With Selection
  .Replace What:="ä", Replacement:="ae", LookAt:=xlPart
  .Replace What:="ö", Replacement:="oe", LookAt:=xlPart
  .Replace What:="ß", Replacement:="ss", LookAt:=xlPart
  .Replace What:="ü", Replacement:="ue", LookAt:=xlPart
End With
Next zelle
End Sub

Bei diesem Makro werden alle Umlaute in Zellen, die Sie vorher markiert (For Each Zelle In Selection) haben, ersetzt. Um das Makro zu starten, wählen Sie in der Tabelle aus dem Menü Extras den Befehl Makro → Makros und starten das Makro mit einem Doppelklick auf den Eintrag ZeichenTauschen im Listenfeld.

Dieses Makro können Sie selbstverständlich anpassen und beispielsweise durch andere Zeichen ersetzen. Dazu geben Sie im Argument What das Zeichen an, das ersetzt werden soll. Im Argument Replacement geben Sie dann das Zeichen an, das als Ersatz herangezogen werden soll.

1.2 Leerzeichen entfernen

Viele Probleme können entstehen, wenn sich führende bzw. nachfolgende Leerzeichen in Zellen befinden. Diese Leerzeichen können beispielsweise oft nach Importen von Daten aus Fremdsystemen auftreten und erschweren Auswertungen mit Excel-Standardfunktionen und Pivot-Tabellen. Eine sichere Methode, um Leerzeichen aus Zellen zu entfernen, bietet das folgende Listing:

Sub LeerzeichenEntfernen()
Dim Zelle As Range
For Each Zelle In Selection
Zelle.Value = Trim(Zelle.Value)
Next Zelle
End Sub

Beim diesem Listing werden alle führenden und nachfolgenden Leerzeichen in den markierten Zellen (For Each Zelle In Selection) entfernt. Sollen nicht alle Leerzeichen, sondern nur Leerzeichen am linken Zellenrand entfernt werden, dann ersetzen Sie die Funktion Trim im Makro durch die Funktion LTrim. Müssen lediglich die Leerzeichen am rechten Rand der Zellen entfernt werden, dann ersetzen Sie die Funktion Trim im Makro durch die Funktion RTrim.

2 Zeilen- und Spaltenprogrammierung

Das nächste Objekt in der Hierarchie von Excel sind die Zeilen und Spalten. Auch zu diesen Objekten folgen nun zwei Beispiele.

2.1 Jede zweite Zeile ausblenden

Im folgenden Beispiel wird in der aktiven Tabelle jede zweite Zeile ausgeblendet.

Sub JedeZweiteZeileAusblenden()
Dim lngZ As long
For lngZ = 1 To ActiveSheet.UsedRange.Rows.Count
If lngZ Mod 2 = 0 Then
  Rows(lngZ).EntireRow.Hidden = True
End If
Next lngZ
End Sub

Dieses Makro können Sie anpassen, wenn Sie beispielsweise nur jede dritte Zeile ausblenden möchten, indem Sie den Befehl lngZ Mod 2 = 1 durch den Befehl lngZ Mod 3 = 1 ersetzen. Die Funktion Mod gibt den Rest einer ganzzahligen Division zweier Zahlen zurück. Über die Variable lngZ wird die jeweilige Zeile dargestellt. Es wird also die jeweilige Zeilennummer (Rows(lngZ)) beispielsweise durch 3 geteilt. Bleibt ein Rest von 0 übrig, dann wird die komplette Zeile (=EntireRow) ausgeblendet (=Hidden). Mit der Anweisung ActiveSheet.UsedRange.Rows.Count wird übrigens die Anzahl der verwendeten Zeilen in einer Tabelle ermittelt.

2.2 Gefilterte Zeilen auf eine neue Tabelle übertragen

Etwas Arbeit kann man sich ersparen, wenn man auf einer Tabelle mit einem Datenfilter (s. Abb. 1) alle gefilterten Zeilen auf eine neue Tabelle automatisch überträgt.

Abb. 1: Die gefilterte Liste soll automatis...

Das ist nur ein Ausschnitt aus dem Produkt Controlling Office. Sie wollen mehr? Dann testen Sie hier live & unverbindlich Controlling Office 30 Minuten lang und lesen Sie den gesamten Artikel.


Meistgelesene beiträge