EDV-Skript - Hochschule Trier

Skript zu Vorlesung
EDV I
Stand 15.05.2016
EDV I
Inhalt
1
2
3
4
ERSTE SCHRITTE ...................................................................................................................................... 5
1.1
STARTEN ...................................................................................................................................................... 5
1.2
STARTOBERFLÄCHE ......................................................................................................................................... 5
1.3
HILFE FUNKTION ............................................................................................................................................ 6
ARBEITSUMGEBUNG ............................................................................................................................... 6
2.1
DAS MENÜBAND ........................................................................................................................................... 6
2.2
DIE STANDARTREGISTER .................................................................................................................................. 7
2.3
KONTEXTSENSITIVE REGISTER ........................................................................................................................... 9
2.4
MINIMIEREN DES MENÜBANDS ........................................................................................................................ 9
DATEIEN UND ARBEITSMAPPEN .............................................................................................................. 9
3.1
DATEIEN SPEICHERN ....................................................................................................................................... 9
3.2
KONZEPT DER ARBEITSMAPPE......................................................................................................................... 10
3.3
ARBEITSBLATT ............................................................................................................................................. 10
3.4
AUSBLENDEN/FIXIEREN................................................................................................................................. 10
EINFACHE BEARBEITUNG ...................................................................................................................... 11
4.1
MARKIEREN ................................................................................................................................................ 11
4.2
AUSFÜLLEN................................................................................................................................................. 11
4.3
VERSCHIEBEN .............................................................................................................................................. 12
4.4
KOPIEREN................................................................................................................................................... 12
4.5
AUTO AUSFÜLLEN ........................................................................................................................................ 13
4.6
EINFÜGEN / LÖSCHEN ................................................................................................................................... 13
4.7
FORMATIERUNG .......................................................................................................................................... 13
4.8
BEDINGTE FORMATIERUNG ............................................................................................................................ 14
4.9
KOMMENTARE ............................................................................................................................................ 14
4.10
5
ÜBUNGEN ZUR EINFACHEN BEARBEITUNG .................................................................................................... 14
FORMELN / FUNKTIONEN...................................................................................................................... 15
5.1
FORMELN ................................................................................................................................................... 15
5.2
RELATIVE UND ABSOLUTE ZELLBEZÜGE ............................................................................................................. 15
5.3
NAMEN ..................................................................................................................................................... 16
5.4
OPERATOREN .............................................................................................................................................. 16
5.5
FUNKTIONEN .............................................................................................................................................. 16
5.6
FUNKTIONSASSISTENT ................................................................................................................................... 17
5.7
EXTERNE BEZÜGE......................................................................................................................................... 17
2
EDV I
6
7
8
9
10
5.8
SPEZIELLE MATRIXFUNKTIONEN ...................................................................................................................... 18
5.9
ZIELWERTSUCHE .......................................................................................................................................... 18
5.10
SOLVER ................................................................................................................................................. 19
5.11
ÜBUNGEN ZU FORMELN UND FUNKTIONEN .................................................................................................. 20
DIAGRAMME ......................................................................................................................................... 22
6.1
ERSTELLEN VON DIAGRAMMEN....................................................................................................................... 22
6.2
ÜBUNGEN ZU DIAGRAMME............................................................................................................................ 23
FORMULARSTEUERELEMENTE ............................................................................................................... 23
7.1
ANWENDUNG VON FORMULARSTEUERELEMENTEN ............................................................................................. 23
7.2
ÜBERSICHT DER STEUERELEMENTE .................................................................................................................. 24
7.3
ÜBUNGEN ZU FORMULARSTEUERELEMENTE ...................................................................................................... 25
MAKROS ............................................................................................................................................... 26
8.1
DER MAKRO REKORDER ................................................................................................................................ 26
8.2
GRENZEN DER MAKRO PROGRAMMIERUNG ...................................................................................................... 26
8.3
BEISPIELANWENDUNG .................................................................................................................................. 26
8.4
STARTEN EINES AUFGEZEICHNETEN MAKROS ..................................................................................................... 29
8.5
ÜBUNGEN ZU MAKROS ................................................................................................................................. 29
VBA-UMGEBUNG .................................................................................................................................. 29
9.1
DER PROJEKTEXPLORER ................................................................................................................................. 29
9.2
EIGENSCHAFTSFENSTER ................................................................................................................................. 30
9.3
OBJEKTKATALOG .......................................................................................................................................... 31
9.4
DIREKTFENSTER ........................................................................................................................................... 31
9.5
LOKALFENSTER ............................................................................................................................................ 31
9.6
ÜBERWACHUNGSFENSTER ............................................................................................................................. 32
VBA-PROGRAMMIERUNG ..................................................................................................................... 33
10.1
PROGRAMM ERSTELLEN ............................................................................................................................ 33
10.2
RESERVIERTE WÖRTER ............................................................................................................................. 35
10.3
VARIABLEN DEKLARIEREN .......................................................................................................................... 35
10.4
KONSTANTE ........................................................................................................................................... 38
10.5
LITERALE ............................................................................................................................................... 39
10.6
DATENFELDER ........................................................................................................................................ 39
10.7
KONTROLLSTRUKTUREN ............................................................................................................................ 40
10.8
SCHLEIFEN ............................................................................................................................................. 45
10.9
MODULARE PROGRAMMIERUNG ................................................................................................................ 47
3
EDV I
11
12
13
OBJEKTE IN EXCEL ................................................................................................................................. 50
11.1
OBJEKTE ................................................................................................................................................ 50
11.2
OBJEKTE BENENNEN ................................................................................................................................ 51
11.3
EIGENSCHAFTEN VON OBJEKTEN ................................................................................................................ 51
11.4
METHODEN VON OBJEKTEN ...................................................................................................................... 52
11.5
EREIGNISSE VON OBJEKTEN ....................................................................................................................... 52
11.6
EIGENSCHAFTEN UND METHODEN DES RANGE-OBJEKTS ................................................................................. 54
11.7
ZUGRIFF AUF ZELLEN................................................................................................................................ 55
11.8
INHALTE IN ZELLEN EINSETZEN UND ABFRAGEN .............................................................................................. 58
11.9
WICHTIGE EIGENSCHAFTEN UND METHODEN DER SHEETS-AUFLISTUNG........................................................... 60
APPLICATION EXCEL........................................................................................................................... 61
12.1
STANDARTSPEICHERORT EXCEL .................................................................................................................. 61
12.2
DAS APPLICATION OBJEKT ..................................................................................................................... 61
12.3
ÜBUNGEN ZUM APPLICATION OBJEKT ......................................................................................................... 63
BENUTZERDEFINIERTE FORMULARE ...................................................................................................... 63
13.1
BENUTZERFORMULAR HINZUFÜGEN ............................................................................................................ 63
13.2
EIGENSCHAFTEN EINES BENUTZERFORMULARS .............................................................................................. 64
13.3
METHODEN EINES BENUTZERFORMULARS .................................................................................................... 66
13.4
EREIGNISSE EINES BENUTZERFORMULARS ..................................................................................................... 66
13.5
STEUERELEMENTE IN BENUTZERFORMULAR EINFÜGEN .................................................................................... 66
13.6
STEUERELEMENTE PLATZIEREN ................................................................................................................... 67
13.7
EIGENSCHAFTEN VON STEUERELEMENTEN FESTLEGEN ..................................................................................... 68
13.8
EREIGNISPROZEDUREN PROGRAMMIEREN .................................................................................................... 69
13.9
WICHTIGE EREIGNISSE EINES FORMULARS .................................................................................................... 69
13.10
ÜBUNGEN ZU FORMULAREN...................................................................................................................... 70
14
GRUNDLAGEN DER FEHLERBEHANDLUNG ............................................................................................. 70
15
NASSI-SHNEIDERMAN-DIAGRAMM ....................................................................................................... 72
15.1
ÜBUNG ZUM NASSI-SHNEIDERMAN DIAGRAMM ........................................................................................... 72
16
TASTENKOMBINATIONEN IN EXCEL ....................................................................................................... 73
17
LITERATURVERZEICHNIS ........................................................................................................................ 76
18
TABELLENVERZEICHNIS ......................................................................................................................... 77
19
ABBILDUNGSVERZEICHNIS .................................................................................................................... 79
4
EDV I
Microsoft Excel 2013
1 Erste Schritte
1.1 Starten
Zum Starten der Anwendung klicken Sie entweder auf die Windows Taste auf der Tastatur oder
mit der Maus auf dem Desktop.
Abbildung 1 - Windows Taste
Anschließend geben Sie in das Suchfeld Excel 2013 ein und bestätigen dies mit Return (Enter).
1.2 Startoberfläche
Dies ist die Startoberfläche von Excel 2013. Auf dieser können verschiedene Vorlagen gewählt
werden, oder mit dem Punkt Weitere Arbeitsmappen öffnen bereits bestehende Dateien von dem
lokalen System oder aus dem OneDrive ausgewählt werden.
Abbildung 2 - Excel Startoberfläche
5
EDV I
1.3 Hilfe Funktion
Excel bietet eine Online Hilfe an die mit der Funktionstaste F1
geöffnet wird. Dieser Assistent ist als Nachschlagewerk ein sehr
nützliches Werkzeug im Bereich der Tabellenkalkulation. Die
Tastenkombinationen sind im Umgang mit Excel sehr nützlich.
Um zur Kontextsensitiven Hilfe zu gelangen muss die shift und F1
Taste gedrückt werden und dann auf das entsprechende Objekt
geklickt werden.
Abbildung 3 - Excel Hilfe
2 Arbeitsumgebung
2.1 Das Menüband
Abbildung 4 - Startansicht
6
EDV I
Abbildung 5 - Menüband - Ribbon
Ganz links befindet sich an herausgehobener Position das Register Datei. Die anderen Register
sind nach Aufgabenbereichen strukturiert und enthalten verschiedene, in Befehlsgruppen
zusammengefasste Schaltflächen.
Die Größe und die Anordnung der Befehlsgruppen und Schaltflächen werden entsprechend der
verfügbaren Auflösung des Bildschirmes variiert und automatisch angepasst. Im unteren Teil
einer Befehlsgruppe befindet sich deren Beschriftung.
2.2 Die Standartregister
Datei: Bietet Dateioperationen sowie die Backstage-Ansicht für weitere Aufgaben.
Start: Enthält die Befehle für die grundlegenden Arbeiten beim Eingeben von Daten:
Verwenden der Zwischenablage, direkte Formatierung von Zellen, Bedingte Formatierung und
Formatvorlagen, Suchen, Ersetzen und Filtern.
Einfügen: Enthält alles, was eingefügt werden kann: Pivot-Tabellen, Tabellen, Grafiken,
Diagramme verschiedenster Art, Symbole, Objekte, Textfelder aber auch Kopf und Fußzeilen.
Seitenlayout: Enthält die Einstellmöglichkeiten für das Dokument und die Seite, wie
Seitenränder und Ausrichtung und Drucktitel aber auch eine Auswahl vorgefertigter Designs.
Formeln: Bietet eine übersichtliche Auswahl der in Microsoft Office Excel 2010 verfügbaren
Funktionen, Tools zur Fehlersuche und zur Verwaltung benannter Bereiche im Arbeitsblatt.
7
EDV I
Daten: Umfasst Steuerelemente für den Import externer Daten, zum Filtern und Sortieren von
Datensätzen sowie zur Datenanalyse.
Überprüfen: Bietet Werkzeuge für die Rechtschreib- und Grammatikhilfe und damit verwandte
Funktionen wie Thesaurus und Übersetzung, sowie für die gemeinsame Nutzung von
Dokumenten beispielsweise in Arbeitsgruppen.
Ansicht: Erlaubt die unmittelbare Auswahl und Anpassung der Arbeitsansicht. So können
beispielsweise die Gitternetzlinien oder die Bearbeitungsleiste ein und ausgeblendet werden.
Entwicklertools: Dieses Register ist standardmäßig ausgeblendet und enthält Werkzeuge für die
Makrobearbeitung und VBA-Programmierung.
Um die Entwicklertools anzuzeigen gehen wird im Register Datei – Optionen – Menüband
anpassen
ein
haken
bei
Entwicklertools
gesetzt
und
dies
mit
OK
bestätigt.
Abbildung 6 - Registerkarte Entwicklertools einblenden
8
EDV I
2.3 Kontextsensitive Register
Je
nach
der
gerade
aktuellen
Arbeitssituation werden zusätzliche
Register
angeboten,
jedoch
nicht
automatisch eingeblendet. Dies betrifft
beispielsweise
die
Arbeit
mit
eingefügten grafischen Elementen oder
auch Kopf- und Fußzeilen. In der
Nebenstehenden Abbildung ist das
Angebotenen Register Diagrammtools
eingerahmt hervorgehoben
Abbildung 7 - Kontextsensitive Register
2.4 Minimieren des Menübands
Das Menüband beansprucht einen recht großen Teil der Arbeitsumgebung dies ist für die eigentliche
Arbeit im Arbeitsblatt eher hinderlich. Deshalb kann das Menüband minimiert werden. Dazu wird das
Kontextmenü
aufgerufen
und
die
Option
Menüband
reduzieren
ausgewählt.
Alternativ kann dies durch die Tastenkombination strg + F1 realisiert werden.
3 Dateien und Arbeitsmappen
3.1 Dateien Speichern
Dateien können Lokal oder im OneDrive gespeichert werden. Die Standard Dateiendung einer
Excel-Arbeitsmappe ist .xlsx . Um eine Datei zu Speichern gibt es mehrere Wege:
Speichern mit der Tastatur: strg + S
Speichern unter mit der Tastatur: F12
Speichern mit der Maus: Datei – Speichern
Speichern unter mit der Maus: Datei - Speichern unter
Der Befehl Speichern unter verhindert das Überschreiben der Daten einer alten Arbeitsmappe
9
EDV I
3.2 Konzept der Arbeitsmappe
Eine Arbeitsmappe besteht aus einer Datei. In einer Arbeitsmappe können beliebig viele
Tabellenblätter eingefügt werden, lediglich der Arbeitsspeicher begrenzt dies. Bei Start von
Excel ist Standardmäßig nur ein Blatt in einer Arbeitsmappe.
Ein weiteres Blatt kann durch klicken auf das Plus erzeugt
werden, aber auch mit der Tastenkombination Alt + shift + F1
Abbildung 8 - Neues Blatt
wird eine neues Blatt eingefügt. Arbeitsblätter können
mithilfe des Kontextmenüs gelöscht, verschoben/kopiert oder bearbeitet werden. Mit der
Verschiebe- und Kopieroperation können Arbeitsblätter auch in eine neue Arbeitsmappe
verschoben bzw. kopiert werden.
3.3 Arbeitsblatt
Ein Arbeitsblatt in Excel 2013 umfasst 16.384 Spalten und 1.048.576 Zeilen und damit
17.179.869.184 Zellen, von denen immer genau eine die sogenannte aktive Zelle ist. Die Spalten
sind üblicherweise mit Buchstaben nummeriert (A bis XFD) und die Zeilen mit arabischen
Zahlen (1 bis 1.048.576). In jeder Zelle können bis zu 32.767 Zeichen stehen.
3.4 Ausblenden/Fixieren
Zeilen oder Spalten können nach dem markieren
mithilfe des Kontextmenüs ausgeblendet werden.
Um diese wieder ein zu fügen, werden die beiden
benachbarten Zeilen/Spalten markiert und im
Kontextmenü der Punkt einblenden ausgewählt.
Fixieren von Spalten/Zeilen ist in großen Tabellen
durchaus sinnvoll. Den fixierten Bereich kann man nicht mehr verschieben.
1.
Markieren Sie die Zelle direkt unter bzw. rechts neben der zu fixierenden Überschrift.
2.
Klicken Sie im Register Ansicht in der Gruppe Fenster auf die Schaltfläche Fenster fixieren
und in der geöffneten Liste den Eintrag Fenster Fixieren
3.
Falls nur die Erste Spalte oder erste Zeile fixiert werden soll, so finden die beiden unteren
Punkte der Schaltfläche Fenster fixieren Anwendung.
4.
Um die Markierung aufzuheben wird auf die Schaltfläche Fixierung aufhaben geklickt.
10
EDV I
4 Einfache Bearbeitung
4.1 Markieren
Zum Markieren von Zellen gibt es mehrere Möglichkeiten
Maus
Tastatur
Einzelauswahl
einfaches klicken
Pfeiltasten
Bereichsauswahl
shift + klicken
Shift + Pfeiltasten
Mehrfachauswahl
strg + klicken
Nicht möglich
ganze Zeile/n
Auf Zeilen klicken
shift + Leertaste
ganze Spalte/n
Auf Spalten klicken
strg + Leertaste
ganze Tabelle
auf das
strg + shift + Leertaste oder
Dreieck
strg + A
klicken
Tabelle 1 - Möglichkeiten zur Zellwahl
4.2 Ausfüllen
Die Eingabe von Daten erfolgt immer in die Aktive Zelle.
Folgende Möglichkeiten gibt es um eine Eingabe zu bestätigen
return
Aktive Zelle wird eine Zeile nach unten Verschoben
return + shift
Aktive Zelle wird eine Zeile nach oben Verschoben
Tab
Aktive Zelle wird eine Spalte nach rechts Verschoben
Tab + shift
Aktive Zelle wird eine Spalte nach links Verschoben
Pfeiltasten
Aktive Zelle wird in jeweilige Richtung verschoben
Pos1
Die erste Zelle der Zeile wird zur aktiven Zelle
End
Aktive Zelle bleibt gleich
Maus
Die neu Ausgewählte Zelle wird zur aktiven Zelle
Tabelle 2 - Möglichkeiten zur Eingabebestätigung in einer Zelle
Um eine Eingabe abzubrechen kann die ESC-Taste benutzt werden.
Die Editierung der Dateien ist entweder durch einen Doppelklick in die aktive Zelle, ein Klick
in die Bearbeitungsleiste oder mithilfe der Taste F2 innerhalb der aktiven Zelle möglich.
11
EDV I
4.3 Verschieben
Beim Verschieben von Zellbezügen werden die Bezüge nicht angepasst. Hierzu ein Beispiel:
Ein Beispiel des Verschiebens von Zellbezügen: In der Zelle B2 steht = C3 , C3 wird nun nach
D5 verschoben  in der Zelle B2 steht nun =D5. Wird B2 nun nach E3 verschoben, so steht in
E3 =D5.
Menüleiste – Ausschneiden - Einfügen
Ausschneiden - Einfügen
Tastenkombination – strg +x – strg + v
Ausschneiden - Einfügen
Tastenkombination – shift + del – shift + ins
Ausschneiden - Einfügen
Tastenkombination – strg + +
Verschieben
Drag & Drop – Zellumrandung verschieben
Verschieben
Kontextmenü – Ausschneiden – Einfügen
Ausschneiden - Einfügen
Tabelle 3 - Verschiebeoperationen
4.4 Kopieren
Beim Kopieren von Zellbezügen werden die Bezüge inkrementell angepasst. Hierzu ein
Beispiel:
In der Zelle B2 steht =C3 , C3 wird nun nach D5 kopiert  in der Zelle D5 steht nun =E6 .
Menüleiste – Kopieren - Einfügen
Kopieren - Einfügen
Tastenkombination – strg +c – strg + v
Kopieren - Einfügen
Tastenkombination – strg +ins – shift + ins
Kopieren - Einfügen
Kontextmenü – Kopieren – Einfügen
Kopieren - Einfügen
Drag & Drop + strg –Zellumrandung verschieben
Kopieren
Tabelle 4 - Kopieroperationen
Die Kopieroperationen können mehrfach wiederholt werden mit der ESC-Taste beendet
werden.
12
EDV I
4.5 Auto Ausfüllen
Excel ist in der Lage sinnvolle Reihen zu ergänzen. Für diese automatische Füllung der Zellen
werden folgende Daten erkannt:
-
Reihen
Ausfüllkästchen Das kleine schwarze Kästchen in der
-
Daten
rechten unteren Ecke der Markierung. Wenn Sie auf das
-
Quartal
-
Wochentage
-
Monate
Abbildung 9 - Ausfüllkästchen
Ausfüllkästchen zeigen, nimmt der Mauszeiger die
Form eines schwarzen Kreuzes an.
Zum schnellen Ausfüllen von Zellen mit verschiedenen Typen von Datenreihen können Zellen
ausgewählt und mit dem Ausfüllkästchen gezogen werden. Für die Verwendung des
Ausfüllkästchens werden Zellen ausgewählt, die als Grundlage für das Ausfüllen weiterer
Zellen dienen. Das Ausfüllkästchen wird dann horizontal oder vertikal über die auszufüllenden
Zellen gezogen.
Alternativ kann im Register START bei der Befehlsgruppe Bearbeiten die Befehlsschaltfläche
Füllbereich verwendet werden
4.6 Einfügen / Löschen
Um in Excel Zeilen/Spalten einzufügen gibt es mehrere möglichleiten. Mithilfe des
Kontextmenüs werden Spalten/Zeilen links/über der Aktiven Zelle eingefügt. Mit der
Tastenkombination strg + + kann dieses Menü ebenfalls aufgerufen werden.
4.7 Formatierung
Um Zellen oder Zellbereiche zu formatieren, müssen diese ausgewählt sein. Um die
ausgewählten Zellen/Zellbereiche zu formatieren gibt es drei Möglichkeiten.
1
Erzeugen der Formatierung über die entsprechenden Elemente im Register Start.
2
Durch klicken des
Symbols im Register Start in der Gruppe Schriftart, Ausrichtung
oder Zahl. Dadurch öffnet sich ein Dialogfeld in dem Sie die gewünschten
Formatierungseinstellungen vornehmen können.
3
Öffnen des Kontextmenüs
Dadurch öffnet sich ein
Dialogfeld in dem Sie die gewünschten Formatierungseinstellungen vornehmen können.
Es können Textausrichtung, Schriftarten, Schriftgrößen, Farben, Rahmen und Zahlenformate
verändert werden.
13
EDV I
4.8 Bedingte Formatierung
Sinn und Zweck der bedingten Formatierung ist die
optische Hervorhebung von Zahlen, die eine bestimmte
Bedingung erfüllen. Auf diese Weise können Sie zum
Beispiel
Umsatzzahlen
kennzeichnen,
die
eine
vorgegebene Schwelle über- oder unterschreiten.
Wichtige bzw. kritische Daten einer Tabelle lassen sich
so auf einen Blick erfassen.
Mit den Befehlen dieses Menüs können Sie den Zellen
eines Tabellenblattes Regeln zuweisen, mit denen die
Formatierung der Zellen gesteuert wird.
4.9 Kommentare
Abbildung 10 - Bedingte Formatierung
Ein Kommentar dient dazu Ihre Arbeit zu dokumentieren um anderen die Lesbarkeit zu
erleichtern. Kommentare werden immer nur der aktiven Zelle zugeordnet. Sie werden entweder
über das Kontextmenü – Kommentar einfügen, oder über das Register Überprüfen – Neuer
Kommentar hinzugefügt.
4.10 Übungen zur Einfachen Bearbeitung
Dateivorlage: (keine Vorlage)
a) Eine Tabelle enthält in der 1. Zeile und der ersten Spalte je die Werte 1, 2, 3
usw. füllen Sie nur drei Zellen selbst aus, den Rest automatisch auf die Größe
10x10 Zellen.
b) Tragen Sie in die Zelle B2 eine Formel so ein, dass nach dem automatischen
Ausfüllen der gesamten Matrix in jeder Zelle das Produkt aus den ersten
Werten der jeweiligen Zeile und Spalte steht.
14
EDV I
5 Formeln / Funktionen
5.1 Formeln
Formeln und Funktionen
Unter einer Formel wird eine Verknüpfung von Werten (Operanden) mit (Rechen-) Operatoren
verstanden. Im Sinne der Tabellenkalkulation sollen als Werte üblicherweise Bezüge auf andere
Zellen des Tabellenblattes verstanden werden und nur in seltenen Ausnahmefällen direkte
Zahlen (Konstanten).
Als erstes Zeichen muss ein Gleichheitszeichen stehen
Eine Formel in Excel hat folgende Syntax:
Abbildung 11 - Syntax einer Formel
Falls in einer Formel mehrere unterschiedliche Operatoren verwendet werden, so wird folgende
Rangfolge dabei angewendet
Rang
Operator
1
-
2
3
4
5
%
^
* und /
+ und -
Beschreibung
Negation eines Wertes – das Minuszeichen steht dabei unmittelbar vor
einem Wert (z. B. -A1)
Division eines Wertes durch Hundert
Potenzierung eines Wertes
Multiplikation und Division
Addition und Subtraktion
Tabelle 5 - Rangfolge von Operatoren
5.2 Relative und absolute Zellbezüge
Standardmäßig verwendet Excel Zellbezüge in Formeln als relative Bezüge Bsp: =B3
Zellbezüge, die sich nicht anpassen, werden als absolute Bezüge bezeichnet. Sie bleiben beim
Kopieren unverändert. Um einen Zellbezug als absoluten Bezug zu kennzeichnen, wird vor der
Zeilen- und Spaltenbezeichnung ein Dollarzeichen ($) eingegeben Bsp: =$B$3. Dies kann beim
Eingeben des Zellbezuges oder später über die Bearbeitungsleiste erfolgen. Außerdem kann
man den Zellbezug in der Formel nachträglich markieren und durch Betätigen der F4-Taste in
einen absoluten Bezug umwandeln.
15
EDV I
Zellbezüge können auch als gemischte Bezüge eingegeben werden. Dies kann dann nützlich
sein, wenn beim Kopieren nur der Spaltenbuchstabe oder die Zeilennummer angepasst werden
soll.
5.3 Namen
Um eine bessere Lesbarkeit von Excel Formeln herzustellen, können Namen in den Formeln
verwendet werden z.B.:
= Kapital * Zinssatz * Tage / 100 /360
Diese Namen werden für bestimmte Zellen oder Zellbereiche vergeben und sind absolut.
5.4 Operatoren
Arithmetische Operatoren: +, -, *, /,%,^.
Logische Operatoren: innerhalb von Excel sind: =, <, >, >=, <=, <>, und, oder, nicht.
Um Texte innerhalb von Excel zu verketten gibt es den & Operator.
Bsp: =(“Hallo“&“Hallo“)
5.5 Funktionen
Die Möglichkeit, Berechnungen über Excel-Funktionen durchführen zu lassen, ist ein sehr
wichtiger Bestandteil in Excel. Eine Funktion ist eine vorgefertigte Berechnungsformel.
Beispiel: Für die Ermittlung des Mittelwertes aus 4 Zahlen
Mit einer Excel Funktion ist das Ergebnis zwar dasselbe, der Weg dahin ist jedoch eine andere.
Man wählt über das Funktionsfenster einfach die Funktion Mittelwert aus. Man setzt wie
gewohnt die Zahlen ein. Man braucht jedoch nicht mehr alle Zahlen zusammenzuzählen und /4
zu teilen. Denn, Excel weiß bereits, dass er die Zahlen addieren soll und danach durch die
Anzahl der Zellen teilen soll. Dafür hat er den Funktionsnamen Mittelwert.
Formel =(Zahl1+Zahl2+Zahl3+Zahl4)/4.
Funktion =Mittelwert(Zahl1;Zahl2;Zahl3;Zahl4)
16
EDV I
5.6 Funktionsassistent
In Excel lassen sich mehr als 400 Funktionen nutzen, diese sind in der Funktionsbibliothek nach
Kategorien zusammen gefasst. Zum Funktionsassistenten gelangen sie durch einen Klick auf
Funktion einfügen im Register Formeln.
Abbildung 12 - Funktionsassistent
5.7 Externe Bezüge
In Formeln können Sie auch auf Zellen bzw. Zellbereiche Bezug nehmen, die auf anderen
Blättern innerhalb der gleichen oder auch in einer anderen Arbeitsmappe enthalten sind.
Die Syntax ist folgende
[𝐷𝑎𝑡𝑒𝑖𝑛𝑎𝑚𝑒]𝑇𝑎𝑏𝑒𝑙𝑙𝑒𝑛𝑏𝑙𝑎𝑡𝑡! 𝑍𝑒𝑙𝑙𝑏𝑒𝑧𝑢𝑔
Für den Fall das der Zielzellbereich innerhalb der Datei liegt, wird die Angabe des
Dateinamens nicht benötigt.
17
EDV I
5.8 Spezielle Matrixfunktionen
INDEX
SVERWEIS
WVERWEIS
VERWEIS
Ermittelt mit der Angabe der Spalten- und Zeilennummer den Inhalt
einer Zelle
Durchsucht die erste Spalte einer Matrix nach einem bestimmten Wert
Durchsucht die erste Zeile einer Matrix nach einem bestimmten Wert
Durchsucht eine Matrix nach einem bestimmten Wert; der Aufbau der
Matrix bestimmt, welche Zeile bzw. Spalte durchsucht wird.
Tabelle 6 - Matrixfunktionen
5.9 Zielwertsuche
Stark vereinfacht gesagt kann man das Zielwertsuchen Tool benutzen um Dreisatzaufgaben zu
lösen. Der Vorteil gegenüber einer normalen Berechnung einer Dreisatzaufgabe ist, dass die
Rechenkette die zum gewünschten Ergebnis führt fast endlos lang sein kann. Deshalb eignet
sich die Zielwertsuche auch sehr gut um gewünschte Resultate in komplexen Modellen zu
berechnen so lange sich nur eine Variabel ändert.
Abbildung 13 - Zielwertsuche
Es
soll
die
benötigte
monatliche
Wachstumsrate ermittelt werden, sodass
im Dezember 5000 Besuche verzeichnet
werden können.
Im Beispiel hier haben die Zellen B3:B18 alle eine Formel die
wie folgt aussieht (hier aus Zelle B3): =B2*(1+$C$2)
Abbildung 14 - Wachstumsrate Zielwertsuche
18
EDV I
5.10 Solver
Beim Solver handelt es sich um ein Add-In, welches prinzipiell wie die Zielwertsuche
funktioniert, bei dem aber noch zusätzliche Bedingungen angegeben werden müssen, um eine
Lösung für das Optimierungsproblem zu finden.
Um den Solver nutzen zu können muss dieser zuerst aktiviert werden. Hierzu klicken Sie
zunächst auf Datei – Optionen – Add-Ins -
Abbildung 15 - Add-Ins Auswahlfenster
Das Add-In Solver ist nun aktiviert und befindet sich im Register Daten in der Gruppe Analyse.
Elemente des Solvers sind Zielzelle, Veränderbare Zellen und die Nebenbedingungen.
In der Zielzelle muss eine Formel stehen, die Bezüge auf die Veränderbaren Zellen enthält. In
den Veränderbaren Zellen dürfen nur Werte enthalten. Diese Werte werden so angepasst, dass
das optimale Ergebnis gefunden wird. Sowohl für die Zielzelle als auch für die veränderbaren
Zellen können Bedingungen gesetzt werden, die die Werte genauer eingrenzen. Diese
Bedingungen nennt man Nebenbedingungen.
19
EDV I
5.11 Übungen zu Formeln und Funktionen
5.11.1 Externe Verweise
Dateivorlage: Übung_ExterneVerweise.xlsx
a. Erstellen Sie einen Externen Verweis zur [Übung_Diagramme.xls]Prozente um
die Tabelle Externe Bezüge zu füllen.
5.11.2 Zielwertsuche
Dateivorlage: Übung_Zielwertsuche.xlsx
b. Berechnen Sie Höhe und Radius einer Getränkedose mit 1000ml so, dass der
Materialverbrauch minimiert wird.
c. Bestimmen Sie bei einer Höhe von 10 cm den Radius der Dose so, dass das
Volumen 1000ml beträgt
5.11.3 Solver
Dateivorlage: Übung_Solver.xlsx
a. Lösen Sie das Gleichungssystem in der Datei auf dem Arbeitsblatt Gauss.
20
EDV I
Beispiel:
Abbildung 16 - LGS - Lösung mit Solver
Abbildung 17 - Solver-Parameter
Beim Hinzufügen von Nebenbedingungen gibt es mehrere Operatoren die zur Auswahl stehen.
In unserem Beispiel haben wir das =. Die Operatoren <= und >= sind gleich wie in der
Mathematik zu deuten. Der Operator INT bedeutet, dass die Zahl ganzzahlig und der Operator
BIN, dass die Zahl Binär(1 oder 0) sein muss. DIF bedeutet, dass der Wert ganzzahlig und
unterschiedlich sein muss. Dies bedeutet, dass ein Wert nicht mehrfach vorkommen darf.
Neben einem Bestimmten Wert können auch der Maximale und der Minimale Zielwert
bestimmt werden. Dazu einfach die Auswahl auf Max. oder Min. stellen.
21
EDV I
6 Diagramme
6.1 Erstellen von Diagrammen
Um Diagramme zu erstellen benötigen sie als erstes die Werte für das Diagramm. Markieren
Sie die Werte der Tabelle und wechseln Sie zum Register Einfügen und suchen Sie die Gruppe
Diagramme. (ALT+F1 erstellt mit den Daten im aktuellen Bereich ein eingebettetes Diagramm.
Alternativ Taste F11 für ein Diagramm auf einem neuen Arbeitsblatt) Hier finden Sie eine
Vielzahl von Diagrammen wie beispielsweise Säulen-, Torten-, Blasen-, Ring-, oder
Balkendiagramme. Ebenso finden Sie die Schaltfläche Empfohlene Diagramme diese analysiert
die eingegebenen Daten und unterbreitet Vorschläge. Um ein Diagramm zu erstellen müssen
Sie jetzt noch den Typ auswählen. Die markierten Werte werden nun automatisch als Diagramm
dargestellt. Die Grafik ist nun fertig, kann nun jederzeit bearbeitet und ergänzt werden.
Um dem Diagramm weitere Daten hinzuzufügen, müssen Sie auf das Diagramm klicken und
die Rechtecke um den Daten entsprechend vergrößern. Somit können Sie Ihre Tabelle um
einzelne Daten erweitern. Das ändern von Daten ist ebenso über das Kontextmenü möglich
hierzu Daten auswählen… auswählen.
Um den Diagrammtyp zu ändern öffnen Sie das Kontextmenü auf der Diagrammfreifläche und
wählen Diagrammtyp ändern….
Abbildung 18 - Diagramm formatieren
Mit einem Klick auf das Diagramm können Sie das Design ändern. Wählen Sie über das
Kontextmenü Diagrammbereich Formatieren aus. Über das Plus-Symbol können Sie unter
anderem weitere Elemente wie Beschriftungen, Linien und eine Legende hinzufügen oder
ausblenden.
22
EDV I
6.2 Übungen zu Diagramme
Dateivorlage: Übung_Diagramme.xlsx
a. Erstellen Sie ein Diagramm nach dem Muster „Umsätze“ in der Arbeitsmappe
Diagramme.
b. Zeichnen Sie ein Makro auf, das die Erstellung dieses Diagrammtyps
automatisiert.
c. Erstellen Sie ein Diagramm für die Tabelle Alter, 3DSäulen, Prozente und
Städte.
7 Formularsteuerelemente
7.1 Anwendung von Formularsteuerelementen
Um Steuerelemente in Excel benutzen zu können, müssen Sie die Entwicklertools aktivieren.
Hierzu klicken sie auf das Register Datei – Optionen – Menüband anpassen.
Abbildung 19 - Menüband anpassen - Entwicklertools
Das Kontrollkästchen bei Entwicklertools aktivieren.
Nun befindet sich ein weiteres Register namens Entwicklertools im Ribbon.
23
EDV I
Abbildung 20 - Gruppe Steuerelemente – Formularsteuerelemente
7.2 Übersicht der Steuerelemente
Abbildung 21 - Formularsteuerelemente
Name
Beispiel
Beschreibung
Bezeichnung
Gibt den Zweck einer Zelle
bzw. eines Textfelds an
Gruppenfeld
Gruppiert verwandte
Steuerelemente in einem
Rechteck
Schaltfläche
Führt ein Makro aus wenn
ein Benutzer darauf klickt.
Kontrollkästchen
Aktiviert oder deaktiviert
einen Wert, der eine
gegenteilige und
unzweideutige Auswahl
kennzeichnet.
24
EDV I
Ermöglicht eine einzelne
Auswahl unter einer
begrenzten Gruppe von sich
gegenseitig ausschließenden
Optionen.
Optionsfeld
Zeigt eine Liste mit
mindestens einem
Textelement an, in der der
Benutzer eine Auswahl
treffen kann.
Listenfeld
Ein Kombinationsfeld ist
eine Kombination aus
Textfeld und Listenfeld.
Kombinationsfelder werden
zum Erstellen von
Dropdown-Listenfeldern
verwendet.
Kombinationsfeld
Führt einen Bildlauf durch
einen Bereich von Werten
durch, wenn Sie auf die
Bildlaufpfeile klicken oder
das Bildlauffeld ziehen.
Bildlaufleiste
Erhöht oder verringert einen
Wert, z. B.
aufeinanderfolgende Zahlen,
Zeit- oder Datumsangaben.
Drehfeld
Tabelle 7 - Werkzeugsammlung
7.3 Übungen zu Formularsteuerelemente
Dateivorlage: Übung_Schraube.xlsx
a. Überprüfen Sie die benannten Bereiche und legen Sie Namen fest, wo sie
bisher fehlen. Hinweis: Der Fehler #Name weist darauf hin, dass in einer
Formel ein Name verwendet wird, der in der Arbeitsmappe nicht vergeben
wurde.
b. Wählen und programmieren Sie die Steuerelemente für die Auswahl der
Schraubenart und der Festigkeitsklasse.
c. Reduktionsfaktor und Streckgrenze fehlen!
d. Die Datei enthält einen Fehler, der zur Unterdimensionierung der
Schraubverbindung führt. Berichtigen Sie diesen.
25
EDV I
8 Makros
8.1 Der Makro Rekorder
Der Makrorecorder bietet die Möglichkeit, Programmcode automatisch aufzeichnen zu lassen.
Bei der Erstellung von Makros werden die einzelnen Arbeitsschritte, die Sie ausführen,
nacheinander
aufgezeichnet.
Das
Aufgezeichnete
Makro
kann
in
der
VBA
Entwicklungsumgebung eingesehen werden. Auch für Programmiererfahrene Benutzer ist dies
Hilfreich, Beispielsweise um sich vergessene Befehle anzeigen zu lassen ohne die Onlinehilfe
hinzuziehen zu müssen.
8.2 Grenzen der Makro Programmierung
Es lassen sich sehr viele Funktionen als Makro mitschreiben, allerdings sind dem auch einige
Grenzen gesetzt. Es ist für Dinge wie
-
Schleifen
-
Bedingungen und
-
Benutzerdefinierte Dialogfelder
Unerlässlich einen eigenen VBA-Code zu schreiben.
8.3 Beispielanwendung
8.3.1 Makroaufzeichnung starten
Hierzu klicken Sie im Register Ansicht in der Gruppe Makros
auf den Pfeil und wählen sie Makro aufzeichnen aus.
Abbildung 23 - Makros Schaltfläche
Abbildung 22 - Makroeigenschaften definieren
26
EDV I
Hier können Sie Ihrem Makro einen Namen Geben z.B. Neues Blatt
Möchten Sie das Makro später über eine Tastenkombination ausführen, geben Sie
einen Buchstaben (oder Buchstaben und Shift) in dieses Feld ein.
Die für ein Makro festgelegte Tastenkombination überschreibt eine
möglicherweise bereits existierende Tastenkombination, solange die Arbeitsmappe
geöffnet ist, die das Makro enthält.
Wählen Sie eine Tastenkombination, die in Excel noch nicht verwendet wird. Im
Anhang finden Sie die bereits in Excel belegten Tastenkombinationen
Belassen Sie den Eintrag Diese Arbeitsmappe, wenn Sie das Makro in der
momentan geöffneten Arbeitsmappe anlegen möchten. Wenn Sie hingegen ein
Makro aufzeichnen, welches immer für jede Arbeitsmappe verfügbar sein soll,
wählen Sie aus dem Dropdown den Eintrag Persönliche Makroarbeitsmappe aus.
(Achtung: Makros die in der Hochschule in der Persönlichen Makroarbeitsmappe
gespeichert werden sind nach dem Abmelden gelöscht.).Falls Sie ein Makro in
einer anderen Arbeitsmappe anlegen möchten, so wählen Sie den Eintrag Neue
Arbeitsmappe.
Geben Sie in dieses Feld eine Beschreibung ein, die beispielsweise den
Ablauf und/oder Verwendungszweck des Makros genauer definiert.
Tabelle 8 - Makroeigenschaften
Nach dem auswählen der Schaltfläche OK befindet sich in der Statusleiste ein Quadrat. Dies
dient dazu die Makroaufzeichnung zu Stoppen. Alle Schritte die Sie jetzt ausführen werden in
das Makro geschrieben.
8.3.2 Relative/Absolute Makroaufzeichnung
Absolute Aufzeichnung
•
Standardmäßig zeichnet Excel genau die
Klicken Sie vor Aufzeichnung des Adressen der Zellen auf, die während der
Makros im Register ANSICHT in der Makroaufzeichnung bearbeitet werden.
Gruppe MAKROS auf den Pfeil des
Symbols MAKROS
•
Deaktivieren
Sie
Eintrag
RELATIVE AUFZEICHUNG.
Relative Aufzeichnung
•
Wenn
ein
Makro
auf
unterschiedliche
Markieren Sie die Zelle, von der die Zellbereiche angewandt werden soll oder
relativen Befehle des Makros ausgehen Zellen zu bearbeiten sind, die relativ zur
sollen.
•
markierten Zelle positioniert sind, kann ein
Aktivieren
Sie
den
Eintrag Makro auch unabhängig von der tatsächlichen
RELATIVE AUFZEICHNNG.
Zellposition aufgezeichnet werden.
Tabelle 9 - Relative/Absolute Makroaufzeichnung
27
EDV I
8.3.3 Beispiel
Ein Neues Arbeitsblatt wird eingefügt und der Name des Blattes geändert.
8.3.4 Makro einsehen
Ein Makro kann auf verschiedene Arten aufgerufen werden.
1. Im Register Ansicht in der
Gruppe
Makros
anzeigen
-
auswählen
Makros
und
anschließend auf Bearbeiten.
2. Die Tastenkombination Alt+F8
und
anschließend
auf
Bearbeiten.
3. Die
Tastenkombination
Alt+F11 um direkt in die VBA
Entwicklungsumgebung
zu
gelangen
Abbildung 24 - Aufgezeichnetes Makro auswählen
Sub NeuesBlatt()
'
' NeuesBlatt Makro
' Fügt ein Neues Blatt ein
'
' Tastenkombination: Strg+Umschalt+N
'
Sheets.Add After:=ActiveSheet
Sheets("Tabelle2").Select
Sheets("Tabelle2").Name = "Blatt Nummer 2"
End Sub
Zeilen im Code die mit einem Hochkomma beginnen sind Kommentarzeilen. Der
Makrorecorder liefert wertvolle Hinweise über die Syntax der aufgezeichneten Befehle,
leider zeichnet er auch Befehle auf die nicht benötigt werden. Im Code 1 z.B. ist der Schritt
Sheets(“Tabelle2“).Select redundant Aus diesem Grund stellt ein Mitschreibmakro oft
nur den ersten Schritt dar, um ein Makro zu erstellen.
28
EDV I
8.4 Starten eines Aufgezeichneten Makros
Ein Makro kann auf verschiedene Arten gestartet werden.
Über die Schaltfläche Ausführen in Abbildung 23
Durch öffnen der VBA Entwicklungsumgebung (Alt+F11) – Makro auswählen und Play
Schaltfläche betätigen (Alternativ F5)
Durch eine eigens dafür vorgesehene Schaltfläche.(7.2 Übersicht der Steuerelemente)
8.5 Übungen zu Makros
Dateivorlage: Übung_Schraube.xlsx
a. Zeichnen Sie ein Makro auf, das nach der Vorlage Stufe1 weitere Blätter
kopiert. Ergänzen Sie dieses Makro um die Möglichkeit, einen Namen für das
neue Blatt zu wählen.
9 VBA-Umgebung
9.1 Der Projektexplorer
Um den Projektexplorer zu sehen müssen Sie wieder in die Entwicklungsumgebung
Alt+F11. Alternativ dazu können Sie mittels Menüpunkt Entwicklertools und der
Schaltfläche Visual Basic die Entwicklungsumgebung aufrufen.
Abbildung 25 VB Schaltfläche
In der linken oberen Ecke sehen Sie den Projekt-Explorer, der
alle geöffneten Arbeitsmappen sowie die darin befindlichen
Tabellenblätter
anzeigt.
Standardmäßig
besteht
ein
VBAProject aus einem Ordner (Microsoft Excel Objekte), das
2 Elemente umfasst:
•
ein Symbol für die Arbeitsmappe selbst und
•
ein weiteres für das Tabellenblatt der Arbeitsmappe
Abbildung 26- Projektexplorer
Möchten Sie innerhalb der Arbeitsmappe oder der Tabelle ein
Makro oder eine Prozedur erstellen. Doppelklicken Sie auf das jeweilige Element. Damit öffnen
Sie das zugehörige Codefenster.
29
EDV I
Sowohl das Projekt selbst als auch seine Elemente können Sie nach Belieben umbenennen.
Dazu benötigen Sie das Eigenschaftsfenster.
9.2 Eigenschaftsfenster
Das Eigenschaftenfenster wird in dem Register Ansicht - Eigenschaftsfenster oder durch die
Funktionstaste F4 geöffnet, falls es noch nicht aktiviert sein sollte. Das Eigenschaftsfenster
zeigt verschiedene Merkmale zum Momentan ausgewählten Element (Abbildung 26).
Abbildung 27 - Eigenschaftsfenster
Der Name der Tabelle1 ist bisher Tabelle1 Sie können diesen nach Belieben ändern.
Nach dem Erstellen eines Makros, erscheint neben dem Ordner Microsoft Excel Objekte ein
weiterer, nämlich Module. Zudem lassen sich u.a. auch eigene Dialogfelder (Formulare) im
Projekt-Explorer erstellen.
Ist der Ordner Module bereits angelegt, erhält er mindestens 1 Element, das standardmäßig als
Modul1 bezeichnet wird. Um ein neues Modul zu erzeugen
Im Register Einfügen – Modul
Im gewünschten Projekt im Kontextmenü Einfügen – Modul
Um ein erstelltes Modul zu entfernen rufen Sie das Kontextmenü des zu entfernenden Moduls
auf – Entfernen von Modul… Nun werden Sie gefragt ob Sie das Modul vor dem Löschen noch
exportieren wollen. Dies bietet sich an, wenn Sie das Modul noch benötigen aber nicht in
diesem Projekt. Ansonsten bestätigen Sie das Entfernen mit Nein.
30
EDV I
9.3 Objektkatalog
Im Objektkatalog finden Sie alle verfügbaren Objekte mit ihren zugeordneten Eigenschaften
und Methoden. Innerhalb von Excel sind alle Komponenten als Objekte angesehen. Somit sind
Arbeitsmappen und Tabellenblätter ebenso Objekte wie eine Schaltfläche oder ein Diagramm.
Sie öffnen den Objektkatalog im Register Ansicht – Objektkatalog oder über F2 öffnen.
9.4 Direktfenster
Das Direktfenster kann im Register Ansicht – Direktfenster oder durch Strg+G geladen werden.
Dort lässt sich der Inhalt von Variablen während der Programmausführung abfragen
Einzelne Programmzeilen können dort eingegeben und getestet werden.
Mittels Debug.Print können dort Programmausgaben aufgelistet werden.
Abbildung 28 - Direktfenster
9.5 Lokalfenster
Mit Hilfe des Lokalfensters können Sie Variablen überwachen, die in dem gerade genutzten
Modul sowie in der aufgerufenen Sub deklariert sind.
Abbildung 29 - Lokalfenster
31
EDV I
9.6 Überwachungsfenster
Das
Überwachungsfenster
ist
ähnlich
wie
das
Lokalfenster,
wobei
bei
dem
Überwachungsfenster vorher festgelegt werden muss, was überwacht wird. Es können alle
Objekte überwacht werden.
Abbildung 30 - Überwachungsfenster
Abbildung 31 - Überwachung Hinzufügen
32
EDV I
10 VBA-Programmierung
10.1 Programm erstellen
In VBA wird als Prozedur bezeichnet, was bislang Makro genannt wurde. Prozeduren werden
durch folgende Schlüsselwörter eingerahmt:
Sub Name
Anweisungsblock innerhalb der Prozedur
End Sub
Um eine Prozedur zu erstellen, öffnen sie Entweder ein Tabellenmodul (Doppelklick auf
Tabelle im Projektexplorer) oder Fügen ein neues Modul ein und öffnen dieses durch einen
Doppelklick. Nach dem Erscheinen des VBA-Editor geben Sie folgenden Code ein:
Sub HelloWorld()
MsgBox “Hello World!“
End Sub
MsgBox ist ein Schlüsselwort. Schlüsselwörter bezeichnen von Excel
reservierte Wörter. Wenn
Sie hinter MsgBox ein Leerzeichen eingeben, erscheint die QuickInfo für diesen Befehl. Darin
wird der jeweilige Aufbau eines Befehls angezeigt.
Zum Start von HelloWorld betätigen Sie einfach die Funktionstaste F5 oder klicken Sie auf die
Schaltfläche Makro Ausführen in dem Register Ausführen.
Abbildung 32 - Makro starten
Dann erscheint auf dem Bildschirm folgende Meldung welche durch klicken von OK
verschwindet.
33
EDV I
Abbildung 33 - Hello World! Msgbox
Die Prozedur HelloWorld soll jetzt noch durch das Einfügen einer InputBox etwas flexibler
gestaltet werden. Eine InputBox erwartet eine Eingabe des Benutzers, die einer Variablen vom
Typ Variant übergeben wird. Für die unten gezeigte InputBox wurde die Titelzeile, der
Beschreibungstext und der Standardtext im Eingabefeld in der Prozedur definiert. Den
Standardtext kann der Benutzer beliebig überschreiben.
Abbildung 34 - Hello World! Inputbox
Sub HelloWorld()
' Variable vom Typ Variant deklarieren
Dim varMsg As Variant
' Meldung erfassen
varMsg = InputBox("Wie lautet die Meldung?", _
"Meldung", "Hello World!")
' Meldung ausgeben
MsgBox varMsg
End Sub
Die Syntax von Anweisungen beruht auf folgenden Regeln :
Eine einfache Anweisung wird durch das Ende einer Codezeile abgeschlossen.
Ein Anweisungsblock besteht aus mehreren einfachen Anweisungen.
Anweisungen, die sich innerhalb spezieller Schlüsselwörter befinden, werden als
Anweisungsblock bezeichnet. Beispiel: Sub … End Sub
34
EDV I
Anweisungsblöcke können geschachtet werden.
Eine Anweisung kann in der nächsten Zeile fortgesetzt werden: Dies erfolgt durch ein
Leerzeichen mit anschließendem Unterstrich ( _ ) am Ende der Zeile. Um die Lesbarkeit zu
erhalten ist es üblich die fortgesetzten Zeilen einzurücken.
10.2 Reservierte Wörter
Alle Wörter die fester Bestandteil von VBA sind, werden als reservierte Wörter bezeichnet.
Dazu zählen u. a. alle
Schlüsselwörter wie As, New, True, False
Wörter die Anweisungen einleiten wie Dim, Public, If, For, Next
Datentypen (siehe dazu Tabelle 9)
10.3 Variablen deklarieren
10.3.1 Bezeichner
Bezeichner sind alle vom Anwender frei wählbaren Namen, mit denen im VBA-Code
Prozeduren, Funktionen, Variablen und Konstanten bezeichnet werden müssen. Der Name der
Prozedur HelloWorld ist beispielsweise ein frei gewählter Bezeichner.
10.3.2 Variablen
Die Deklaration der Variablen sollte bei der VBA-Programmierung immer erfolgen. In der
Entwicklungsumgebung im Menü Extras - Optionen, Register Editor, Rahmen CodeEinstellungen wird deshalb das Kontrollkästchen Variablendeklaration erforderlich aktiviert.
Dadurch wird automatisch im Deklarationsbereich eines jeden Moduls der Befehl Option
Explicit eingefügt. VBA zwingt einem damit, alle Variablen mit der Dim Anweisung zu
deklarieren, bevor sie benutzt werden. Die explizite Variablendeklaration hilft ganz enorm
dabei, Programmierfehler zu vermeiden.
35
EDV I
10.3.3 Datentypen
Gebräuchliche Standarddatentypen sind:
Datentyp
Boolean
Byte
Currency
Präfix für
Variable
bln oder
bol
byt
cur
Date
Double
dat oder
dtm
dbl
Integer
Long
Object
Single
int
lng
obj
sng
String
Variant
str
var
Art
Wahrheitswert
Ganze Zahl
Festkommazahl
(für Währung)
Datums-/Zeitwert
Dezimalzahl
(Fließkommazahl)
Ganze Zahl
Ganze Zahl
Objektvariable
Dezimalzahl
(Fließkommazahl)
Text
Alle Typen
Kurzbezeichnung
keines
keines
@
keines
#
%
&
keines
!
$
keines
Wertebereich
TRUEoder FALSE
0 bis 255
15 Vor- und 4 Nachkommastellen
1.1.100 bis 31.12.9999
Zahlen mit insgesamt (Vor- und
Nachkommastellen) 16 Stellen
-32.768 bis 32.767
-2.147.483.648 bis 2.147.483.647
Verweist auf ein Objekt
Zahlen mit insgesamt (Vor- und
Nachkommastellen) 8 Stellen
Zeichenketten
zunächst unbestimmt
Tabelle 10 - Datentypen
10.3.4 Variablenamen
Der Gebrauch von Variablen in VBA-Anweisungen ist vergleichbar mit dem Einsatz von
Variablen in mathematischen Formeln. Für die Bildung von Variablennamen gelten bestimmte
Regeln:
Der Name des entsprechenden Bezeichners muss mit einem Buchstaben beginnen.
Danach kann jede mögliche Kombination von Zahlen und Buchstaben sowie das
Unterstreichungszeichen ( _ ) folgen.
Bezeichner dürfen keine Leerstellen, Punkte ( . ) oder andere Zeichen enthalten, die bei VBA
für mathematische Berechnungen oder Vergleichsoperationen (=, +, - usw.) eingesetzt
werden.
Sonderzeichen wie #, %, &, ! oder ? sind nicht erlaubt.
Variablennamen und andere Bezeichner dürfen maximal 255 Zeichen enthalten.
Variablennamen dürfen nicht mit bestimmten VBA-Schlüsselwörtern identisch sein. Wird
ein Variablenname gewählt, der mit einem reservierten Wort identisch ist, meldet VBA einen
Syntaxfehler.
36
EDV I
Bezeichner müssen in ihrem Gültigkeitsbereich eindeutig sein. Das heißt, dass der Name
innerhalb der Prozedur oder des Moduls, in dem die Variable eingesetzt wird, nur einmal
deklariert sein darf. Wenn versehentlich zwei Variablen in einer Prozedur den gleichen
Namen haben oder der Name der Variablen mit dem Namen einer Prozedur in dem gleichen
Modul identisch ist, meldet VBA bei der Ausführung der Prozedur mit einem Laufzeitfehler.
Der Name einer Variablen sollte ihre Aufgabe so gut wie möglich verdeutlichen.
Variablen sollten nicht größer als erforderlich dimensioniert werden.
Zwischen Groß- und Kleinschreibung von Bezeichnern unterscheidet VBA nicht.
10.3.5 Präfix
Sinnvollerweise wird ein Präfix vor den eigentlichen Variablennamen gesetzt, aus dem der
Datentyp hervorgeht. Das erleichtert die Lesbarkeit des Codes:
Datentyp
Variableninhalt
Beispiel für Variablennamen
String
Text (Zeichenkette)
Integer
Ganze Zahlen von -32.768 bis 32.767
Date
Datum oder Zeit
strVorname, strNachname, strMsg
intZaehler, intPersonen,
intSpalte
dtmStart, dtmEnde, dtmHeute
Tabelle 11 - Präfix-Variablen
Wenn Sie Ihre Variablennamen übersichtlich und aussagekräftig definieren möchten, empfiehlt
sich folgende Schreibweise mit Präfix und Festlegung des Datentyps:
Dim dtmStart As Date
Zur expliziten Deklaration einer Variablen mit Festlegung des Datentyps wird die DimAnweisung benutzt. Sie bewirkt, dass VBA eine Variable vom Typ Datum erzeugt. Variablen
eines Typs sollten möglichst in einer Codezeile deklarieren werden.
10.3.6 Deklarationsregeln
Eine Variable, die innerhalb einer Prozedur deklariert wird, ist auch nur innerhalb dieser
Prozedur gültig.
Innerhalb des Gültigkeitsbereichs einer Variablen muss diese eindeutig benannt sein.
In VBA ist es möglich, Variablen zu deklarieren, die in mehreren Prozeduren gültig sind.
Wenn Variablen mit gleichem Namen sich in unterschiedlichen Gültigkeitsbereichen
befinden, benutzt VBA die Variable mit der lokalsten Gültigkeit.
37
EDV I
Die Gültigkeitsbereiche und –Dauer von Variablen sind folgender Tabelle zusammengefasst:
Variablenty Gültigkeitsbereich
Sie müssen nur einmal
Globale
p
deklariert werden und
Variablen
gelten dann in allen
Prozeduren eines
Projekts.
Gültigkeitsdauer
Während der
gesamten Laufzeit
der Anwendung.
Deklaration
Werden durch das
Schlüsselwort Public im
allgemeinen Deklarationsteil
des Moduls deklariert.
Gelten nur in der
jeweiligen Prozedur, in
der sie deklariert
werden.
Bestehen nur
solange, wie die
jeweilige Prozedur
abläuft.
Wenn mit dem Schlüsselwort
Dim in der jeweiligen
Prozedur deklariert, in der sie
benutzt werden.
Sind lokale Variablen,
die nur in der jeweiligen
Prozedur gültig sind.
Während der
gesamten Laufzeit
der Anwendung.
Stehen allen Prozeduren
des Moduls zur
Verfügung, in dem sie
deklariert werden.
Während der
gesamten Laufzeit
der Anwendung.
Werden mit dem
Schüsselwort Static in der
jeweiligen Prozedur
deklariert, in der sie benutzt
werden.
Werden mit dem
Schlüsselwort Private im
allgemeinen Deklarationsteil
des Moduls deklariert.
Lokale
Variablen
Statische
Variablen
Variablen
auf
Modulebene
Tabelle 12 - Gültigkeitsbereiche und -dauer von Variablen
10.3.7 Übungen zu Variablen
Dateivorlage: Übung_Variablen.xlsm
a. Schreiben Sie eine Prozedur, die die Summe der Kehrwerte der natürlichen
Zahlen bis zu einer bestimmten Zahl (Benutzereingabe) bildet.
b. Schreiben Sie eine Prozedur, die einen Tippfehler in einem Variablennamen
enthält und einen entsprechenden Fehler produziert. Schalten sie dabei die
Option Explicit zunächst aus.
c. Schalten Sie im Modul 1 Option Explicit ein und deklarieren Sie die
Variablen nach den Erfordernissen (Double, String).
10.4 Konstante
10.4.1 Benutzerdefinierte Konstante
Verwenden Sie Werte in einem VBA-Programm, die sich nicht ändern, können diese als
Konstanten mit dem Schlüsselwort const deklariert werden. Gemäß empfohlener
Namenskonvention werden Konstanten mit dem Präfix con gekennzeichnet. Dazu ein Beispiel:
[Public|Private ] Const conAnzahl As Integer = 20
38
EDV I
10.4.2 Integrierte Konstante
VBA stellt eine große Zahl von integrierten Konstanten zur Verfügung. Am Präfix des Namens
dieser Konstanten lässt sich erkennen, zu welche Anwendung oder zu welchem Bereich sie
gehören:
vb
VBA-Konstante, z. B. vbYesNo
xl
Excel-Konstante, z. B. xlColumn
fm
MSForms für Formulare, z. B. fmActionPaste
Tabelle 13 - Integrierte Konstanten
10.5 Literale
Feste Werte (z. B. Zahlen oder Texte), die im Programmcode eingegeben werden, bezeichnet
man als Literale. Ihre Schreibweise ist abhängig vom jeweiligen Datentyp
Numerische Datentypen
Gültig sind die Ziffern 0 bis 9 und die Vorzeichen + und –
Fließkommazahlen
erhalten
einen
Punkt
( .)
als
Dezimaltrennzeichen.
Zeichen und Zeichenfolgen
Diese werden in Anführungszeichen ( " ) eingeschlossen
Boolescher Datentyp
Zulässig sind: True oder False
Datum und Zeit
Diese
werden
in
Raute-Zeichen
(
#
) eingebettet
#12/31/2011#
Zulässig ist auch die übliche deutsche Schreibweise:
"31.12.2011"
10.6 Datenfelder
Zu einem Datenfeld können Werte gleicher Art in einer Liste zusammengefasst werden. Sie
werden alle mit demselben Namen anhand ihres Indexes angesprochen. Oft erhalten Felder
noch einen Präfix. Gemäß gängiger Namenskonventionen beginnen sie entweder mit „arr“
oder nur mit „a“, womit gekennzeichnet wird, dass es sich im ein Feld (engl. array) handelt.
Dazu zwei Beispiele:
Dim arrWochenNummer(1 To 52) As Integer
Dim asngMwstSatz(3) As Single
39
EDV I
Um beispielsweise 7 Wochentage unter dem Namen varWochenTag anzusprechen, wird
folgendes Datenfeld benötigt:
varWochenTag
Index:
Mo
0
Di
1
Mi
2
Do
3
Fr
4
Sa
5
Sa
6
Tabelle 14 - Beispiel für ein Datenfeld mit sieben Elementen
Auf den ersten Wert (Mo) des Datenfeldes muss mit dem Index 0 zugegriffen werden, denn
Datenfelder basieren standardmäßig auf null. Soll der Index stattdessen mit 1 beginnen, muss
vorher im allgemeinen Deklarationsbereich der Befehl Option Base 1 eingefügt werden.
Die folgende Prozedur DatenfeldAuslesen nutzt das in Tabelle 13 veranschaulichte Datenfeld
varWochenTag.
Sie verwendet die in VBA eingebauten Datumsfunktionen WeekDay und Date,
um den Wochentag des aktuellen Tagesdatums als ganze Zahl zurückzugeben. Dann wird mit
Hilfe der Fallstruktur Select Case...End Select das Kürzel des jeweiligen Wochentags ermittelt
und anschließend mit Debug.Print im Direktfenster ausgegeben.
Sub DatenfeldAuslesen()
Dim varWochenTag As Variant
Dim strTag As String
varWochenTag = Array("Mo", "Di", "Mi", "Do", "Fr", "Sa", "So") Select Case
Weekday(Date, vbMonday) ' gibt Wochentag zurück
Case 1
strTag = varWochenTag(0)
Case 2
strTag = varWochenTag(1)
Case 3
strTag = varWochenTag(2)
Case 4
strTag = varWochenTag(3)
Case 5
strTag = varWochenTag(4)
Case 6
strTag = varWochenTag(5)
Case 7
strTag = varWochenTag(6) End Select
Debug.Print "Heute ist " & strTag End Sub
10.7 Kontrollstrukturen
In VBA werden zwei wichtige Gruppen von Kontrollstrukturen unterschieden:
•
Verzweigungen und
•
Schleifen
40
EDV I
Abbildung 35 - Kontrollstrukturen in VBA
10.7.1 Einseitige Auswahl
Eine einseitige Auswahl ist wie folgt aufgebaut:
Sie beginnt mit dem Schlüsselwort If.
Dahinter wird eine Bedingung (engl. condition) geschrieben.
Nach der Bedingung folgt das Schüsselwort Then.
Anschließend folgen eine oder mehrere Anweisungen, die ausgeführt werden, wenn die
Auswertung der Bedingung den Wert True ergibt. Liefert die Auswertung der Bedingung
dagegen den Wert False, erfolgt keine Aktion.
Eine einseitige Anwahl wird mit End If abgeschlossen. Das ist allerdings nur dann
notwendig, wenn mehr als eine einzelne Anweisung hinter Then folgen.
41
EDV I
Beispiel: Die Prozedur RabattBerechnen berechnet einen Rabatt von 5 Prozent auf einen
Mindestumsatz von 100 €. Die Prozedur enthält zwei einseitige Bedingungen, die geschachtelt
sind. Diese Schachtelung wird im Programmcode durch Einrücken verdeutlicht. Dafür wird
häufig der Begriff Strukturierte Programmierung verwandt.
Zuerst wird die numerische Eingabe des Preises abgefragt. Danach wird geprüft, ob der Preis
größer oder gleich 100 € ist.
Sub RabattBerechnen()
Const conFmt
As String = "##,###.00 €"
'Formatierungs-String
Dim varEingabe As Variant
Dim curPreis As Currency, curRabatt As Currency
varEingabe = InputBox("Bitte geben Sie den Preis ein:", "Rabatt",
100)
If IsNumeric(varEingabe) Then
curPreis = CCur(varEingabe)
If curPreis >= 100 Then
curRabatt = curPreis * 0.05
curPreis = curPreis - curRabatt
End If
MsgBox "Der Endpreis beträgt: " & Format(curPreis, conFmt)
End If
End Sub
10.7.2 Zweiseitige Auswahl
Die zweiseitige Auswahl…
wird angewendet, wenn es genau zwei Verzweigungsmöglichkeit mit dazugehörigen
Anweisungsblöcken gibt.
führt in Abhängigkeit
von einem Bedingungsausdruck nur einen der beiden
Bedingungsblöcke aus.
Die Syntax lässt sich aus folgendem Beispiel gut erkennen
If Bedingung Then
Anweisung(en)1
Else
Anweisung(en)2
End If
In die bereits vorgestellte Prozedur RabattBerechnen kann beispielweise im Else-Zweig
folgender Rabatthinweis eingebaut werden:
If curPreis >= 100 Then
curRabatt = curPreis * 0.05
42
EDV I
curPreis = curPreis - curRabatt
MsgBox "Der Endpreis beträgt: " & Format(curPreis, conFmt)
Else
MsgBox "Der Endpreis beträgt: " & Format(curPreis, conFmt)& _
vbCrLf &"Ab einem Betrag von 100 € erhalten Sie 5 % Nachlass."
End If
10.7.3 Mehrstufige Auswahl
If Bedingung1 Then
Anweisung(en)1
ElseIf Bedingung2 Then
Anweisung(en)2
ElseIf Bedingung3 Then
Anweisung(en)3
Else
Anweisung(en)4
End If
In die bereits vorgestellte Prozedur RabattBerechnen kann beispielweise folgende 4- stufige
Rabattstaffel {0%, 5%, 7,5%, 10%} in Abhängigkeit von der Preishöhe aufgenommen werden:
Dim sngRabatt As Single
If curPreis < 100 Then
MsgBox "Der Endpreis beträgt: " & _
Format(curPreis, conFmt) & vbCrLf & _
"Ab einem Betrag von 100 € erhalten Sie 5 % Nachlass."
ElseIf curPreis < 250
Then sngRabatt = 0.05
ElseIf curPreis < 500
Then sngRabatt = 0.075
Else
sngRabatt = 0.1
End If
curRabatt = Round(curPreis * sngRabatt, 2)
curPreis = curPreis - curRabatt
MsgBox "Der Endpreis beträgt: " & Format(curPreis, conFmt)
10.7.4 Mehrseitige Auswahl
Bei der mehrseitigen Fallauswahl wird der Wert einer Variablen oder eines Ausdrucks
abgefragt. Diese Variable oder dieser Ausdruck wird als Selektor bezeichnet. In Abhängigkeit
vom Wert des Selektors können je nach Fall (engl. case) unterschiedliche Anweisungsblöcke
durchlaufen werden.
Die folgende Prozedur JahreszeitBestimmen demonstriert die Anwendung der mehrseitigen
Fallauswahl, auch Select Case-Anweisung genannt:
43
EDV I
SubJahreszeitBestimmen()
Dim strSaison As String
Select Case Month(Date)
Case 3, 4, 5
strSaison = "Frühlings"
Case 6, 7, 8
strSaison = "Sommer"
Case 9, 10, 11
strSaison = "Herbst"
Case Else
strSaison = "Winter"
End Select
MsgBox "Zur Zeit ist " & strSaison & "-Saison."
End Sub
Als Vergleichswerte können verschiedene Varianten gewählt werden.
Variante
 Ein einzelner Wert
 Eine Liste von Werten
 Ein Wertebereich
 Ein Vergleichsausdruck
Beispiel:
Case 1
Case 1, 2, 3
Case 1 To 3
Case Is > 1
Tabelle 15 - Select Case Schlüsselwerte
10.7.5 Übungen zu Kontrollstrukturen
Dateivorlage: (keine Vorlage)
a. Schreiben Sie eine Prozedur die eine Eingabe von 4 Parametern ermöglicht.
Der Parameter mit dem größten Wert soll ermittelt werden.
b. Schreiben Sie eine Prozedur zur Bestimmung der Werte und berechnen Sie die
jeweilige Abweichung zu dem Wert Pi [4 atan(1)] Ordnen Sie die Werte der
Genauigkeit an, geben Sie diese mit den Abweichungen aus.
Annäherungen an die Zahl 𝜋 wie sie früher benutzt wurden.
1
Babylonier mit dem Wert
38
16 2
In Ägypten
(9)
In China
in Europa
√10
1
38
44
EDV I
10.8 Schleifen
10.8.1 Schleifenaufbau
Um einen bestimmten Programmteil mehrmals zu durchlaufen, werden Schleifen benötigt. Eine
Schleife besteht im Wesentlich aus zwei Teilen:
der Schleifensteuerung und
dem Schleifenrumpf
Die Schleifensteuerung bestimmt, wie oft die Anweisungen des Schleifenrumpfs ausgeführt
werden bzw. nach welchen Kriterien entschieden wird, ob eine Wiederholung erforderlich ist.
10.8.2 Schleifenarten
Zwei Arten der Schleifensteuerung werden im Allgemeinen unterschieden:
Abbildung 36 - Kopf/Fußgesteuerte Schleife
Kopfgesteuerte Schleife
(= abweisende Schleife)
Die Prüfung, ob die Anweisungen im
Schleifenrumpf ausgeführt werden, erfolgt
gleich zu Beginn.
Falls das Kriterium zu Beginn nicht erfüllt ist,
wird der Schleifenrumpf gar nicht ausgeführt
Fußgesteuerte Schleife
(= nicht abweisende Schleife)
Die Anweisungen im Schleifenrumpf warden
mindestens einmal ausgeführt.
Am Ende der Schleife erfolgt die Prüfung, ob
einer weiterer Durchlauf erforderlich ist.
Tabelle 16 - Kopf/Fußgesteuerte Schleifen
10.8.3 Die Kopfgesteuerte Schleife
Die kopfgesteuerte Schleife ist definitionsgemäß eine abweisende Schleife. Drei Varianten
werden unterschieden:
45
EDV I
Do While
Do Until
Do [{While} Bedingung]
[Anweisungen]
[Exit Do
[Anweisungen]
Loop
While
Do [{Until} Bedingung]
[Anweisungen]
[Exit Do]
[Anweisungen]
Loop
While Bedingung
[Anweisungen]
[Go To Sprungmarke]
[Anweisungen]
Wend
Tabelle 17 - Syntax der Kopfgesteuerten Schleife
Mit dem Schlüsselwort While wird eine Schliefe durchlaufen, solange die Bedingung zutrifft.
Mit dem Schüsselwort Until wird eine Schleife solange durchlaufen, bis die Bedingung erfüllt
ist. Wenn erforderlich, kann mit Exit Do bzw. GoTo Sprungmarke die Schleife vorzeitig beendet
werden.
Die While...Wend-Anweisung ist veraltet. Die beiden Do...Loop-Anweisungen stellen eine
strukturiertere und flexiblere Möglichkeit zur Ausführung von kopfgesteuerten Schleifen dar.
10.8.4 Die Fußgesteuerte Schleife
Bei den Fußgesteuerten Schleifen werden zwei Arten unterschieden.
Do…Loop While
Do…Loop Until
Do
Do
[Anweisungen]
[Exit Do]
[Anweisungen]
Loop While Bedingung
[Anweisungen]
[Exit Do]
[Anweisungen]
Loop Until Bedingung
Tabelle 18 - Syntax der Fußgesteuerten Schleife
Das Schlüsselwort While bestimmt, dass die Schleife ausgeführt wird, solange die Bedingung
erfüllt ist. Das Schüsselwort Until bestimmt, dass die Schleife solange durchlaufen wird, bis
die Bedingung erfüllt ist.
10.8.5 Die Zählschleife
Sie können eine zählergesteuerte Schleife verwenden, um einen Block von Anweisungen für
eine genau bestimmte Anzahl von Wiederholungen auszuführen. Kennzeichnendes Merkmal
einer Zählschleife ist, wie ihr Name bereits verrät, eine Zählervariable. Deren Wert wird mit
jedem Schleifendurchlauf erhöht oder vermindert. Syntax:
For Zähler = Startwert To Endwert [Step Schrittweite] [Anweisungen]
[Exit For]
[Anweisungen]
Next [Zähler]
Argument
Zähler
Beschreibung
Erforderlich. Besteht aus einer numerischen Variablen, die als Schleifenzähler dient.
46
EDV I
Startwert
Endwert
Schrittweite
Anweisungen
Exit For
Das ist der Startwert vom Zähler.
Legt den Endwert von Zähler fest.
Optional. Damit kann der Wert bestimmt werden, um den sich Zähler
bei jedem Schleifendurchlauf verändert. Falls kein Wert angegeben
wird, ist die Voreinstellung eins.
Die Anweisungen zwischen For…Next werden so oft ausgeführt wie
an- gegeben.
Optional. Innerhalb der Schleife kann eine beliebige Anzahl von Exit
For-Anweisungen an beliebiger Stelle als Möglichkeit zum Verlassen
der Schleife verwendet werden.
Tabelle 19 - Argumente der Zählschleife
10.8.6 Sonderform der Zählschleife
Eine besondere Form der For-Next-Schleife in VBA ist die For-Each-Next-Schleife. Damit
können Sie jedes Element in einem Array oder einer Auflistung (= Liste von Objekten)
ansprechen. Die Zahl der Elemente muss vorher nicht bekannt sein.
In der nun folgenden Prozedur TabellenblätterAuslesen wird eine For-Each-NextSchleife benutzt, um die Namen aller Tabellenblätter in der aktuellen Arbeitsmappe in das
Direktfenster zu schreiben.
Public Sub TabellenblätterAuslesen()
' Die Namen aller Tabellenblätter auslesen
Dim ws As Excel.Worksheet
For Each ws In Worksheets
Debug.Print "Name: " & ws.Name
Next ws
End Sub
10.9 Modulare Programmierung
Kern
der
Modularen
Programmierung
ist
das
Auslagern
von
abgeschlossenen
Programmstücken, deren Algorithmen an verschiedenen Stellen gebraucht werden: Der Code
wird dadurch wiederverwendbar.
10.9.1 Prozedur
Eine Prozedur wird durch folgende Syntax deklariert:
[Public|Private ]Sub Prozedurname([ByVal|ByRef] Parameter1 [As Typ1][,...])
...
[Exit Sub]
...
End Sub
47
EDV I
Der Prozedurkopf beginnt mit dem Schlüsselwort Sub, gefolgt von dem Prozedurnamen und
einem Klammerpaar ().
Der Prozedurname muss sich an die Regeln für Bezeichner halten.
Die Prozedur kann mit der Anweisung Exit Sub verlassen werden.
Die Programmausführung wird mit der ersten Anweisung fortgesetzt, die dem
Prozeduraufruf folgt.
Die Deklaration der Prozedur wird mit End Sub beendet.
Im
Prozedurkopf
kann
Public
oder
Private
vorangestellt
werden,
um
den
Gültigkeitsbereich der Prozedur festzulegen. Prozeduren mit den Schlüsselwort Public
besitzen globale Gültigkeit, solche mit Private nur lokale. Wird keines dieser beiden
Schlüsselwörter angegeben, dann handelt es sich standardmäßig um eine globale Prozedur.
Optional kann die Prozedur mit Parameterübergabe aufgerufen werden. Dabei können die
Parameter entweder als Wert (ByVal) oder als Verweis (ByRef) übergeben werden.
Die beiden unterschiedlichen Parameterübergaben wirken sich wie folgt aus:
Parameter wird als Wert übergeben
Sub Aendern(ByVal)intZ As Integer
intZ = intZ + 10
End Sub
Parameter wird als Verweis übergeben
Sub Aendern(ByRef)intZ As Integer
intZ = intZ + 10
End Sub
Wirkung: übergebener Wert wird ni
cht geändert
Wirkung: übergebener Wert wird geändert
Um eine Prozedur zu starten schreiben Sie eine Anweisung, die nur aus dem Namen der
aufzurufenden Prozedur besteht oder setzen Sie optional noch das Schlüsselwort Call davor.
Optional enthält der Aufruf der Prozedur noch eine Liste der Werte, Konstanten oder
Ausdrücke, die an diese übergeben werden sollen.
[Call ]Prozedurname (Ausdruck [, ...])
Eine Prozedur, die von einer anderen Prozedur aus aufgerufen wird, bezeichnet man im
Allgemeinen als Subprozedur, Subroutine oder Unterprogramm.
Eine Prozedur, von der aus eine oder mehrere Subprozeduren aufgerufen werden und selbst von
keinem Programm aus ausgerufen wird, wird als Hauptprogramm bezeichnet
48
EDV I
10.9.2 Funktion
Funktionen sind Prozeduren, die nur einen Wert zurückgeben. Sie können sogen.
benutzerdefinierte Funktionen schreiben, die auf einem Tabellenblatt wie eine in Excel
eingebaute Funktion über den Funktionsassistenten aufgerufen werden kann.
Allgemein wird eine Funktion definiert durch:
[Public|Private ]Function Name(Parameteri [As Typi])
...
[Exit Function]
[Name = Rückgabewert]
End Function
Innerhalb der Klammer () wird von der aufrufenden Prozedur eine Parameterliste an die
Funktion übergeben. Wird die Deklaration des Datentyps weggelassen, wird standardmäßig
von Datentyp Variant ausgegangen.
Mehrere übergebene Parameter werden jeweils durch ein Komma getrennt.
Da eine Funktion einen Wert zurückgibt, ist es sinnvoll, im Funktionsaufruf den Datentyp des
Wertes von Name hinter der schießenden Klammer der Parameterliste festzulegen. Innerhalb
der Funktion wird der Rückgabewert der Funktion durch die Zeile Name =… bestimmt. Der
Funktionsaufruf erfolgt durch:
Variable = Name([Variable1], ...)
Tabelle 20 - Select Case Schlüsselwerte
10.9.3 Übungen zur Modularen Programmierung
Dateivorlage: (keine Vorlage)
a) Schreiben Sie eine aufrufende und eine aufgerufene Prozedur. Die Prozeduren
tauschen vier Zahlen als Parameter aus. Die aufgerufene Prozedur soll ermitteln,
welcher der ersten beiden Parameter die geringste Differenz zum dritten hat und
die Nummer des Parameters (1 oder 2) als vierten Parameter wieder an das
Hauptprogramm zurückgeben.
b) Schreiben Sie eine Prozedur, die eine andere aufruft und je nach Art der
Parameterübergabe (ByVal) unterschiedliche Ergebnisse liefert.
49
EDV I
11 Objekte in Excel
11.1 Objekte
In Excel sind mehr als 150 Objekte definiert. Das umfasst im Wesentlichen alles, was
programmiert und kontrolliert werden kann
Jedes Excel-Objekt wird durch seine Eigenschaften und Methoden beschrieben. Ein wichtiges
Objekt ist beispielsweise eine Arbeitsmappe. Sie hat einen bestimmten Namen, einen Autor
oder sie ist durch ein Passwort geschützt. Sie besitzt standardmäßig ein Tabellenblatt. Sie kann
auch Diagrammblätter enthalten.
Auf Excel-Objekte lassen sich Methoden anwenden. Methoden, die auf Arbeitsmappen
angewandt werden können, sind zum Beispiel: Aktivieren, schließen, speichern, drucken oder
löschen.
Die folgende Tabelle beinhaltet einen kleinen Überblick über die am häufigsten benutzten
Objekte in Excel:
Objekttyp
Application
Cells
Chart
Charts
Dialog
Dialogs
Module
Modules
Names
Range
Sheets
Window
Windows
Workbook
Workbooks
Worksheet
Worksheets
Kurzbedeutung
Die aktuelle Anwendung, praktisch Excel selbst
Zellen auf in einem Arbeitsblatt
Stellt ein Diagramm in einer Arbeitsmappe dar
Eine Auflistung der in einer Arbeitsmappe enthaltenen Diagrammblätter
Stellt ein einzelnes Dialogfeld dar.
Eine Auflistung der in einer Arbeitsmappe enthaltenen Dialog-Objekte
Ein einzelnes Modulblatt
Eine Liste der in einer Arbeitsmappe enthaltenen Modulblätter
Gibt eine Auflistung aller arbeitsblattspezifischen Namen zurück.
Umfasst eine einzelnen Zelle, eine ganze Zeile oder Spalte, einen
Zellbereich, usw.
Eine Auflistung der augenblicklich verfügbaren Tabellen-, Diagramm- und
sonstige Blätter in der angegebenen Arbeitsmappe.
Ein einzelnes Fenster
Eine Auflistung verschiedener augenblicklich geöffneter Fenster
Eine einzelne Arbeitsmappe
Eine Auflistung aller augenblicklich geöffneten Arbeitsmappen
Stellt ein einzelnes Arbeitsblatt dar.
Eine Auflistung aller in einer Arbeitsmappe enthaltenen Tabellenblätter
50
EDV I
11.2 Objekte benennen
Um Objekte zu bearbeiten, müssen sie genau benannt werden. Beispiel: Sie haben sowohl in
der Arbeitsmappe Rechnung als auch in der Arbeitsmappe Auftrag jeweils ein Tabellenblatt
namens TABELLE1. Ein eindeutiges Referenzieren der Zelle B1 in Tabelle1 der Arbeitsmappe
Rechnung ist nur unter Angabe der Arbeitsmappe möglich.
Workbooks("Rechnung").Worksheets("Tabelle1").Range("B1").Value = "Datum"
Wird allerdings nur mit einer einzigen Arbeitsmappe gearbeitet, dann ist das Referenzieren der
Arbeitsmappe überflüssig. Obige Anweisung verkürzt sich dann zu:
Worksheets("Tabelle1").Range("B1").Value = "Datum"
Ist Tabelle1 bereits das aktive Tabellenblatt, dann genügt sogar die Anweisung
Range("B1").Value = "Datum"
um die richtige Zelle im richtigen Tabellenblatt zu adressieren.
11.3 Eigenschaften von Objekten
Eigenschaften (engl. properties) beschreiben Objekte. Eigenschaften von Objekten können
abgefragt und häufig auch geändert werden. Einige Eigenschaften können nur gelesen, aber
nicht geändert werden.
Die folgende Prozedur SpeicherStatus prüft, ob die aktive Arbeitsmappe bereits gespeichert
wurde und gibt eine entsprechende Meldung aus
Sub SpeicherStatus()
Dim bolSaved AsBoolean Dim strMsg As String
bolSaved = ActiveWorkbook.Saved
If bolSaved Then
strMsg = "Die Arbeitmappe wurde gespeichert."
Else
strMsg = "Die Arbeitsmappe wurde noch nicht gespeichert."
End If
MsgBox strMsg, vbInformation, "Speicherstatus"
End Sub
51
EDV I
11.4 Methoden von Objekten
Als Methode wird ein Vorgang bzw. eine Tätigkeit bezeichnet, die mit einem Excel-Objekt
ausgeführt werden kann. Um eine Methode anzuwenden, wird ein Bezug auf das betreffende
Objekt benötigt, gefolgt von einem Punkt und dem Namen der Methode, also allgemein so:
Objekt.Methode
Im folgenden Beispiel wird der Tabellenbereich A1:B2 ausgewählt und kopiert
Sub BereichKopieren()
With ActiveSheet
MsgBox "Das aktulle Tabellenblatt heißt " & .Name
Range("A1:B2").Select
End With
Selection.Copy
End Sub
Viele Methoden übergeben Argumente, um ihre Ausführung zu spezifizieren. Beispielsweise
kann mit der Methode PrintOut das Argument Copies:=1 übergeben werden.
ActiveWindow.SelectedSheets.PrintOut Copies:=1
11.5 Ereignisse von Objekten
Jede vom Bediener ausgelöste Aktion - z.B. Tastenschlag Mausklick - löst in Windows ein
Ereignis aus. Windows wertet das Ereignis aus und gibt ggf. die Nachricht an das entsprechende
Anwendungsprogramm weiter.
In Excel können ebenfalls Programme geschrieben werden, die bei gewissen Ereignissen
ausgeführt werden.
Mausklick auf ein Symbol, ein Steuerelement oder einen Menüpunkt
Tastatureingaben
Öffnen oder Schließen einer Arbeitsmappe oder eines Fensters
Die entsprechenden Programme werden Ereignisprozeduren genannt.
Syntax. Das Objekt und das zugehörige Ereignis werden durch
Schaltfläche_Click()
Arbeitsmappe_Open()
Tabellenblatt_Activate()
Diagramm_Change()
einen Unterstrich ( _ ) hintereinander geschrieben.
Tabelle 21 - Syntax Ereignisprozeduren
Um die Ereignisprozeduren zu benennen bedarf es einer kleinen
52
EDV I
Das folgen Beispiel zeigt die Schritte für eine Ereignisprozedur für die Arbeitsmappe
DieseArbeitsmappe, die durch das Öffnen – also dem Ereignis open – ausgeführt werden
soll
Klicken Sie im Projekt-Explorer doppelt auf
das Objekt DieseArbeitsmappe
Für dieses Objekt wird das CodeFenster
geöffnet.
Vgl. Abbildung 25- Projektexplorer
Geben Sie Option Explicit ein, falls noch
nicht vorhanden.
Wählen Sie im linken Listenfeld des CodeFensters den Eintrag Workbook.
Es wird automatisch die leere
Prozedur Workbook_Open für das
Ereignis open erstellt.
Fügen Sie die gewünschte Ereignisprozedur
ein.
Private Sub Workbook_Open()
Ereignisprozedur
End Sub
Tabelle 22 - Arbeitsschritte zur Erstellung einer leeren Workbook_Open Ereignisprozedur
Neben Workbook_Open ist Workbook_BeforeClose ein weiteres wichtiges Ereignis. Es tritt
automatisch ein, sobald die Arbeitsmappe geschlossen wird. Aber es gibt noch mehr Ereignisse
für Arbeitsmappen. Weiter Ereignisse für Arbeitsmappen enthält Tabelle 22:
Ereignisprozeduren für Tabellenblätter oder Diagrammblätter können auf ganz ähnliche Weise
erstellet werden.
Einige wichtige Ereignisse für Tabellenblätter enthält Tabelle 21:
Ereignis (WorkSheet_...)
Activate
Change
Calculate
Deactivate
SelectionChange
Beschreibung (Tritt ein, …
sobald eine Tabellenblatt aktiviert wird.
wenn sich der Wert einer Zelle ändert.
wenn ein Tabellenblatt neu berechnet wird.
wenn ein Tabellenblatt deaktiviert (z. B. verlassen) wird.
wenn sich die Markierung auf einemTabellenblatt ändert.
Tabelle 23 - Ausgewählte Ereignisse für Tabellenblätter
53
EDV I
Ereignis (WorkBook_...)
Activate
BeforePrint
BeforeSave
Deactivate
NewSheet
SheetActivate
SheetBeforeDoubleClick
SheetBeforeRightClick
SheetCalculate
SheetDeactivate
SheetChange
SheetSelectionChange
WindowActivate
WindowDeactivate
WindowResize
Beschreibung (Tritt ein, …
sobald eine Arbeitsmappe aktiviert wird.
vor dem Druck einer Arbeitsmappe oder einzelner
Tabellenblätter.
vor dem Speichern.
wenn eine Arbeitsmappe deaktiviert wird.
wenn ein neues Tabellenblatt in die Arbeitsmappe eingefügt
wird.
wenn ein beliebiges Tabellen- oder Diagrammblatt in der
Arbeitsmappe aktiviert wird.
wenn mit der linken Maustaste ein Doppelklick auf eine
beliebige Stelle des Arbeitsblatts ausgeführt wird.
wenn mit der rechten Maustaste ein Klick auf eine beliebige
Stelle des Arbeitsblatts ausgeführt wird.
bei Neuberechnung eine beliebigen Tabellenblatts.
sobald ein beliebiges Tabellenblatt verlassen wird.
bei Änderung eines beliebigen Tabellenblatts.
wenn sich die Markierung auf einem Tabellenblatt ändert
wenn ein Fenster der Arbeitsmappe aktiviert wird.
wenn ein Fenster der Arbeitsmappe deaktiviert wird.
wenn ein die Größe eines Fenster der Arbeitsmappe geändert
wird.
Tabelle 24 - Ausgewählte Ereignisse für Arbeitsmappen
11.6 Eigenschaften und Methoden des Range-Objekts
Die wichtigsten Eigenschaften und Methoden des Range-Objekts sind in Tabelle 23 enthalten:
Eigenschaft
Methode
Cells
Column, Columns
Row, Rows
Border
Interior
Offset
Resize
Font
ColumnWidth
Zellen
Spalten
Zeilen
Rahmen
Innenbereich
Versetzung
Größenänderung
Zeichensatz
Spaltenbreite
Copy
Cut
PasteSpecial
Clear
Delete
Find
Insert
Select
AutoFill
Kopieren
Ausschneiden
Einfügen
Inhalt löschen
Objekt löschen
Suchen
Einfügen
Markieren
Füllen (automatisch)
Tabelle 25 - Wichtige Eigenschaften und Methoden des Range-Objekts
Eine zentrale Rolle beim Zugriff auf Zellen und Zellenbereiche spielt die Range-Auflistung. Sie
kann aus einer einzelnen Zelle, einer Spalte, einer Zeile oder einer ausgewählten Menge von
Zellen bestehen.
Die Bearbeitung von Zellen oder Zellenbereichen kann entweder
direkt erfolgen oder
nach einer vorhergehender Selektion der Zellen.
54
EDV I
Bei der direkten Bearbeitung folgt auf eine Range-Eigenschaft, die eine Auflistung zurückgibt,
die gewünschte Eigenschaft.
Range("A1:B5").Font.Bold = True
Bei der zweiten Möglichkeit wird der Bereich durch die Methode Select markiert. Der Zugriff auf diesen Bereich erfolgt dann mit der Selection-Eigenschaft.
Range("A1:B5").Select Selection.Font.Bold = True
11.7 Zugriff auf Zellen
11.7.1 Zugriff auf die aktuelle Zelle
Auf die aktive Zelle wird mit der ActiveCell-Eigenschaft zugegriffen. Sie gibt ein RangeObjekt zurück. Haben Sie beispielsweise auf Zelle A1 mit
Range("A1").Activate
oder
Range("A1").Select
zugegriffen, dann greifen Sie mit ActiveCell auf diese aktive Zelle zu, um ihr einen Wert
zuzuordnen oder sie zu formatieren
SubZugriffAufAktiveZelle()
Range("A1").Select
With ActiveCell
.Value = "Wert"
.Font.Bold = True
End With
End Sub
Mit der folgenden Prozedur wird die aktive Zelle A1 nach A2 verschoben
SubAktiveZelleEineZeileNachUnten()
Range("A1").Select
ActiveCell.Offset(1, 0).Select
End Sub
55
EDV I
Die Adresse der aktuellen Zelle sowie die aktuelle Zeilen- und Spaltennummer werden durch
folgende Prozedur ins Direktfenster ausgegeben
SubAktiveZelleEineZeileNachUnten()
Range("A1").Select
With ActiveCell
.Offset(1, 0).Select
Debug.Print "Adresse der aktuellen Zelle: " & .Address
Debug.Print "Aktive Zeile: " & .Row
Debug.Print "Aktive Spalte:" & .Column
End With
End Sub
Grundsätzlich können alle Methoden und Eigenschaften des Range-Objekts auf die aktive Zelle
ActiveCell
angewandt werden.
11.7.2 Relativer Zugriff auf Zellen und Zellenbereiche
Mit der Offset-Eigenschaft lässt sich der relative Zugriff auf Zellen ziemlich einfach durchführen. Sie lautet allgemein:
Objekt.Offset(RowOffset, ColumnOffset)
Sie gibt ein Range-Objekt zurück, das einen Bereich darstellt, der gegenüber dem angegebenen
Bereich versetzt ist. Als Argumente können positive und negative Zahlen oder null verwandt
werden. Die Prozedur RelativerZugriff verschiebt die aktuelle Zelle von A1 nach B3
Sub RelativerZugriff()
Range("A1").Select
ActiveCell.Offset(RowOffset:=2, ColumnOffset:=1).Activate
End Sub
Positive (negative) Werte für RowOffset bewirken eine Verschiebung unten (oben). Positive
(negative) Werte für ColumnOffset bewirken eine Verschiebung nach rechts (links).
11.7.3 Zugriff auf Zeilen und Spalten
Um mit Spalten zu arbeiten, gibt es auch die Eigenschafen Colum, Columns und
EntrireColumn.
Für Zeilen existieren die vergleichbaren Eigenschaften Row, Rows und
EntireRow.
56
EDV I
Eigenschaft
Column
Columns
EntireColumn
Bedeutung
Gibt eine Nummer zurück, die die erste Spalte in dem Bereich angibt, auf
den die Eigenschaft bezogen wird.
Gibt ein Range-Objekt zurück, das die Spalten im angegebenen Bereich
darstellt.
Gibt ein Range-Objekt zurück, das eine oder mehrere ganze Spalten darstellt,
die im angegebenen Bereich enthalten sind
Tabelle 26 - Eigenschaften von Spalten
Die Prozedur ColumnProp gibt in einer MsgBox die Zahl 2 zurück, weil es sich bei der Spalte B
um die zweite Spalte handelt.
Sub ColumnProp()
With Worksheets("Tabelle1")
.[B2:E4].Select
End With
MsgBox Selection.Column
End Sub
Mit der Prozedur ColumnsProp wird die erste Spalte des ersten Tabellenblatts markiert.
Sub ColumnsProp()
With Worksheets("Tabelle1")
.Columns(1).Select
End With
MsgBox "Die Auswahl enthält " & _ Selection.Columns.Count & "
Spalte(n)."
End Sub
Die Meldung gibt an, wie viele Spalten (hier 1) die Markierung enthält.
57
EDV I
11.8 Inhalte in Zellen einsetzen und abfragen
11.8.1 Werte zuweisen
Zur Zuweisung eines Wertes oder eines Textes an eine Zelle sowie zum Abfragen ihres Inhalts
wird die Eigenschaft Value benutzt. In der Prozedur WerteZuweisen werden den Zellen A1 bis
A4 Werte zugewiesen und anschließend wieder ausgelesen.
Sub WerteZuweisen()
Dim strMsg As String
Dim intZahl As Integer
Dim intLoop As Integer
With Worksheets(1)
' Inhalte einsetzen
.Range("A1").Value = "Zahlen" ' vereinfachte Schreibweise
.[A2].Value = 12
.[A3].Value = 24
.[A4].Value = 36
' Inhalte entnehmen
strMsg = .[A1].Value & ": "
' Zählschleife über drei Zellen
For intLoop = 1 To 3
' Inhalt zwischenspeichern
intZahl = .Range("A" & intLoop + 1).Value
strMsg = strMsg & intZahl & ", "
Next intLoop
End With
strMsg = Left(strMsg, Len(strMsg) - 2)
' Ergebnis anzeigen
MsgBox strMsg, vbInformation, "Zellinhalte setzen und abfragen"
End Sub
Bei der Value-Eigenschaft handelt es sich um eine Standardeigenschaft. Sie ist deshalb optional
und kann weggelassen werden. Wird keine Eigenschaft angegeben, wird diese automatisch
verwendet.
11.8.2 Formeln einsetzen
Eine Formel wird in eine Zelle eingesetzt, indem sie als string (Zeichenkette) übergeben wird.
SubFormelnEinsetzen()
With Worksheets(1)
' Addiern
.[B1] = 10
.[B2] = 5
.[B3].Value = "=B1+B2"
End With
End Sub
11.8.3 Zellinhalte löschen
Es gibt verschiedene Arten Zelleninhalte bzw. Formatierungen zu löschen
58
EDV I
ClearContents
Clear
löscht den Inhalt einer Zelle.
löscht sowohl den Inhalt als auch die Formatierung.
ClearFormats
ClearNotes
löscht die Formatierung.
löscht Notizen zu Zellen
11.8.4 Hintergrundfarbe setzen
Die Prozedur FarbenAusgeben zeigt, wie die Zellen eines Tabellenblatts eingefärbt werden
können. Die Prozedur gibt 56 Hintergrundfarben mit dem jeweils dazugehörigen Farbindex
(ColorIndex) aus.
Sub FarbenAusgeben()
Dim intFarbe As Integer
Dim intSpalte As Integer
Dim intZeile As Integer
Dim rng As Range
With Worksheets(1)
' Spaltenbreite setzen
.Range("B:B, D:D, F:F, H:H").ColumnWidth = 7.5
.Range("A:A, C:C, E:E, G:G").ColumnWidth = 3
' Farbe entfernen
.[A1:H14].Interior.ColorIndex = xlNone
.[A1].Select
Set rng = ActiveCell
' Zellen einfärben
For intFarbe = 0 To 55
intSpalte = (intFarbe \ 14) * 2
' ganzzahlige Division
intZeile = intFarbe Mod 14
' Divisionsrest best.
With rng
.Offset(intZeile, intSpalte) = intFarbe + 1
.Offset(intZeile, intSpalte + 1) _
.Interior.ColorIndex = intFarbe + 1
End With
Next intFarbe
End With
End Sub
Abbildung 37 - Hintergrundfarben setzen
59
EDV I
11.8.5 Schriftart und Schriftattribute bestimmen
Die Veränderung von Schriftart und Schriftattributen erfolgt mit Eigenschaften des FontObjekts. Das Beispiel zeigt die Syntax.
With [Bereich].Font
.Name = "Arial" ' Schriftart
.Size = 20 ' Schriftgröße
.Bold = True
' Fettdruck
.Italic = True
' Kursivdruck
End With
11.9 Wichtige Eigenschaften und Methoden der Sheets-Auflistung
Eigenschaft/
Bedeutung
Count
Methode
Es wird die Anzahl der Blätter in der Auflistung angezeigt.
MsgBox ActiveWorkbook.Sheets.Count
Add
Erzeugt ein neues Blatt vom Typ xlWorksheet oder vom Type xlChart.
Sub BlattHinzufuegen()
Sheets.Add after:=Sheets("Tabelle1"), Type:=xlWorksheet
End Sub
Select
Mit dieser Methode wird das angegebene das Objekt markiert.
ActiveWorkbook.Sheets("Tabelle2").Select
Item
Diese Eigenschaft gibt ein einzelnes Blatt der Auflistung zurück.
Sheets.Item(3).Select
11.9.1 Übungen zu Objekten
Dateivorlage: (keine Vorlage)
a) Benutzen Sie die With – Anweisung, um für eine Zelle die italic –
Eigenschaft auf „True“ und die Size – Eigenschaft auf „16“ zu setzen.
b) Lesen Sie den Wert aus der Zelle A6, multiplizieren Sie ihn mit 5 und
schreiben Sie ihn in die Zelle A7. Verwenden Sie zur Angabe der Zellen
Objektvariable.
c) Eine Tabelle enthält in der Spalte A eine Folge von Zahlen. Zählen Sie die
Anzahl der Zellen bis zur 100sten Zeile, in denen der Wert 5 steht.
60
EDV I
12
Application
Excel
12.1 Standartspeicherort Excel
Der Standartspeicherort für Excel Arbeitsmappen kann im Register Datei – Optionen –
Speichern – Lokaler Standartspeicherort für Datei verändert werden.
Abbildung 38 - Standartspeicherort ändern
Mit VBA kann der Standardspeicherort z.B. über das Direktfenster mit folgender Codezeile
bestimmt werden:
Application.DefaultFilePath = "C:\MeinPfad\MeinOrdner"
12.2 Das Application Objekt
Mit dem Application-Objekt kann nicht nur auf sogen. Auflistungen zugegriffen werden,
sondern auch auf Objekte zur Programmsteuerung. Für verschiedene aktive Objekte, wie die
aktive Arbeitsmappe, das aktive Tabellenblatt oder die aktuell selektierte Zelle, sind verkürzte
Zugriffswege vorhanden.
Die verschiedenen Elemente des Application-Objektes können im Objektkatalog (F2)
eingesehen werden.
12.2.1 Zugriff auf Arbeitsmappen
Der Zugriff auf Arbeitsmappen kann auf verschieden Art und Weise erfolgen. In Tabelle 25
sind die verschiedenen Möglichkeiten zusammengestellt
61
EDV I
ActiveWorkbook
ThisWorkbook
Sie haben Zugriff auf die aktuelle Arbeitsmappe.
Sie haben Zugriff auf die Arbeitsmappe, in der die aktuelle
Prozedur ausgeführt wird.
Workbooks(1)
Workbooks.Item(1)
Workbooks("Name")
Workbooks.Item("Name")
Workbooks-Objektvariable
Workbooks-Auflistung
Über ein Element der Workbooks-Auflistung oder eine
Objekt-variable von Type Workbook besteht ebenfalls Zugriff
auf eine bestimmte Arbeitsmappe
Damit können Aktionen durchgeführt werden, die sich
allgemein auf die Verwaltung von Arbeitsmappen beziehen.
Tabelle 27 - Zugriffsmöglichkeiten auf Arbeitsmappen
In der folgenden Tabelle sind wichtige Eigenschaften und Methoden des Workbook-Objekts
zusammengestellt:
Eigenschaft
FullName
Name
Theme
Saved
Sheets
Worksheets
Charts
Methode
Vollständiger Dateiname
Dateiname
Design der Mappe
Speicherstatus
Auflistung aller Blätter
Auflistung Arbeitsblätter
Auflistung Diagrammblätter
Activate
Close
Save /
SaveAs
PrintOut
Protect
Unprotect
ApplyTheme
Aktivieren
Schließen
Speichern
Ausdrucken
Schützen
Schutz aufheben
Design zuweisen
Tabelle 28 - Ausgewählte Eigenschaften und Methoden des Workbook-Objekts
12.2.2 Arbeitsmappen speichern
Um die Arbeitsmappe zu Speichern benutzen wir die SaveAs-Methode.
ActiveWorkbook.SaveAs Filename:=strPfadname
Bei Bedarf kann auch auf den integrierten Dialog Speichern unter (F12) zurückgegriffen
werden, der mit der Methode Show ausgeführt wird.
Sub SpeichernUnterDialog()
' Anwendung des integriertes Dialogfelds 'Speichern unter'
If Application.Dialogs(xlDialogSaveAs).Show = True Then
MsgBox Prompt:="Gespeichert!", Buttons:=vbInformation, _
Title:="Speichern unter"
End If
End Sub
Die Auflistung Dialogs enthält alle integrierten Dialoge von Excel. Die Konstante
xlDialogSaveAs
dient zum Aufrufen des Speichern unter-Dialogs von Excel.
62
EDV I
12.2.3 Dokumenteigenschaften anzeigen
Mit der Prozedur DisplayDocProperties lassen sich mittels eingebautem Dialogfeld die
Dokumenteigenschaften anzeigen und pflegen:
Sub DisplayDocProperties()
Dim dlg As Dialog 'Eingebauter Dialog
Dim bolErgebnis As Boolean 'Rückgabewert
Set dlg = Application.Dialogs(xlDialogProperties)
bolErgebnis = dlg.Show
End Sub
12.3 Übungen zum Application Objekt
Dateivorlage: (keine Vorlage)
a) Schreiben Sie eine Prozedur die die Bildunterschrift (caption) des
Application
Objekts auf Microsoft Word setzt.
b) Schreiben Sie eine Prozedur die den Standartspeicherort auf H:\ändert.
13 Benutzerdefinierte Formulare
Wird ein spezielles Dialogfenster benötigt, kann ein Benutzerformular (sogen. UserForm )
erstellt werden. Je nach Bedarf wird dieses Formular mit Steuerelementen bestückt, die aus
einer vorgegebenen Werkzeugsammlung ausgewählt werden. Durch das Programmieren von
Ereignisprozeduren werden dem Benutzerformular die gewünschten Funktionalitäten
hinzugefügt.
13.1 Benutzerformular hinzufügen
In der VBA-Entwicklungsumgebung sind folgende Schritte notwendig um einer UserForm zu
erstellen.
Schrit
t
Beschreibung
Markieren Sie im Projekt-Explorer ein Projekt, dem die UserForm
hinzugefügt werden soll.
Rufen Sie den Menüpunkt Einfügen – UserForm auf oder verwenden
Sie die Schaltfläche UserForm einfügen.
Symbol
Tabelle 29 - Erstellen einer Userform
Ihnen wird eine leere UserForm angezeigt, welche Sie nun mit den benötigten Steuerelementen
bestücken können (Abb. 39). Die Steuerelemente befinden sich in der Werkzeugsammlung.
63
EDV I
Abbildung 39 - Werkzeugsammlung
Abbildung 40 - Benutzerformular (UserForm) einfügen
Die Darstellung der UserForm ist standardmäßig gerastert, um die Positionierung der
Steuerelemente zu vereinfachen. Die Größe des Formulars bestimmen Sie entweder über das
zugehörige Eigenschaftsfenster oder indem Sie den Rand der UserForm mit der Maus
verschieben.
Um zu dem Code zu gelangen öffnen Sie das Kontextmenü und wählen Code Anzeigen
,
über einen Doppelklick auf die UserForm, direkt auf Code Anzeigen oder mit der Taste F7.
13.2 Eigenschaften eines Benutzerformulars
Im Eigenschaftsfenster können Sie den Namen des Formulars, die Größe, die Position und
einiges mehr ändern
Eigenschaft
Kurzbeschreibung
64
EDV I
Name des Formular. Damit wird es bei der Programmierung
angesprochen.
BackColor
Hintergrundfarbe des Formulars
Caption
Bezeichnung des Formulars in der Titelzeile
Height, Width
Höhe und Breite des Formulars
StartUpPosition Position des Formulars bei der Anzeige
(Name)
Tabelle 30 - Wichtige Eigenschaften eines Benutzerformulars
65
EDV I
13.3 Methoden eines Benutzerformulars
Das Anzeigen eines Formulars erfolgt mit der Methode Show. Um es zu schließen, wird
entweder die Methode Hide (Ausblenden) oder Unload (Entfernen) angewandt. Beim Entfernen
werden die Werte aller Variablen gelöscht, die in den Prozeduren des Formulars verwandt
werden. Dagegen bleiben beim Ausblenden des Formulars alle Einstellungen im Speicher
erhalten.
13.4 Ereignisse eines Benutzerformulars
Jedes Formular besitzt Ereignisse, welche Sie mit entsprechenden Ereignisprozeduren codieren
müssen. Die wichtigsten Ereignisse eines Benutzerformulars sind in nachfolgender Tabelle
zusammengestellt:
Ereignis
Initialize
Click
Keypress
KeyDown / KeyUp
MouseDown,
MouseUp,
MouseMove
QueryClose
Terminate
Kurzbeschreibung
Tritt ein, wenn das Formular geladen wird, also vor dem ersten Anzeigen.
Tritt ein, wenn ein Klick mit der linken Maustaste auf das Formular
erfolgt.
Tritt ein, wenn eine Taste betätigt wird, die ein darstellbares Zeichen
darstellt.
Tritt ein, wenn eine beliebige Taste gedrückt oder losgelassen wird.
Dieses Ereignis tritt auch bei Tastenkombinationen ein.
Tritt ein, wenn die Maus betätigt (MouseDown), losgelassen (MouseUp)
oder über das Formlar bewegt (MouseMove) wird.
Tritt ein, wenn das Formular geschlossen werden soll. Es kann jedoch
verhindert werden, z. B. wenn die Eingabedaten nicht korrekt sind.
Tritt ein, wenn das Formular geschlossen wird. Mit Hilfe der
Anweisung Unload oder durch das Klicken auf das Kreuz in der
Titelleiste wird dieses Ereignis ausgelöst.
Tabelle 31 - Wichtige Ereignisse eines Benutzerformulars
13.5 Steuerelemente in Benutzerformular einfügen
Nach dem Erzeugen eines Formulars und der Festlegung seiner Eigenschaften können dem
Formular die benötigten Steuerelemente hinzugefügt werden. Diese befinden sich in der
sogenannten Werkzeugsammlung (Abbildung 38).Mit dem Menüpunkt Ansicht –
Werkzeugsammlung oder dem Symbol
kann auf die Werkzeugsammlung zugegriffen
werden. In Tabelle 7 sind die standardmäßig verfügbaren Steuerelemente aufgeführt.
Das Element
in der Werkzeugsammlung dient zum Markieren und Bearbeiten von
Steuerelementen. Damit wird kein Steuerelement erstellt.
66
EDV I
Präfix
Steuerelement
Bezeichnung
Kombinationsfeld
chk
cmd
fra
CheckBox
CommandButton
Frame
img
Image
lbl
Label
Kontrollkästchen
Befehlsschaltfläche
Rahmen
(Bild-)Anzeige
Bezeichnungsfeld
lst
mpg
opt
ListBox
MultiPage
OptionButton
scr
ScrollBar
spn
SpinButton
Listenfeld
Multiseiten (Tabulatorfeld)
Optionsfeld
Bildlaufleiste (Rollbalken)
Drehfeld
tab
TabStrip
Register (Tabulatorstreifen)
tgl
ToggleButton
txt
TextBox
ref
RefEdit
Umschaltfeld
Achtung: veraltet!
Textfeld
Eingabefeld f. Bereiche
cbo
ComboBox
Tabelle 32 - Namenskonventionen für Steuerelemente in der Werkzeugsammlung
Alternativer
Präfix
mpg
scr
spn
tab
txt
ref
fra
lbl
img
chk
cmd
lst
opt
tgl
cbo
Präfix
mup
vsb / hsb
spb
tbs
frm
lab
btn
cbx
Standard-
Standard-
Bezeichnung
MultiPage
ereignis
Change
eigenschaft
Value
ScrollBar
SpinButton
TabStrip
TextBox
RefEdit
Frame
Change
Change
Change
Change
Change
Click
Value
Value
Value
Value
Value
Caption
Label
Image
CheckBox
CommandButton
ListBox
OptionButton
ToggleButton
ComboBox
Click
Click
Click
Click
Click
Click
Click
Click
Caption
Picture
Value
Value
Value
Value
Value
Value (Text)
Tabelle 33 - Standardereignis und Standardeigenschaft von Steuerelementen
13.6 Steuerelemente platzieren
Um ein Steuerelement auf der UserForm abzulegen, markieren Sie es in der
Werkzeugsammlung und ziehen Sie im Formular bei gedrückter Maustaste ein Rechteck auf,
das so groß ist, wie das Steuerelement angezeigt werden soll.
67
EDV I
Wollen Sie nachträglich die Größe eines Steuerelements ändern, ziehen Sie das markierte
Element mittels des Markierungsrahmens auf die gewünschte Größe. Um die Position eines
Elements zu verändern, klicken Sie in das Element und ziehen Sie es bei gedrückter Maustaste
an die neue Position.
Alternativ können Sie Größe und Position eines Steuerelements exakt im Eigenschaften- fenster
des jeweiligen Steuerelements einstellen.
Das Ausrichten von Steuerelementen untereinander wird durch das Kontextmenü realisiert.
Hierzu müssen Sie die anzugleichenden Steuerelemente mit gedrückter Strg-Taste auswählen
und anschließend das Kontextmenü öffnen.
13.7 Eigenschaften von Steuerelementen festlegen
Für Steuerelemente lassen sich sehr viele Eigenschaften einstellen. Markieren Sie das
betreffende Steuerelement Verändern Sie die Eigenschaften nach ihren Vorstellungen. Die
Änderungen werden sofort sichtbar.
Im Folgenden werden einige Eigenschaften beschrieben, die für alle Steuerelemente gelten:
Eigenschaft
BackColor
ControltipText
Enabled
Height
Left
Name
TabIndex
Tag
Top
Visible
Width
Bedeutung
bestimmt die Farbe des Hintergrunds eines Steuerelements
definiert den optionalen Hilfetext zu einem Steuerelement
legt fest, ob das Steuerelement ausgewählt werden kann oder nicht
gibt die Höhe eines Steuerelements an
legt die Position des linken Rands eines Steuerelements fest
legt den Namen fest, der im Quellcode verwendet wird, um auf das
Steuerelement zuzugreifen
definiert die Reihenfolge der Auswahl mittels der Tab-Taste.
ist vorgesehen für optionale Zusatzinformationen
legt die Position des oberen Rands eines Steuerelements fest
legt fest, ob das Steuerelement sichtbar oder unsichtbar ist
definiert die Breite eines Steuerelements
Tabelle 34 - Allgemeingültige Eigenschaften von Steuerelementen
13.7.1 Dem Kombinationsfeld Listenfeldeinträge zuordnen
Beim Klick auf den Pfeil des Kombinationsfeldes sollen mögliche Optionen erscheinen. Die
Auswahlmöglichkeiten werden über die Eigenschaft RowSource des Kombinationsfeldes
zugeordnet. Dort wird angegeben, in welchem Tabellenblatt und in welchen Zellenbereich die
entsprechenden Einträge zu finden sind.
68
EDV I
13.8 Ereignisprozeduren programmieren
Erst die Programmierung von Ereignisprozeduren erzeugt die gewünschte Funktionalität einer
UserForm.
Ereignisprozeduren werden für ein bestimmtes Steuerelement und ein
dazugehöriges Ereignis benötigt.
Abbildung 41 - Ereignisbehandlung festlegen
Markieren Sie das betreffende Steuerelement
Öffnen Sie das Code-Fenster
Wählen Sie im linken Listenfeld einen Eintrag aus.
Wählen Sie anschließend im rechten Listenfeld das Ereignis aus, auf das reagiert werden soll.
Sofort nach der Wahl eines Ereignisses wird automatisch ein leererer Prozedurrumpf erzeugt,
in dem Sie die Codezeilen einfügen, die beim Eintritt des Ereignisses ausgeführt werden
sollen.
Der Name der Ereignisprozedur (hier: CommandButton1_Click()) setzt sich zusammen aus
dem Namen des Steuerelements (CommandButton1) und dem Namen des Ereignisses (Click),
verbunden durch einen Unterstrich.
13.9 Wichtige Ereignisse eines Formulars
Die wichtigsten Ereignisse eines Formulars sind Initialize und Terminate.
Initialize
tritt ein, wenn das Formular geladen wird, also vor dem ersten Anzeigen.
Terminate
wird ausgelöst, wenn das Formular geschlossen wird. Dabei kann das Formular
sowohl über das Kreuz in der Titelleiste als auch mit der Anweisung Unload geschlossen
werden.
69
EDV I
13.10 Übungen zu Formularen
Dateivorlage: (Übung_SchraubeFormular.xlsm)
a) Fügen Sie die fehlenden Steuerelemente Tb_Lk, Cb_Sa und Cb_Fk hinzu.
b) Entfernen Sie die Controlsource bei TB_FB und ersetzen Sie sie durch
entsprechenden Code.
c) Vervollständigen Sie die Programmierung bis zur Funktionsfähigkeit
d) Fügen Sie eine Prozedur ein, die beim Aufrufen des Dialogs die Steuerelemente
Cb_Av , Cb_Sa
und Cb_Fk mit Werten versorgt.
Dateivorlage: (Übung_Taschenrechner.xlsm)
e) Erstellen Sie ein Formular basierten Taschenrechner für alle Grundrechenarten
sowie zur Berechnung der Wurzel.(+, −,×,÷, √ )
14 Grundlagen der Fehlerbehandlung
In der Regel müssen Fehler vom Programmierer abgefangen werden, so dass ungültige
Eingaben gar nicht erst vorkommen können. Als letzte Sicherung kann man zusätzlich eine
Fehlerroutine einbauen. Diese ist im Prinzip immer folgendermaßen aufgebaut:
Sub Fehler()
On Error GoTo Sprungmarke
Anweisungen der Prozedur ausführen
Exit Sub
Sprungmarke:
MsgBox (Err.Description)
End Sub
Mit dem Befehl On Error GoTo Sprungmarke wird die eigene Fehlerbehandlung eingeleitet.
Tritt ein Laufzeitfehler auf, verzweigt das Programm zur Sprungmarke und arbeitet die dort
aufgeführten Befehle ab. Die drei Befehle Resume, Resume Sprungmarke und Resume Next
werden eingesetzt, um nach einem Laufzeitfehler den Programmfluss weiter abzuarbeiten. Die
Folgende Abbildung veranschaulicht die Folgen der Fehlerbehandlung mit den ResumeBefehlen:
70
EDV I
Abbildung 42 - Fehlerbehandlung mit Resume-Befehlen
71
EDV I
15 Nassi-Shneiderman-Diagramm
Das Nassi-Shneiderman-Diagramm –Struktogramm- wurde in den 70er Jahren für die
Methodische Entwicklung von Mikroprozessorsoftware eingeführt. Diese Art des Pseudocodes
ermöglichte eine einheitliche Dokumentierung und nachträgliches ändern von Programmen.
Die Diagramme sind grafische Blockdiagramme.
Abbildung 43 – Blockdiagramme
Gegenüber dem Flussdiagramm wird beim Nassi-Shneiderman Diagramm die strukturierte
Programmierung erzwungen.
Ein Nassi-Shneiderman Diagramm wird von oben nach
unten gelesen.
Die Anwendung strukted32.exe
erleichtert die
Erstellung dieser Diagramme.
15.1 Übung zum Nassi-Shneiderman Diagramm
a) Sie sollen um 14 Uhr an einer
Besprechung teilnehmen. Ist ihr
Stellvertreter nicht bis 13.55 Uhr bei
Ihnen, sagen Sie die Besprechung
ab. Ansonsten nehmen Sie teil.
Abbildung 44 - Beispiel Nassi-Shneiderman
72
EDV I
16 Tastenkombinationen in Excel
Taste
Beschreibung
STRG+BILD-AB
Wechselt zwischen Arbeitsblattregistern, von links nach rechts.
STRG+BILD-AUF
Wechselt zwischen Arbeitsblattregistern, von rechts nach links.
STRG+UMSCHALT+&
Fügt den markierten Zellen einen Außenrahmen hinzu.
STRG+UMSCHALT+_
Entfernt den Außenrahmen um die markierten Zellen.
STRG+UMSCHALT+~
Weist das Standardzahlenformat zu.
STRG+UMSCHALT+$
Weist das Währungsformat mit zwei Dezimalstellen zu (negative
Zahlen in Klammern).
STRG+UMSCHALT+%
Weist das Prozentformat ohne Dezimalstellen zu.
STRG+UMSCHALT+^
Weist das wissenschaftliche Zahlenformat (Exponentialzahl) mit
zwei Dezimalstellen zu.
STRG+UMSCHALT+#
Weist das Datumsformat mit Tag, Monat und Jahr zu.
STRG+UMSCHALT+@
Weist das Zeitformat mit Stunde und Minute mit dem Zusatz AM
oder PM zu.
STRG+UMSCHALT+!
Weist das Zahlenformat mit zwei Dezimalstellen,
Tausendertrennzeichen und Minuszeichen (-) für negative Werte
zu.
STRG+UMSCHALT+*
Markiert den aktuellen Bereich um die aktive Zelle (Datenbereich,
der von leeren Zeilen und leeren Spalten umgeben ist).
STRG+UMSCHALT+:
Gibt die aktuelle Uhrzeit ein.
STRG+UMSCHALT+"
Kopiert den Wert aus der Zelle über der aktiven Zelle in die Zelle
oder in die Bearbeitungsleiste.
STRG+UMSCHALT+'+'
Zeigt das Dialogfeld Zellen einfügen an, um leere Zellen
einzufügen.
STRG+UMSCHALT+F
Öffnet das Dialogfeld Zellen formatieren, wobei die
Registerkarte Schriftart ausgewählt ist.
STRG+Umschalt+O
Markiert alle Zellen, die Kommentare enthalten.
STRG+Minus (-)
Zeigt das Dialogfeld Löschen an, um die markierten Zellen zu
löschen.
STRG+.
Gibt das aktuelle Datum ein.
STRG+`
Wechselt zwischen der Anzeige von Zellwerten und der Anzeige
von Formeln im Arbeitsblatt.
STRG+'
Kopiert eine Formel aus der Zelle über der aktiven Zelle in die
Zelle oder in die Bearbeitungsleiste.
STRG+1
Zeigt das Dialogfeld Zellen formatieren an.
STRG+2
Formatiert fett oder hebt die Formatierung auf.
73
EDV I
STRG+3
Formatiert kursiv oder hebt die Formatierung auf.
STRG+4
Unterstreicht oder hebt die Unterstreichung auf.
STRG+5
Streicht durch oder hebt die Formatierung auf.
STRG+6
Wechselt zwischen Ein- und Ausblenden von Objekten.
STRG+8
Blendet die Gliederungssymbole ein oder aus.
STRG+9
Blendet die markierten Zeilen aus.
STRG+0
Blendet die markierten Spalten aus.
STRG+A
Markiert das gesamte Arbeitsblatt. Wenn das Arbeitsblatt Daten
enthält, wird mit STRG+A der aktuelle Bereich markiert. Wenn
STRG+A ein zweites Mal gedrückt wird, wird das gesamte
Arbeitsblatt markiert. Wenn sich die Einfügemarke in einer Formel
rechts STRG+UMSCHALT+A fügt die Argumentnamen und
Klammern ein, wenn sich die Einfügemarke in einer Formel rechts
neben einem Funktionsnamen befindet.neben einem
Funktionsnamen befindet, wird das
Dialogfeld Funktionsargumente angezeigt.
STRG+B
Formatiert fett oder hebt die Formatierung auf.
STRG+C
Kopiert die markierten Zellen.
STRG+D
Verwendet den Befehl Unten ausfüllen, um den Inhalt und das
Format der obersten Zelle eines markierten Bereichs in die
darunter liegenden Zellen zu kopieren.
STRG+E
Fügt weitere Werte zur aktiven Spalte unter Verwendung der
Daten hinzu, die diese Spalte umgeben.
STRG+F
Zeigt das Dialogfeld Suchen und Ersetzen an, wobei die
Registerkarte Suchenausgewählt ist.
STRG+G
Zeigt das Dialogfeld Gehe zu an.
STRG+H
Zeigt das Dialogfeld Suchen und Ersetzen an, wobei die
Registerkarte Ersetzenausgewählt ist.
STRG+I
Formatiert kursiv oder hebt die Formatierung auf.
STRG+K
Zeigt das Dialogfeld Link einfügen zum Einfügen eines neuen
Hyperlinks an. Wenn ein vorhandener Hyperlink markiert ist, wird
das Dialogfeld Hyperlink bearbeitenangezeigt.
STRG+L
Zeigt das Dialogfeld Tabelle erstellen an.
STRG+N
Erstellt eine neue, leere Arbeitsmappe.
STRG+O
Zeigt das Dialogfeld Öffnen an, um eine Datei zu öffnen oder zu
suchen.
STRG+P
Zeigt die Registerkarte Drucken in der Microsoft Office
Backstage-Ansicht an.
74
EDV I
STRG+Q
Zeigt die Schnellanalyse-Optionen für die Daten an, wenn Zellen
mit diesen Daten markiert sind.
STRG+R
Verwendet den Befehl Rechts ausfüllen, um den Inhalt und das
Format der Zelle ganz links in einem markierten Bereich in die
Zellen rechts daneben zu kopieren.
STRG+S
Speichert die aktive Datei unter dem aktuellen Dateinamen im
aktuellen Dateiformat am aktuellen Speicherort.
STRG+T
Zeigt das Dialogfeld Tabelle erstellen an.
STRG+U
Unterstreicht oder hebt die Unterstreichung auf.
STRG+V
Fügt den Inhalt der Zwischenablage an der Einfügemarke ein und
ersetzt eine mögliche Auswahl. Nur verfügbar, wenn zuvor ein
Objekt, Text oder Zelleninhalte ausgeschnitten oder kopiert
wurden.
STRG+W
Schließt das markierte Arbeitsmappenfenster.
STRG+X
Schneidet die markierten Zellen aus.
STRG+Y
Wiederholt den letzten Befehl oder die letzte Aktion, sofern
möglich.
STRG+Z
Verwendet den Befehl Rückgängig, um den letzten Befehl
rückgängig zu machen oder den zuletzt eingegebenen Eintrag zu
löschen.
75
EDV I
17 Literaturverzeichnis
Excel Workshop Tabellenwerk von Dr. Volker Thormählen
Excel 2013 - Grundlagen 1. Auflage |RRZN
Excel 2013 - Fortgeschrittene Techniken 1. Auflage |RRZN
Excel 2013 - Automatisierung und Programmierung 1. Auflage |RRZN
www.umsonsttraum.org/download/script_excel_2010a.pdf (20.11.2014)
76
EDV I
18 Tabellenverzeichnis
Tabelle 1 - Möglichkeiten zur Zellwahl ................................................................................... 11
Tabelle 2 - Möglichkeiten zur Eingabebestätigung in einer Zelle ........................................... 11
Tabelle 3 - Verschiebeoperationen ........................................................................................... 12
Tabelle 4 - Kopieroperationen.................................................................................................. 12
Tabelle 5 - Rangfolge von Operatoren ..................................................................................... 15
Tabelle 6 - Matrixfunktionen ................................................................................................... 18
Tabelle 7 - Werkzeugsammlung............................................................................................... 25
Tabelle 8 - Makroeigenschaften ............................................................................................... 27
Tabelle 9 - Relative/Absolute Makroaufzeichnung.................................................................. 27
Tabelle 10 - Datentypen ........................................................................................................... 36
Tabelle 11 - Präfix-Variablen ................................................................................................... 37
Tabelle 12 - Gültigkeitsbereiche und -dauer von Variablen .................................................... 38
Tabelle 13 - Integrierte Konstanten .......................................................................................... 39
Tabelle 14 - Beispiel für ein Datenfeld mit sieben Elementen ................................................ 40
Tabelle 15 - Select Case Schlüsselwerte .................................................................................. 44
Tabelle 16 - Kopf/Fußgesteuerte Schleifen .............................................................................. 45
Tabelle 17 - Syntax der Kopfgesteuerten Schleife ................................................................... 46
Tabelle 18 - Syntax der Fußgesteuerten Schleife ..................................................................... 46
Tabelle 19 - Argumente der Zählschleife ................................................................................. 47
Tabelle 20 - Syntax Ereignisprozeduren .................................................................................. 52
Tabelle 21 - Arbeitsschritte zur Erstellung einer leeren Workbook_Open Ereignisprozedur . 53
Tabelle 22 - Ausgewählte Ereignisse für Tabellenblätter ....................................................... 53
Tabelle 23 - Ausgewählte Ereignisse für Arbeitsmappen ........................................................ 54
Tabelle 24 - Wichtige Eigenschaften und Methoden des Range-Objekts ................................ 54
Tabelle 25 - Eigenschaften von Spalten ................................................................................... 57
Tabelle 26 - Zugriffsmöglichkeiten auf Arbeitsmappen .......................................................... 62
Tabelle 27 - Ausgewählte Eigenschaften und Methoden des Workbook-Objekts................... 62
Tabelle 28 - Erstellen einer Userform ...................................................................................... 63
Tabelle 29 - Wichtige Eigenschaften eines Benutzerformulars ............................................... 65
Tabelle 30 - Wichtige Ereignisse eines Benutzerformulars ..................................................... 66
Tabelle 31 - Namenskonventionen für Steuerelemente in der Werkzeugsammlung ............... 67
Tabelle 32 - Standardereignis und Standardeigenschaft von Steuerelementen ........................ 67
77
EDV I
Tabelle 33 - Allgemeingültige Eigenschaften von Steuerelementen ....................................... 68
78
EDV I
19 Abbildungsverzeichnis
Abbildung 1 - Windows Taste ................................................................................................... 5
Abbildung 2 - Excel Startoberfläche .......................................................................................... 5
Abbildung 3 - Excel Hilfe .......................................................................................................... 6
Abbildung 4 - Startansicht.......................................................................................................... 6
Abbildung 5 - Menüband - Ribbon ............................................................................................ 7
Abbildung 6 - Registerkarte Entwicklertools einblenden .......................................................... 8
Abbildung 7 - Kontextsensitive Register ................................................................................... 9
Abbildung 8 - Neues Blatt ........................................................................................................ 10
Abbildung 9 - Fenster fixieren ................................................. Error! Bookmark not defined.
Abbildung 10 - Ausfüllkästchen .............................................................................................. 13
Abbildung 11 - Bedingte Formatierung ................................................................................... 14
Abbildung 12 - Syntax einer Formel ........................................................................................ 15
Abbildung 13 - Funktionsassistent ........................................................................................... 17
Abbildung 14 - Zielwertsuche .................................................................................................. 18
Abbildung 15 - Wachstumsrate Zielwertsuche ........................................................................ 18
Abbildung 16 - Add-Ins Auswahlfenster ................................................................................. 19
Abbildung 17 - LGS - Lösung mit Solver ................................................................................ 21
Abbildung 18 - Solver-Parameter ............................................................................................ 21
Abbildung 19 - Diagramm formatieren .................................................................................... 22
Abbildung 20 - Menüband anpassen - Entwicklertools ........................................................... 23
Abbildung 21 - Gruppe Steuerelemente – Formularsteuerelemente ........................................ 24
Abbildung 22 - Formularsteuerelemente .................................................................................. 24
Abbildung 23 - Makroeigenschaften definieren ....................................................................... 26
Abbildung 24 - Makros Schaltfläche ....................................................................................... 26
Abbildung 25 - Aufgezeichnetes Makro auswählen ................................................................ 28
Abbildung 26 - VB Schaltfläche .............................................................................................. 29
Abbildung 27- Projektexplorer ................................................................................................. 29
Abbildung 28 - Eigenschaftsfenster ......................................................................................... 30
Abbildung 29 - Direktfenster ................................................................................................... 31
Abbildung 30 - Lokalfenster .................................................................................................... 31
Abbildung 31 - Überwachungsfenster ...................................................................................... 32
Abbildung 32 - Überwachung Hinzufügen .............................................................................. 32
79
EDV I
Abbildung 33 - Makro starten .................................................................................................. 33
Abbildung 34 - Hello World! Msgbox ..................................................................................... 34
Abbildung 35 - Hello World! Inputbox.................................................................................... 34
Abbildung 36 - Kontrollstrukturen in VBA ............................................................................. 41
Abbildung 37 - Kopf/Fußgesteuerte Schleife ........................................................................... 45
Abbildung 38 - Hintergrundfarben setzen ................................................................................ 59
Abbildung 39 - Standartspeicherort ändern.............................................................................. 61
Abbildung 40 - Werkzeugsammlung ....................................................................................... 64
Abbildung 41 - Benutzerformular (UserForm) einfügen ...................................................... 64
Abbildung 42 - Ereignisbehandlung festlegen ......................................................................... 69
Abbildung 43 - Fehlerbehandlung mit Resume-Befehlen ...................................................... 71
Abbildung 44 – Blockdiagramme ............................................................................................ 72
Abbildung 45 - Beispiel Nassi-Shneiderman ........................................................................... 72
80