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 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
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:
- 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.
- Menüleiste "Excel" » Einstellungen ... » Ansicht » Dialogfeld "Ansicht" wird geöffnet
- Unten im Dialogfeld einen Haken bei "Registerkarte Entwickler" setzen
- Fenster schließen
VBA 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".
Wird die Funktion global ("public") deklariert, so kann man diese in der Funktionsleiste von Excel aufrufen, ihr ein oder mehrere Argumente übergeben und Berechnungen durchführen lassen.
Berechnung der Quadratwurzel mit der Übergabe eines Arguments | Berechnung des Funktionswertes einer quadratischen Funktion mit mehreren Argumenten |
---|---|
Public Function Quadratwurzel(x) 'Abs() liefert den absoluten Werten einer Zahl xAbs = Abs(x) Quadratwurzel = xAbs ^ (1 / 3) End Function |
Public Function QuadrFunkt(x, a, b, c) QuadrFunkt = (a * (x ^ 2)) + b * x + c 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:
Sub Array_Click() Dim BspArray(5) As Variant BspArray(0) = "21" 'Zahl als String BspArray(1) = 0.25 'Dezimalzahl BspArray(2) = #3/26/2016# 'Datum BspArray(3) = #7:20:00 PM# 'Uhrzeit BspArray(4) = "BSB" 'String BspArray(5) = 123 'Zahl 'vbCrLf = Zeilenumbruch; "_" erlaubt einen Zeilenumbruch im Code zur besseren Lesbarkeit des Codes MsgBox "Element in Array bei index = 0 : " & BspArray(0) & vbCrLf & _ "Element in Array bei index = 1 : " & BspArray(1) & vbCrLf & _ "Element in Array bei index = 2 : " & BspArray(2) & vbCrLf & _ "Element in Array bei index = 3 : " & BspArray(3) & vbCrLf & _ "Element in Array bei index = 4 : " & BspArray(4) & vbCrLf & _ "Element in Array bei index = 5 : " & BspArray(5) End Sub
Die Zuweisung von Werten kann auch innerhalb der Deklarationszeile des Arrays erfolgen
Dim BspArray() As Double = {1.23, 2.34, 3, 4.56, 5}
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:
'Mit einem Klick auf einen Button wird die Prozedur gestartet Sub MD_Array_Click() 'Das BspArray wird als String deklariert, da es mit Zeichenketten gefüllt wird Dim BspArray(2, 3) As String ' Das BspArray besitzt 3 Zeilen und 4 Spalten 'Das BspArray wird mit Werten gefüllt (siehe obere Grafik) BspArray(0, 0) = "Muskat" BspArray(0, 1) = "Zimt" BspArray(0, 2) = "Borretsch" BspArray(0, 3) = "Apfel" BspArray(1, 0) = "Orange" BspArray(1, 1) = "Kirsche" BspArray(1, 2) = "Yuzu" BspArray(1, 3) = "Himbeere" BspArray(2, 0) = "Tencha" BspArray(2, 1) = "Mohn" BspArray(2, 2) = "Tonkabohne" BspArray(2, 3) = "Thymian" 'vbCrLf = Zeilenumbruch; "_" erlaubt einen Zeilenumbruch im Code zur besseren Lesbarkeit des Codes MsgBox "Element in Array bei Index = 0,1 : " & BspArray(0, 1) & vbCrLf & _ "Element in Array bei Index = 2,2 : " & BspArray(2, 2) End Sub
Collections
Mit Collections kann man Listen, ähnlich wie Arrays, erstellen.
Vorteil | Nachteil |
---|---|
|
|
Das managen dieser Liste erfolgt über die Add(), Remove(), Clear(), Contains() Methode und der Count bzw. Item-Eigenschaft.
Sub Fruit_Collection() Dim colFruit As Collection 'Deklaration der Collection "Fruit" Dim colCitrus As Collection 'Deklaration der Collection "Citrus" Dim colBanana As Collection 'Deklaration der Collection "Banana" Set colFruit = New Collection 'Erzeugung der Collection, damit man mit ihr arbeiten kann Set colCitrus = New Collection Set colBanana = New Collection With colCitrus 'Hinzufügen von Daten in die colCitrus-Liste .Add "Yuzu", "Art" .Add "Sapindales", "Ordnung" .Add "Rutaceae", "Familie" .Add "Citrus", "Gattung" End With colFruit.Add colCitrus 'Die colCitrus-Liste wird der colFruit-Liste hinzugefügt With colBanana 'Hinzufügen von Daten in die colBanana-Liste .Add "Dessertbanane", "Art" .Add "Zingiberales", "Ordnung" .Add "Musaceae", "Familie" .Add "Bananen", "Gattung" End With colFruit.Add colBanana 'Die colBanana-Liste wird der colFruit-Liste hinzugefügt End Sub
Konvertierung/Manipulation von Daten
VBA liefert über Hundert Möglichkeiten, Daten zu manipulieren. Die bedeutesten sind hier in einer Tabelle zusammengefasst.
Funktion | Beschreibung |
---|---|
Vartype() | Gibt einen integeren Wert wieder, der Aufschluss über den Datentyp zulässt. |
CCur(), CInt(), CDbl(), CVar(), CStr() | Konvertieren beliebige Ausdrücke in einen definierten Datentyp. |
Trim(), RTrim(), LTrim() | Entfernt Leerzeichen einer Zeichenkette von beiden Seiten, bzw. Rechts oder Links. |
Left(), Mid(), Right() | Liefert den linken, mittleren bzw. rechten Inhalt eines Strings. Die jeweiligen Startpositionen müssen vorher festgelegt werden. |
InStr() | Liefert einen booleschen Wert. Überprüft ob ein Ausdruck in einem String vorhanden ist. |
UCase(), LCase() | Umwandlung von Zeichenfolgen in Groß- und Kleinbuchstaben. |
Split() | Trennt eine Zeichenkette nach einem vorher definierten Muster auf. Als Rückgabewert erhält man ein eindimensionales Array, bei dem das Muster in der Zeichenkette entfernt wurde. |
Replace() | Austausch eines Ausdrucks innerhalb einer Zeichenkette. |
Join() | Zusammenfügung einer Zeichenkette, mit einem definierten Trennsymbol. |
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 | |
Single (!) | Dezimalzahl mit sechstelliger Genauigkeit | |
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) |
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.
Standardform |
Dim Variable As Integer, Variable2 As String, Variable3 As Single |
Kurzform |
Dim Variable%, Variable2$, Variable3! |
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. |
VBA und Excel
Um Visual Basic for Applications in Excel zu verstehen, ist es wichtig zu erkennen, dass dies eine objektorientierte Programmiersprache ist. Sie besteht aus Objekten, Methoden und Eigenschaften.
Ein VBA Objekt ist etwas, das eine bestimme Funktion, Eigenschaften, Daten oder vererbte Objekte enthält.
Um das besser zu verstehen, nehmen wir uns ein Beispiel aus der realen Welt. Ein Haus zum Beispiel, ist ein Objekt. Fenster, Türen, usw. sind vererbte Objekte des Hauses. Ein Haus hat ein paar Charakteristiken oder Eigenschaften, wie beispielsweise die Farbe, Höhe, Anzahl an Etagen und besitzt ebenso einige Events, wie "Tür öffnen", "Tür schließen", etc.
Vergleichsweise ähnlich verhält es sich mit einem Excel Arbeitsblatt. Dieses ist ein Objekt und eine Reihe oder Zellen des Arbeitsblatts sind die vererbten Objekte von einem Arbeitsblatt. Das Arbeitsblatt besitzt bestimmte Eigenschaften, Methoden und Events.
Excel 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
Objekt | Beschreibung |
---|---|
Application | Repräsentiert die gesamte MS Office Anwendung |
Workbooks | Auflistung aller geöffneten Workbook-Objekte |
Worksheets | Auflistung aller zugehörigen Tabellenblätter |
Charts | Auflistung aller Diagramme innerhalb einer Arbeitsmappe |
ActiveWorkbook | Derzeit aktives Workbook |
ActiveSheet | Derzeit aktives Tabellenblatt |
ActiveCell | Derzeit aktive Zelle |
ActiveChart | Derzeit aktives Diagramm |
ThisWorkbook | Arbeitsmappe mit dem derzeit verrichteten VBA-Code. Es muss nicht zwingend das Aktive sein. |
ThisWorksheet | Tabellenblatt mit dem Ort der Prozedur. Es muss nicht zwingend das Aktive sein. |
Cells(Zeile,Spalte) | Fokussiert eine Zelle |
Selection | Fokussiert einen markierten Zellverbund |
ActiveSheet.UsedRange | Fokussiert den aktuell benutzten Platz in der Tabelle |
Excel Methoden
Aktionen mit Arbeitsmappen/Tabellenblättern
Bei der Kombination von Excel und VBA kommt es zwangsläufig immer mal vor, dass man auf Tabelleninhalte zurückgreifen und diese auch manipulieren kann.
In der nachfolgenden Tabelle sind die essentiellen Methoden fett markiert:
Methode | Funktion |
---|---|
Workbooks / Arbeitsmappen | |
Workbooks("Arbeitsmappe.xslm").Activate | Die deklarierte Arbeitsmappe wird aktiviert. |
Workbooks("Arbeitsmappe.xslm").Close | Die deklarierte Arbeitsmappe wird geschlossen. |
Workbooks.Open "Arbeitsmappe.xslm" | Eine deklarierte Arbeitsmappe wird geöffnet. |
ActiveWorkbook.Name | Der Name der aktuellen Datei wird wiedergegeben. |
ActiveWorkbook.Save | Die aktuelle Arbeitsmappe wird gesichert. |
ThisWorkbook.Path | Liefert den aktuellen Pfad der Datei. |
ThisWorkbook.SaveAs | Die aktuelle Arbeitsmappe wird in einem deklarierten Pfad gespeichert. |
Workbook.Count | Liefert die aktuelle Anzahl der Arbeitsmappen. |
Worksheets / Tabellenblätter | |
Worksheets(“Tabellenblatt1”).Activate | Aktiviert ein deklariertes Tabellenblatt. |
Range(„A1:M3“).Select | Ein bestimmter Zellbereich wird ausgewählt. |
Worksheets(“Tabellenblatt1”).Select | Aktiviert ein deklariert Tabellenblatt und stellt es innerhalb der Prozedur in den Vordergrund. |
Worksheets.Add | Fügt ein neues Datenblatt hinzu. |
Worksheets("Tabellenblatt1").Move after/before:=Worksheets("Tabellenblatt3") | Verschiebt ein deklariertes Tabellenblatt nach/vor ein anderes. |
Worksheets(“Tabellenblatt1”).Visible = False/True | Ein deklariertes Tabellenblatt ist ausgeblendet/sichtbar. |
Worksheets.Count | Liefert die aktuelle Anzahl an Tabellenblättern. |
Zellenbereiche (Zellen/Spalten) verändern
Filterfunktionen
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:
Funktion | Methode |
---|---|
Bei einer Funktion besteht die Option zur Festlegung eines Default-Wertes, welcher als Zeichenkette an die aufrufende Prozedur zurück gegeben wird. Wird vom Benutzer der Abbrechen-Button betätigt, wird ein String ohne Inhalt zurückgegeben. | Im Vergleich zur Funktion, kann der "Type" eines übergebenen Wertes über das Eingabefeld definiert und geprüft werden. Wird der Abbrechen-Button betätigt wird "false" als Wert übergeben. Entspricht der eingegebene Wert, dem des eingestellten Datentyps, so wird dieser als Rückgabewert übermittelt. |
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
Name | Erforderlich/Optional | Datentyp | Beschreibung | ||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
Prompt | Erforderlich | String | Definiert die Meldung die im Dialogfeld angezeigt werden soll. Kann vom Datentyp eine Zeichenfolge, Zahl, Datum, oder boolescher Wert sein. Sämtliche Typen werden als String umgewandelt. | ||||||||||||||||
Title | Optional | Variant | Definiert den Titel des Eingabefeldes. Falls nicht angegeben, wird standardmäßig "Eingabe" verwendet. | ||||||||||||||||
Default | Optional | Variant | Gibt einen Wert bei Aufruf des Dialogfeldes im Eingabefeld an. Hier kann auch ein Range-Objekt verwendet werden. | ||||||||||||||||
Left | Optional | Variant | Dialogfeld-Position: X-Koordinate: Gibt den relativen Abstand zur linken oberen Bildschirmecke an. | ||||||||||||||||
Top | Optional | Variant | Dialogfeld-Position: Y-Koordinate: Gibt den relativen Abstand zur linken oberen Bildschirmecke an. | ||||||||||||||||
HelpFile | Optional | Variant | Name der Hilfedatei für das Eingabefeld. Eine Schaltfläche ? (Hilfe) wird angezeigt, wenn die Parameter "HelpFile" und "HelpContextID" übergeben wurden. | ||||||||||||||||
HelpContextID | Optional | Variant | KontextID des Hilfethemas in "HelpFile" | ||||||||||||||||
Type | Optional | Variant | Definiert den Rückgabewert für den Datentyp. Wird keins angegeben, wird vom Dialogfeld Text wiedergegeben.
|
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 angegeben werden. Eine Kombination erfolgt mit "+". Will man den Wert in einer Variable übergeben lassen, d.h. man will wissen, welchen Button der Benutzer gedrückt hat, so muss man die bei der Deklaration angegeben Klammern setzen:
'Unabhängig davon, wie die Schalfläche deklariert ist (alpha- oder numerisch), liefert die Variable den Wert des jeweils ausgewählten Button MsgBox_Aktivierte_Schaltflaeche = MsgBox("Mitteilung", 3 + 64 + 0, "Information")
Schaltflächen | ||
Wert | Konstante | Funktion |
---|---|---|
0 | vbOKOnly | "OK" erzeugen (Standardmäßig eingestellt) |
1 | vbOKCancel | "OK" und "Abbrechen" erzeugen |
2 | vbAbortRetryIgnore | "Abbrechen", "Wiederholen", "Ignorieren" erzeugen |
3 | vbYesNoCancel | "Ja", "Nein", "Abbrechen" erzeugen |
4 | vbYesNo | "Ja" und "Nein" erzeugen |
5 | vbRetryCancel | "Wiederholen" und "Abbrechen" erzeugen |
Symbole | ||
Wert | Konstante | Funktion |
16 | vbCritical | "Stop"-Symbol erzeugen |
32 | vbQuestion | "Fragezeichen"-Symbol erzeugen |
48 | vbExclamation | "Ausrufezeichen"-Symbol erzeugen |
64 | vbInformation | "Info"-Symbol erzeugen |
Aktivierte Schaltflächen | ||
Wert | Konstante | Aktivierte Schaltfläche |
0 | vbDefaultButton1 | Erste Schalfläche |
256 | vbDefaultButton2 | Zweite Schaltfläche |
512 | vbDefaultButton3 | Dritte Schaltfläche |
768 | vbDefaultButton4 | Vierte Schaltfläche |
Bindung des Dialogfeldes | ||
Wert | Konstante | Aktivierte Schaltfläche |
0 | vbApplicationModal | Ist an die Anwendung gebunden. Die Fortsetzung der Anwendung erfolgt nur dann, wenn der MsgBox-Dialog beendet wird. |
256 | vbSystemModal | Sämtliche Anwendungen werden systemweit angehalten, bis der MsgBox-Dialog beendet wird. |
VBA MsgBox Video-Tutorial von Andreas Thehos
https://www.youtube.com/watch?v=ssei8pEt2rM
Kombination von Input- und MsgBox
'Prozedur wird mit Klick auf einen Button gestartet Sub QuadrFunktSub_Click() 'inpt-Variablen und QdrFkt werden als Double Datentyp deklariert Dim inptX#, inptA#, inptB#, inptC#, QdrFkt# 'Eingabewerte aus InputBox werden an die inpt-Variablen übergeben inptX = Application.InputBox("x", "x Eingabeaufforderung", , , , , , 1) inptA = Application.InputBox("a", "a Eingabeaufforderung", , , , , , 1) inptB = Application.InputBox("b", "b Eingabeaufforderung", , , , , , 1) inptC = Application.InputBox("c", "c Eingabeaufforderung", , , , , , 1) 'Berechnung des Funktionswertes einer quadratischen Funktion aus übermittelten Werten QdrFkt = (inptA * (inptX ^ 2)) + inptB * inptX + inptC 'Ausgabe des Funktionswertes in einer MsgBox MsgBox "Ermittelter Funktionswert:" & vbCrLf & QdrFkt End Sub
Excel Eigenschaften
Eigenschaften sind die Charakteristiken eines Objektes, die gemessen und quantifiziert werden können. Im Bezug auf das Haus können das die Breite, die Höhe, die Farbe, etc. sein.
In Excel hat man ebenfalls verschiedene Eigenschaften die gemessen und quantifiziert werden können. Dies wäre z.B. bei einem Range Objekt, die Eigenschaften Value, Font.ColorIndex, Interior.ColorIndex, ...
Die Deklaration der Farbe kann über verschiedene Wege erfolgen. Es stehen drei Möglichkeiten zur Verfügung:
ColorIndex (abhängig von der Farbpalette in Excel; Index reicht von 1 bis 56):
RGB(r,g,b):
Die zugehörigen Argumente liefert ein Generator:
http://www.rapidtables.com/web/color/color-scheme.htm
Integrierte Konstanten zuweisen:
vbBlack, vbRed, vbGreen, vbYellow, vbBlue, vbMagenta, vbCyan, vbWhite
Schrift-Formatierung
Eigenschaft | Beschreibung |
---|---|
Range().Font.Size = 8 | Legt die Größe der Schriftart in einer Zelle oder einer Auswahl fest. |
Range().Font.Italic = true | In einer Auswahl oder Zelle wird die Schriftart kursiv gesetzt. |
Range().Font.Bold = true | In einer Auswahl oder Zelle wird die Schriftart fett gesetzt. |
Range().Font.Underline = true | In einer Auswahl oder Zelle wird die Schriftart unterstrichen. |
Range().Font.name = “Trebuchet MS” | Der Schrifttyp wird in einer Auswahl oder Zelle festgelegt. |
Range().Font.Color = vbGreen | Die Farbe des Schrifttyps wird in der Auswahl oder Zelle anhand VBA interner Konstanten festgelegt (s.o.) |
Range().Font.ColorIndex = 19 | Die Farbe des Schrifttyps wird über einen Excel abhängigen Index festgelegt (s.o.) |
Range().Font.Subscript = true | In der Auswahl oder Zelle wird die Ausgabe tiefgestellt dargestellt. |
Range().Font.Superscript = true | In der Auswahl oder Zelle wird die Ausgabe hochgestellt dargestellt. |
Rahmen-Formatierung
Eigenschaften | Beschreibung |
---|---|
Range().Borders | Wählt einen Rahmen von einer Auswahl oder Zelle aus. |
Range().Borders(Index) | Wählt einen Teilrahmen (links, rechts, oben, unten) über einen Index aus: xlLeft, xlRight, xlTop, xlBottom, xlNone (löscht einen Rahmen). |
Range().BorderAround | Erzeugt einen Rahmen um den Auswahlbereich oder Zelle. |
Range().Borders.LineStyle = xlDash | Legt den Rahmentyp bei einer Auswahl oder Zelle fest: xlContinous, xlDash, xlDot, xlNone |
Range().Borders.Weight = xlThin | Legt die Dicke des Rahmens einer Auswahl oder Zelle fest: xlThick, xlMedium, xlThin, xlHairline |
Range().Borders.ColorIndex = 4 | Legt die Rahmenfarbe einer Auswahl oder Zelle fest. Abhängig vom Indexwert von Excel (s.o.) |
Range().Borders.Color = vbGreen | Die Farbe des Rahmens wird in der Auswahl oder Zelle anhand VBA interner Konstanten festgelegt (s.o.) |
Zellen-Formatierung
Eigenschaften | Beschreibung |
---|---|
ActiveCell.Interior.ColorIndex = 4 | Eine aktive Zelle wird mit einer Farbe gefüllt. Abhängig vom Indexwert von Excel (s.o.) |
ActiveCell.Interior.Color = RGB(0,255,0) | Farbzurdnung der aktiven Zelle kann über die VBA internen Konstanten oder über RGB() (s.o.) erfolgen. |
Selection.Columns.AutoFit | Die ausgewählten Spalten werden automatisch der Breite angeoasst. |
Selection.Rows.AutoFit | Die ausgewählten Zeilen werden automatisch an die Zellenhöhe angepasst. |
Formatierungsbeispiel
Sub RangeEigenschaften_Click() '### S C H R I F T ### 'Der Auswahl A1:C3 in der Tabelle1 wird der Wert 123 in jeder Zelle zugewiesen. Worksheets("Tabelle1").Range("A1:C3").Value = 123 'Die Schriftfarbe der Zelle C3 in der Tabelle1 mit Weiss anfärben Worksheets("Tabelle1").Range("C3").Font.ColorIndex = 2 '### R A H M E N #### 'Der Auswahl C6:E8 in der Tabelle1 werden Rahmeneigenschaften zugewiesen With Worksheets("Tabelle1").Range("C6:E8").Borders 'Rahmendicke = Dünn .Weight = xlThin 'Linienart = gestrichelt .LineStyle = xlDash 'Farbe = Marineblau .Color = RGB(0, 17, 255) End With '### Z E L L E N ### 'Zelle C3 in der Tabelle1 mit Cyan anfärben Worksheets("Tabelle1").Range("C3").Interior.ColorIndex = 8 'Zelle C3 in der Tabelle1 automatisch in der Breite anpassen Worksheets("Tabelle1").Range("C3").Columns.AutoFit End Sub
Makro
Add-In
Wenn man über "Add-In's" redet, meint man eigentlich Tabellenblatt-Add-ins. Das heisst, dass dem Tabellenblatt etwas hinzugefügt wird, um seine Funktionalität zu erweitern. Microsoft Office beispielsweise liefert Add-Ins in seiner Office-Suite mit. Das meist bekannte ist das "Analyse Funktionen" Add-In, welches statistische und analytische Funktionen hinzufügt, die standardmäßig in Excel nicht implementiert sind.
Add-In aktivieren
- Wenn die Registerkarte "Entwicklertools" aktiviert ist, auf den Button "Add-In" klicken.
- Einen Haken bei dem jeweiligen Add-In setzen und mit "OK" bestätigen.
- Register "Datei" » Optionen » Dialogfeld "Excel-Optionen" wird geöffnet
- Auf die linke Seite des Dialogfelds "Add-Ins" klicken
- Unten im Dialogfeld, in der Dropdown-Liste "Verwalten", "Excel-Add-Inns" auswählen und auf "Gehe zu..." klicken.
- Gewünschtes Add-In auswählen und mit "OK" bestätigen.
Add-In zur Verfügung stellen
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. (2010): "Datensammlung - Arrays und Collections", unter http://www.office-loesung.de/ftopic429248_0_0_asc.php (abgerufen am 28.03.2016)
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).
Sens, G.-M. (2016): "Microsoft Office Excel - Fortgeschrittene", unter http://www.staff.uni-giessen.de/~g021/excel_fortgeschrittene.htm (abgerufen am 29.03.2016).
- eigene Funktion erstellen
- Add Ins erstellen