Universittsrechenzentrum

Johannes Gogolok Abt. Wiss. Anwendungen URZ B/012/9911

VBA Programmierung mit Excel Grundlagen

REIHE: INTEGRIERTE SYSTEME

VBA Programmierung mit Excel Grundlagen

FernUniversitt Hagen November 1999

Seite 2

Inhaltsverzeichnis1 2 EINLEITUNG .................................................................................................................................... 7 DAS ERSTE EXCEL-PROGRAMM EIN AUFGEZEICHNETES MAKRO.................................... 9 2.1 MAKRO AUFZEICHNEN .................................................................................................................. 9 2.1.1 Eine Beispieltabelle............................................................................................................ 9 2.1.2 Die Makroaufzeichnung ................................................................................................... 10 2.1.3 Die Ausfhrung des Makros............................................................................................. 11 2.1.4 Verwaltung von Makros.................................................................................................... 12 2.2 ZUORDNUNG ZU TASTENKOMBINATIONEN, SCHALTFLCHEN UND MENS ..................................... 12 2.2.1 Aufruf ber Tastenkombinationen .................................................................................... 12 2.2.2 Aufruf ber Schaltflchen................................................................................................. 13 2.2.3 Aufruf ber Symbolleisten ................................................................................................ 14 3 VBA GRUNDLAGEN ..................................................................................................................... 17 3.1 3.2 3.3 3.4 4 OBJEKTE UND IHRE HIERARCHIE ................................................................................................. 17 EIGENSCHAFTEN........................................................................................................................ 19 METHODEN ............................................................................................................................... 19 EREIGNISSE .............................................................................................................................. 20

DIE ENTWICKLUNGSUMGEBUNG.............................................................................................. 21 4.1 4.2 4.3 4.4 DER PROJEKT-EXPLORER .......................................................................................................... 22 DER OBJEKTKATALOG ................................................................................................................ 23 DER PROGRAMMEDITOR ............................................................................................................ 25 DAS DIREKTFENSTER ................................................................................................................. 26

5

DAS SPRACHKONZEPT VON VBA ............................................................................................. 27 5.1 DATENTYPEN ............................................................................................................................. 29 5.2 OPERATOREN, OPERANDEN, AUSDRCKE .................................................................................. 29 5.2.1 Ausdrcke ........................................................................................................................ 29 5.2.2 Operatoren ....................................................................................................................... 30 5.3 VARIABLEN, KONSTANTEN, ARRAYS ........................................................................................... 32 5.3.1 Konstanten ....................................................................................................................... 32 5.3.2 Integrierte Konstanten...................................................................................................... 33 5.3.3 Variablen .......................................................................................................................... 33 5.3.4 Arrays (Datenfelder)......................................................................................................... 35 5.3.5 Dynamische Arrays .......................................................................................................... 36 5.3.6 Benutzerdefinierte Datentypen......................................................................................... 37 5.3.7 Objektvariablen ................................................................................................................ 37 5.4 KONTROLLSTRUKTUREN ............................................................................................................. 38 5.4.1 Entscheidungsstrukturen (Verzweigungen) ..................................................................... 38 5.4.2 Schleifenstrukturen .......................................................................................................... 42 5.5 KONVERTIERUNG UND MANIPULATION VON DATEN. ..................................................................... 48 5.5.1 Ermittlung des Datentyps ................................................................................................. 48 5.5.2 Konvertieren von Datentypen........................................................................................... 49 5.5.3 Manipulieren von Daten ................................................................................................... 49

6

UNTERPROGRAMMTECHNIK ..................................................................................................... 53 6.1 PROZEDUREN ............................................................................................................................ 53 6.1.1 Aufruf und Parameterbergabe........................................................................................ 53 6.1.2 Optionale Argumente ....................................................................................................... 55 6.2 BENUTZERDEFINIERTE FUNKTIONEN ........................................................................................... 55

7

ABLAUFSTEUERUNG I ................................................................................................................ 57 7.1 7.2 7.3 7.4 7.5 7.6 CURSORPOSITION FESTSTELLEN................................................................................................. 57 VERSETZEN DES CURSORS (OFFSET METHODE) ...................................................................... 57 ZELLEN GEZIELT AUSWHLEN ..................................................................................................... 58 INHALTE IN EINZELNE ZELLEN EINTRAGEN.................................................................................... 59 FORMELN IN ZELLEN EINTRAGEN ................................................................................................ 59 AUSSCHNEIDEN ......................................................................................................................... 59

7.7 7.8 7.9 8

KOPIEREN ................................................................................................................................. 59 EINFGEN ................................................................................................................................. 60 ASCII W ERTE / ASCII ZEICHEN........................................................................................... 60

DIALOGE (TEIL I) .......................................................................................................................... 63 8.1 MSGBOX ................................................................................................................................... 63 8.2 INPUTBOX ................................................................................................................................. 66 8.2.1 Die Funktion InputBox...................................................................................................... 66 8.2.2 Die Methode InputBox...................................................................................................... 67

9

TABELLENNAVIGATION.............................................................................................................. 68 9.1 ABSOLUTE POSITIONIERUNG AUF ZELLEN UND ZELLBEREICHE ..................................................... 69 9.1.1 Positionierung ber die Range Methode....................................................................... 69 9.1.2 Positionierung ber die Cells - Eigenschaft ..................................................................... 70 9.2 RELATIVE POSITIONIERUNG AUF ZELLEN UND ZELLBEREICHE ....................................................... 71 9.3 ZUGRIFF AUF TABELLENBLTTER UND ARBEITSMAPPEN ............................................................... 72

10 MANIPULATION VON ZELLEN UND ZELLBEREICHEN ............................................................ 73 10.1 AUSWAHL VON ZELLEN UND ZELLBEREICHEN .............................................................................. 73 10.2 EINFGEN VON ZELLEN, ZEILEN UND SPALTEN ............................................................................ 74 10.3 ZUWEISEN VON ZELLINHALTEN ................................................................................................... 74 10.4 LSCHEN VON ZELLINHALTEN UND ZEILEN .................................................................................. 75 10.5 EINFGEN VON KOMMENTAREN .................................................................................................. 76 10.6 BENENNEN VON ZELLEN UND ZELLBEREICHEN............................................................................. 77 10.7 SUCHEN VON ZELLINHALTEN ...................................................................................................... 78 10.8 SUCHEN VON ZELLENINHALTEN BER SCHLEIFEN ........................................................................ 78 10.9 SCHRIFTEN, RAHMEN, FARBEN .................................................................................................. 80 10.9.1 Zuordnung von Schriften.................................................................................................. 80 10.9.2 Zuordnung von Rahmen .................................................................................................. 81 10.9.3 Zuordnung von Farben..................................................................................................... 82 11 AKTIONEN AUF TABELLENBLTTERN..................................................................................... 83 11.1 11.2 11.3 EINFGEN NEUER TABELLENBLTTER ......................................................................................... 83 VERSCHIEBEN VON TABELLENBLTTERN ..................................................................................... 84 TABELLENBLTTER AKTIVIEREN .................................................................................................. 84

12 DIALOGE (TEIL II) ......................................................................................................................... 85 12.1 DAS TABELLENBLATT ALS FORMULAR ......................................................................................... 85 12.1.1 Vorbereitende Arbeiten .................................................................................................... 85 12.2 FORMULARSTEUERELEMENTE..................................................................................................... 86 12.3 EIN TABELLENFORMULAR FFNEN .............................................................................................. 86 12.4 BEISPIELANWENDUNG 1 ............................................................................................................. 87 12.5 STEUERELEMENTE AUS DER TOOLBOX ........................................................................................ 93 12.5.1 Einfgen der Elemente ins Tabellenblatt ......................................................................... 93 12.5.2 Eigenschaften der Elemente ............................................................................................ 93 12.5.3 Das Bezeichnungsfeld (Label) ......................................................................................... 95 12.5.4 Schaltflchen, Wechselschaltflchen (CommandButton, ToggleButton) ........................ 96 12.5.5 Textfelder (TextBox)......................................................................................................... 96 12.5.6 Listen, Kombinationsfelder (ListBox, ComboBox)............................................................ 97 12.5.7 Drehfelder, Laufleisten (SpinButton,ScrollBar) ................................................................ 99 12.5.8 Kontrollkstchen, Optionsfelder (CheckBox, OptionButton) ............................................ 99 12.5.9 Verbindung Zelle Steuerelement ................................................................................ 100 12.5.10 Blattschutz .................................................................................................................. 100 12.6 BEISPIELANWENDUNG 2 ........................................................................................................... 100 12.7 SELBSTDEFINIERTE DIALOGE - USERFORM ............................................................................... 106 12.7.1 Beispiel 1........................................................................................................................ 106 12.7.2 Beispiel 2........................................................................................................................ 107 12.7.3 Beispiel 3........................................................................................................................ 109 13 ANHANG ...................................................................................................................................... 113 13.1Seite 4

BEISPIELPROGRAMM 1 ............................................................................................................. 113

13.2 13.3 13.4 13.5

BEISPIELPROGRAMM 2 ............................................................................................................. 116 EINFACHE USERFORM 1 .......................................................................................................... 119 EINFACHE USERFORM 2 .......................................................................................................... 119 USERFORM 3 .......................................................................................................................... 120

14 SCHLUBEMERKUNG ............................................................................................................... 123 15 LITERATURLISTE ....................................................................................................................... 125

Seite 5

Verzeichnis der AbbildungenAbbildung 1: Tabelle fr die Makroaufzeichnung............................................................................................. 9 Abbildung 2: Makrodefinition............................................................................................................................. 10 Abbildung 3: Makroaufruf .................................................................................................................................. 11 Abbildung 4: Tabelle nach mehrfacher Ausfhrung des Beispielmakros................................................... 11 Abbildung 5: Symbolleiste Formular ................................................................................................................ 13 Abbildung 6: Schaltflche im Tabellenblatt..................................................................................................... 13 Abbildung 7: Zuordnung eines Makros zur Schaltflche .............................................................................. 13 Abbildung 8: Generieren einer Symbolschaltflche zum Makroaufruf........................................................ 14 Abbildung 9: Schaltflchen - Definitionsmen................................................................................................ 15 Abbildung 10: Objekthierarchie von Excel (Ausschnitt) ................................................................................ 18 Abbildung 11: Die VB-Symbolleiste ................................................................................................................. 21 Abbildung 12: Die VBA-Entwicklungsumgebung (hier mit Editor) ............................................................... 21 Abbildung 13: Der Projekt - Explorer ............................................................................................................... 22 Abbildung 14: Der Objektkatalog...................................................................................................................... 23 Abbildung 15: Bibliotheksauswahl.................................................................................................................... 23 Abbildung 16: Suchergebnis im Objektkatalog............................................................................................... 24 Abbildung 17: Das Fenster des Programmeditors......................................................................................... 25 Abbildung 18: Methoden- / Eigenschaften - Auswahl ................................................................................... 25 Abbildung 19: Das Direktfenster....................................................................................................................... 26 Abbildung 20: Das Modulfenster ...................................................................................................................... 27 Abbildung 21: Ergebnis des Prozeduraufrufs................................................................................................. 54 Abbildung 22: MsgBox - Dialogfeld.................................................................................................................. 65 Abbildung 23: Dialogfeld InputBox ................................................................................................................... 66 Abbildung 24: INPUTBOX mit dem Ergebnis einer Bereichsmarkierung ...................................................... 68 Abbildung 25: Die Symbolleiste FORMULAR .................................................................................................... 86 Abbildung 26: Tabellenblatt des Beispielprogramms .................................................................................... 87 Abbildung 27: Formularblatt whrend der Datenerfassung.......................................................................... 89 Abbildung 28: Formularblatt nach Abschlu des Erfassungsvorganges.................................................... 90 Abbildung 29: Datenkorrektur (nur eingetragene Daten sichtbar)............................................................... 91 Abbildung 30: Inhalt des Blattes Hilfe .............................................................................................................. 92 Abbildung 31: Dialog Eigenschaften am Beispiel einer Schaltflche.......................................................... 94 Abbildung 32: Unterschiedliche Formen des LABEL -Feldes ........................................................................ 95 Abbildung 33: Unterschiedliche Formen des Schaltflchen......................................................................... 96 Abbildung 34: Formen von Textfeldern ........................................................................................................... 96 Abbildung 35: Einfache Listenfelder ................................................................................................................ 97 Abbildung 36: Mehrspaltiges Listenfeld........................................................................................................... 98 Abbildung 37: Ein mehrspaltiges Kombinationsfeld ...................................................................................... 98 Abbildung 38: Drehfeld und Laufleiste............................................................................................................ 99 Abbildung 39: Options- und Kontrollfelder ...................................................................................................... 99 Abbildung 40: Formularblatt Programmbeispiel 2........................................................................................ 101 Abbildung 41: Lschabfrage zu Bsp. 2.......................................................................................................... 103 Abbildung 42: Online-Hilfe in einer USERFORM ............................................................................................ 104 Abbildung 43: Formular aus Programm 2 nach Datenerfassung .............................................................. 105 Abbildung 44: VBA - Dialogfenster mit Werkzeugsammlung.................................................................... 106 Abbildung 45: Einfacher VBA-Dialog ............................................................................................................. 106 Abbildung 46: Einfacher VBA-Dialog (2) ....................................................................................................... 107 Abbildung 47: UserForm des Beispiels 3 ...................................................................................................... 109 Abbildung 48: UserForm mit Password - Abfrage ...................................................................................... 109 Abbildung 49: Ausgeflltes Buchungsformular ............................................................................................ 111 Abbildung 50: Buchungstabelle des Programmbeispiels............................................................................ 112

Seite 6

Einleitung

1 EinleitungEXCEL ist bereits in seiner Standardform ein mchtiges Instrument zur Tabellenkalkulation mit vielen Formen und Funktionen der Kalkulation, der Datenanalyse und prsentation. In Verbindung mit VBA (VISUAL BASIC FOR APPLICATIONS) wird es zu einem noch mchtigeren Entwicklungssystem, welches es dem Anwender erlaubt, das Spektrum des Programms an die eigenen Bedrfnisse anzupassen und die Anwendungen betrchtlich zu erweitern. VBA ist allerdings ein sehr umfangreiches Instrument, dessen Beherrschung stndige bung erfordert. Es enthlt sehr viele Sprachelemente. Deren vollstndige Behandlung wrde den Rahmen dieser Broschre sprengen. Deshalb erhebt diese Unterlage nicht den Anspruch, VBA vollstndig zu behandeln, sondern nur jene Arbeitsanweisungen vorzustellen, die zur Steuerung von VBA-Programmen und die Erstellung benutzerdefinierter Ablufe bentigt werden. Sie soll den Anwender in die Lage versetzen, das Grundgerst eines VBA-Programms erstellen zu knnen und dieses um Anweisungen zu ergnzen, die eine der zu lsenden Aufgabe angepate Verarbeitung mittels eigener Anwendungen gestatten. Zwar sind die interaktiven Funktionen von EXCEL immer flexibler und umfangreicher geworden, parallel dazu hat sich aber auch die Programmierbarkeit entwickelt. Aus einer recht einfachen Makro Sprache ist eine recht umfangreiche, objektorientierte Programmiersprache geworden, die den Vergleich mit anderen Entwicklungssystemen nicht frchten mu. Das VBA ist in EXCEL in englischer Sprache implementiert. Die EXCEL Funktionen sind jedoch in der deutschen Version von EXCEL in deutscher Sprache definiert, obwohl VBA sie im Programmcode auf Englisch erwartet. Kleiner Tip schon an dieser Stelle: Im leeren Tabellenblatt den Makrorekorder starten, Funktion in eine Zelle eintragen und den aufgezeichneten Programmcode ins Programm bertragen. Fr die erfolgreiche Arbeit mit dieser Unterlage sind mindestens gute Grundkenntnisse von WINDOWS und EXCEL erforderlich. Vorausgesetzt werden auch zumindest Grundkenntnisse des VB (Visual Basic), weil VB den Sprachkern von VBA liefert. Im Text der Unterlage befinden sich am rechten Textrand Hinweiszeichen mit der folgenden Bedeutung:

= = Is Like Bedeutung kleiner als kleiner oder gleich grer als grer oder gleich gleich ungleich Objektvergleich Mustervergleich

Die zu vergleichenden Ausdrcke mssen vom Typ her vertrglich sein. Zustzlich gehren in den Bereich der Vergleichsoperatoren noch zwei, in der Tabelle nicht aufgefhrte Operatoren: IS vergleicht zwei Variablen in Bezug darauf, ob sie auf dasselbe Objekt verweisen:Ergebnis = Objekt1 Is Objekt2

Wenn sowohl Objekt1 als auch Objekt2 auf dasselbe Objekt verweisen, ist das Ergebnis TRUE, andernfalls ist das Ergebnis FALSE.Seite 30

Operatoren, Operanden, Ausdrcke

LIKE dient dem Vergleich einer Zeichenfolge mit einem Muster:Ergebnis = Zeichenfolge Like Muster

Das Argument MUSTER kann unterschiedliche Zeichen, Listen mit Zeichen, Platzhalter oder Zeichenbereiche enthalten: