Erste Schritte mit VBA in Excel 2010

  • Artikel

Zusammenfassung: Dieser Artikel bietet Hauptbenutzern von Excel, die noch keine Programmierer sind, eine Einführung in Visual Basic für Applikationen (VBA) in Excel 2010. Dieser Artikel enthält eine Übersicht über die Programmiersprache VBA, Anleitungen für den Zugriff auf VBA in Excel 2010, eine ausführliche Lösung für eine praxisbezogene Problemstellung beim Programmieren in VBA für Excel sowie Tipps zum Programmieren und Debuggen.

Letzte Änderung: Freitag, 11. Dezember 2009

Communitymitglied-Symbol Ben Chinowsky, SDK Bridge

Inhalt dieses Artikels
Gründe für das Verwenden von VBA in Excel 2010
VBA-Programmierung 101
Makros und der Visual Basic-Editor
Beispiel aus der Praxis
Ändern des aufgezeichneten Codes
Weitere Aufgaben, die Sie mithilfe von VBA erledigen können
Nächste Schritte
Weitere Ressourcen

November 2009

Gilt für: Excel 2010 | Office 2010 | SharePoint Server 2010 | VBA | Visual Basic for Applications (VBA)

Inhalt

  • Gründe für das Verwenden von VBA in Excel 2010

  • VBA-Programmierung 101

  • Makros und der Visual Basic-Editor

  • Beispiel aus der Praxis

  • Ändern des aufgezeichneten Codes

  • Weitere Aufgaben, die Sie mithilfe von VBA erledigen können

  • Nächste Schritte

  • Weitere Ressourcen

Gründe für das Verwenden von VBA in Excel 2010

Microsoft Excel 2010 ist ein überaus leistungsstarkes Programm zum Bearbeiten, Analysieren und Präsentieren von Daten. Doch mitunter wünschen Sie sich trotz der zahlreichen Funktionen auf der standardmäßigen Excel-Benutzeroberfläche eine einfachere Möglichkeit, eine sich wiederholende Routineaufgabe zu erledigen oder eine Aktion auszuführen, die auf der Standardbenutzeroberfläche nicht möglich ist. Glücklicherweise bieten Office-Anwendungen wie Excel mit VBA (Visual Basic für Applikationen) eine Programmiersprache, mit der Sie diese Anwendungen erweitern können.

VBA basiert auf der Ausführung von Makros, d. h. schrittweisen Prozeduren, die in Visual Basic geschrieben werden. Programmieren zu lernen, mag dem einen oder anderen schwer fallen, doch mit etwas Geduld und mithilfe einiger Beispiele wie denen in diesem Artikel stellen viele Benutzer fest, dass sie durch Erlernen des Umgangs mit nur einer kleinen Menge an VBA-Code ihre Arbeit vereinfachen können. Außerdem erhalten sie die Möglichkeit, Aktionen in Office auszuführen, die sie zuvor für nicht möglich hielten. Nachdem Sie sich ein wenig mit VBA vertraut gemacht haben, werden Ihnen die nächsten Schritte wesentlich einfacher fallen, und es ergeben sich nahezu unbegrenzte Möglichkeiten.

Der weitaus gängigste Grund, VBA in Excel zu verwenden, ist die Automatisierung sich wiederholender Aufgaben. Angenommen, Sie haben mehrere Dutzend Arbeitsmappen, von denen jede zahlreiche Arbeitsblätter enthält, auf denen jeweils verschiedene Änderungen vorgenommen werden müssen. Dabei kann es sich um einfache Vorgänge wie das Anwenden einer neuen Formatierung auf einen festen Zellenbereich oder komplexe Vorgänge handeln, z. B. das Untersuchen bestimmter statistischer Daten auf den einzelnen Blättern, das Auswählen des besten Diagrammtyps zum Anzeigen der Daten mit diesen Merkmalen und das anschließende entsprechende Erstellen und Formatieren des Diagramms.

Auf jeden Fall wäre es Ihnen bestimmt lieber, wenn Sie diese Aufgaben nicht manuell ausführen müssten, wenigstens nicht regelmäßig. Sie können diese Aufgaben glücklicherweise mithilfe von VBA automatisieren, indem Sie explizite Anweisungen schreiben, die Excel anschließend befolgt.

VBA eignet sich jedoch nicht nur für sich wiederholende Aufgaben. Mithilfe von VBA können Sie auch neue Funktionen für Excel entwickeln (z. B. neue Algorithmen für die Analyse Ihrer Daten, deren Ergebnisse Sie anschließend mit den Diagrammfunktionen in Excel anzeigen können) und Aufgaben durchführen, die Excel mit anderen Office-Anwendungen wie Microsoft Access 2010 integrieren. Von allen Office-Anwendungen wird Excel am ehesten wie eine allgemeine Entwicklungsplattform genutzt. Zusätzlich zu den offenkundigen Aufgaben, die sich auf Listen und Buchhaltung beziehen, nutzen Entwickler Excel für eine Vielzahl von Aufgaben – von der Datenvisualisierung bis hin zur Prototyperstellung für Software.

Trotz aller guten Gründe für VBA in Excel 2010 sollten Sie stets daran denken, dass für die beste Lösung eines Problem VBA womöglich gar nicht erforderlich ist. Excel bietet auch ohne VBA sehr viele nützliche Funktionen, sodass selbst erfahrene Benutzer nicht mit allen vertraut sind. Bevor Sie sich für eine VBA-Lösung entscheiden, sollten Sie die Hilfe und Onlineressourcen durchsuchen, um sicherzustellen, dass es keine einfachere Möglichkeit gibt.

VBA-Programmierung 101

Verwenden von Code, damit Anwendungen bestimmte Vorgänge ausführen

Sie glauben vielleicht, dass das Schreiben von Code mysteriös oder schwierig ist, doch die Grundprinzipien basieren lediglich auf logischem Denken und sind einfach zugänglich. Die Office 2010-Anwendungen wurden so entwickelt, dass sie Objekte genannte Elemente verfügbar machen, die Anweisungen empfangen können. Die Interaktion mit Anwendungen erfolgt durch Senden von Anweisungen an verschiedene Objekte in der Anwendung. Diese Objekte sind zahlreich, verschiedenartig und flexibel, aber auch begrenzt. Sie können nur das leisten, wofür sie entwickelt wurden, und tun nur das, was Sie wollen, dass sie tun.

Objekte

Programmierungsobjekte stehen in einer Hierarchie, die als Objektmodell der Anwendung bezeichnet wird, systematisch miteinander in Beziehung. Das Objektmodell spiegelt grob wider, was Sie auf der Benutzeroberfläche sehen. Das Excel-Objektmodell enthält beispielsweise neben vielen anderen Objekten die Objekte Application, Workbook, Sheet und Chart. Das Objektmodell ist eine konzeptuelle Abbildung der Anwendung und ihrer Funktionsmöglichkeiten.

Eigenschaften und Methoden

Sie können Objekte bearbeiten, indem Sie ihre Eigenschaften festlegen und ihre Methoden aufrufen. Durch das Festlegen einer Eigenschaft ändern sich die Merkmale des Objekts. Das Aufrufen einer Methode bewirkt, dass das Objekt eine Aktion ausführt. Für das Workbook-Objekt gibt es beispielsweise die Close-Methode zum Schließen der Arbeitsmappe und die ActiveSheet-Eigenschaft, die das Blatt darstellt, das gegenwärtig in der Arbeitsmappe aktiv ist.

Auflistungen

Viele Objekte gibt es sowohl in der Singular- als auch der Pluralversion, z. B. Workbook und Workbooks, Worksheet und Worksheets. Die Pluralversionen werden als Auflistungen bezeichnet. Auflistungsobjekte dienen zum Anwenden einer Aktion auf mehrere Elemente in der Auflistung. Im weiteren Verlauf dieses Artikels wird erklärt, wie mithilfe der Worksheets-Auflistung der Name aller Arbeitsblätter in einer Arbeitsmappe geändert wird.

Makros und der Visual Basic-Editor

Nun da Sie eine Vorstellung davon haben, wie Microsoft Excel 2010 sein Objektmodell verfügbar macht, können Sie das Aufrufen von Objektmethoden und Festlegen von Objekteigenschaften ausprobieren. Dazu müssen Sie Code an einer Stelle und mit einer Vorgehensweise schreiben, die von Office "verstanden" wird – das ist in der Regel der Visual Basic-Editor. Dieser Editor ist zwar standardmäßig installiert, vielen Benutzern aber so lange gänzlich unbekannt, bis er auf dem Menüband aktiviert wird.

Registerkarte "Entwicklertools"

Alle Office 2010-Anwendungen stellen das Menüband zur Verfügung. Eine der Registerkarten auf dem Menüband ist die Registerkarte Entwicklertools, über die Sie den Visual Basic-Editor und andere Tools für Entwickler aufrufen können. Da die Registerkarte Entwicklertools in Office 2010 nicht standardmäßig angezeigt wird, müssen Sie sie wie folgt aktivieren:

So aktivieren Sie die Registerkarte Entwicklertools

  1. Klicken Sie auf der Registerkarte Datei auf Optionen, um das Dialogfeld Excel-Optionen zu öffnen.

  2. Klicken Sie auf der linken Seite des Dialogfelds auf Menüband anpassen.

  3. Wählen Sie auf der linken Seite des Dialogfelds unter Befehle auswählen den Eintrag Häufig verwendete Befehle aus.

  4. Klicken Sie auf der rechten Seite des Dialogfelds unter Menüband anpassen auf Hauptregisterkarten, und aktivieren Sie dann das Kontrollkästchen Entwicklertools.

  5. Klicken Sie auf OK.

Nachdem Sie die Registerkarte Entwicklertools aktiviert haben, sehen Sie links die Schaltflächen Visual Basic und Makros.

Abbildung 1 – Registerkarte Entwicklertools in Excel 2010

Registerkarte 'Entwicklertools' in Excel 2010

Überlegungen zur Sicherheit

Klicken Sie auf die Schaltfläche Makrosicherheit, um anzugeben, welche Makros unter welchen Bedingungen ausgeführt werden dürfen. Wenngleich nicht autorisierter Makrocode Ihren Computer ernsthaft beschädigen kann, können Sicherheitsbedingungen, die Sie an der Ausführung hilfreicher Makros hindern, Ihre Produktivität stark einschränken. Makrosicherheit ist ein komplexes und kompliziertes Thema, mit dem Sie sich umfassend auseinandersetzen sollten, wenn Sie mit Excel-Makros arbeiten.

Beachten Sie im Kontext dieses Artikels, dass wenn beim Öffnen einer Arbeitsmappe, die ein Makro enthält, die Leiste Sicherheitswarnung: Makros wurden deaktiviert zwischen dem Menüband und dem Arbeitsblatt angezeigt wird, Sie auf die Schaltfläche Inhalt aktivieren klicken können, um die Makros zu aktivieren.

Ferner können Sie als Sicherheitsmaßnahme ein Makro nicht im standardmäßigen Excel-Dateiformat (.xlsx) speichern, sondern müssen ein Format mit der speziellen Erweiterung (.xlsm) zum Speichern des Makros wählen.

Visual Basic-Editor

Nachfolgend wird beschrieben, wie Sie eine neue leere Arbeitsmappe erstellen können, in der Sie Ihre Makros ablegen können. Anschließend können Sie die Arbeitsmappe im XLSM-Format speichern.

So erstellen Sie eine neue leere Arbeitsmappe

  1. Klicken Sie auf der Registerkarte Entwicklertools auf die Schaltfläche Makros.

  2. Geben Sie im nun angezeigten Dialogfeld Makro unter Makroname den Namen Hello ein.

  3. Klicken Sie auf die Schaltfläche Erstellen, um den Visual Basic-Editor mit der Gliederung eines neuen Makros zu öffnen.

VBA eine vollständig ausgestattete Programmiersprache mit einer entsprechenden vollständig ausgestatteten Programmierumgebung. In diesem Artikel werden nur die Tools behandelt, die Sie für die ersten Programmierungsschritte benötigen, was die meisten Tools im Visual Basic-Editor unberücksichtigt lässt. Schließen Sie vor diesem Hintergrund auf der linken Seite des Visual Basic-Editors das Fenster Eigenschaften, und ignorieren Sie die beiden Dropdownlisten, die über dem Code angezeigt werden.

Abbildung 2 – Visual Basic-Editor

Visual Basic-Editor

Der Visual Basic-Editor enthält den folgenden Code.

Sub Hello()

End Sub

Sub steht für Subroutine (Unterroutine), die Sie vorerst als "Makro" definieren können. Wenn Sie das Hello-Makro ausführen, wird der gesamte Code ausgeführt, der zwischen Sub Hello() und End Sub steht.

Bearbeiten Sie das Makro jetzt so, dass es wie der folgende Code aussieht.

Sub Hello()
   MsgBox ("Hello, world!")
End Sub

Kehren Sie zurück zur Registerkarte Entwicklertools in Excel zurück, und klicken Sie erneut auf die Schaltfläche Makros.

Wählen Sie in der angezeigten Liste das Makro Hello aus, und klicken Sie auf Ausführen, um ein kleines Meldungsfeld mit dem Text "Hello, world!" anzuzeigen.

Sie haben gerade benutzerdefinierten VBA-Code in Excel erstellt und implementiert. Klicken Sie in dem Meldungsfeld auf OK, um die Meldung zu schließen und die Ausführung des Makros zu beenden.

Falls das Meldungsfeld nicht angezeigt wird, überprüfen Sie die Sicherheitseinstellungen für Makros, und starten Sie Excel neu.

Makros leicht zugänglich machen

Sie können auch auf der Registerkarte Ansicht zum Dialogfeld Makros gelangen. Wenn Sie aber ein Makro häufig benötigen, ist es meist bequemer, wenn Sie es über eine Tastenkombination oder eine Schaltfläche auf der Symbolleiste für den Schnellzugriff ausführen können.

Gehen Sie wie folgt vor, um eine Schaltfläche für das Makro Hello auf der Symbolleiste für den Schnellzugriff zu erstellen.

Nachfolgend wird erläutert, wie Sie eine Schaltfläche für ein Makro auf der Symbolleiste für den Schnellzugriff erstellen:

So erstellen Sie eine Schaltfläche für ein Makro auf der Symbolleiste für den Schnellzugriff

  1. Klicken Sie auf die Registerkarte Datei.

  2. Klicken Sie auf Optionen, um das Dialogfeld Excel-Optionen zu öffnen, und klicken Sie dann auf Symbolleiste für den Schnellzugriff.

  3. Wählen Sie in der Liste unter Befehle auswählen: den Eintrag Makros aus. Suchen Sie in der nun angezeigten Liste den Text mit einer ähnlichen Bezeichnung wie Book1!Hello heraus, und wählen Sie diesen Text aus.

  4. Klicken Sie auf die Schaltfläche Hinzufügen >>, um das Makro in die Liste auf der rechten Seite zu übertragen. Klicken Sie dann auf die Schaltfläche Ändern…, um ein Schaltflächenbild auszuwählen, das dem Makro zugeordnet werden soll.

  5. Klicken Sie auf OK. Die neue Schaltfläche sollte nun auf der Symbolleiste für den Schnellzugriff oberhalb der Registerkarte Datei angezeigt werden.

Jetzt können Sie das Makro jederzeit rasch ausführen, ohne die Registerkarte Entwicklertools verwenden zu müssen. Probieren Sie es aus!

Beispiel aus der Praxis

Angenommen, Sie haben eine Arbeitsmappe, die auf vielen Arbeitsblättern Listen enthält, und Sie möchten den Namen jedes Arbeitsblatts so ändern, dass er der Überschrift der Liste auf dem jeweiligen Arbeitsblatt entspricht. Nicht jedes Arbeitsblatt enthält eine Liste. Falls doch, befindet sich die Überschrift in Zelle B1. Falls nicht, ist Zelle B1 leer. Die Namen von Arbeitsblättern ohne Liste sollen unverändert bleiben.

Im Normalfall wäre dies eine komplexe Aufgabe. Sie müssten sich jedes Arbeitsblatt anschauen, um zu prüfen, ob es eine Liste enthält und , falls ja, den Namen kopieren, auf die Registerkarte Tabelle klicken und anschließend den neuen Namen einfügen. Anstatt all diese Schritte manuell ausführen zu müssen, können Sie mithilfe von Excel VBA die Arbeitsblätter automatisch umbenennen.

Informationen zu Objekten

Zum Lösen eines VBA-Programmierungsproblems müssen Sie zuerst herausfinden, welche Objekte vom Code geändert werden. Ein wichtiges Instrument zum Ermitteln dieser Informationen ist die Excel-Objektmodellreferenz, die zur Excel 2007-Entwicklerreferenz auf der MSDN-Website (Microsoft Developer Network) gehört.

Diese Referenzinformationen werden für Excel 2010 aktualisiert, sobald die Markteinführung erfolgt ist, doch die Excel 2007-Entwicklerreferenz ist auch für die meisten Excel 2010-Zwecke geeignet.

Abbildung 3 – Excel-Objektmodellreferenz auf MSDN

Excel-Objektmodellreferenz auf MSDN

Der erste Schritt ist das Ermitteln, wie die Objekte geändert werden müssen, mit denen Sie zur Erledigung der Aufgaben arbeiten müssen, z. B. Arbeitsblätter, Arbeitsblattnamen, Zellen und Zelleninhalte. In Excel gibt es mindestens zwei Möglichkeiten, dieses Problem anzugehen:

  • Direkt die Objektmodellreferenz konsultieren

  • Einige der Aktionen aufzeichnen, die Sie automatisieren möchten, prüfen, wie der aufgezeichnete Code die Objekte ändert, und dann zum Suchen weiterer Informationen zur Objektmodellreferenz wechseln

Die Meinungen gehen auseinander, welcher Ansatz vorzuziehen sei. Zum jetzigen Augenblick wollen wir mit der Makroaufzeichnung beginnen.

Arbeiten mit der Makroaufzeichnung

Mitunter ist nicht mehr als ein einfaches aufgezeichnetes Makro erforderlich. In diesen Fällen müssen Sie sich nicht einmal mit dem Code beschäftigen. Meistens ist das Aufzeichnen allein allerdings nicht genug, sondern lediglich das Ausgangspunkt des folgenden Prozesses.

Verwenden der Makroaufzeichnung als Ausgangspunkt für Ihre Lösung

  1. Zeichnen Sie die Aktionen auf, die Sie codieren möchten.

  2. Überprüfen Sie den Code, und ermitteln Sie die Zeilen, die die jeweiligen Aktionen ausführen.

  3. Löschen Sie den Rest des Codes.

  4. Ändern Sie den aufgezeichneten Code.

  5. Fügen Sie Variablen, Steuerstrukturen und andere Codezeilen hinzu, die von der Makroaufzeichnung nicht aufgezeichnet werden können.

Beginnen Sie Ihre Untersuchung mit der Aufzeichnung eines Makros, das ein Arbeitsblatt in New Name umbenennt. Sie können anschließend das aufgezeichnete Makro zum Entwickeln eines eigenen Makros nutzen, das mehrere Arbeitsblätter basierend auf ihrem Inhalt umbenennt.

So zeichnen Sie ein Makro auf, das ein Arbeitsblatt umbenennt

  1. Klicken Sie auf der Registerkarte Entwicklertools auf die Schaltfläche Makro aufzeichnen.

  2. Nennen Sie das Makro RenameWorksheets, benennen Sie Sheet1 in New Name um, und klicken Sie auf Aufzeichnung beenden.

  3. Wechseln Sie zur Registerkarte Entwicklertools oder Ansicht, klicken Sie auf die Schaltfläche Makros, und wählen Sie Bearbeiten aus, um den Visual Basic-Editor zu öffnen.

Der Code im Visual Basic-Editor muss ähnlich wie der folgende Code aufgebaut sein.

Sub RenameWorksheets()
'
' RenameWorksheets Macro
'
'
    Sheets("Sheet1").Select
    Sheets("Sheet1").Name = "New Name"
End Sub

Die ersten vier Zeilen hinter der Zeile Sub sind Kommentarzeilen. Jede Zeile, die mit einem Apostroph beginnt, ist ein Kommentar und hat keine Auswirkung auf die Makroaktivität. Kommentare haben die folgenden wichtigen Zwecke:

  • Sie sollen den Code verständlicher machen, nicht nur für Sie, sondern für alle anderen, die den Code ggf. später bearbeiten müssen.

  • Sie dienen zum vorübergehenden Deaktivieren einer Codezeile (was als herauskommentieren bezeichnet wird).

Die vier Kommentare in diesem aufgezeichneten Makro dienen keinem Zweck und können deshalb gelöscht werden.

In der nächsten Zeile finden Sie die Select-Methode vor, mit der das Element Sheet1 des Sheets-Auflistungsobjekts ausgewählt wird. Im VBA-Code ist es im Allgemeinen nicht erforderlich, Objekte auszuwählen, bevor sie bearbeitet werden, was bei der Makroaufzeichnung jedoch erfolgt. Deshalb ist diese Codezeile überflüssig, sodass Sie auch sie löschen können.

In der letzten Zeile des aufgezeichneten Makros wird die Name-Eigenschaft des Sheet1-Elements der Sheets-Auflistung geändert. Diese Zeile darf nicht gelöscht werden.

Nachdem sie Ihre Änderungen vorgenommen haben, sollte der aufgezeichnete Code nun wie folgt aussehen.

Sub RenameWorksheets()
    Sheets("Sheet1").Name = "New Name"
End Sub

Benennen Sie das Blatt New Name wieder in Sheet1 um, und führen Sie anschließend das Makro aus. Der Name sollte sich wieder in New Name ändern.

Ändern des aufgezeichneten Codes

Nun ist es Zeit, die Sheets-Auflistung zu untersuchen, die von der Makroaufzeichnung verwendet wird. Das Thema Sheets in der Objektmodellreferenz enthält folgenden Text:

"Die Sheets-Auflistung kann Chart- oder Worksheet-Objekte enthalten. Wenn Sie nur mit Blättern eines Typs arbeiten müssen, lesen Sie das Objektthema des jeweiligen Blatttyps."

Da Sie nur mit Arbeitsblättern arbeiten, können Sie den Code wie folgt ändern.

Sub RenameWorksheets()
    Worksheets("Sheet1").Name = "New Name"
End Sub

Schleife

Eine Einschränkung des Codes bis zu dieser Stelle ist, dass er nur eine Änderung an einem Arbeitsblatt vornimmt. Sie könnten eine weitere Zeile für jedes Arbeitsblatt hinzufügen, das Sie umbenennen möchten, doch was ist, wenn Sie die Gesamtzahl der Arbeitsblätter bzw. deren aktuellen Namen nicht kennen? Sie benötigen eine Möglichkeit zum Anwenden derselben Regel auf alle Arbeitsblätter in der Arbeitsmappe.

VBA bietet die Konstruktion einer For Each-Schleife, die ideal geeignet ist. Die For Each-Schleife untersucht jedes Element im Auflistungsobjekt, z. B. Worksheets-Objekte, und kann verwendet werden, um eine Aktion (wie das Ändern eines Namens) auf einige oder alle diese Elemente anzuwenden.

Weitere Informationen zur For Each-Schleife finden Sie in der VBA-Sprachreferenz. Klicken Sie auf "Visual Basic Conceptual Topics" und anschließend auf "Using For Each...Next Statements". Beachten Sie auch, dass sich die VBA-Sprachreferenz wie die Objektmodellreferenz aufgrund ihrer Informationsvielfalt zum Durchstöbern geradezu anbieten und Lösungen bereitstellen, wenn Sie mit Ihrem Code nicht weiter vorankommen.

Bearbeiten Sie unter Befolgung des dritten Beispiels im Thema "Using For Each...Next Statements" das Makro so, dass es dem folgenden Code ähnlich sieht.

Sub RenameWorksheets()
For Each myWorksheet In Worksheets
    myWorksheet.Name = "New Name"
Next
End Sub

myWorksheet ist eine Variable, was bedeutet, das ihr Wert variieren kann. In diesem Fall stellt die myWorksheet-Variable nacheinander die einzelnen Arbeitsblätter in der Worksheets-Auflistung dar. Sie müssen nicht mit myWorksheet arbeiten. Sie können auch mit "x", "ws", "GemäßInhaltVonZelleB1UmzubenennendesArbeitsblatt" oder (bei wenigen Einschränkungen) nahezu jeden beliebigen Namen arbeiten. Eine gute Wahl sind Variablennamen, die lang genug sind, um Sie an den Zweck der Variable zu erinnern, aber nicht so lang sind, dass sie den Code unübersichtlich machen.

Wenn Sie das Makro im aktuellen Zustand ausführen, wird ein Fehler generiert, da Excel anfordert, dass jedes Arbeitsblatt in einer Arbeitsmappe einen eindeutigen Namen hat. Doch die folgende Zeile weist Excel an, jedem Arbeitsblatt denselben Namen zuzuordnen.

    myWorksheet.Name = "New Name"

Um die Zeile so zu korrigieren, dass Sie sicherstellen können, dass die For Each-Schleife funktioniert, ändern Sie die Zeile wie folgt.

    myWorksheet.Name = myWorksheet.Name & "-changed"

Anstatt jedem Arbeitsblatt denselben Namen zuzuweisen, ändert diese Zeile den aktuellen Namen jedes Arbeitsblatts (myWorksheet.Name) in den aktuellen Namen plus den Zusatz "-changed".

Sinnvolles Umbenennen

Das Makro nähert sich allmählich einer Lösung an, mit der das aktuelle Problem gelöst werden kann. Was Sie nun brauchen, ist eine Möglichkeit, Informationen den Arbeitsblättern selbst zu entnehmen, insbesondere aus Zelle B1 jedes Arbeitsblatts, und diese Informationen in die Namen der Arbeitsblätter einzufügen.

Anstatt mithilfe der Makroaufzeichnung zu bestimmen, wie auf eine Zelle verwiesen werden soll, wollen wir nun prüfen, ob uns nicht das Cell-Objekt zu Hilfe kommen kann. Netter Versuch, denn wenn Sie die Objektmodellreferenz öffnen und nach dem Cell-Objekt suchen, werden Sie feststellen, dass es kein Cell-Objekt gibt! Es gibt jedoch ein CellFormat-Objekt.

Das Thema zum CellFormat-Objekt enthält im ersten Codebeispiel den folgenden Code.

    ' Set the interior of cell A1 to yellow.
    Range("A1").Select

Es stellt sich heraus, dass das Range-Objekt verwendet wird, um einen Zellbereich oder lediglich eine Zelle anzugeben. Wiederum brauchen Sie nicht den .Select-Abschnitt, doch Sie müssen bestimmen, wie auf den Inhalt des Range-Objekts und nicht auf das Range-Objekt selbst verwiesen wird. Wenn Sie zum Thema des Range-Objekts wechseln, erfahren Sie, dass das Range-Objekt sowohl Methods (Methoden) als auch Properties (Eigenschaften) hat. Der Inhalt eines Range-Objekts ist eine Sache und keine Aktion, weshalb es sich wahrscheinlich um eine Property (Eigenschaft) handelt. Wenn Sie die Liste durchsuchen, gelangen Sie zur Value-Eigenschaft. Versuchen Sie deshalb Folgendes:

Sub RenameWorksheets()
For Each myWorksheet In Worksheets
    myWorksheet.Name = myWorksheet.Range("B1").Value
Next
End Sub

Sie erhalten eine Fehlermeldung, wenn Sie diesen Code auf eine Arbeitsmappe anwenden, die Arbeitsblätter mit leerer Zelle B1 enthalten, da ein leeres Range-Objekt den Wert "" (leere Textzeichenfolge) enthält, was kein zulässiger Arbeitsblattname ist. Es ist ohnehin Zeit, einige Beispieldaten zu erstellen. Fügen Sie dem Arbeitsmappe drei Blätter hinzu, die so wie in der folgenden Abbildung aussehen, und führen Sie anschließend das Makro aus.

Abbildung 4 – Beispieldaten für das RenameWorksheets-Makro

Beispieldaten für das RenameWorksheets-Makro

 

Beispieldaten für das RenameWorksheets-Makro

 

Beispieldaten für das RenameWorksheets-Makro

 

Die Arbeitsblattnamen sollten sich entsprechend ändern.

Suchen nach leeren Zellen

Wie bereits angemerkt, generiert das Makro einen Fehler, wenn eine der B1-Zellen in der Arbeitsmappe leer ist. Anstatt jedes einzelne Arbeitsblatt manuell zu prüfen, können Sie das Makro für diese Aufgabe codieren. Fügen Sie vor der Zeile myWorksheet.Name die folgende Codezeile ein.

If myWorksheet.Range("B1").Value <> "" Then

Und fügen Sie hinter der Zeile myWorksheet.Name den folgenden Text hinzu.

End If

Dies ist eine sog. If…Then-Anweisung. Die If…Then-Anweisung weist Excel an, die Befehle in den Zeilen zwischen der If-Zeile und der End If-Zeile auszuführen, aber nur, wenn die Bedingung in der If-Zeile erfüllt ist. Im Beispiel gibt die folgende Zeile die zu erfüllende Bedingung an.

myWorksheet.Range("B1").Value <> ""

<> bedeutet "ungleich", und die Anführungszeichen ohne Inhalt dazwischen stehen für eine leere Textzeichenfolge, d. h. gar keinen Text. Deshalb werden die Codezeilen zwischen der If-Zeile und der End If-Zeile nur ausgeführt, wenn der Wert in B1 nicht nichts entspricht, d. h. wenn Text in Zelle B1 enthalten ist.

Weitere Informationen zur If…Then-Anweisung finden Sie in der VBA-Sprachreferenz. (Der vollständige Name ist "If…Then…Else statement", wobei Else eine optionale Komponente ist.)

Variablendeklarationen

Eine weitere Verbesserung, die Sie am Makro vornehmen sollten, ist das Setzen einer Deklaration der myWorksheet-Variablen an den Anfang des Makros.

Dim myWorksheet As Worksheet

Dim ist eine Abkürzung von "Dimension", und Worksheet ist der Typ dieser bestimmten Variablen. Diese Anweisung informiert VBA, welche Art von Entität myWorksheet darstellt. Nach der Eingabe von As zeigt der Visual Basic-Editor ein Popupfenster an, in dem alle verfügbaren Variablentypen aufgelistet sind. Dies ist ein Beispiel der IntelliSense-Technologie, d. h. der Visual Basic-Editor reagiert auf das, was Sie versuchen auszuführen, und bietet eine Liste geeigneter Optionen an. Sie können eine Option in der Liste auswählen oder mit der Eingabe fortfahren.

Wenngleich Variablendeklarationen in VBA nicht erforderlich sind, wird ihr Einsatz ausdrücklich empfohlen! Variablendeklarationen erleichtern ungemein die Nachverfolgung Ihrer Variablen und von Fehlern im Code. Achten Sie beim Deklarieren einer Variablen mit einem Objekttyp (z. B. Worksheet), dass IntelliSense eine Liste mit geeigneten zu diesem Objekt gehörenden Eigenschaften und Methoden anzeigt, wenn Sie im weiteren Verlauf des Makros die Objektvariable verwenden.

Kommentare

Das Makro ist nun so komplex, dass es sich empfiehlt, Kommentare hinzuzufügen, die Sie daran erinnern, was der Code macht. Die Anzahl der Kommentare ist zum Teil eine Frage des persönlichen Geschmacks, doch in der Regel sind zu viele Kommentare besser als zu wenige. Ohne Kommentare kann es schwierig sein nachzuvollziehen, was im Code vor sich geht. Das gilt insbesondere dann, wenn die Person, die den Code ändert, nicht der Person entspricht, die den Code ursprünglich geschrieben hat. Das Hinzufügen von Kommentaren für die If-Bedingung und für die Zeile, die die Arbeitsblätter umbenennt, führt zu folgendem Code.

Sub RenameWorksheets()
Dim myWorksheet As Worksheet
For Each myWorksheet In Worksheets
    'make sure that cell B1 is not empty
    If myWorksheet.Range("B1").Value <> "" Then
        'rename the worksheet to the contents of cell B1
        myWorksheet.Name = myWorksheet.Range("B1").Value
    End If
Next
End Sub

Benennen Sie zum Testen des Makros die Arbeitsblätter wieder in Sheet1, Sheet2 und Sheet3 um, und löschen Sie den Inhalt von Zelle B1 auf einem oder mehreren der Arbeitsblätter. Führen Sie das Makro aus, um zu prüfen, ob es die Arbeitsblätter umbenennt, die Text in Zelle B1 haben, und die anderen Arbeitsblätter unverändert lässt. Das Makro funktioniert für eine beliebige Anzahl von Arbeitsblättern mit beliebigen Kombinationen aufgefüllter und leerer B1-Zellen.

Weitere Aufgaben, die Sie mithilfe von VBA erledigen können

In diesem Abschnitt werden weitere Aufgaben vorgestellt, die Sie mit VBA in Excel 2010 erledigen können. Die Beispiele in diesem Abschnitt sollen Ihnen eine Vorstellung der Funktionsmöglichkeiten von VBA vermitteln, ohne dass der Schwerpunkt auf spezifischen praxisbezogenen Szenarien liegt. Bei Durcharbeiten der Beispiele kann es hilfreich sein, die Informationen im Objektreferenzmodell zu den Objekten in den einzelnen Schritten zu überprüfen.

Seien Sie offen

Eine gute Möglichkeit, das Programmieren im Allgemeinen und von Excel VBA im Besonderen zu lernen, ist eine Strategie, bei der Sie etwas ausprobieren, die gestellte Aufgabe lösen und sich anschließend folgende Fragen stellen:

  • Was könnte ich als Nächstes ausprobieren?

  • Was möchte ich bei Verwenden von VBA zuerst erlernen?

  • Was könnte einfach nur Spaß machen, was ist interessant zu wissen?

  • Was macht mich neugierig?

Der Leser wird ausdrücklich ermuntert, alle Möglichkeiten auf dem Weg zum Wissenserwerb auszuprobieren.

Diagramme

Eine gängige Aufgabe in Excel ist das Erstellen eines Diagramms basierend auf einem Zellbereich. Erstellen Sie das Makro AssortedTasks, und geben Sie dann den folgenden Text in den Visual Basic-Editor ein.

Dim myChart As ChartObject

Fügen Sie eine Zeile zum Erstellen des Diagrammobjekts hinzu, und weisen Sie ihm die myChart-Variable zu.

Set myChart = ActiveSheet.ChartObjects.Add(100, 50, 200, 200)

Die Werte in den Klammern bestimmen Position und Größe des Diagramms. Die ersten beiden Werte sind die Koordinaten der linken oberen Ecke, die zweiten beiden Werte geben Breite und Höhe an.

Erstellen Sie ein neues leeres Arbeitsblatt, und führen Sie das Makro aus. Das erstellte Diagramm ist nutzlos, da es keine Daten enthält. Löschen Sie das Diagramm, das Sie gerade erstellt haben, und fügen Sie am Ende des Makros die folgenden Zeilen hinzu.

With myChart
    .Chart.SetSourceData Source:=Selection
End With

Dies ist ein gängiges Muster bei der VBA-Programmierung. Zuerst erstellen Sie ein Objekt, dem Sie eine Variable zuweisen. Anschließend bestimmen Sie in der With…End With-Konstruktion, was mit dem Objekt geschehen soll. Der Beispielcode weist das Diagramm an, die aktuelle Auswahl als Daten zu verwenden. (Selection ist ein Wert für den Source-Parameter der SetSourceData-Methode und kein Wert einer Objekteigenschaft, weshalb die VBA-Syntax anfordert, dass Sie einen Doppelpunkt und ein Gleichheitszeichen (:=) anstatt nur ein Gleichheitszeichen (=) verwenden, um den Wert zuzuweisen.)

Geben Sie verschiedene Werte in die Zellen A1:A5 ein, wählen Sie die Zellen aus, und führen Sie das Makro aus. Das Diagramm wird mit dem Standardtyp (Balkendiagramm) angezeigt.

Abbildung 5 – Mit VBA erstelltes Balkendiagramm

Mit VBA erstelltes Balkendiagramm

Wenn Ihnen das Balkendiagramm nicht gefällt, können Sie es mithilfe von Code wie dem Folgenden in einen anderen Diagrammtyp ändern.

With myChart
    .Chart.SetSourceData Source:=Selection
    .Chart.ChartType = xlPie
End With

xlPie ist ein Beispiel einer vordefinierten Konstante, die auch als Enumerationskonstante bezeichnet wird, von denen es in Excel zahlreiche gibt und die allesamt umfassend dokumentiert sind. Weitere Informationen zu vordefinierten Konstanten finden Sie im Abschnitt "Enumerationen" der Objektmodellreferenz. Die Konstanten für Diagrammtypen sind beispielsweise unter "XlChartType-Enumeration" aufgeführt.

Sie können die Daten ändern. Fügen Sie z. B. diese Zeile direkt hinter der Deklaration der Variablen hinzu.

Application.ActiveSheet.Range("a4").Value = 8

Sie können Eingaben vom Benutzer anfordern und diese Eingaben zum Ändern der Daten nutzen.

myInput = InputBox("Please type a number:")
Application.ActiveSheet.Range("a5").Value = myInput

Fügen Sie schließlich die folgenden Zeilen am Ende des Makros hinzu.

ActiveWorkbook.Save
ActiveWorkbook.Close

Das vollständige Makro sollte etwa folgendermaßen aussehen:

Sub AssortedTasks()
Dim myChart As ChartObject
Application.ActiveSheet.Range("a4").Value = 8
myInput = InputBox("Please type a number:")
Application.ActiveSheet.Range("a5").Value = myInput
Set myChart = ActiveSheet.ChartObjects.Add(100, 50, 200, 200)
With myChart
    .Chart.SetSourceData Source:=Selection
    .Chart.ChartType = xlPie
End With
ActiveWorkbook.Save
ActiveWorkbook.Close
End Sub

Prüfen Sie, ob der Bereich A1:A5 weiter ausgewählt ist, führen Sie das Makro aus, geben Sie einen Wert in das Eingabefeld ein, und klicken Sie dann auf OK. Der Code speichert und schließt die Arbeitsmappe. Öffnen Sie die Arbeitsmappe erneut, und beachten Sie die Änderung am Kreisdiagramm.

UserForm-Formulare

Im vorherigen Abschnitt wurde veranschaulicht, wie mit einem einfachen Eingabefeld Eingaben vom Benutzer angefordert werden können. Zusätzlich zum entsprechenden Meldungsfeld, in dem Informationen angezeigt werden, bietet VBA umfassende Möglichkeiten zum Erstellen benutzerdefinierter Dialogfelder, zum Codieren von Steuerelementen, die unmittelbar unter Arbeitsblättern platziert werden, oder zum Bearbeiten der Standarddialogfelder in Excel. Weitere Informationen zu diesen Möglichkeiten finden Sie unter Steuerelemente, Dialogfelder und Formulare in der Excel 2007-Entwicklerreferenz.

Mit diesem Abschnitt, in dem wir einen schnellen Blick auf UserForm-Formulare werfen, endet diese Einführung in Excel VBA.

Klicken Sie auf der Registerkarte Entwicklertools auf die Schaltfläche Visual Basic, um den Visual Basic-Editor zu öffnen. Wechseln Sie dann zum Menü Einfügen, und wählen Sie UserForm aus, um die UserForm-Entwurfsansicht zu öffnen.

Sie sehen zwei Fenster. Eines enthält das zu erstellende UserForm-Formular, das andere, Werkzeugsammlung, die verschiedenen Steuerelemente, die Sie Ihrem UserForm-Formular hinzufügen können, z. B. Befehls- und Optionsschaltflächen, Kontrollkästchen usw. Sie können die Maus über ein Steuerelement in der Werkzeugsammlung bewegen, um zu bestimmen, welcher Typ von Steuerelement mit ihm erstellt wird.

Erstellen Sie ein UserForm-Formular mit einer einzelnen Schaltfläche, die das weiter oben beschriebene Makro "Hello" ausführt. Klicken Sie in Werkzeugsammlung auf das Steuerelement CommandButton, und ziehen Sie es auf das UserForm-Formular, um eine Befehlsschaltfläche zu erstellen. Klicken Sie mit der rechten Maustaste auf die Befehlsschaltfläche, und wählen Sie Code anzeigen aus.

Das Sub-Element, das Sie sehen, ist der Grundbaustein für eine Ereignisprozedur, die ausgeführt wird, wenn ein bestimmtes Ereignis erfolgt. In diesem Fall ist das Ereignis, das den Code ausführt (wie der Name des Sub-Elements angibt), ein Click-Ereignis auf CommandButton1. Fügen Sie der Ereignisprozedur die folgende Zeile hinzu.

Run("Hello")

Der Visual Basic-Editor muss ähnlich wie die folgende Abbildung aussehen.

Abbildung 6 – Ereignisprozedur "CommandButton1_Click"

CommandButton1_Click-Ereignisprozedur

Speichern Sie die Arbeitsmappe, wechseln Sie zum Menü Fenster, und wählen Sie UserForm1 (UserForm) aus, um das UserForm-Formular erneut anzuzeigen. Klicken Sie auf der Symbolleiste auf den grünen Pfeil, um das UserForm-Formular auszuführen. Wenn das Dialogfeld angezeigt wird, klicken Sie auf die Befehlsschaltfläche, um das Makro "Hello" auszuführen, das das Meldungsfeld "Hello, world!" anzeigt. Schließen Sie das Meldungsfeld, um zum ausgeführten UserForm-Formular zurückzukehren, und schließen Sie dann das ausgeführte UserForm-Formular, um zu Entwurfsansicht zurückzukehren.

Nächste Schritte

Sie werden feststellen, dass die Informationen in diesem Artikel in Kombination mit einem gewissen Experimentieren und einer eingehende Befassung mit der Objektmodellreferenz und der VBA-Sprachreferenz Ihnen genügend Informationen an die Hand geben, um jedwede Aufgabe zu erledigen, die Sie motiviert hat, VBA zu erlernen. Falls ja, großartig! Falls nein, bietet es sich auf jeden Fall an, Ihren Horizont zu erweitern, um ein allgemeineres Verständnis von VBA zu erlangen.

Eine Möglichkeit, mehr über VBA zu erfahren, ist das Studieren von funktionierendem Code. Zusätzlich zu den Beispielen in der Objektmodellreferenz und VBA-Sprachreferenz steht in verschiedenen Onlinequellen überaus viel Excel VBA-Code zur Verfügung, z. B. auf der MSDN-Website, auf Websites von Microsoft Most Valuable Professionals (MVPs), die sich auf Excel spezialisiert haben, sowie auf anderen Websites, die Sie über eine schnelle Suche im Internet ausfindig machen können.

Mithilfe des Codes in diesen Quellen können Sie Ihre unmittelbaren Codierungsprobleme lösen. Außerdem erhalten Sie Ideen für Projekte, an die Sie ggf. noch nicht gedacht haben.

Wenn Sie sich eher systematisch mit VBA auseinandersetzen möchten, gibt es diverse nützliche Bücher zu VBA, zu denen Sie Besprechungen im Internet finden, damit Sie das Buch wählen können, das Ihrem Lernstil entspricht.

Weitere Ressourcen