Dr. Erhard Henkes   Stand: 08.07.2015

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. Nach dem Start sieht man zumindest eine "Tabelle" (worksheet).

Nun wollen wir den "Editor" starten, um Quellcode (Sourcecode) eingeben zu können.


Es gibt verschiedene Wege die Entwicklungsumgebung (development environment) zu starten. Ich empfehle: Alt + F11 (geht am schnellsten und bleibt über die Office-Versionen gleich)

In der Auflistung der Excel Objekte wählen Sie die erste Tabelle (worksheet) und wählen mit Rechtsklick "Code anzeigen".
Danach öffnet sich der Code-Editor. Dort wählen wir den Code für das Objekt Worksheet und das Ereignis SelectionChange:



Der Editor (rechts) ist so aufgebaut, dass wir Objekte und ihre zugeordneten Elemente auswählen können. Der Code des Elements "Selection_Change" tritt in Aktion, wenn wir eine andere Zelle auswählen. Hier können wir nun Sourcecode im Visual Basic-Stil eintragen. Objekte und zugehörige Elemente (Event, Funktionen, Property) findet man im Objektkatalog (F2). 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 für das ausgewählte Ereignis schreiben und testen:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   
    Dim HelloMsg  'speichert Text
    HelloMsg = "Selection has" & Chr(13) & "changed."
    Application.Speech.Speak "Selection has changed"
    MsgBox HelloMsg, vbOKOnly + vbInformation, "Information"

End Sub

Fügen Sie dies oben im Editor ein. Mit einer MessageBox kann man zumeist wenig verderben, und tatsächlich, nach der Auswahl einer neunen Zelle wird das Event Selection_Change gesendet und die von uns mit Code gefüllte VBA-Funktion ausgeführt:



MsgBox ist ein gutes Hilfsmittel, um während der Entwicklung oder auch dem Anwender während der Programmausführung Informationen zu liefern. Hier sehen wir z.B., dass unsere Funktion tatsächlich ausgeführt wird. Mit Dim wird zunächst eine Varibale definiert. Dieser wird dann ein Textstring zugeordnet, der dann mittels der Variable als Parameter in MsgBox ausgegeben wird.

Noch viel mehr. Wir erhalten mit
Application.Speech.Speak zusätzlich eine kleine Sprachausgabe (die Standardsprachqualität ist wahrscheinlich miserabel). Sie sehen hier auch, wie man einen String aus mehreren Substrings im VBA Stil zusammen baut. VBA liefert viele vorgefertigte Werte wie z.B. vbOKOnly usw., die man sich vielleicht leichter merken kann als eine nicht sprechende Zahl. 

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. Der Makrorecorder arbeitet nach einem vorgegebenen Schema, das ziemlich "vollgemüllten" Code liefert. Man sieht aber, welche Objekte bei bestimmten Abläufen eine Rolle spielen und kann dann bei diesen sich die Events, Porperties und Funktionen anschauen. Im Internet findet man ausreichend Hilfe und Beispiele.

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.

Bein unserem ersten Versuch haben wir dieses "As String" nicht angefügt. Darum kümmert sich dann VBA selbst beim Kompilieren. man sollte das aber besser selbst erledigen, wenn  man es sicher weiß. daher ergänzen wir unseren ersten Code mit "As String": Dim HelloMsg As String 'speichert Text


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 D 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:




Nun probieren Sie noch folgende Variante: ActiveSheet.PasteSpecial Paste:=xlPasteFormats  anstelle  ActiveSheet.Paste

Das wirkt natürlich nur, wenn man in den Tabellen 1-3 den zu kopierenden Zellen ein gewisses Format, z.B. Schrift- oder Hintergrundfarbe verpasst.

wird fortgesetzt