Excel VBA: Unterschied zwischen den Versionen

Version vom 28. März 2016, 14:16 Uhr

VBA

• VBA = Visual Basic for Applications
• Basiert auf der objektorientierten Programmiersprache Visual Basic von Windows
• Spezielle Programmiersprache für MS Office
• Automatisierung von Sub-Routinen und Funktionen für Excel, die nach dem Eigenbedarf angepasst werden können
• VBA Code kann Objekte verändern

VBA-Editor

Screenshot des VBA-Editors in Excel unter Windows

Screenshot des VBA-Editors in Excel unter Mac OSX

Der VBA-Editor kann über verschiedene Wege aufgerufen werden. Der schnellste erfolgt jeweils über die Variante mit den Shortcuts, unabhängig vom Betriebssystem.

Shortcuts

Windows:
MS Excel starten » im geöffneten Excel die Tastenkombination "Alt + F11" drücken

Mac:
MS Excel starten » im geöffneten Excel die Tastenkombination "fn + alt + F11" drücken
Problem: In der Office 365 Version wurde der VBA-Editor um einige elementare Funktionen reduziert.
Laut MS soll aber in der Office 2016 Version der VBA-Editor wieder vollständig implementiert sein.

Registerkarte "Entwicklungstools"

Bei sämtlichen Produkten von Office 2010 steht das Register "Entwicklungstools" zur Verfügung. Standardmäßig ist dieses deaktiviert. Um es zu aktivieren, muss man folgende Schritte unternehmen:

Windows:

1. Register "Datei" » Optionen » Dialogfeld "Excel-Optionen" wird geöffnet
2. Auf die linke Seite des Dialogfelds "Menüband anpassen" klicken
3. Auf der rechten Seite des Dialogfelds in der Dropdown-Liste "Befehle auswählen", "Häufig verwendete Befehle" in der Liste auswählen
4. Auf der rechten Seite des Dialogfelds einen Haken bei "Entwicklungstools" setzen.
5. Mit "OK" bestätigen.
   

Mac:

1. Menüleiste "Excel" » Einstellungen ... » Ansicht » Dialogfeld "Ansicht" wird geöffnet
2. Unten im Dialogfeld einen Haken bei "Registerkarte Entwickler" setzen
3. Fenster schließen
   

Programmierungssyntax

Sub-Routinen / Funktionen

Einen VBA-Code den man im VBA-Editor schreibt, bezeichnet man als Prozedur. Gängige Prozeduren hierbei sind "Sub" und "Function". Die meisten Makros die man mit VBA realisiert sind Sub-Prozeduren.

Sub

Eine Gruppe von VBA-Anweisungen, die eine (oder mehrere) Aktionen in Excel durchführen (= Ausführung eines Befehls). Welche Aktion durchgeführt wird, hängt vom programmierten Sub-Code ab.

Die Initialisierung einer Sub-Prozedur erfolgt mit dem Schlagwort "Sub" und endet mit "End Sub". Hier ist ein Codebeispiel:

 Sub TextMitteilung()
 MsgBox “Hallo Welt! Dies ist eine Textmitteilung.”
 End Sub

Function

Eine Gruppe von VBA-Anweisungen, die eine Berechnung anhand übergebener Argumente durchführt und einen einzelnen Wert liefert.

Jede Funktion startet mit dem Schlagwort "Function" und endet mit "End Function". Anbei ein Codebeispiel:

Function Quadratwurzel(Zahl) 
Quadratwurzel = Zahl ^ (1 / 3)
End Function

Variablen und Konstanten

Variablen sind Daten, die sich während des Organisationsablauf im Programm verändern können. Diese müssen mit einer zweistufigen Methode zunächst deklariert und anschließend initialisiert werden. Bei Konstanten erfolgt dies mittels einstufiger Methode.

Wichtig hierbei ist, dass VBA zwischen "expliziter" und "impliziter" Variablendeklaration unterscheidet:

• explizite Deklaration: Festlegung der Variable vor ihrem Einsatz
• implizite Deklaration: Variable wird bei ihrer ersten Verwendung in der Prozedur automatisch erzeugt (= fehleranfällig; erschwert das debuggen)
  

Soll eine Variable ihren Wert niemals verändern (bspw. physikalische Größen wie Kelvin, Erdbeschleunigung, o.ä.), nutzt man den Einsatz von Konstanten.

Zur einfachen Handhabung empfiehlt es sich daher, Variablen vor dem Einsatz genau zu definieren:

Um eine Variablendeklaration zu erzwingen, kann man den Befehl Option Explicit in der ersten Zeile am Modulanfang hinzufügen. Der VBA-Code wird solange nicht ausgeführt bzw. gibt einen Fehlermeldung aus, bis alle Variablen korrekt deklariert sind.

Benennung von Parametern

Für die Benennung von Parametern wie bspw. Variablen, Konstanten, Sub und Functions, muss folgendes beachtet werden:

• Sie können aus Buchstaben, Zahlen und einigen Satzzeichen bestehen. Das erste Zeichen muss allerdings aus einem Buchstaben bestehen.
• Es dürfen keine Punkte oder Leerzeichen im Namen verwendet werden.
• Visual Basic for Applications unterscheidet nicht zwischen Groß- und Kleinschreibung.
• Folgende Zeichen können nicht verwendet werden: #, $, %, &, @, ^, *, !
• Wenn eine Function in einem Formular vorgesehen ist, sollte sie nicht wie eine Zelladresse (bspw. AC12) benannt werden. Excel erlaubt zwar diese Benennung, aber man muss sich nicht unnötig verwirren lassen.
• Eine Zeichenkette darf nicht länger als 255 Zeichen lang sein.

Zuweisungen

Wert = 10+11*12/13
Text = “Hallo” & “Welt”

Formula1 := “$C$3”
FileName := ”Exceldateiname.xls"

Set Auswahl = Selection
Set Dok1 = ActiveDocument

Gültigkeitsbereiche von Variablen

Mit dem Schlagwort Dim werden Variablen festgelegt, die nur innerhalb einer Prozedur/Funktion laufen sollen.

Legt man Variablen im Deklarationsteil eines Moduls fest, im Modulkopf, vor der ersten Funktion/Prozedur, so sind diese Variablen global verfügbar.

Durch das Schlagwort Public werden Variablen festgelegt, die über alle Module hinweg verfügbar sein sollen.

Werden Variablen nicht deklariert, so sind diese nur standardmäßig lokal gültig.

Modulkopf

Public Variable As Integer

Dim Variable As Integer

Private Variable As Integer

Prozedur/Funktion

Dim Variable As Integer

Static Variable As Integer

Arrays / Collections

Arrays

Wie bei anderen Programmiersprachen üblich, können Variablen in Form von Feldern, sogenannten Arrays, zusammengefasst werden. Hierbei werden Elemente durch einen fortlaufenden Index unterschieden und angesprochen. Elemente können verschiedene Werte/Datentypen in einem Array speichern (siehe Beispielcode).

Obwohl die Größe des Arrays nachfolgend im Beispiel mit 5 indiziert ist, können 6 Werte im Array gespeichert werden, da ein Array immer bei 0 anfängt. Der Array-Index kann niemals negativ sein.

Deklaration und Aufruf eines Arrays in einer Mitteilungsbox mit Hilfe eines Buttons:

Private Sub Array_Output_MsgBox_Click()
 array(5) As Variant
 
 array(0) = "21"                  'Zahl als String
 array(1) = 0.25                  'Dezimalzahl
 array(2) = #03/26/2016#          'Datum
 array(3) = #19.20 PM#            'Uhrzeit
 array(4) = "BSB"                 'String
 array(5) = 123                   'Zahl
 
 msgbox("Element in Array bei index = 0 : " & array(0))
 msgbox("Element in Array bei index = 1 : " & array(1))
 msgbox("Element in Array bei index = 2 : " & array(2))
 msgbox("Element in Array bei index = 3 : " & array(3))
 msgbox("Element in Array bei index = 4 : " & array(4))
 msgbox("Element in Array bei index = 5 : " & array(5))
End Sub

Mehrdimensionale Arrays

Arrays sind nicht auf eine einfache Dimension limitiert. Das Maximum liegt bei 60 Dimensionen. Zweidimensionale Arrays sind die mit am häufigst verwendeten:

Private Sub Array_Output_MsgBox_Click()
 Dim array(2,3) As Variant	' Das Array besitzt 3 Zeilen und 4 Spalten
  
 array(0,0) = "Muskat" 
 array(0,1) = "Zimt"
 array(0,2) = "Borretsch"           
 array(0,3) = "Apfel" 
 array(1,0) = "Orange"           
 array(1,1) = "Kirsche"           
 array(1,2) = "Yuzu"           
 array(1,3) = "Himbeere"           
 array(2,0) = "Tencha"             
 array(2,1) = "Mohn"            
 array(2,2) = "Tonkabohne"             
 array(2,3) = "Thymian"            
 
 msgbox("Element in Array bei Index = 0,1 : " &  array(0,1))
 msgbox("Element in Array bei Index = 2,2 : " &  array(2,2))
End Sub

Collections

Konvertierung/Manipulation von Daten

Datentypen

Die Deklaration der Datentypen kann auch in einer verkürzten Form vorgenommen werden. Hierzu verwendet man das jeweilige Symbol des Datentyps. Die Bezeichnung "As Datentyp" entfällt.

Dim Variable As Integer, Variable2 As String, Variable3 As Single

Dim Variable%, Variable2$, Variable3!

Operatoren

Kontrollstrukturen

Wie bei allen gängigen Progammiersprachen üblich, besitzt Visual Basic for Applications ebenfalls eine Auswahl an Kontrollstrukturen. Diese sind nachfolgend tabellarisch aufgelistet.

Bedingungen

If Bedingung_erfuellt 
Then Anweisung1 
Else Anweisung2 
End If

If Bedingung_erfuellt Then Anweisung1 
ElseIf Bedingung_erfuellt Then Anweisung 2
Else Anweisung3 
End If

IIf (Bedingung_erfuellt, Anweisung 1, Anweisung 2)

Select Case Variable 
Case Ausdruck 
  Anweisung1
Case Else Anweisung2
End Select

Switch (Bedingung1, Ausdruck1, Bedingung2, Ausdruck2, ... ,Bedingung_n,Ausdruck_n)

Choose (Index, Ausdruck1, Ausdruck2, ... , Ausdruck_n)

Schleifen

While Bedingung_erfuellt
 Anweisung
End While

Do While Bedingung_erfuellt
 Anweisung
Loop

Do Until Bedingung_erfuellt
 Anweisung
Loop

Do
 Anweisung
Loop While Bedingung_erfuellt

Do
 Anweisung
Loop Until Bedingung_erfuellt

For i = Startwert To Endwert Step Schrittweite
 Anweisung
Next i

For Each Objekt In Auflistung
 Anweisung
Next

With Objekt 
 Anweisung
End With

Objekte

Anbei sind hier die wichtigsten Objekte aufgelistet. Für weitere Objekte bedient man sich der Online verfügbaren Referenz von Objekten auf der Microsoft Developer Network Webseite: Microsoft Developer Network

Ein- und Ausgabeanweisungen

Für die Interaktion mit dem Benutzer ist es erforderlich, mit diesem zu kommunizieren. Dies erfolgt über die Input- bzw. Mitteilungsbox von VBA.

InputBox

Die InputBox ist ein Dialogfeld für die Benutzereingabe, die sämtliche Informationen wiedergibt, die in das Eingabefeld übergeben wurden.

Sie kann sowohl als Funktion, als auch als Methode innerhalb von VBA verwendet werden:

InputBox(Prompt, Title, Default, Left, Top)

Object.InputBox(Prompt, Title, Default, Left, Top, HelpFile, HelpContextID, Type)

Werden nicht alle Parameter benötigt, so können diese mit einem Leerzeichen ausgelassen werden:

Object.InputBox("Mitteilungsaufforderung an den Benutzer", "Eingabeaufforderung", , , , , , 1)

Wenn man nur spezifische Parameter unabhängig von der festgelegten Reihenfolge übergeben bzw. nicht den kompletten Satz an Parametern ausfüllen will, so bedient man sich der Parameterübergabe in Prozeduren ":=":

Object.InputBox(Type:=1, Prompt:="Mitteilungsaufforderung an den Benutzer", Title:="Eingabeaufforderung")

Parameter

VBA InputBox Video-Tutorial von Andreas Thehos
https://www.youtube.com/watch?v=-e0IfPyyxck

MsgBox

Die Mitteilungsbox dient der Wiedergabe von einfachen Informationen (Status- oder Warnmeldungen) und Ergebnissen. Des Weiteren stellt diese auch Schaltflächen, sog. Buttons, zur Verfügung, die den weiteren Prozessablauf steuern können.

'Deklaration der MsgBox erfolgt ähnlich wie bei der InputBox
MsgBox (Prompt, Buttons, Title, Helpfile, Context)
 
'Codebeispiel mit alphanumerischen Parametern "Buttons"
MsgBox ("Mitteilung", vbYesNoCancel + vbInformation + vbDefaultButton1, "Information", , )
 
'Codebeispiel mit numerischen Parametern "Buttons"
MsgBox ("Mitteilung", 3 + 64 + 0, "Information", , )

Bei dem Parameter "Buttons" muss entweder eine Zahl oder eine Konstante für die Definition angeben werden. Eine Kombination erfolgt mit "+":

VBA MsgBox Video-Tutorial von Andreas Thehos
https://www.youtube.com/watch?v=ssei8pEt2rM

Range Objekt

Makro

Add-In

Bibliographie

Die hier aufgeführten Informationen und Codeausschnitte zu diversen Anwendungsfällen wurden den unten aufgeführten Quellen entnommen oder angelehnt und dem Wikiverlauf entsprechend zusammengestellt.

Literaturverzeichnis

Abulawi, J. (2005): "Einführung in die Excel-Makroprogrammierung", unter https://www.haw-hamburg.de/pers/Abulawi/ExcelmakrosSS05.pdf (abgerufen am 18.03.2016).

Bürschgens, M. (2011): "Einstieg in VBA und die Officeprogrammierung am Beispiel von Excel", unter https://www.matse.itc.rwth-aachen.de/dienste/public/show_document.php?id=7946 (abgerufen am 18.03.2016).

Friedrich, S., Klein, A. (2005): "Excel 2003 Progammierung mit Visual Basic", unter http://www.merkwerk.de/files/exvba.pdf (abgerufen am 18.03.2016)

Gogolok, J. (1999): "VBA-Programmierung mit Excel", unter ftp://ftp.fernuni-hagen.de/pub/pdf/urz-broschueren/broschueren/b012.pdf (abgerufen am 18.03.2016).

Kämper, S. (2009): Grundkurs Programmieren mit Visual Basic, Wiesbaden, Vieweg+Teubner.

Shichang, W., Schmidt, W. (2015): Berechnungen in der Chemie und Verfahrenstechnik mit Excel und VBA, Weinheim, Wiley-VCH.

Internetverzeichnis

o.A. (o.J.): „Objektmodell (VBA-Referenz für Excel)“, unter: https://msdn.microsoft.com/de-de/library/office/ff194068.aspx (abgerufen am 26.03.2016).

o.A. (o.J.): „Schnellstart: Aktivieren und Verwenden eines Add-Ins“, unter: https://support.office.com/de-de/article/Schnellstart-Aktivieren-und-Verwenden-eines-Add-Ins-2df59054-a46f-4fe4-bcb5-92ea461c315f (abgerufen am 25.03.2016).

o.A. (o.J.): „VBA Tutorial“, unter: http://www.tutorialspoint.com/vba/ (abgerufen am 26.03.2016).

Chinowsky, B. (2009): „Erste Schritte mit VBA in Excel 2010“, unter: https://msdn.microsoft.com/de-de/library/office/ee814737(v=office.14).aspx (abgerufen am 25.03.2016).

- eigene Funktion erstellen - Add Ins erstellen