Dieses Wiki, das alte(!) Projektwiki (projektwiki.zum.de)
wird demnächst gelöscht.
Bitte sichere Deine Inhalte zeitnah,
wenn Du sie weiter verwenden möchtest.
Gerne kannst Du natürlich weiterarbeiten
im neuen Projektwiki (projekte.zum.de).Excel VBA
Inhaltsverzeichnis |
VBA
- VBA = Visual Basic for Applications
- Basiert auf der 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
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:
- Register "Datei" » Optionen » Dialogfeld "Excel-Optionen" wird geöffnet
- Auf die linke Seite des Dialogfelds "Menüband anpassen" klicken
- Auf der rechten Seite des Dialogfelds in der Dropdown-Liste "Befehle auswählen", "Häufig verwendete Befehle" in der Liste auswählen
- Auf der rechten Seite des Dialogfelds einen Haken bei "Entwicklungstools" setzen.
- Mit "OK" bestätigen.
Mac:
- Menüleiste "Excel" » Einstellungen ... » Ansicht » Dialogfeld "Ansicht" wird geöffnet
- Unten im Dialogfeld einen Haken bei "Registerkarte Entwickler" setzen
- 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:
| Einsatz | Syntax |
| Lokale Variable |
Dim [Name] As [Datentyp] |
| Modul | [Name] As [Datentyp] |
| Konstante | Const [Name] As [Datentyp] = [Wert] |
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übergabe
|
Wert = 10+11*12/13 Text = “Hallo” & “Welt” |
| Parameterübergabe in Prozeduren
|
Formula1 := “$C$3”
FileName := ”Exceldateiname.xls" |
| Zuweisung eines Objektverweises
|
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
| Deklaration | Bekanntheit der Variable | Lebensdauer |
|---|---|---|
Public Variable As Integer |
in allen Modulen | bis zum Neustart des Moduls |
Dim Variable As Integer |
nur im aktuellen Modul | |
Private Variable As Integer |
Prozedur/Funktion
| Deklaration | Bekanntheit der Variable | Lebensdauer |
|---|---|---|
Dim Variable As Integer |
nur in der aktuellen Prozedur/Funktion | bis zum Ende der Prozedur/Funktion |
Static Variable As Integer |
bis zum Neustart des Moduls |
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
Collections
Datentypen
| Übersicht | ||
| Datentyp | Wertebereich und Bedeutung | |
| Boolean | Wahrheitswert: "True" oder "False" | |
| Byte | Ganze Zahl: 0 bis 255 | |
| Integer | Ganze Zahl: -32.768 bis 32.767 | |
| Long | Ganze Zahl: -2,1*109 bis 2,1*109 | |
| Double | Gleitkommazahl mit einfacher Genauigkeit: negative Werte: ca. -3,4*1038 bis ca. -1,4*10-45 | |
| Date | Datumangabe zw. 1. Januar 100 bis 31. Dezember 9999 | |
| Object | Verweis auf ein Objekt in der Anwendung | |
| String | Zeichenkette mit variabler Länge | |
| Variant | Datentyp nicht explizit festgelegt (=universell) | |
Operatoren
| Übersicht | |
| Arithmetisch |
+ (Addition) |
| Addition von Zeichenketten |
+ (Verkettung von (alpha-)numerischen Inhalten zu einer neuen Zeichenkette. Kann bei numerischen Ausdrücken schnell falsch verwendet werden (statt Verknüpfung findet eine Addition statt!) |
| Vergleichsoperatoren |
= (Gleich) |
| Logische Operatoren |
and (Und: Das Ergebnis ist wahr, wenn beide Ausdrücke wahr sind) |
Kontrollstrukturen
Wie bei allen gängigen Progammiersprachen üblich, besitzt Visual Basic for Applications ebenfalls eine Auswahl an Kontrollstrukturen. Diese sind nachfolgend tabellarisch aufgelistet.
Bedingungen
| Syntax | Beschreibung |
|---|---|
If Bedingung_erfuellt Then Anweisung1 Else Anweisung2 End If |
Fallunterscheidung in ihrer Standardform |
If Bedingung_erfuellt Then Anweisung1 ElseIf Bedingung_erfuellt Then Anweisung 2 Else Anweisung3 End If |
Fallunterscheidung Alternative |
IIf (Bedingung_erfuellt, Anweisung 1, Anweisung 2) |
Kurzform der Standard-Fallunterscheidung |
Select Case Variable Case Ausdruck Anweisung1 Case Else Anweisung2 End Select |
Die ausgeführte Anweisung ist abhängig vom jeweiligen Wert des Ausdrucks. |
Switch (Bedingung1, Ausdruck1, Bedingung2, Ausdruck2, ... ,Bedingung_n,Ausdruck_n) |
Eine Auflistung von n Bedingungen wird ausgewertet und liefert den jeweiligen n Ausdruck. |
Choose (Index, Ausdruck1, Ausdruck2, ... , Ausdruck_n) |
Der ganzzahlige Indexwert gibt die Position eines Ausdrucks innerhalb einer Liste wieder und liefert den zugehörigen Ausdruck in Abhängigkeit vom Wert. Die Liste kann maximal 1 bis 29 Ausdrücke enthalten. Werte < 1 oder >29 geben den Ausdruck "#Value!" wieder. |
Schleifen
| Syntax | Beschreibung |
|---|---|
While Bedingung_erfuellt Anweisung End While |
Schleifenstruktur in ihrer Standardform |
Do While Bedingung_erfuellt Anweisung Loop |
Pre-Check Schleife: Überprüfung vor Durchführung der Anweisungen solange die Bedingung wahr ist.
Mit Exit Do wird die Anweisung verlassen. |
Do Until Bedingung_erfuellt Anweisung Loop |
Pre-Check Schleifen: Überprüfung vor Durchführung der Anweisungen bis die Bedingung wahr ist.
Mit Exit Do wird die Anweisung verlassen. |
Do Anweisung Loop While Bedingung_erfuellt |
Post-Check Schleifen: Überprüfung nach Durchführung der Anweisungen solange die Bedingung wahr ist.
Mit Exit Do wird die Anweisung verlassen. |
Do Anweisung Loop Until Bedingung_erfuellt |
Post-Check Schleifen: Überprüfung nach Durchführung der Anweisungen bis die Bedingung wahr ist.
Mit Exit Do wird die Anweisung verlassen. |
For i = Startwert To Endwert Step Schrittweite Anweisung Next i |
Indizierte Schleife: Anweisungen werden n-mal durchlaufen (abhängig vom Start- und Endwert). Die Angabe der Schrittweite "Step" ist obligatorisch und beträgt standardmäßig den Wert 1.
Mit Exit For kann die Anweisung bzw. Schleife frühzeitig verlassen werden. |
For Each Objekt In Auflistung Anweisung Next |
Array/Auflistungsschleife: Für jedes Objekt in der Auflistung werden Anweisungen durchlaufen.
Mit Exit For kann die Anweisung bzw. Schleife frühzeitig verlassen werden. |
With Objekt Anweisung End With |
Eine Schleife mit einmaligem Durchlauf. Manipuliert dabei in Abhängigkeit von der Anweisung, die Eigenschaften eines Objektes, ohne dass der Objektname erneut angegeben werden muss. |
Objekte
Objektreferenz: Microsoft Developer Network
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).
Shichang, 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
