VBA

Dr. Erhard Henkes Stand: 29.07.2008

VBA-Programmierung für MS Excel

Entwicklungsumgebung

VBA steht für "Vicual Basic for Applications". Früher nannte sich dies Excel-Makros. Also benötigen wir zunächst eine Anwendung (application). Wir verwenden MS Excel. Also starten wir eine neue MS Excel "Mappe" mit den standardisiert eingestellten drei "Tabellen". Nun wollen wir den "Editor" starten, um Quellcode (Sourcecode) eingeben zu können.

Es gibt verschiedene Wege die Entwicklungsumgebung (development environment) zu starten:
1) Alt + F11 (geht am schnellsten)
2) Menü - Extras - Makro - Visual Basic-Editor (lässt sich vielleicht besser merken)

Danach öffnet sich die VBA-Entwicklungsumgebung. Wir sehen die Unterteilung der Excel-Objekte in eine Arbeitsmappe (Workbook) mit drei Tabellen (Worksheet):

Der Editor (rechts) ist so aufgebaut, dass wir Objekte und ihre zugeordneten Elemente auswählen können. Das Element Workbook_Open()greift z.B., wenn wir die Arbeitsmappe öffnen. Hier können wir nun Sourcecode im Visual Basic-Stil eintragen. Objekte und zugehörige Elemente (Event, Funktionen, Property) findet man im Objektkatalog (F2). Wir nehmen als Beispiel die Klasse Workbook und das Ereignis (Event) Open:

Schauen Sie sich hier ein wenig um. Hier können Sie für jede Klasse die zugehörigen Elemente einsehen.

Nun wollen wir ein kleines Programm schreiben:

Private Sub Workbook_Open()
MsgBox ("Mappe wurde geöffnet.")
End Sub

Mit einer MessageBox kann man zumeist wenig verderben, und tatsächlich, nach dem Speichern/Schließen und Wiederöffnen der Excel-Mappe wird das Event Workbook.Open gesendet und die Funktion Workbook_Open() ausgeführt:

MsgBox(...) ist damit ein gutes Hilfsmittel, um uns während der Entwicklung oder auch dem Anwender während der Programmausführung Informationen zukommen zu lassen. Hier sehen wir beispielsweise, dass unsere Funktion Workbook_Open() tatsächlich ausgeführt wird.

Nun gehen wir ein wenig weiter:

Private Sub Workbook_Open()
Dim HelloMsg 'speichert den Text für die Nachricht
HelloMsg = "Hallo Welt!"
MsgBox HelloMsg, vbOKOnly + vbCritical, "Begrüßungsfeld"
End Sub

Hier wird mit Dim (kommt von Dimension) zunächst eine Varibale definiert. Dieser wird dann ein Textstring zugeordnet, der dann mittels der Variable als Parameter in MsgBox ausgegeben wird. Nachfolgend einige Werte für den Parameter VbMsgBoxStyle der Funktion MsgBox(...):

Buttons
vbOKOnly	0	nur OK (default)
vbOKCancel	1	OK, Abbrechen
vbAbortRetryIgnore	2	Abbrechen, Wiederholen,Ignorieren
vbYesNoCancel	3	Ja, Nein, Abbrechen
vbYesNo	4	Ja, Nein
vbRetryCancel	5	Wiederholen, Abbrechen
Symbole
vbCritical	16	kritischer Hinweis
vbQuestion	32	Fragezeichen
vbExclamation	48	Warnung
vbInformation	64	Information

Lernen mit Makros

Der Makrorecorder bietet eine ideale Möglichkeit, direkt mit einfachen von Excel selbst generierten Beispielen zu lernen. Hierzu startet man den Makrorecorder, führt einige Aktionen durch, stoppt den Makrorecorder und schaut sich das Ergebnis in der VBA-Entwicklungsumgebung an. Man kann dann mittels Variationen weitere Aktionen in gang bringen. Nehmen wir ein kleines Beispiel. Wir starten den Recorder (Extras - Makro - Aufzeichnen), geben in die Zellen A1 bis A4 die Buchstaben a,b,c,d ein und bleiben in der Zelle A5 mit der Auswahl stehen, ohne ein weiteres Zeichen einzugeben. Als Makro-Tastenkombination wählen wir Strg-b. Dies führt zu folgendem Sourcecode:

Sub Makro1()
'
' Makro1 Makro
' Makro am ... von ... aufgezeichnet
'
' Tastenkombination: Strg+b
'
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "a"
    Range("A2").Select
    ActiveCell.FormulaR1C1 = "b"
    Range("A3").Select
    ActiveCell.FormulaR1C1 = "c"
    Range("A4").Select
    ActiveCell.FormulaR1C1 = "d"
    Range("A5").Select
End Sub

Der Makrorecorder arbeitet nach einem vorgegebenen Schema, das ziemlich "vollgemüllten" Code liefert. Ändern Sie im Editor das Makro wie folgt ab:

Sub Makro1()
'
' Tastenkombination: Strg+b
'
    Range("A1").FormulaR1C1 = "a"
    Range("A2").FormulaR1C1 = "b"
    Range("A3").FormulaR1C1 = "c"
    Range("A4").FormulaR1C1 = "d"
    Range("A5").Select
End Sub

Wie man sieht, ist "Select" und "ActiveCell" überflüssig. Nun läuft das Programm schneller. "FormulaR1C1" ist hier ebenfalls nicht nötig, da wir keine Formel eingeben, sondern feste Werte. Dafür verwendet man "Value":

Sub Makro1()
'
' Tastenkombination: Strg+b
'
    Range("A1").Value = "a"
    Range("A2").Value = "b"
    Range("A3").Value = "c"
    Range("A4").Value = "d"
    Range("A5").Select
End Sub

"Value" kann man übrigens ebenfalls weglassen, da es default ist.

Sub Makro1()
'
' Tastenkombination: Strg+b
'
    Range("A1") = "a"
    Range("A2") = "b"
    Range("A3") = "c"
    Range("A4") = "d"
    Range("A5").Select
End Sub

Nun sieht das Programm besser aus.

Möchten Sie die Tastenkombination abändern, so wählen Sie Extras - Makros -Makro (Alt F8) und dort Optionen:

Einfache Programme selbst schreiben

Mit Hilfe der Input- und Message-Box kann man bereits einfache Abläufe testen. Ein Beispiel:

Mit Dim name As String erzeugt man eine Stringvariable mit der Bezeichnung "name". Diese Variable kann das Ergebnis der InputBox aufnehmen. Die MessageBox (MsgBox) gibt den String anschließend direkt aus.

Interessanter wird dies in Verbindung mit einer For-Next-Schleife, die die eingegebenen Daten nacheinander in die Tabelle schreibt:

Hier fügen wir die Zieladresse als String aus "a" und der Schleifenvariable zusammen. Damit können Sie Eingaben auf einfache Weise an den vorgesehenen Ort transferieren.

Selektieren oder Referenzieren?

Irgendwie fühlen sich Anfänger - angeleitet durch den ständig sinnlosen Ballast produzierenden Makrorekorder - in gewisser Weise wohl, wenn sie "selektieren" können. Dies ist aber absolut nicht nötig! Man kann auch direkt "referenzieren".

Schauen wir die verschiedenen Vorgehensweisen konkret an:

Wir haben drei Tabellen in einer gemeinsamen Mappe, die jeweils an der gleichen Stelle "B6:E6" Daten aufweisen, die wir in einer neuen Tabelle "Aggregation" zeilenweise anzeigen wollen. Zusätzlich sollen die Namen der Tabellen voran und die Bezeichnungen A bis E darüber gestellt werden.

Hier unser Code:

Sub Aggregation()

    Sheets("Aggregation").Select
    Range("B5") = "A"
    Range("C5") = "B"
    Range("D5") = "C"
    Range("E5") = "D"

    'Selektierer
    Sheets("Tabelle1").Select
    Range("B6:E6").Select
    Selection.Copy
    Sheets("Aggregation").Select
    Range("B6").Select
    ActiveSheet.Paste
    Range("A6") = Sheets("Tabelle1").Name

    'Referenzierer
    Worksheets("Tabelle2").Range("B6:E6").Copy Worksheets("Aggregation").Range("B7")
    Worksheets("Aggregation").Range("A7") = Sheets("Tabelle2").Name

    'Selektierer mit etwas verkürzter Variante (Mischung aus beiden Stilen)
    Sheets("Tabelle3").Select
    Range("B6:E6").Select
    Selection.Copy Worksheets("Aggregation").Range("B8")
    Worksheets("Aggregation").Range("A8") = Sheets("Tabelle3").Name

    Worksheets("Aggregation").Range("B5:E5").HorizontalAlignment = xlRight
    Worksheets("Aggregation").Activate

End Sub

Dies ist das Ergebnis:

wird fortgesetzt