Mini-Tutorial Excel VBA
© 2012, Daniel Hofer Version 1.1 Seite 1 von 50
Mini-Tutorial Excel VBA
Inhaltsverzeichnis Was ist VBA, was sind Makros? .................................................................................................................................... 2
Wozu überhaupt VBA? ................................................................................................................................................. 2
Benutzerdefinierte Funktionen ................................................................................................................................ 2
Nachträgliche Formatierung ..................................................................................................................................... 3
Voraussetzungen, um mit VBA programmieren zu können ......................................................................................... 4
Office 2010 ............................................................................................................................................................... 4
Office 2007 ............................................................................................................................................................... 5
Zwei Möglichkeiten, zu beginnen ................................................................................................................................. 6
Manuell ..................................................................................................................................................................... 6
Makro-Rekorder ....................................................................................................................................................... 8
VBA-Grundlagen ......................................................................................................................................................... 11
Zwei Arten von Code .............................................................................................................................................. 11
Aufbau einer Prozedur............................................................................................................................................ 11
Unsere erste Prozedur, welche auch was macht ................................................................................................... 13
Eine Beispiel-Prozedur ............................................................................................................................................ 15
Die Messagebox nervt ............................................................................................................................................ 18
Kommentare ........................................................................................................................................................... 20
Funktionen .............................................................................................................................................................. 21
Rekapitulation Prozeduren/Funktionen ................................................................................................................. 25
Ablauf eines Programms kontrollieren ................................................................................................................... 27
Datentypen ............................................................................................................................................................. 30
Kontrollstrukturen .................................................................................................................................................. 32
Fehler in VBA .............................................................................................................................................................. 35
Allgemeines ............................................................................................................................................................ 35
Probleme mit If-Verzweigungen ............................................................................................................................. 37
Interaktion mit Excel ................................................................................................................................................... 39
Tabellenblätter ....................................................................................................................................................... 39
Zellen ...................................................................................................................................................................... 40
Formatierungen mit dem Makro-Rekorder rausfinden.......................................................................................... 44
Benutzerdefinierte Funktionen .................................................................................................................................. 48
Grenzen von benutzerdefinierten Funktionen ....................................................................................................... 49
Ausblick ....................................................................................................................................................................... 50
Mini-Tutorial Excel VBA
Seite 2 von 50 Version 1.1 © 2012, Daniel Hofer
Was ist VBA, was sind Makros? VBA ist eine Programmiersprache. Die Abkürzung heisst „Visual Basic for Applications“ und ist von Microsoft.
Jedes Office-Produkt von Microsoft hat VBA als Programmiersprache eingebaut.
Früher (vor 20 Jahren) gab es z.B. in Word eine Programmiersprache WordBasic, welche nur ganz einfache
Abläufe erledigen konnte. Solchen Abläufen sagt man auf Englisch „Macros“. Ein Makro ist ein Programmablauf.
Damals war die Programmiersprache sogar noch in Deutsch.
Auch Excel (damals in der Version 4) hatte noch nicht VBA als Programmiersprache, sondern eine ganz
rudimentäre Makrosprache eingebaut. Auch diese war auf Deutsch.
Irgendwann (mit Excel 5 und Word 6) fand Microsoft, dass es unübersichtlich ist, für jedes Programm eine andere
Programmiersprache zu haben. Aus diesem Grund haben sie VBA eingeführt. Der grosse Unterschied zu früher ist,
dass nun die komplette Programmiersprache in Englisch ist (auch in deutschen Programmen).
Heute spricht man immer noch von Makros. Dies liegt einfach daran, dass viele Programmierer sich noch gewohnt
sind, von Makros zu sprechen, anstelle von Programmen oder anderen Ausdrücken. Wenn ich also sage, ich
schreibe in Excel ein Makro, dann meine ich damit, dass ich mittels VBA ein Programm, einen Ablauf oder eine
Prozedur (alles bedeutet etwa das Gleiche) schreibe.1
Wozu überhaupt VBA? VBA in Excel wird recht häufig benötigt. Ich zeige hier zwei Anwendungsbeispiele.
Benutzerdefinierte Funktionen
Du erinnerst dich vielleicht an das Beispiel mit der Briefanrede von letzter Woche. Da haben wir mittels einer
verschachtelten WENN-Formel aufgrund der Anrede die Briefanrede „zusammengebastelt“:
Für dich ist diese WENN-Funktion wohl kein Problem. Wollen wir aber, dass andere Leute damit arbeiten, wird
das für sie ev. anstrengend. Aus diesem Grund könnten wir ihnen eine spezielle Funktion anbieten. Das obige
1 Access ist noch heute ein Spezialfall: da gibt es eine deutsche Makroprogrammiersprache, welche einfache aber auch sehr eingeschränkte Möglichkeiten bietet. Daneben hat aber auch Access VBA eingebaut und bietet die gleichen Möglichkeiten wie VBA in Word oder Excel.
Mini-Tutorial Excel VBA
© 2012, Daniel Hofer Version 1.1 Seite 3 von 50
Beispiel würde dann z.B. so aussehen:
Anstelle der komplizierten WENN-Formel bieten wir den Leuten eine Funktion fAnrede an, welche wir selber
programmieren. Achtung, für uns als Entwickler ist der Aufwand immer noch gleich gross resp. z.T. sogar grösser.
Es geht nur darum, anderen Leuten, welche unser Excel-Sheet verwenden sollen, das Leben einfacher zu machen.
Nachträgliche Formatierung
Du musst als Beispiel jeden Tag einen Import aus SAP machen. Du holst Kundendaten ins Excel, welche nach dem
Import so aussehen:
Deine Aufgabe ist, jeden Tag den Import zu machen, danach die Liste aber auch entsprechend aufzubereiten.
Damit meine ich, die Liste so zu präparieren, dass sie am Schluss z.B. so aussieht:
Mini-Tutorial Excel VBA
Seite 4 von 50 Version 1.1 © 2012, Daniel Hofer
Dies war jetzt eine einfache Liste, welche gut manuell formatiert werden kann. Aber eine kompliziertere Liste mit
hunderten von Spalten und tausenden von Zeilen wird mühsam...hier wäre es praktisch, wenn man diese
Formatierungsarbeit automatisieren könnte. Und genau hier kommt wieder ein Makro (siehst du, auch ich
spreche von Makros, wenn ich eigentlich VBA meine) zum Zug.
Voraussetzungen, um mit VBA programmieren zu können Um sinnvoll mit VBA programmieren zu können, benötigen wir ein zusätzliches Register „Entwicklertools“,
welches standardmässig ausgeblendet ist.
Office 2010
Das Standard-Menüband in Excel sieht so aus:
Um das Register einzublenden, klickst du mit rechter Maustaste z.B. auf das Register „Ansicht“. Dann erscheint
das Kontextmenü:
Hier wählst du „Menüband anpassen...“. Es erscheint ein unübersichtliches Fenster. Hier musst aber lediglich im
rechten Bereich das folgende Häkchen aktivieren:
Danach bestätigst du das Ganze mit „OK“ und das Fenster verschwindet. Jetzt sieht das Menüband so aus:
Mini-Tutorial Excel VBA
© 2012, Daniel Hofer Version 1.1 Seite 5 von 50
Vor allem die Symbole ganz links in der Gruppe „Code“ werden wir häufiger benötigen.
Office 2007
In Office 2007 wechselst du über den regulären Weg in die Optionen:
Unter „Häufig verwendet“ aktivierst du folgende Checkbox:
Mini-Tutorial Excel VBA
Seite 6 von 50 Version 1.1 © 2012, Daniel Hofer
Zwei Möglichkeiten, zu beginnen Um mit der Programmierung zu beginnen, hast du zwei Möglichkeiten:
Entweder von Hand, d.h. du wechselst in den sog. VBA-Editor und beginnst da die Eingabe
Oder du zeichnest mit dem Makro-Rekorder einen Ablauf auf
Welchen Weg du wählst, ist grundsätzlich egal. Willst du eine benutzerdefinierte Funktion schreiben, dann wählst
du normalerweise den manuellen Weg. Willst du aber Formatierungen an einem Blatt anbringen, dann wählst du
den Rekorder.
Wichtig: der Makro-Rekorder zeichnet haargenau deine Mausklicks auf. In den allermeisten Fällen musst du
nachträglich den Code noch anpassen, damit er das macht, was du eigentlich wolltest. Ich zeige später ein Beispiel
dafür.
Manuell
Als Ausgangslage befindest du dich als Beispiel in diesem Blatt:
Um in den VBA-Editor zu gelangen, drückst du am einfachsten die Tastenkombination Alt & F11. Als Alternative
kannst du auch folgenden Befehl wählen im Menüband:
Jetzt erscheint ein leeres Fenster:
Mini-Tutorial Excel VBA
© 2012, Daniel Hofer Version 1.1 Seite 7 von 50
Darin wirst du dich bewegen, wenn du mit VBA programmierst, d.h. Makros schreibst.
Um überhaupt etwas machen zu können, müssen wir ein neues sog. Modul2 einfügen. Dafür klickst du mit rechter
Maustaste links in den weissen Bereich, und wählst danach die angegeben Punkte:
Nun wird links in der Baumstruktur ein neuer Eintrag „Module“ mit dem neuen Modul eingefügt, und rechts
davon wird eben das weisse Dokument eingefügt. Der Cursor blinkt ganz oben am Dokument:
Jetzt sind wir bereit, um Code erfassen zu können.
2 Ein Modul ist einfach ein weisses Blatt Papier (wie ein Word-Dokument), auf welchem du nun Code schreibst. Das Dokument kann beliebig lang sein.
Mini-Tutorial Excel VBA
Seite 8 von 50 Version 1.1 © 2012, Daniel Hofer
Makro-Rekorder
Diesmal befinden wir uns als Beispiel in der Datei mit der umfangreichen Adressliste, welche formatiert werden
muss:
Am besten wechselst du als erstes in die Zelle A1. Danach klickst du auf folgenden Button:
Im folgenden Fenster gibst du einen (sinnvollen3) Namen für das Makro (Fachsprache: Prozedur) ein:
Zusätzlich kannst du entscheiden, wo das Makro gespeichert werden soll. Wir lassen das mal auf „Diese
Arbeitsmappe“. Mit einem Klick auf „OK“ wird der Rekorder gestartet. Ab sofort musst du gut überlegen, welche
Mausklicks du machst. Es wird nämlich alles aufgezeichnet (innerhalb einer Excel-Arbeitsmappe).
Wir machen als Beispiel folgende Schritte:
Spalten A, E und F löschen
Zwei Zeilen oberhalb der Zeile 1 einfügen
Spaltenüberschriften fett
In Zelle A1 eine Überschrift reinschreiben und formatieren
3 Die sinnvolle Namensvergebung von Objekten ist in der Programmierung extrem wichtig, damit jede Entwicklerin und jeder Entwickler „auf der Welt“ versteht, was gemacht wird. So sprechen wir alle vom Gleichen. Mehr dazu später.
Mini-Tutorial Excel VBA
© 2012, Daniel Hofer Version 1.1 Seite 9 von 50
Also sieht die Liste am Schluss etwa so aus:
Jetzt ist ganz wichtig, dass du nicht mehr fröhlich in der Gegend rumklickst, sondern sobald du fertig bist, die
Aufzeichnung sofort stoppst4:
Jetzt solltest du als erstes deine Excel-Arbeitsmappe speichern. Denk dran, dass du eine Makro-fähige
Dateiendung wählst:
Vom Excel-Fenster aus siehst du das Makro im Moment noch nicht. Um das Makro noch einmal auszuführen (z.B.
mit anderen Daten), musst du irgendwie darauf zugreifen können. Häufig passt man die Symbolleiste für den
Schnellzugriff so an, dass man mit einem Klick das Makro ausführen kann:
Als Entwicklerin willst du allerdings in der Regel das Makro, d.h. den Code zuerst kontrollieren. Diesen Knopf da
links oben ist dann wirklich die allerletzte Priorität.
4 In der Praxis musst du häufig den Rekorder einige Male starten, weil du versehentlich „Schrott“ aufgezeichnet hast.
Mini-Tutorial Excel VBA
Seite 10 von 50 Version 1.1 © 2012, Daniel Hofer
Mit Alt & F11 wechselst du auch hier in den VBA-Editor, welcher sich wie folgt zeigt:
Hier nicht verwirren lassen. Excel hat zwar links einen Ordner „Module“ erstellt, aber aus einem unerklärlichen
Grund zeigt er nicht an, was drin ist. Also klicke einfach auf das Plus-Zeichen. Dann siehst du, was drin ist:
Mit einem Doppelklick auf „Modul 1“ links wird rechts wieder das Dokument geöffnet. Was allerdings bei der
manuellen Version von weiter oben ein leeres Dokument zeigte, zeigt dir hier unser aufgezeichnetes Makro
pFormatierung (oberste Zeile).
Bevor wir den Code darin genauer betrachten, folgen einige VBA-Grundlagen.
Mini-Tutorial Excel VBA
© 2012, Daniel Hofer Version 1.1 Seite 11 von 50
VBA-Grundlagen Wir kehren wieder zu unserem ersten Beispiel zurück, bei welchem wir manuell den VBA-Editor geöffnet und
danach ein Modul eingefügt hatten:
In diesem Kapitel schauen wir einige wichtige Punkte an, damit du mit Programmieren beginnen kannst.
Zwei Arten von Code
Grundsätzlich sprechen wir in der Programmierung von zwei Arten von Code-Blöcken:
Prozeduren (umgangssprachlich auch Makro genannt)
Funktionen
Prozeduren
Eine Prozedur ist ein Ablauf von Befehlen. Die Prozedur wird immer von oben nach unten durchgearbeitet. Ist
Excel bei der letzten Zeile angekommen, gilt die Ausführung als beendet.
Funktionen5
Funktionen funktionieren eigentlich genau gleich. Auch diese werden immer von oben nach unten abgearbeitet.
Aber am Schluss gibt die Funktion einen Wert zurück.
Sämtliche Excel-Funktionen (auf einem Excel-Blatt, nicht VBA) sind eben solche Funktionen. Diese geben immer
einen Wert zurück. Schreibst du als Beispiel in einer Zelle folgendes rein:
dann erscheint dort schlussendlich das aktuelle Datum. Der Befehl Heute() dient dazu, dass Excel irgendwie (uns
interessiert es nicht, wie Excel das macht) das heutige rausfindet. Aber am Schluss schreibt es dieses Datum in
diese Zeile.
Aufbau einer Prozedur
Eine Prozedur besteht immer aus einer Zeile Code, welche den Start, und einer Zeile Code, welche das Ende der
Prozedur definiert. Zwischen diesen Zeilen kannst du deinen eigenen Code eingeben.
5 Funktionen werden erst weiter hinten behandelt. Wir schauen zuerst Prozeduren an.
Mini-Tutorial Excel VBA
Seite 12 von 50 Version 1.1 © 2012, Daniel Hofer
Meistens drücke ich als erstes 2x ENTER, um nicht so weit oben am Blatt zu kleben:
(ist nur ein „Soumödeli“ von mir)
Die erste Zeile einer Prozedur beginnt in der Regel mit dem Schlüsselwort Sub. Ich gebe dies bewusst immer in
Kleinbuchstaben ein6:
Jetzt schreiben wir nach einem Leerschlag den Namen unserer Prozedur:
Dies ist ein Beispiel, das man in jedem Programmier-Buch als erstes Beispiel findet, deshalb machen wir das auch
so. Den Namen, den du da vergibst, kannst du beliebig vergeben. Du kannst deiner Prozedur auch „Prozedur34“
sagen, nur weisst du vermutlich schon nach einer Woche nicht mehr, was die macht. Benenne deine Prozeduren
also sinnvoll.
Am Ende der Zeile drücken wir ENTER. Danach baut der VBA-Editor automatisch eine Prozedur:
Der VBA-Editor hat also folgende Arbeiten automatisch ausgeführt:
Das vorher klein geschriebene sub wird nun zu Sub in blauer Farbe7
Am Ende der Zeile werden 2 Klammern hinzugefügt (wie bei Excel-Funktionen auch)
Zwei Zeilen weiter unten wird ein End Sub in blauer Farbe eingefügt
Der Cursor blinkt dazwischen
Jetzt haben wir bereits eine funktionsfähige Prozedur. Diese macht zwar noch nichts, aber Excel wartet nun mit
dem blinkenden Cursor auf Befehle von uns.
Eine Prozedur muss immer so aussehen! Ohne diese beiden Zeilen (damit Excel weiss, wo dein Code beginnt und
wo er aufhört) funktioniert das Ganze nicht.
6 Erkläre ich später, weshalb das Sinn macht. 7 Wenn du das sub klein schreibst, und am Ende der Zeile nach einem ENTER merkst, dass es gross und blau wird, dann hast du eine einfache Kontrolle, ob du keinen Schreibfehler hast. Das ist die Idee von der Kleinschreibung.
Mini-Tutorial Excel VBA
© 2012, Daniel Hofer Version 1.1 Seite 13 von 50
Unsere erste Prozedur, welche auch was macht
Und nun schreiben wir den ersten Befehl zwischen die Zeilen:
(auch diesen Befehl schreibe ich wieder klein. Habe ich ihn korrekt geschrieben, wird er am Schluss nach einem
ENTER auch sein Format ändern).
Die Anweisung „MsgBox“ gibt eine einfache Meldung auf dem Bildschirm aus. Wir müssen nun noch sagen, was
denn ausgegeben werden soll. Dies geben wir dahinter, getrennt durch einen Leerschlag, an.
Drücken wir aber jetzt die Leertaste, erscheint ein sog. Tooltip als Hilfe für dich:
Dies ist eine geniale Hilfe in VBA, um schneller zu programmieren und um Fehler zu vermeiden. In vielen Fällen
brauchst du diese Info nicht. Manchmal stört sie sogar, weil sie nämlich den Text darunter verdeckt...wenn das
der Fall ist, drücke einfach die Taste ESC.
Diesmal machen wir das nicht, aber lassen uns auch nicht verwirren von dieser Hilfe. Wir schreiben einfach
weiter:
(ich habe jetzt trotzdem ESC gedrückt, sonst würdest du die letzte Zeile gar nicht sehen). Jetzt kannst du
entweder ENTER drücken (dann wird aber eine leere Zeile eingefügt), oder in diesem Fall eine der Cursor-Tasten
auf oder ab, was zu folgendem Resultat führt:
Du siehst, dass sich nun das MsgBox in der Formatierung geändert hat. Auch hier haben wir sofort die Kontrolle,
ob wir uns verschrieben haben. Falls du nämlich MsgBox versehentlich mit 2 x geschrieben hättest, würde das
nun so aussehen:
Und so merkst du sofort, dass irgendwo ein Schreibfehler drin steckt.
Ausführung einer Prozedur
Nun, unsere erste Prozedur steht, aber wie führen wir diese aus? Auch hier könnte man die Symbolleiste für den
Schnellzugriff anpassen. Aber als Entwicklerin brauchst du dies selten. Deine Welt wird in Zukunft im VBA-Editor
sein. Also muss es eine Möglichkeit geben, das Programm hier auszuführen. Und dies ist ganz einfach: klicke
Mini-Tutorial Excel VBA
Seite 14 von 50 Version 1.1 © 2012, Daniel Hofer
einfach auf den Play-Button:
Damit wird die Prozedur ausgeführt. Und in unserem Beispiel erscheint wie geplant folgende Meldung:
Eine kleine Unschönheit ist, dass der VBA-Editor verschwindet, das Excel-Fenster nach vorne kommt, und erst
dann die Meldung erscheint. Damit müssen wir leben.
Prozedur wird nicht ausgeführt, sondern ein Fenster erscheint
Vielleicht erscheint nach dem Klick auf den Play-Button folgendes Fenster:
Dies liegt daran, dass beim Klick auf den Play-Button dein Cursor nicht innerhalb, sondern ausserhalb der
gewünschten Prozedur sich befand:
Also obiges Fenster einfach abbrechen, den Cursor in die Prozedur setzen und nochmals den Play-Button klicken8.
8 Wenn schon das komische Fenster offen ist, darfst du natürlich auch gleich hier auf „Ausführen“ klicken. Das kommt aufs Gleiche raus.
Mini-Tutorial Excel VBA
© 2012, Daniel Hofer Version 1.1 Seite 15 von 50
Andere Möglichkeiten, um eine Prozedur zu starten
Es gibt auch die Möglichkeit, die Taste F5 zu drücken anstelle des Klickes auf den Play-Button. Auch im Menü
„Ausführen“ findest du den entsprechenden Punkt:
Es gibt noch weitere Möglichkeiten, welche ich später erkläre.
Wie weiter?
Das war mal der erste Schritt. Normalerweise beginnst du eine Prozedur immer so. Jetzt geht es darum, diese zu
erweitern, damit sie dann auch das macht, was wir wollen.
Eine Beispiel-Prozedur
In diesem Beispiel zeige ich einige ganz einfache Möglichkeiten von VBA. Es geht darum, dass wir von der Person,
welche unsere Excel-Datei bedient, zwei Zahlen eingeben lassen. Diese beiden Zahlen multiplizieren wir und
geben das Resultat in einer Messagebox9 aus.
Um das zu erledigen, geben wir als erstes unterhalb der vorherigen Prozedur folgende Zeile ein:
Du siehst, dass ich auch hier gleich wieder einen vernünftigen Namen vorschlage. Nach Drücken von ENTER sollte
wieder der vollständige Rumpf der Prozedur erstellt werden:
Variablen
Jetzt müssen wir uns kurz über Variablen unterhalten. Es geht ja darum, dass wir von der Anwenderin resp. dem
Anwender zwei Zahlen erhalten möchten. Dafür gibt es in VBA eine Funktion, welche diese Zahlen erfragt. Aber
diese Zahlen müssen wir irgendwo speichern.
Fragst du in der Klasse zwei Personen um beliebige Zahlen, kannst du diese im Gedächtnis behalten. VBA hat auch
ein solches Gedächtnis, und das sind Variablen (auch vorstellbar als Kaffeebecher, in welche wir was reinfüllen,
oder auch kleine Zettelchen, auf welche die Zahlen aufgeschrieben werden).
In unserem Beispiel wollen wir zwei Zahlen speichern, also benötigen wir auch zwei Variablen. Was im Gegensatz
zu unserem Gehirn ein wenig mühsam ist: wir müssen die Variablen vorgängig erst deklarieren resp. definieren.
Excel muss wissen, wie gross der Kaffeebecher sein soll (sonst schütten wir plötzlich einen Liter Milch rein, und
9 Dieses Meldungsfenster, das du vorher kennengelernt hast.
Mini-Tutorial Excel VBA
Seite 16 von 50 Version 1.1 © 2012, Daniel Hofer
das würd „es Gsou gä“). Verteilen wir zwei Personen im Raum Zettel, um Zahlen drauf zu schreiben, geben wir
ja auch entsprechend grosse Zettel ab, je nachdem welche Zahl wir erwarten.
Also, Variablen sollten immer ganz am Anfang unserer Prozedur deklariert werden. Und dies geht wie folgt:
Diese ersten Zeilen dimensionieren die Grösse der Variablen vZahl1, vZahl2 und vResultat. Dieses kleine v vor der
Variablen ist wieder so eine Namenskonvention, welche hilft, den Code später besser zu verstehen. Der Ausdruck
„as Integer“ definiert nun die Grösse der Variablen. Die grösste Zahl, welche im Datentyp Integer gespeichert
werden kann, ist 32767. Zu Datentypen sage ich später noch was.
Sind die Variablen definiert, können wir sie mal Testes halber mit Zahlen füllen. Und das geht so:
Achtung, mathematisch gesehen könnte dies eine Gleichung sein. Wir sprechen aber von einer Zuordnung,
welche aber in der Regel wie folgt dargestellt werden:
vZahl1:= 4 oder als Alternative 4 ⇒ vZahl1
Es gibt Programmiersprachen (Pascal, Java, etc.), welche eine „korrekte“ Zuordnung machen:
vZahl1:= 4
Microsoft fand diesen Doppelpunkt wohl überflüssig und hat es schlicht weggelassen.
Die obige Prozedur kannst du ausführen, aber es geschieht scheinbar nichts. Es wird zwar durchlaufen, aber mehr
als diesen Variablen die Zahlen zuzuordnen, macht es nicht. Wir müssen also noch etwas einbauen, was uns eine
Meldung bringt.
Bevor wir dies machen, wollen wir aber noch die Multiplikation erledigen:
Mini-Tutorial Excel VBA
© 2012, Daniel Hofer Version 1.1 Seite 17 von 50
Auch jetzt sehen wir noch nichts. Deshalb bauen wir eine Messagebox ein:
Führst du das Programm nun aus, sollte folgende Meldung erscheinen:
Änderst du die Zahlen, dann sollte auch ein anderes Resultat erscheinen.
So, und jetzt wollen wir die beiden Zahlen 4 und 5 nicht fix vorgeben, sondern erfragen. Und dies geschieht mit
der Funktion InputBox. Wir ersetzen obige ersten beiden Zeilen in folgendes:
Führst du die Prozedur wieder mit F5 aus, sollte folgende erste Meldung erscheinen:
Also ist unsere Beispiel-Prozedur soweit mal fertig:
Du siehst nach der dritten Zeile eine Leerzeile. Diese dient lediglich der besseren Lesbarkeit.
Mini-Tutorial Excel VBA
Seite 18 von 50 Version 1.1 © 2012, Daniel Hofer
Die Messagebox nervt
Gerade bei der Entwicklung einer Prozedur, wenn du noch nicht fertig bist, nervt es dich vermutlich ziemlich
rasch, dass bei jedem Ausführen die blöde Messagebox erscheint. Für dich als Entwicklerin gibt es dafür eine
coole Hilfe, und zwar geht es um das sog. Direktfenster.
Das Direktfenster oder den Direktbereich siehst du im unteren Teil des VBA-Editors:
Fehlt er bei dir, kannst du ihn im Menü „Ansicht“ einblenden. Dies ist mal die Voraussetzung für das, was wir jetzt
machen wollen.
Wir ändern nämlich jetzt die Zeile
MsgBox vResultat
Auf den folgenden Befehl:
Debug.Print vResultat
Jetzt führst du die Prozedur nochmals aus, gibst die zwei Zahlen ein, und schaust, was passiert:
Anstelle der lästigen Messagebox, welche immer das ganze Excel blockiert, hast du die Ausgabe nun in diesem
Direktfenster. Dies ist vor allem während der Entwicklung eines Programms äusserst praktisch. Ganz am Schluss
kannst du das Debug.Print wieder ändern auf MsgBox.
Du darfst diese Methode der Ausgabe in das Direktfenster so häufig einsetzen, wie du möchtest. Das ist wirklich
ein Hilfsmittel für dich.
Mini-Tutorial Excel VBA
© 2012, Daniel Hofer Version 1.1 Seite 19 von 50
Ich würde als Beispiel das Programm noch so erweitern, dass auch die Variablen vZahl1 und vZahl2 ausgegeben
werden. Das könnte als Beispiel so aussehen:
Das Resultat sieht dann so aus:
Das ist für dich schon eine prima Kontrolle, um zu sehen, ob die Variablen auch korrekt gefüllt werden und ob am
Schluss auch richtig gerechnet wird. Ich würde die Zeilen sogar noch erweitern:
Unten siehst du gleich die Ausgabe. Das hilft, vor allem, wenn du die Prozedur einige Monate nicht mehr
angeschaut hast, enorm. So musst du nicht lange überlegen, was diese Zahlen im Direktfenster wohl bedeuten.
Unschönheit des Direktfensters
Das Unschöne am Direktfenster ist leider, dass dies nicht automatisch gelöscht wird. Du musst also von Hand
reinklicken, am besten mit Ctrl & a alles markieren und mit Delete löschen. Ein wenig mühsam, aber man
gewöhnt sich daran...
Wenn die Inputbox nervt...
Auch die Inputbox wird dir irgendwann auf den Kecks gehen. Aus diesem Grund ersetzt man häufig diese Zeilen
während der Entwicklung mit fixen Zuordnungen, wie wir das ganz am Anfang dieses Beispiels schon gemacht
hatten.
Mini-Tutorial Excel VBA
Seite 20 von 50 Version 1.1 © 2012, Daniel Hofer
Kommentare
Es gibt in der Regel zwei Gründe, den Code mit sog. Kommentaren zu versehen:
1. Wir wollen beschreiben, was der Code macht
2. Wir wollen gewisse Codezeilen kurzfristig deaktivieren
Beschreibung von Code
Du schreibst als Beispiel einige Zeilen Code, welche heute funktionieren. Du legst diese Excel-Datei weg, und in
einem halben Jahr gehst du wieder rein. Da bist du plötzlich ganz erstaunt, was du damals programmiert hast.
Das tönt jetzt zwar vielleicht komisch, ist aber in der Tat so. Das obige Programm ist nur ganz kurz. In der Praxis
schreibst du bedeutend längere Programme, vielleicht zum Teil mit einigen hundert Zeilen. Und hier ist es wichtig
(nicht für andere, sondern für dich ganz alleine), das Zeugs sauber zu beschriften.
Ich habe jetzt mal als Beispiel vorherige Prozedur beschriftet:
Die Beschriftung ist wunderschön rot. Und wie wir wissen, ist rot gefährlich...also habe ich was falsch gemacht. Ja
klar, eine Beschriftung in VBA muss immer mit einem Hochkomma beginnen am Anfang der Zeile (mit der Taste
rechts der Zahl 0). Dann sieht der Code nämlich so aus:
Das sieht schon besser aus. Und genau das kannst du dir merken: grüne Zeilen werden von VBA überhaupt nicht
beachtet. Diese dienen wirklich nur als Kommentar.
Mini-Tutorial Excel VBA
© 2012, Daniel Hofer Version 1.1 Seite 21 von 50
Was sollte man kommentieren?
In der Regel schreibt man grad unterhalb des Prozedurnamens (also in der Zeile 2) eine kurze Beschreibung, was
die grundsätzliche Aufgabe dieser Prozedur ist. Du glaubst es nicht, aber ich habe bei mir auch alte Prozeduren,
bei welchen ich heute keinen Plan mehr habe, wozu ich diese geschrieben habe...
Bei speziellen Zeilen kannst du auch dahinter (oder natürlich auch oberhalb) einen Kommentar schreiben, damit
du später noch weisst, wozu diese Zeile gut ist. Z.B. so:
Tipp: lieber zu viel dokumentieren als zu wenig. Aber ich weiss auch, dass ich hier Wasser predige...ich mache es
nämlich in vielen Fällen auch nicht konsequent und habe dann genau das Problem, dass ich nicht mehr weiss, was
ich eigentlich wollte...
Code-Zeilen deaktivieren
Man sagt dieser Technik auch, man würde Code auskommentieren.
Du hast als Beispiel noch die lästige Messagebox im Code:
Willst du diese Zeile nun kurzfristig (manchmal auch länger) deaktivieren, kommentierst du sie aus, indem du
voran ein Hochkomma stellst:
Jetzt wird diese Zeile deaktiviert und nicht mehr ausgeführt. Das brauchst du häufig auch, wenn du einen Fehler
im Code hast, den du einfach im Moment nicht findest. Damit du trotzdem weiter programmieren kannst,
kommentierst du die fehlerbehaftete Zeile einfach aus. Irgendwann reparierst du sie und kannst sie wieder
aktivieren.
Funktionen
Jetzt ist es an der Zeit, die Idee einer Funktion zu erklären. Du erinnerst dich, dass eine Prozedur einfach ein
Ablauf von oben nach unten ist. Die Funktion machte genau das Gleiche, nur dass diese am Schluss einen Wert
(z.B. eine Zahl oder einen Text) zurückgibt.
Nun, wozu braucht man das? Ich zeige dir ein Beispiel.
Mini-Tutorial Excel VBA
Seite 22 von 50 Version 1.1 © 2012, Daniel Hofer
Angenommen, du hast folgende Prozedur geschrieben:
Du siehst, ich war sogar anständig, und habe einen Kommentar eingefügt.
Es geht nun darum, mit den gegebenen Werten das Endkapital zu rechnen.
Klar könntest du jetzt einfach die Zinseszins-Formel einfügen und gut ist:
vEndkapital = vStartkapital * (1 + vZinssatz) ^ vJahre
Aber jetzt kommt genau der Clou: entweder wird eine solche Formel unglaublich kompliziert und du möchtest es
ein wenig vereinfachen, oder du weisst nicht, wie man das Problem löst.
Du weisst als Beispiel einfach, dass du gerne das Endkapital rechnen möchtest, hast aber keinen Plan, wie das
geht. Du weisst aber, dass das eine Kollegin von dir grad kennt, und sie soll das für dich machen.
Dann würdest du deine Prozedur vielleicht wie folgt ergänzen:
Du schreibst also die Zeile mit dem Endkapital hin, schreibst aber eine benutzerdefinierte Funktion hin. In den
Klammern dieser Funktion musst du die Werte übergeben, wie du das bei einer beliebigen Excel-Funktion auch
machen musst.
Die Idee davon ist, dass „man“, d.h. entweder du oder sonst wer (deine Kollegin als Beispiel) in einer separaten
Funktion genau diese Berechnung von vorher vornimmt.
Du als Entwicklerin der Prozedur pKapitalberechnung kümmerst dich nicht um den Inhalt der Funktion fZins,
sondern überlässt dies jemand anderem.
Mini-Tutorial Excel VBA
© 2012, Daniel Hofer Version 1.1 Seite 23 von 50
Jetzt lassen wir unsere Prozedur mal laufen. Nun, das funktioniert noch nicht. Beim Ausführen der Prozedur
erscheint natürlich folgendes:
VBA ist soweit also noch cool. Es sagt dir sogar, welche Prozedur oder Funktion nicht definiert ist. Und diesmal hat
VBA natürlich Recht. Wir haben diese Funktion noch gar nicht definiert. Mehr zu Fehlerbehandlungen später.
Definition der Funktion
Unterhalb der Prozedur kannst du folgende Zeilen eingeben:
Das sieht also genau gleich aus wie die Prozedur, ausser dass Sub halt jetzt Function steht. Ganz korrekt ist diese
Definition noch nicht, aber das erkläre ich später.
Wir müssen allerdings noch was erledigen. Und zwar müssen wir ihm innerhalb der beiden Klammern sagen,
welche Werte die Funktion aufnehmen soll. Wir wissen ja, dass du das Startkapital, den Zins und die Laufzeit mir
übergibst, und so muss ich die Funktion auch definieren:
Beachte, dass ich hier vor den Variablennamen kein kleines v geschrieben habe. Das ist ein eher unwichtiges
Detail. Es sieht beim Aufruf der Funktion einfach schöner aus.
Testen der Funktion
Auch wenn die Funktion im Moment noch leer ist, kannst du sie testen, indem du die Prozedur aufrufst. Im
Direktfenster müsste nun die Zahl 0 stehen. Die Funktion rechnet ja noch überhaupt nichts; also kommt auch
einfach 0 raus.
Nochmals einen Funktionsaufruf testen
Gib in der Prozedur an einer beliebigen Stelle nochmals folgendes ein:
Mini-Tutorial Excel VBA
Seite 24 von 50 Version 1.1 © 2012, Daniel Hofer
vEndkapital = fZins(
und schau, was geschieht! Weil wir die Funktion nämlich jetzt vorgängig definiert haben, erhalten wir auch von
unserer eigenen Funktion eine kleine Hilfe in VBA in Form eines Tooltips:
Du weisst also beim Aufruf, dass du der Funktion 3 Parameter übergeben musst: Startkapital, Zinssatz und Jahre.
Funktion funktionsfähig machen
Jetzt geht es darum, unsere noch leere Funktion mit der gewünschten Funktion zu füllen.
Normalerweise definiert man für das Resultat der Berechnung wieder eine Variable (wie wir das schon gemacht
haben):
Jetzt kannst du in einem ersten Schritt mal eine sog. Dummy-Berechnung durchführen. Diese ist noch nicht
korrekt, aber immerhin siehst du, ob überhaupt was gerechnet wird. Als Beispiel machen wir folgendes:
Du siehst, rechnerisch ist das Blödsinn. Aber egal, wir schauen, ob der überhaupt was macht.
Führe mal die Prozedur aus. Was kommt raus? Null? Hehe Genau, leider sind wir noch nicht ganz fertig. Die
Berechnung wird zwar durchgeführt (wie du das rausfindest, siehst du im Kapitel „Rekapitulation
Prozeduren/Funktionen
Also, nochmals als kurze Zusammenfassung. Der Aufbau einer Prozedur sieht immer so aus:
Im Gegensatz dazu sieht die Funktion so aus:
Der einzige Unterschied ist also der Rückgabewert.
Mini-Tutorial Excel VBA
© 2012, Daniel Hofer Version 1.1 Seite 25 von 50
Ob wir nun innerhalb der Klammern Werte übergeben wollen, ist unabhängig von Prozedur oder Funktion. Beide
Programmblöcke können Werte von anderen Teilen aufnehmen.
Ablauf eines Programms kontrollieren“ auf Seite 25).
Das Problem ist im Moment, dass wir zwar etwas rechnen und das Resultat sogar in der Variablen vResult drin
haben. Aber was dann? Das Resultat bleibt dort. Es muss jedoch zurückgegeben werden. In dieser Zeile wollen wir
ja dem Endkapital das Resultat der Berechnung zuordnen:
Der Funktion fZins übergeben wir zwar korrekt 3 Werte, aber sie gibt uns noch nichts zurück. Das ist das Problem.
Dies lässt sich aber ganz einfach lösen. Und zwar braucht es nur noch eine einzige Zeile in der Funktion, ganz am
Schluss:
Wir übergeben quasi der Funktion selber den Wert des Resultats. So, und wenn du die Prozedur jetzt ausführst,
wird ein Resultat rauskommen.
Korrektur der Funktion
Einen Schönheitsfehler hat unsere Funktion noch. Wenn wir schon einen Rückgabewert definieren, müssen wir
korrekterweise noch sagen, um welchen Datentypen es sich handelt (mehr zu Datentypen auf Seite 30). Das
macht man durch folgende Angabe ganz am Schluss der Zeile:
Funktion noch fertig definieren
Jetzt müssen wir nur noch unsere Dummy-Berechnung durch die richtige Berechnung ersetzen. Am Schluss sieht
die Funktion wie folgt aus:
Rekapitulation Prozeduren/Funktionen
Also, nochmals als kurze Zusammenfassung. Der Aufbau einer Prozedur sieht immer so aus:
Mini-Tutorial Excel VBA
Seite 26 von 50 Version 1.1 © 2012, Daniel Hofer
Im Gegensatz dazu sieht die Funktion10 so aus:
Der einzige Unterschied ist also der Rückgabewert.
Ob wir nun innerhalb der Klammern Werte übergeben wollen, ist unabhängig von Prozedur oder Funktion. Beide
Programmblöcke können Werte von anderen Teilen aufnehmen.
10 Hier einfach dran denken, als letzte Zeile der Funktion selber noch das Resultat der Berechnung zu übergeben, Bsp: fTest = vResult
Mini-Tutorial Excel VBA
© 2012, Daniel Hofer Version 1.1 Seite 27 von 50
Ablauf eines Programms kontrollieren
Bis jetzt haben wir immer das Programm vollständig ausgeführt, also entweder mit der Taste F5 oder mittels Play-
Button. So haben wir entweder ein zufriedenstellendes Resultat erhalten oder nicht. Falls es nicht geklappt hatte,
war es eine sehr gute Frage, was wohl falsch sein könnte.
Der VBA-Editor bietet uns einige Hilfen, um den Ablauf ein wenig zu steuern resp. um Fehler rascher zu finden.
Schauen wir nochmals folgenden Code an (vorheriges Zins-Beispiel):
Es gibt in VBA nun zwei Möglichkeiten, die Prozedur pKapitalbestimmung einigermassen geordnet durchlaufen zu
können:
Entweder durch schrittweises Abarbeiten
Oder durch Setzen von sog. Haltepunkten
Schrittweises Abarbeiten
Sorge wie immer dafür, dass du dich mit dem Cursor in der Prozedur befindest. Danach kannst du entweder
folgenden Punkt auswählen:
oder, wie rechts davon angegeben, die Taste F8 drücken. Also anstelle F5 drücken wir F8. Das wirkt sich wie folgt
Mini-Tutorial Excel VBA
Seite 28 von 50 Version 1.1 © 2012, Daniel Hofer
aus:
Die erste Zeile der Prozedur wird plötzlich gelb. Das heisst für dich, dass das Programm aktuell grad am Laufen ist,
aber jetzt gerade mit angezogener Handbremse da steht. Also bitte jetzt nicht nach Hause gehen oder Kaffee
trinken.
Jetzt geht es darum, dass du mit der Taste F8 jeweils eine Zeile weiterkommst im Code (einige Zeile werden z.T.
übersprungen, das ist normal):
Was soll nun das Ganze? Jetzt kommt ein kleiner Trick. Fahr mal mit der Maus auf die Variable vStartkapital, ohne
zu klicken. Dann sollte nämlich ein Tooltip erscheinen:
So können wir den Inhalt der Variablen kontrollieren. Gehen wir mit F8 eine Zeile weiter, und kontrollieren mit
der Maus nochmals, sollte dort nun 10000 drin stecken:
Das ist das Geheimnis am Ganzen. So kannst du nach jeder Zeile schauen, ob die Variable wirklich den Inhalt
erhalten hat, welcher du erwartet hast.
Sobald du ab einer Stelle im Code weisst, dass jetzt alles gut läuft, musst du nicht unbedingt mit F8 jede Zeile
durchklicken, sondern du kannst nach Belieben jederzeit F5 drücken. Danach wird das Programm weiter
ausgeführt bis zum Schluss.
Mini-Tutorial Excel VBA
© 2012, Daniel Hofer Version 1.1 Seite 29 von 50
Haltepunkte
Du weisst z.B. im folgenden Code, dass dieser bis zum roten Pfeil tadellos funktioniert. Erst danach gibt’s irgend
ein Problem. So macht es doch keinen Sinn, vorher jede Zeile mit F8 durchzuklicken, um dann schliesslich an der
gewünschten Zeile anzukommen? Damit du hier möglichst wenig Arbeit hast, kannst du mit der Maus genau dort
in dieser Zeile, wo der rote Pfeil ist, in den grauen Bereich klicken.
Diese Zeile sieht dann so aus:
Dies ist diesmal kein Fehler, sondern sagt uns, dass wir jetzt einen Haltepunkt haben. Du kannst das Programm
wie gewohnt mit F5 starten, und es stoppt automatisch bei jedem Haltepunkt. Du kannst übrigens in einer
Prozedur oder Funktion beliebig viele Haltepunkte einbauen. Ich habe meistens auch mehrere definiert.
Denk einfach dran, vor der Auslieferung der Excel-Datei die Haltepunkte wieder zu entfernen, sonst sind deine
Anwenderinnen und Anwender leicht irritiert, wenn plötzlich der VBA-Editor erscheint.
Mini-Tutorial Excel VBA
Seite 30 von 50 Version 1.1 © 2012, Daniel Hofer
Datentypen
Wir haben bereits gesehen, dass Variablen beliebige Werte aufnehmen können, um diese zwischen zu speichern.
Nun müssen wir vorgängig immer bestimmen, welche Werte eine Variable aufnehmen kann. Zwei Datentypen
kennst du schon: Integer und Single. Das sind zwei Datentypen, welche unterschiedliche Arten von Zahlen
aufnehmen können. Daneben gibt’s noch Datentypen wie Boolean, Date, String, etc. Hier mal eine kurze Liste mit
jeweils einem Beispiel:
Datentyp Code-Beispiel Beschreibung
Boolean Dim vMitglied as Boolean vMitglied = True
Variablen vom Datentyp Boolean nehmen nur zwei Zustände an: True oder False resp. 1 oder 0
Byte Dim vWochentag As Byte vWochentag = 7
Der Datentyp Byte kann nur Zahlen von 0 bis 255 enthalten.
Integer Dim vMonat As Integer vMonat = 12
Integer fasst Zahlen von -32768 bis +32767. Vorsicht: Integer kennt keine Nachkommastellen.
Long Integer Dim vKundenNr As Long vKundenNr = 45367
Dieser Datentyp ist sehr gross, er umfasst von rund -2Mia bis +2Mia. Auch Long Integer kann nur ganze Zahlen speichern.
Single Dim vZins As Single vZins = 0.063
Single dient dazu, auch rationale und reelle Zahlen zu speichern. Nebst Single gibt’s noch den Datentypen Double, welcher noch mehr speichern kann11.
String Dim vName As String vName = „Huber“
Dieser Datentyp brauchst du häufig, er nimmt nämlich beliebigen Text auf.
Hinweis: es gibt noch den Datentypen Variant. Dieser kann einen beliebigen Wert annehmen. Bitte deklariere
aber nicht jede Variable mit Variant. Das wird sehr langsam und braucht sehr viel Speicher. Variant wird nur in
ganz speziellen Fällen benötigt (ein Beispiel siehst du im Kapitel „IF-Funktion“ auf Seite 32.
Option Explicit
In einem Programm hast du irgendwann sehr viele Variablen deklariert und verlierst vermutlich den Überblick.
Jetzt kann es passieren, dass du plötzlich vergisst, Variablen zu deklarieren, sie aber trotzdem verwendest. Das
heisst, irgendwo in deinem Code steht ein:
vOrt = „Bern“
Aber du hast weiter oben nirgends eine Deklaration:
Dim vOrt As String
Leider akzeptiert VBA in der Standard-Einstellung, dass man Variablen Werte zuweisen kann, ohne sie vorher zu
deklarieren. Dies ist äusserst unschön und Fehler-anfällig. Die Gefahr, dass du irgendwo mit einer Variablen i
arbeitest, dann aber plötzlich aufgrund eines Schreibfehlers der Variablen j einen Wert zuweist, ist extrem gross.
Die Fehlersuche kann hier z.T. Stunden dauern!
11 Arbeitest du mit Beträgen, arbeite besser nicht mit Single oder Double, sondern mit Currency. Erstere beiden Datentypen haben Probleme mit der Genauigkeit, d.h. es tauchen Rundungsprobleme auf.
Mini-Tutorial Excel VBA
© 2012, Daniel Hofer Version 1.1 Seite 31 von 50
Aus diesem Grund lohnt es sich, jeweils im ersten Modul einer Datei ganz oben (noch oberhalb der ersten
Prozedur oder Funktion), folgende Zeile einzufügen:
Damit du das nicht jedes Mal von Hand eingeben musst, kannst du auch in die Optionen vom VBA-Editor:
Da aktivierst du folgende Option:
Wenn du dich nämlich jetzt vertippst, dann erscheint eine Fehlermeldung:
Mini-Tutorial Excel VBA
Seite 32 von 50 Version 1.1 © 2012, Daniel Hofer
Kontrollstrukturen
Unter Kontrollstrukturen versteht man einerseits WENN-Funktionen und Schleifen. Nur dass unter VBA eine
WENN-Funktion IF heisst.
IF-Funktion
Wir schauen nochmals das vorherige Beispiel an:
Diesmal habe ich aber beim Startkapital eine Inputbox eingebaut, damit eine Meldung kommt. Das kennst du
bereits. Soweit haben wir kein Problem. Aber stell dir nun mal vor, jemand gibt in dieser Meldung folgendes ein:
Wir erwarten eine Zahl als Startkapital, aber die Person hat Text eingegeben. Was geschieht nun? Nach dem Klick
auf OK erscheint die berüchtigte Fehlermeldung:
Wir haben oben in der ersten Zeile gesagt (deklariert), dass die Variable vStartkapital vom Datentyp Single ist. Das
heisst, sie kann beliebige Zahlen beliebiger Zahlenräume (ausser komplexe) aufnehmen. Aber Text kann sie nicht
aufnehmen. Du kannst übrigens auf „Debuggen“ klicken, dann erscheint der VBA-Editor und das Programm
wartet an der Zeile, welche den Fehler verursacht hat. Da kannst du als Beispiel mit F8 weitergehen.
Du als Entwicklerin bist nun verantwortlich, dass diese komische Fehlermeldung nicht mehr erscheint. Und genau
dies kannst du mit einem IF erledigen. Zuerst müssen wir aber noch eine kleine Änderung machen:
Ich habe eine neue Variable vInput deklariert, welche jetzt den speziellen Datentypen Variant enthält. Nun
weisen wir nicht direkt der Variablen vStartkapital das Resultat der Funktion Inputbox zu, sondern der Variablen
vInput. Und da wir nicht wissen, ob da Text oder eine Zahl daher kommt, deklarieren wir diese als Variant.
Mini-Tutorial Excel VBA
© 2012, Daniel Hofer Version 1.1 Seite 33 von 50
Das kannst du auch bereits testen. Die Variable vInput enthält nun entweder eine Zahl oder sonst einen Blödsinn.
Nun geht es darum, zu kontrollieren, ob dort drin eine Zahl steckt.
Dafür benötigen wir einerseits eine Funktion, welche testet, ob vInput eine Zahl oder ein Text ist, andererseits
verpacken wir das Ganze in eine IF-Funktion. In Pseudocode (Umgangssprache) sieht das etwa so aus:
IF vInput is numeric THEN
da dürfen wir weiterrechnen
ELSE
Fehlermeldung zeigen
END IF
Die funktionsfähige Prozedur sieht so aus:
Die Funktion IsNumeric gibt den Wert True zurück, falls die überprüfte Variable eine Zahl ist, sonst gibt sie False
zurück. In der Prozedur berechnen wir das Endkapital nur dann, wenn die Person in der Inputbox wirklich eine
Zahl eingegeben hat.
Man kann dieses Problem auf verschiedene Arten lösen; dies ist einfach mal eine Möglichkeit.
Schleifen
Ich zeige dir hier nur einige wenige Typen von Schleifen, einfach, damit du mal eine Ahnung davon hast.
For-Schleife
Die For-Schleife sieht grundsätzlich so aus:
For i = 1 to 10
Debug.Print i
Next i
Mini-Tutorial Excel VBA
Seite 34 von 50 Version 1.1 © 2012, Daniel Hofer
Diese Schleife wird genauso oft durchlaufen, wie du das in der ersten Zeile angibst; hier in diesem Fall 10x. Die
Variable i muss natürlich vorgängig wie üblich deklariert werden. Das Programm dazu sieht so aus:
Nach der Ausführung solltest du 10 Zahlen im Direktfenster haben. Wir werden später sehen, wie du damit auf
Excel-Zellen zugreifen kannst.
Do-Until-Schleife
Der Name sagt es eigentlich schon: die Schleife wird solange durchgelaufen, bis du entscheidest, dass sie
abgebrochen werden soll. Obiges Beispiel mit einer solchen Schleife sieht so aus:
Während die For-Schleife automatisch nach oben zählt, musst du hier manuell mit i=i+1 dafür sorgen, dass die
Variable i erhöht wird. Wenn du das nicht machst, gibt’s eine Endlosschleife12.
Do-While-Schleife
Diese Schleife ist fast identisch mit der vorherigen, nur dass die Abbruch-Bedingung bereits am Anfang geklärt
wird:
Häufig spielt es keine Rolle, was du nimmst. Aber definiere vorgängig i mal mit i=12. Die Do-Until-Schleife wird
trotzdem einmal ausgeführt (weil die Prüfung ja erst am Schluss erfolgt). Die Do-While-Schleife wird gar nie
durchgeführt.
In der Praxis merkst du jeweils relativ rasch, welche Schleife praktisch ist.
12 Welche du mit Ctrl & Pause (Break) unterbrechen musst
Mini-Tutorial Excel VBA
© 2012, Daniel Hofer Version 1.1 Seite 35 von 50
Fehler in VBA Es geht in diesem Kapitel nicht darum, mittels Code Fehler zu beheben, sondern wie du als Entwicklerin darauf
reagieren kannst.
Allgemeines
Der wichtigste Grundsatz ist: du musst zwingend dafür sorgen, dass keine sog. Laufzeitfehler bei den Kundinnen
und Kunden eintreten. Also immer wenn eine Meldung wie die folgende erscheint, ist das schlecht:
Die Leute wissen nicht, wie sie darauf reagieren sollen. Klicken sie auf „Beenden“, geschieht gar nix. Die Hilfe ist
eh nicht brauchbar, und mit einem Klick auf „Debuggen“ kommen sie in den VBA-Editor. Und das wollen wir
definitiv nicht.
Aus diesem Grund haben wir nach der Inputbox diese mühsame Abfrage mit If und IsNumeric, damit eben keine
Fehlermeldung kommt.
Du als Entwicklerin klickst natürlich bei obiger Meldung auf „Debuggen“. Dann springt Excel dorthin, wo der
Fehler liegt:
Diese gelb markierte Zeile verursacht den Fehler. Jetzt überlegst du dir, warum dieser Fehler verursacht wird. Hier
ist es recht offensichtlich: die Variable i haben wir als Integer definiert. Sie kann also nur ganze Zahlen
aufnehmen. Wir wollen ihr in dieser Zeile aber den Text „hallo“ übergeben. Und genau das geht nicht.
Mini-Tutorial Excel VBA
Seite 36 von 50 Version 1.1 © 2012, Daniel Hofer
Folgender Code sollte Zahlen im Direktbereich ausgeben:
Dummerweise erscheint dort gar nichts. Entweder siehst du sofort, dass wir die Variable j ausgeben wollen, diese
aber nirgends deklariert13 wird und auch nicht mit einem Wert zugewiesen wird (ich habe mich ganz einfach
vertippt). Wenn du den Fehler nicht sofort siehst, dann ist jetzt der nächste Schritt, die Prozedur mit F8
schrittweise durchzugehen.
Vielleicht hast du schon gemerkt, dass der Beginn in Ordnung ist, also könntest du gleich hier einen Haltepunkt
definieren:
Und wenn wir jetzt mitF5 die Prozedur ausführen, springt VBA genau dort hin:
Und jetzt fährst du mit der Maus auf die Variable j:
Und schon hast du das Problem festgestellt. Achtung! In der Praxis weisst du zwar jetzt, dass die Variable leer ist,
was sie aber nicht sein dürfte. Jetzt musst du den Fehler suchen gehen, WARUM die Variable leer ist. Dies ist
manchmal ein wenig mühsam...
13 Hätten wir ein „Option Explicit“ am Anfang des Dokuments, wäre eine Fehlermeldung gekommen, und wir hätten das Problem sofort gefunden.
Mini-Tutorial Excel VBA
© 2012, Daniel Hofer Version 1.1 Seite 37 von 50
Probleme mit If-Verzweigungen
Schau dir mal folgende Prozedur an:
Es ist die gleiche wie vorher, nur geben wir nur dann was aus, wenn i=12. Auch diese Prozedur gibt nichts raus.
Hier bringt ein Haltepunkt in der Verzweigung nichts:
Du weisst ja gar nicht, ob die Bedingung im If überhaupt jemals erfüllt ist. Also setzt du einen Haltepunkt genau
auf das If:
Jetzt startest du die Prozedur mit F5, das Programm sollte an der Stelle halten:
Mini-Tutorial Excel VBA
Seite 38 von 50 Version 1.1 © 2012, Daniel Hofer
Jetzt fährst du mit der Maus wieder auf die Variable i:
Es macht übrigens sogar Sinn, auch kurz die anderen Variablen anzuschauen. Ev. hast du dich irgendwo vertippt,
aber siehst es grad nicht. So findest du Fehler recht schnell. Aber jetzt zurück zu unserem Beispiel:
Jetzt überlegst du dir, ob die Variable i den korrekten Wert hat. Sie hat den Wert 1 im Moment. Das scheint gut zu
sein. Jetzt drücken wir F5, und die Schleife wird einmal durchgelaufen und hält wieder dort. Wir kontrollieren die
Variable wieder:
Das ist ja die Idee der Schleife, somit sieht das recht gut aus. Meistens merkst du in der Praxis den Fehler. Wir
haben ja die Bedingung If i = 12. Und da realisierst du: Moment, die Variable i kann ja gar nie den Wert 12
erreichen, da die For-Schleife bei i=10 ja bereits verlassen wird.
Manchmal sieht man es nicht so rasch, und muss halt noch einige Male weiter mit F5 oder F8 den Code
durcharbeiten.
Dies sind übrigens sehr häufige Fehler, welche man noch nach Jahren Programmiererfahrung macht. Aber das ist
halb so tragisch; du wirst jeweils immer schneller im Finden von deinen eigenen Fehlern.
Mini-Tutorial Excel VBA
© 2012, Daniel Hofer Version 1.1 Seite 39 von 50
Interaktion mit Excel Bis jetzt hast du dich immer innerhalb von VBA bewegt. Die einzige „Kommunikation“ mit einer Person war eine
Messagebox oder eine Inputbox. Jetzt wollen wir kurz anschauen, wie du mittels VBA konkret Zellen verändern
und formatieren kannst.
Hinter Excel und seinen Mappen und Blättern steckt ein sog. Objektmodell. Jedes Ding, das wir in Excel
ansprechen, ist ein Objekt. So ist die Mappe ein Objekt, jedes Blatt, aber auch jede Zelle.
Die Kunst ist jetzt, herauszufinden, wie diese Objekte heissen. Da man sich das Zeugs nie merken kann, wenn man
nicht so häufig in Excel programmiert, habe ich mal eine Excel VBA-Referenz geschrieben. In dieser findest du die
wichtigsten Themen.
Für die folgenden Ausführungen habe ich Excel und den VBA-Editor so am Bildschirm angeordnet, dass wir beides
sehen:
Ich habe auch gleich in neues Modul „Modul3“ eingefügt, damit wir Platz haben.
Tabellenblätter
Tabellenblätter auswählen
Um ein bestimmtes Tabellenblatt auszuwählen, geben wir in einer Prozedur folgendes ein:
Du siehst, auch hier benötigen wir einen Rahmen, sprich eine Prozedur um die eigentliche Code-Zeile.
Mini-Tutorial Excel VBA
Seite 40 von 50 Version 1.1 © 2012, Daniel Hofer
Blätter können auch mit ihrem Index angesprochen werden:
Sheets(1).Select
Jetzt wird einfach das erste Blatt ausgewählt (Excel zählt immer von links).
Wählst du ein Blatt aus, das es nicht gibt, erscheint folgende Meldung:
Dies ist eine klassische Fehlermeldung im Zusammenhang mit Excel-Objekten. Du siehst, dass die Fehlermeldung
nicht wirklich deutlich sagt, wo das Problem liegt. Das musst du selber rausfinden.
Tabellenblätter umbenennen
Du kannst ein Blatt wie folgt umbenennen:
Sheets("Tabelle1").Name = "Januar"
Zellen
Mit Zellen kann man relativ viel anstellen. Hier findest du die wichtigsten Aktionen.
Zellen auswählen
Eine Zelle kann auf zwei Arten ausgewählt werden:
Erstens über das Objekt Cells
Zweitens über das Objekt Range (Bereich)
Zellen über das Cells-Objekt auswählen
Cells(1, 1).Select
Beachte bei der Eingabe des Befehls die Hilfe im Tooltip:
VBA sagt dir also, dass zuerst die Zeile, und erst danach die Spalte verlangt wird (in Excel selber ist es ja grad
umgekehrt: A1 heisst, zuerst Spalte und danach Zeile).
Mini-Tutorial Excel VBA
© 2012, Daniel Hofer Version 1.1 Seite 41 von 50
Wichtig! Dieser Befehl wird immer im aktuellen Blatt ausgeführt. Hast du versehentlich ein „falsches“ Blatt
aktiviert (z.B. ein Diagramm-Blatt), dann erscheint eine merkwürdige Fehlermeldung:
Leider ist diese ziemlich unklar definiert; damit musst du in VBA ein wenig leben. Meistens sind die
Fehlermeldung unverständlich.
Willst du also sicherstellen, dass du auf dem korrekten Blatt die entsprechende Zelle markierst, kannst du das
Blatt mit angeben:
Sheets("Tabelle2").Select
Cells(1, 1).Select
Zelle mit dem Range-Objekt ansprechen
Eine Alternative für:
Cells(1,1).Select
ist die folgende:
Range(„A1“).Select
Manchmal geht das am einfachsten, manchmal bist du aber froh, wenn du statt des Buchstaben As im Range-
Objekt mit Zahlen arbeiten kannst. Ein Beispiel siehst du unter „Mittels einer Schleife Zellen füllen“ auf Seite 42.
Willst du einen ganzen Bereich markieren, geht das mit:
Range(„A1:C3“).Select
In Zellen reinschreiben
In Zellen schreiben mittels Cells-Objekt
Das Reinschreiben in eine Zelle geht ganz einfach:
Cells(1,1) = „Hallo“
Mini-Tutorial Excel VBA
Seite 42 von 50 Version 1.1 © 2012, Daniel Hofer
Schau mal dazu den folgenden Screenshot an:
Fällt dir was auf? Wir befinden uns ja gar nicht in Zelle A1!!
Und genau das ist das Geheimnis! Sehr viele Leute (auch der Makrorekorder...) springen zuerst mit
Cells(1,1).Select
In die Zelle, um danach dort was reinzuschreiben:
Selection = „Hallo“
Dies solltest du dir so rasch als möglich abgewöhnen resp. gar nie machen. Wehe, ich sehe das mal: dann ziehe
ich dir die Ohren lang. Nein im Ernst: immer zuerst auf eine Zelle zu springen, um dann was reinzuschreiben,
dauert extrem viel länger als wenn man das nicht macht. Und in der Praxis ist dies häufig wirklich relevant. Falls
du das grad am Lesen bist, dann rufe mich doch kurz; ich zeige dir den Unterschied!
In Zellen schreiben mittels Range-Objekt
Auch das geht ganz einfach:
Range(„A3“) = 5
Weil wir hier auch wieder von einem Bereich sprechen, kannst du natürlich auch gleich einen ganzen Bereich mit
Zeugs füllen:
Range("a3:c6") = 5
Mittels einer Schleife Zellen füllen
Jetzt wollen wir kurz anschauen, warum Schleifen in Zusammenhang mit Excel manchmal praktisch sind.
Angenommen, du willst in den Zellen A1 bis A10 Zahlen reinschreiben, und zwar jeweils auch die Zahlen 1 bis 10.
Mini-Tutorial Excel VBA
© 2012, Daniel Hofer Version 1.1 Seite 43 von 50
Mit einer Schleife könnte dies wie folgt aussehen:
Der Einsatz des Cells-Objekts ist hier sehr praktisch. Wir können einfach den Zeilenindex der Cells-Auflistung
durch unsere Variable i ersetzen.
Würden wir das mit dem Range-Objekt erledigen, dann müssten wir den Bereich A1, A2, A3, etc. dynamisch
erzeugen, was so aussehen könnte:
Beide Varianten funktionieren prima. Stellst du dir aber nun vor, dass du die Zellen A1 bis J1 füllen willst, dann
bist du mit Cells(1,i) viel besser beraten, als mit Range(„A1“). Wie willst du das A in einer Schleife dynamisch
ändern? Es gibt keine For-Schleifen für Buchstaben!
Das Range-Objekt mit Cells kombinieren
Obiges Beispiel kannst du insofern lösen, wenn du das Range-Objekt mit Cells kombinierst. Vielleicht hast du bei
der Eingabe von Range( schon gesehen, dass die Hilfe so erscheint:
Mini-Tutorial Excel VBA
Seite 44 von 50 Version 1.1 © 2012, Daniel Hofer
Also will VBA scheinbar eine Zelle, aber man könnte noch eine weitere Zelle angeben. Das ist wirklich so. Ein
Bereich kann ja mehr als eine Zelle bedeuten.
Dies können wir wie üblich so lösen:
Range("A1", "B3").Select
(vergleiche: vorher haben wir „A1:B3“ geschrieben. Es geht beides.)
Wenn wir aber Zellen angeben, müssen wir dies mittels Cells-Auflistung erledigen:
z.B. Cells(1,1) oder Cells(3,2)
Wenn wir beides kombinieren, sieht das so aus:
Range(Cells(1, 1), Cells(3, 2)).Select
Zellformatierung anpassen
Willst du als Beispiel die Hintergrundfarbe der Zelle A1 ändern auf grün, dann geht das so:
Range("A1").Interior.Color = 434223
Ist doch logisch oder?
Hehe, das war natürlich gemein. Wie willst du erraten, wie diese Zahl lauten soll? Und richtig: vergiss es. Solchen
Blödsinn können wir weder wissen noch lernen wir das auswendig. Und warum braucht es plötzlich noch dieses
blöde Wort „Interior“ dabei? Auch das ist merkwürdig.
Also benötigen wir eine andere Taktik, und diese lautet: Makro-Rekorder.
Formatierungen mit dem Makro-Rekorder rausfinden
Der Makro-Rekorder produziert zwar häufig Blödsinn, aber wenn es darum geht, herauszufinden, wie man die
Hintergrundfarbe auf grün ändert, oder wie man einen doppelten Rahmen unten definiert, kommen wir nicht
drum herum, den Rekorder zu verwenden.
Als Ausgangslage befinden wir uns beim vorherigen Beispiel:
Mini-Tutorial Excel VBA
© 2012, Daniel Hofer Version 1.1 Seite 45 von 50
Du willst rechts in der Prozedur pTest1 die Zelle A1 grün einfärben. Beachte, dass wir im Moment ein einziges
Modul „Modul1“ in dieser Datei haben.
Jetzt wechselst du ins Excel ins Register „Entwicklertools“ und zeichnest ein Makro auf:
Den Namen des Makros musst du nicht ändern. Wir brauchen den Code ja nur temporär. Danach löschen wir
diese Prozedur „Makro1“ eh wieder. Aber du musst dir merken, wie das Makro heisst! Vielleicht zeichnest du in
einem VBA-Projekt mehrere Male mit dem Makro-Rekorder auf. Da verlierst du schnell den Überblick.
So, wenn nun der Rekorder läuft, änderst du die Hintergrundfarbe auf dein gewünschtes grün:
Mini-Tutorial Excel VBA
Seite 46 von 50 Version 1.1 © 2012, Daniel Hofer
Und jetzt sofort den Rekorder wieder stoppen:
Wenn du jetzt in den VBA-Editor schauen gehst, sieht das so aus:
Du befindest dich rechts immer noch in deiner Prozedur, aber links hat‘s ein neues Modul erstellt (das macht VBA
automatisch beim Aufzeichnen eines Makros!). Mit einem Doppelklick auf „Modul2“ siehst du rechts den Inhalt:
Jetzt haben wir genau das Problem, dass das Makro viel mehr aufgezeichnet hat, als wir eigentlich wollten. Und
vor allem ist die Schreibweise kompliziert (aber sinnvoll, siehe nächster Abschnitt). Eigentlich interessiert uns nur
die Zahl hinter .Color. Alles andere ist eigentlich egal.
Das ist genau das Problem in der Praxis: du musst ein wenig rausfinden (und dazu braucht’s Übung), welche
dieser Zeilen für dich überhaupt wichtig ist.
Und schau mal den unglaublich sinnvollen Kommentar, den der Rekorder eingefügt hat...
Mini-Tutorial Excel VBA
© 2012, Daniel Hofer Version 1.1 Seite 47 von 50
Was bedeutet das Wort With?
Im obigen Screenshot siehst du das Wort With. Dies ist äusserst praktisch, weil es hilft, den Code ein wenig zu
vereinfachen. Hätten wir das Wort nicht, und wir wollten wirklich alle die obigen Formatierungen erledigen,
würde unser Code so aussehen:
Selection.Interior.Pattern = xlSolid
Selection.Interior.PatternColorIndex = xlAutomatic
Selection.Interior.Color = 5296274
Selection.Interior.TintAndShade = 0
Selection.Interior.PatternTintAndShade = 0
Und du siehst, dass wir 5x Selection.Interior. geschrieben haben. Und genau dies kann mit einem With abgekürzt
werden:
Was soll dieses Selection?
Das Selection-Objekt repräsentiert die gerade aktive Markierung. Meistens gibst du aber direkt einen Bereich an
mit z.B. Range(„A1:C3“). Wie schon weiter oben erklärt, markierst du üblicherweise einen Bereich nicht
vorgängig. Aus diesem Grund darfst du den Code nicht einfach kopieren und bei deiner Prozedur reinkopieren,
sondern du kopierst nur das Notwendige:
Es kommt noch schlimmer...
Am schlimmsten wird der Code, wenn du mit dem Rekorder die Zelle A1 so änderst, dass du unten eine doppelte
Rahmenlinie kriegst:
Und genau das ist jetzt wirklich totaler Schrott, was der Rekorder da gemacht hat!!!
Mini-Tutorial Excel VBA
Seite 48 von 50 Version 1.1 © 2012, Daniel Hofer
Du hast lediglich gesagt, dass er eine doppelte Rahmenlinie unten machen soll. Du hast NICHT gesagt, er soll die
Linie oben, links und rechts rausnehmen. Aber genau diesen Blödsinn macht der Rekorder. Das Einzige, was du in
diesem Fall wirklich brauchst, ist folgender Teil, welcher wirklich die untere Linie behandelt:
Benutzerdefinierte Funktionen Wie schon auf Seite 2 beschrieben, kann man VBA auch zur Erstellung von benutzerdefinierten Funktionen
verwenden. Ich habe damals das Beispiel mit der Anrede gebracht. Willst du mittels WENN-Funktion in Excel eine
Briefanrede zusammenbasteln, dann sieht das so aus:
Hast du allerdings eine benutzerdefinierte Funktion geschrieben, dann sieht der Aufruf so aus:
Dieser Aufruf „=fAnrede(D3)“ ist doch um Welten einfacher als die WENN-Funktion. Nun, dies stimmt zwar für die
Person, welche unser Excel-Sheet verwendet. Wir als Entwicklerinnen und Entwickler haben dafür jetzt die Arbeit.
Wir müssen nämlich das WENN nachbilden.
Benutzerdefinierte Funktionen kommen immer in ein Modul, nie in eine Objekt-Mappe:
Mini-Tutorial Excel VBA
© 2012, Daniel Hofer Version 1.1 Seite 49 von 50
Es spielt keine Rolle, ob im entsprechenden Modul schon etwas drinsteht oder nicht. Du kannst selber
entscheiden, wo die Funktion rein soll. Der Rumpf der Funktion sieht als Beispiel so aus14:
Jetzt kannst du mittels IF-Statement die WENN-Funktion ersetzen. Das könnte z.B. so aussehen:
Du siehst, dass in diesem Beispiel „Sehr geehrte Damen und Herren“ zurückgegeben wird, falls VBA die Anrede
nicht finden kann. Du könnest auch eine Fehlermeldung zurückgeben. Je nach Praxisanwendung würde das mehr
Sinn machen. Der Code-Ausschnitt könnte so aussehen:
Anstelle von IF-Statements könntest du auch ein SELECT…CASE verwenden. Dies ist eine andere Art. Aber obige
Version mit IF resp. ELSEIF ist die schnellste Version.
Du siehst, dass es je nach Anzahl von Anreden, die du abdecken möchtest, doch recht umfangreich wird. Wie
gesagt, jemand hat Aufwand. Entweder die Anwenderin resp. der Anwender, oder halt eben wir als Entwickler.
Grenzen von benutzerdefinierten Funktionen
Obiges Beispiel ist zwar äusserst praktisch und für die AnwenderInnen eine grosse Hilfe. Aber was ist, wenn
zusätzliche Anreden dazu kommen? Dann musst jedes Mal DU den Code anpassen. Es wäre schön, wenn die
AnwenderInnen selber Anreden mit den dazugehörigen Briefanreden erfassen könnten.
Deshalb ist dieses Beispiel hier unter Umständen mittels Excel-Funktion SVERWEIS besser und einfacher lösbar.
Denn dort können die Anreden und Briefanreden im Tabellenblatt stehen. Oder vielleicht würde eine
Kombination aus beiden Varianten Sinn machen. Dies müssen wir jeweils für jeden Fall einzeln anschauen.
14 Das Option Explicit hat natürlich nichts direkt mit unserer Funktion zu tun. Aber ich schreibe es trotzdem immer wieder auf, damit du dich daran gewöhnst, und es nicht weglässt.
Mini-Tutorial Excel VBA
Seite 50 von 50 Version 1.1 © 2012, Daniel Hofer
Ausblick Das war ein erster sanfter Einblick in die Welt der Excel-Programmierung. Das Wichtigste am Ganzen ist die
Übung. Bücher lesen nützt wenig; du musst wirklich üben, üben, üben. Häufig wirst du frustriert sein, aber
irgendwann geht es besser.
Meine Excel VBA-Referenz kann dir helfen, nach längerer Pause den Einstieg rascher zu finden. Sonst suche dir
beim Stauffacher ein Buch, das dir sympathisch erscheint. Im Internet gibt’s übrigens auch sehr viele Hilfen. Eine
Seite, bei der du auch Fragen stellen kannst, ist http://www.office-loesung.de. Dabei handelt es sich um ein
extrem gut besuchtes Forum rund um Microsoft Office.
Willst du mir persönlich eine Frage stellen, dann gehe doch auf meine Seite www.officehilfe.ch. Dort findest du
einerseits schon einige Tipps & Tricks, andererseits kannst du mir da ein Email schreiben. Falls deine Frage auch
für andere EntwicklerInnen interessant sein könnte, werde ich sie auf dieser Seite veröffentlichen.