VBA

.XUVXQWHUODJHQ
3&6XSSRUWHU6,=
/HKUJDQJ3&6XSSRUWHU6,=
9,68$/%$6,&)25$33/,&$7,2169%$
Visual Basic for Applications
(VBA)
Grundlagen
Copyright: Robert Bonomo
Ausgabe 7. Februar 2001
.XUVXQWHUODJHQ
3&6XSSRUWHU6,=
/HKUJDQJ3&6XSSRUWHU6,=
9,68$/%$6,&)25$33/,&$7,2169%$
Dokumentendaten
Dokument
Bezeichnung Visual Basic for Applications (VBA) / Grundlagen
Ablageort
http://surf.to/bonomo
1.4
Version
07.02.2001
letzte
Änderung
Druckdatum 7. Februar 2001
Änderungshistorie
Neues Dokument
02.04.2000
Checkfragen und Übung für Programmierung Grundlagen mit Lösungen eingebaut
27.05.2000
Fragen und Antworten der relevanten SIZ-Prüfungsfragen 1998/1999 eingebaut
04.06.2000
Neues Kapitel Dialoge
07.06.2000
Komplette Überarbeitung
01.10.2000
Kapitel VBA in Microsoft Excel
02.10.2000
Kapitel VBA in Microsoft Winword
04.10.2000
Übung Programmablaufplan mit Lösung eingebaut
10.10.2000
Kapitel Kommunikation zwischen Office Anwendungen über ActiveX Objekte
11.10.2000
Überarbreitung aufgrund der praktischen Erfahrung aus dem OS-Lehrgang 11/2001 IFA
07.02.2001
Copyright »
2000 by Robert Bonomo, Lerchenberg 9, 8046 Zürich
http://surf.to/bonomo
i
.XUVXQWHUODJHQ
3&6XSSRUWHU6,=
/HKUJDQJ3&6XSSRUWHU6,=
9,68$/%$6,&)25$33/,&$7,2169%$
Inhaltsverzeichnis
'2.80(17(1'$7(1
,
(,1)h+581*,1',(352*5$00,(581*
1.1 Elektronische Datenverarbeitung
1
1.2 Programmiersprachen
1
1.3 Programmelemente
1.3.1 Programmstruktur
1.3.1 Datentypen
3
3
3
1.4 Kontrollkonstrukte
1.4.1 Die Folge
1.4.2 Die Auswahl
1.4.3 Die Wiederholung (Iteration)
6
6
6
7
1.5 Operatoren
1.5.1 arithmetische Operatoren
1.5.2 Vergleichsoperatoren
1.5.2 Logische Operatoren
1.5.3 Bitoperatoren
8
8
8
8
8
9,68$/%$6,&)25$33/,&$7,216
2.1 Einführung
9
2.2 Der Makro-Rekorder
9
2.3 Objekthierarchie
12
2.4 Fehlerbehandlung
12
2.5 Dialoge
2.5.1 Vordefinierte Dialogboxen (VBA)
2.5.2 benutzerdefinierte Systemdialoge (User-Forms)
2.5.3 integrierte Systemdialoge
14
14
14
15
2.6 VBA in Microsoft Excel
2.6.1 Das Microsoft Excel Objektmodell
2.6.2 Absolute und relative Zellreferenzen
2.6.3 Die Active Properties
2.6.4 Zellen und Ranges
2.6.4 Microsoft Excel Objektlandkarte
17
17
19
21
21
22
2.7 VBA in Microsoft Winword
2.7.1 Das Microsoft Winword Objektmodell
2.7.2 Microsoft Winword Objektlandkarte
23
23
26
2.8 Interaktion zwischen Microsoft Office Anwendungen
2.8.1 Erstellung, Referenzierung und Dereferenzierung von ActiveX Objekten
2.8.2 Referenzierung von bestehenden ActiveX Objekten
2.8.3 Kommunikation zwischen Office Anwendungen über ActiveX Objekte
27
27
28
29
Copyright »
2000 by Robert Bonomo, Lerchenberg 9, 8046 Zürich
http://surf.to/bonomo
ii
.XUVXQWHUODJHQ
3&6XSSRUWHU6,=
/HKUJDQJ3&6XSSRUWHU6,=
9,68$/%$6,&)25$33/,&$7,2169%$
h%81*(1
3.1 Einführung in die Programmierung
30
3.2 Datentypen
31
3.3 Programmablaufpläne
32
3.4 VBA
33
3.5 Programm-Analyse
33
3.6 ActiveX Objekte
36
/g681*(1
4.1 Einführung in die Programmierung
37
4.2 Datentypen
38
4.3 Programmablaufpläne
39
4.4 VBA
40
4.5 Programm Analyse
41
4.6 ActiveX Objekte
43
67,&+:2579(5=(,&+1,6
Copyright »
2000 by Robert Bonomo, Lerchenberg 9, 8046 Zürich
http://surf.to/bonomo
iii
.XUVXQWHUODJHQ
3&6XSSRUWHU6,=
Copyright »
/HKUJDQJ3&6XSSRUWHU6,=
9,68$/%$6,&)25$33/,&$7,2169%$
2000 by Robert Bonomo, Lerchenberg 9, 8046 Zürich
http://surf.to/bonomo
iv
.XUVXQWHUODJHQ
3&6XSSRUWHU6,=
/HKUJDQJ3&6XSSRUWHU6,=
9,68$/%$6,&)25$33/,&$7,2169%$
1.0 Einführung in die Programmierung
1.1 Elektronische Datenverarbeitung
Mit Hilfe der elektronischen Datenverarbeitung (EDV) können komplexe Aufgabenstellungen
unterstützend automatisiert werden. Die elektronische Datenverarbeitung funktioniert nach dem
EVA-Prinzip (Eingabe - Ausgabe - Verarbeitung). Ein mögliche Komponenten-Übersicht sehen Sie
in der nachfolgenden Übersicht:
Eingabe
Tastatur
Mikrofon
Mikrofon
Scanner
Modem
Datenträger
Verarbeitung
Arbeitsspeicher
Prozessor (CPU)
Datenträger
Ein-Ausgabesteuerung
Ausgabe
Bildschirm
Drucker
Datenträger
Lautsprecher
Modem
Die Art der Verarbeitung von Daten wird über das Betriebssystem und zusätzliche Programme
vordefiniert.
1.2 Programmiersprachen
Ein Programm ist eine Folge von Anweisungen, die in einer für das Datenverarbeitungsgerät
verständlichen Sprache (Programmiersprache) formuliert sind. Grundsätzlich versteht ein
Datenverarbeitungsgerät nur die im Prozessor vordefinierten Funktionen (Maschinensprache). Da
die Maschinensprachen sehr abstrakt sind (Binär-Codierung), nur mit grossen Fachwissen erstellt
und analysiert werden können und von System zu System unterschiedlich sind, hat man sogenannte
Übersetzungsprogramme erstellt. Die Übersetzungsprogramme erlauben es dem Menschen, in einer
weniger abstrakten Sprache eine Verarbeitungslogik zu schreiben, die dann durch diese Programme
in Maschinencode übersetzt werden. Man unterscheidet bei den Übersetzungsprogrammen
zwischen zwei grundsätzlich Kategorien, Compiler und Interpreter.
Copyright »
2000 by Robert Bonomo, Lerchenberg 9, 8046 Zürich
http://surf.to/bonomo
1
.XUVXQWHUODJHQ
3&6XSSRUWHU6,=
/HKUJDQJ3&6XSSRUWHU6,=
9,68$/%$6,&)25$33/,&$7,2169%$
In der nachfolgenden Tabelle werden Compiler und Interpreter kurz charaktierisiert.
Typ
Compiler
Interpreter
Funktionsweise
komplette Übersetzung von PseudoCode in Maschinensprache, danach
Ausführung
Der Code wird Befehl nach Befehl in
Maschinencode
übersetzt
und
ausgeführt
ú
ú
ú
ú
Vorteile
schnelle Ausführung
Code-Optimierung
(Syntax, Semantik)
Portierbarkeit
einfache Testbarkeit
ú
ú
ú
ú
Nachteile
Kompilationszeit
Test erst nach Compile
möglich
langsame Ausführung
Fehler im Programm
werden erst bei der
Ausführung der
entsprechenden
Codezeile bemerkt
Unter einer Programmiersprache versteht man nach DIN 44300 eine zum Erstellen von
Computerprogrammen geschaffene Sprache. Jede Programmiersprache kann mittels Syntax und
Semantik-Definitionen klar beschrieben werden.
Syntax
Definition der der verwendeten Zeichen (z.B. Alphabet), der Worte (z.B. Msgbox) und der
Rechtschreibung (Formale Richtigkeit der Sprache)
Definition der Beziehung zwischen den einzelnen Sprachwörtern und deren Bedeutung
(Programmlogik, grammatikalische Regeln). Nach einem If muss z.B. immer ein End if
kommen.
Semantik
Man unterscheidet 5 Generationen von Programmiersprachen, wie Sie nachfolgender Übersicht
entnehmen können.
Programmiersprache
1. Generation
Maschinensprachen
2. Generation
maschinen-orientiert
3. Generation
problem-orientierte
4. Generation
daten-orientierte
5. Generation
wissens-orientierte
Copyright »
Beschreibung
Die Maschinensprache ist die interne Sprache eines
Datenverarbeitungs-Systems. Die Befehle sind meist binär,
oktal oder hexadezimal formuliert.
Die maschinen-orientierten Sprachen werden auch
Assembler genannt. Sie sind ebenfalls auf ein
Datenverarbeitungssystem zugeschnitten, bieten aber
zusätzliche
Verbesserungen
für
Programmierer
(ausgeschriebene Befehle, symbolische Speicheradressen,
Makrobefehle)
Diese Programm-Generation umfasst die sogenannt
prozeduralen Programmiersprachen, da die Lösung eines
Problems als Abfolge von abstrakten Anweisungen
dargestellt werden kann
Diese Programm-Generation umfasst die sogenannt nichtprozeduralen Programmiersprachen, da die Lösung eines
Problems auch von unerfahrenen Mitarbeitern direkt
formuliert werden kann. (beschreibende Sprachen)
Als wissens-orientierte Sprachen bezeichent man die für die
Entwicklung
von
Experten-Systemen
geeigneten
Programmiersprachen (künstliche Intelligenz, heuristische
Problemlösung)
2000 by Robert Bonomo, Lerchenberg 9, 8046 Zürich
http://surf.to/bonomo
Beispiele
Z80, 80486,6800
Makroassembler
Basic, ADA, Pascal,
Fortran, ALGOL, C,
PL/1, COBOL,APL,
SQL, ADABAS
LISP,PROLOG,
Wizard, Agent
2
.XUVXQWHUODJHQ
3&6XSSRUWHU6,=
/HKUJDQJ3&6XSSRUWHU6,=
9,68$/%$6,&)25$33/,&$7,2169%$
1.3 Programmelemente
Jedes Programm besitzt eine Struktur besteht aus verschiedenen Programmteilen und Elementen. In
diesem Kapitel soll auf eine Auswahl dieser Teile und Elemente eingegangen werden.
1.3.1 Programmstruktur
Ein Programm, das in einer höheren bzw. einer objektorientierten Programmiersprache verfasst
wurde, besitzt typischerweise die folgenden Strukturen:
Name
Module
Deklarationsteil
Funktionen
Prozeduren
Beschreibung
In Visual Basic für Applications können mehrere Module zu
einem Programm zusammengefügt werden. Jedes Modul
verfügt über einen eigenen Deklarations-, Funktions- und
Prozeduren-Teil.
Im Deklarationsteil werden Variablen und Konstanten
initialisiert und einem entsprechenden Datentyp und ggf.
einem Initialwert zugeteilt..
Funktionen sind wichtige Bestandteile von modular
aufgebauten Programmen. Eine Funktion beinhaltet einen
für sich abgeschlossenen Programmteil, der aus einer
Prozedur oder Funktion mit Parameter aufgerufen werden
kann und einen Rückgabewert liefert
Eine
Prozedur
ist
ebenfalls
eine
eigenständige
Progeammeinheit mit Anweisungsfolgen, die aus anderen
Funktionen oder Prozeduren aufgerufen werden kann. Sie
liefert im Gegensatz zur Funktion jedoch keinen
Rückgabewert
Beispiel
File global.bas
Dim Lohn as long
Dim Ferien as string
public function myF
msgbox "hallo"
myF = 11
End function
sub mySub
msgbox "hallo"
a=2
End sub
1.3.1 Datentypen
Variable und Konstante Datentypen können zur Laufzeit des Programmes gesteuert über den
Variablen- oder Konstanten-Namen bestimmte Werte aufnehmen. Der Datentyp einer Variablen
legt dabei den Wertebreich und die Grösse des reservierten Speicherplatzes im Hauptspeicher fest.
In Visual Basic und Visual Basic für Applications ist es nicht zwingend notwendig, einer
eingesetzten Variablen und Konstanten vorgängig einen Datentyp zuzuweisen. Es ist aber sehr
sinnvoll, da sonst wertvoller Speicherpplatz verloren gehen kann oder bei der Ausführung des
Programmes plötzlich unverträgliche Datentypen aufeinander prallen. Mit der Anweisung Option
Explicit kann im (General)(Declarations) Teil des Visual Basic Editors die Deklaration von
Variablen als obligatorisch definiert werden, bei der Ausführung des Programmes meldet der
Interpreter oder Compiler dann vergessene Deklarationen von Datentypen (Compile Error, Variable
not defined)
Copyright »
2000 by Robert Bonomo, Lerchenberg 9, 8046 Zürich
http://surf.to/bonomo
3
.XUVXQWHUODJHQ
3&6XSSRUWHU6,=
/HKUJDQJ3&6XSSRUWHU6,=
9,68$/%$6,&)25$33/,&$7,2169%$
Zur Darstellung der ganzzahligen Datentypen verwendet man in Visual Basic for Applications die
Datentypen Integer und Long.
Zur Darstellung der gleitkomma Datentypen verwendet man die Datentypen single, double und
Currency. Eine Gleitkommazahl setzt sich aus dem Vorzeichen (+,-), dem Exponenten (legt die
Position des Kommas fest) und der Mantisse (Alle Zahlen ohne Komma) zusammen
Wenn Variablen keine Zahlen sondern Zeichenketten aufnehmen sollen, so wird im Visual Basic
für Aplications der Datentyp string verwendet. Ex existieren zusätzlich die speziellen Datentypen
date (nimmt Datum und Zeitangaben auf), Variant (kann sowohl Zahlen vom Typ long als auch
Strings aufnehmen) und Boolean (Falsch oder Wahr)
Copyright »
2000 by Robert Bonomo, Lerchenberg 9, 8046 Zürich
http://surf.to/bonomo
4
.XUVXQWHUODJHQ
3&6XSSRUWHU6,=
/HKUJDQJ3&6XSSRUWHU6,=
9,68$/%$6,&)25$33/,&$7,2169%$
Wird für eine Variable kein Datentyp deklariert oder die Deklaration ganz weggelassen, so wird per
Default (implizit) der Datentyp Variant zugeweisen. Die explizite Deklaration des Datentyps und
die Wertzuweisung erfolgt für Variablen im Visual Basic for Applications mittels folgendem Code:
Was
Deklaration
Wertzuweisung
Deklaration mit
Gültigkeitsbereich
Deklaration und
Wertzuweisung für
Konstanten
Code
Dim Variablenname as Datentyp
Dim Variablenname[Datentypzeichen]
Variablenname=100
Variablenname[Datentypzeichen]="Dies ist ein Text"
[Gültigkeitsbereich]
Variablenname
as
Datentyp
[Gültigkeitsbereich] Variablenname[Datentypzeichen] as Datentyp
Const Konstantenname as Datentyp = Wert
Const Konstantenname[Datentypzeichen] as Datentyp = Wert
Um zu bestimmen, an welcher Stelle des Programms auf eine Variable zugegriffen werden kann,
kann bei der Variablendeklaration optional bzw. anstelle des DIM Statements ein sogenannter
Gültigkeitsbereich angegeben werden. Mögliche Gültigkeitsbereich sind
Name
public
global
private
Dim
static
Beschreibung
Mit public deklarierte Variablen stehen jeder Funktion und Prozedur
innerhalb eines Moduls während der gesamten Laufzeit des Programmes
zur Verfügung. Die Deklaration muss im (General)(Declarations)-Teil eines
Moduls oder einer Form durchgeführt werden.
Mit global deklarierte Variablen stehen jeder Funktion und Prozedur in
allen Modulen während der gesamten Laufzeit des Programmes zur
Verfügung. Die Deklaration muss im (General)(Declarations)-Teil des
Moduls durchgeführt werden
Mit private dekklarierte Variablen stehen nur innerhalb des
Deklarationsrahmens zur Verfügung (Funktion, Prozedur, Modul). Private
ist die Default Deklaration
Mit Dim können Variablen innerhalb Funktionen, Prozeduren oder
Modulen (Deklarationsteil) deklariert werden. Die Dim Anweisung
innerhalb einer Prozedur oder Funktion initialisiert bei jedem Prozedur
oder Funktionsaufruf die entsprechende Variable von neuem und definiert
gleichzeitig den Gültigkeitsbereich (eigene Prozedur, eigenes Funktio,
eigenes Modul). Dim entspricht dem Private Statement
Mit static deklarierte Variablen sind lokale Variablen und bleiben innerhalb
einer Funktion oder Prozedur während der ganzen Laufzeit der
Anwendung bestehen. Ausserhalb der Prozedur oder Funktion sind sie
jedoch nichst ansprechbar. Die Deklaration wird innerhalb einer Funktion
oder Prozedur durchgeführt.
Beispiel
public Lohn as
integer
global Lohn as
integer
private Lohn as
integer
Dim lohn as integer
statgic lohn as
integer
Wird in einem Programm eine grosse Anzahl von Daten des gleichen Datentyps benötigt, so bietet
sich die Verwendung von Datenfeldern (Arrays) an. Alle Elemente innerhalb eines Arrays haben
den gleich Namen und werden über einen Index beginnend mit 0 angesprochen. Die Indexnummer
wird dem Datentyp in einer runden Klammer angehängt. Die Deklaration und Wertzuweisung
geschieht mittels folgendem Code:
Dim Konstantenname(0 to 100) as Datentyp
Konstantenname(0)=85
…..
Konstantenname(100)=1024
Copyright »
2000 by Robert Bonomo, Lerchenberg 9, 8046 Zürich
http://surf.to/bonomo
5
.XUVXQWHUODJHQ
3&6XSSRUWHU6,=
/HKUJDQJ3&6XSSRUWHU6,=
9,68$/%$6,&)25$33/,&$7,2169%$
1.4 Kontrollkonstrukte
Unter Kontrollkonstrukten versteht man eine Folge von Anweisungen, die in einer definierten
Reihenfolge bzw. aufgrund von Bedingungen ein Null oder mehrere Male durchlaufen werden. Für
die
Visualisierung
von
Kontrollstrukturen
gibt
es
verschiedene
normisierte
Darstellungsmöglichkeiten. Es sind dies das Flussdiagramm nach DIN 66001-PA, das
Nassi/Shneidermann Diagramm nach DIN 66261 und das Jackson Struktogramm. Ein sauber
ausgedachtes Programm wird immer zuerst grafisch dargestellt, bevor man mit der Codierung
beginnt (strukturierte Programmierung).
1.4.1 Die Folge
Die Folge ist das einfachste Kontrollstrukt der Programmierung. Man bezeichent als Folge das
sequentielle abarbeiten von Anweisungen.
1.4.2 Die Auswahl
Die Auswahl ist ein Kontrollkonstrukt, dass es erlaubt, abhängig von einer Bedingung,
unterschiedliche Anweisungen auszuführen. Man unterscheidet dabei zwischen einfachen
Alternativen (Prüfung einer Bedingung auf Wahr oder Falsch), mehrfach Alternativen oder auch
Selektion (Wertprüfung einer Variablen) und bedingter Verarbeitung (bedingte Ausführung einer
Anweisung).
Copyright »
2000 by Robert Bonomo, Lerchenberg 9, 8046 Zürich
http://surf.to/bonomo
6
.XUVXQWHUODJHQ
3&6XSSRUWHU6,=
/HKUJDQJ3&6XSSRUWHU6,=
9,68$/%$6,&)25$33/,&$7,2169%$
Einfache und mehrfache Alternativen werden in VBA mit den Keywörtern If und Select Case unter
Berücksichtigung der folgenden Syntax programmiert:
If condition then
[statements]
[ElseIf condition-n then]
[statements]
[Else]
[statements]
End If
Select Case testexpression
[Case expressionlist-n]
[statements]
[Case Else]
[statements]
End Select
1.4.3 Die Wiederholung (Iteration)
Wiederholungen, die auch Iterationen oder Schleifen genannt werden, können in der
Programmierpraxis eingesetzt werden, um gewisse Anweisungen abhängig von einer Bedingung
Null, ein oder mehrere Male durchlaufen zu lassen. Man unterscheidet grundsätzlich zwischen
kopfgesteuerten Iterationen, wo die Bedingung vor der ersten Ausführung der Anweisung
durchlaufen wird und fussgesteuerten Iterationen, wo die Anweisung zuerst durchlaufen wird und
die Bedingung erst nach dem ersten Ausführen der Anweisung durchlaufen wird.
Schleifen werden in VBA mit den Keywörtern For und While unter Berücksichtigung der folgenden
Syntax programmiert:
For counter = start to End [Step Step]
[statements]
[Exit For]
[statements]
Next [Counter]
While condition
[statements]
Wend
Copyright »
2000 by Robert Bonomo, Lerchenberg 9, 8046 Zürich
http://surf.to/bonomo
7
.XUVXQWHUODJHQ
3&6XSSRUWHU6,=
/HKUJDQJ3&6XSSRUWHU6,=
9,68$/%$6,&)25$33/,&$7,2169%$
1.5 Operatoren
Ein Operator bildet zusammen mit seinen Operanden einen vom Compiler auswertbaren Ausdruck,
der eine numerische Antwort zurückliefert. Besitzt ein Operator nur einen Operanden, so bezeichnet
man ihn als unär.
1.5.1 arithmetische Operatoren
Die arithemtischen Operatoren sind Plus (+, binär und unär), Minus (-, binär und unär),
Multiplikation (*), Division (/) und Divisionsrest oder Modulo (%).
1.5.2 Vergleichsoperatoren
Die Vergleichoperatoren werden auch relationale Operatoren genannt und sind immer binär, dass
heisst es sind immer 2 Operanden im Ausdruck. Man unterscheidet zwischen gleich (==), ungleich
(!=), grösser (>), grösser gleich (>=), kleiner (<) und kleiner gleich (<=).
1.5.2 Logische Operatoren
Von den logischen Operatoren sind 2 binär und einer unär (!). Wie bei den Vergleichoperatoren ist
das Resultat der Operation immer TRUE (1) oder FALSE(0). Die Domäne TRUE/FALSE
bezeichnet man auch als BOOLSCHE Werte. Die Operanden einer logischen Operation enthalten
immer BOOLSCHE Werte.
1.5.3 Bitoperatoren
Von den Bitoperatoren sind 3 binär und einer unär. Im Gegensatz zu den logischen Operatoren ist
das Resultat nicht TRUE oder FALSE sondern 1 oder 0. Ein bitweise UND Verknüpfung der
Buchstaben "C"(Auf Bitebene 01000011) und "F" (Auf Bitebene 01000110) ergibt als Antwort "
"B" (Auf Bitebene 01000010).
Neu ist die exklusive bitweise ODER Verknüpfung (auch XOR genannt). Sie liefert bei der
Operation mit 2 Operanden nur den Wert 1 zurück wenn nur einer der beiden Operatoren den Wert
0 enthält.§
Copyright »
2000 by Robert Bonomo, Lerchenberg 9, 8046 Zürich
http://surf.to/bonomo
8
.XUVXQWHUODJHQ
3&6XSSRUWHU6,=
/HKUJDQJ3&6XSSRUWHU6,=
9,68$/%$6,&)25$33/,&$7,2169%$
2.0 Visual Basic for Applications
2.1 Einführung
Visual Basic for Applications (VBA) wird in allen Microsoft Office Produkten als standardisierte
Programmier-Schnittstelle angeboten (ab Version Office 97). Mittels VBA können Daten unter den
Office-Produkten direkt ausgetauscht werden, dadurch lassen sich sehr einfach
programmübergreifende Funktionen realisieren.
2.2 Der Makro-Rekorder
In jedem Office-Produkt (exkl. MS-Access) ist ein sogenannter Makro-Rekorder implementiert, mit
dem sich Arbeitsabläufe und Befehlsfolgen aufzeichen und anschliessend automatisch ausführen
lassen. Jedes Makro ist eine Folge von Anweisungen in VBA, durch den Makro-Rekorder lässt sich
also automatisch VBA-Code generieren. Um benutzerspezifische Funktionen oder Prozeduren zu
erstellen, oder um aufgezeichnete Befehlsfolgen zu bearbeiten, können Sie auch direkt im VBAEditor Code programmieren.
Den Makro-Rekorder finden Sie in den
Microsoft Office Produkten unter Menüpunkt
Tools/Macro bzw. Extras/Makros. Wenn Sie
ein neues Makro aufzeichen wollen, so
wählen Sie Record New Macro, wenn Sie ein
bestehendes Makro bearbeiten wollen so
wählen Sie Macros. Mittels dem Menüpunkt
Visual Basic Editor gelangen Sie direkt auf
die Modulebene, wo Sie VBA-Code
programmieren können, bzw. den durch den
Makro-Rekorder automatisch generierten
Code analysieren können
Wenn Sie die gewünschte Befehlsfolge aufgezeichnet haben, so kann die Makro-Aufzeichnung mit
dem Menü-Befehl Tools/Macro/Stop Recording beendet werden.
Der MakroName kann frei gewählt werden, es ist jedoch darauf zu achten, dass gewisse
Makronamen automatisch eine vordefinierte Zusatzfunktion ausführen:
Makroname
Auto_Open
Auto_Close
Copyright »
Beschreibung
Dieses Makro wird automatisch beim Öffnen eines Workbooks ausgeführt
Dieses Makro wird automatisch beim Schliessen eines Workbooks ausgeführt
2000 by Robert Bonomo, Lerchenberg 9, 8046 Zürich
http://surf.to/bonomo
9
/HKUJDQJ3&6XSSRUWHU6,=
.XUVXQWHUODJHQ
3&6XSSRUWHU6,=
9,68$/%$6,&)25$33/,&$7,2169%$
Makros können an versciedenen Orten abgespeichert werden. Die nachfolgende Übersicht zeigt die
Unterschiede der Speichermöglichkeiten auf:
Speicherort
aktuelle Arbeitsmappe
(This Workbook)
neue Arbeitsmappe
(New Workbook)
persönliche Arbeitsmappe
(Personal Macro Workbook)
aktuelles Dokument
Beschreibung
Microsoft Excel
Das Makro wird in einem Modul der aktuellen Arbeitsmappe
abgespeichert
Es wird zuerst eine neue Arbeitsmappe geöffnet und das Makro in dieser
Mappe als Modul abgespeichert.
Das Makro wird in der persönlichen Arbeitsmappe abgespeichert. Es
handelt sich hierbei um eine separate Datei mit dem Namen personal.xls,
welche beim Excel-Start immer automatisch geöffnet wird. Die darin
enthaltenen Makros sind also bei jedem geöffneten Workbook
vorhanden. Die Arbeitsmappe personal.xls wird immer versteckt geöffnet
(hidden). Damit in ihr vorhandene Makros editiert werden könne, muss
man das Workbook immer zuerst sichtbar machen (Menü Window,
Unhide)
Microsoft Winword
Das Makro wird in einem Modul im aktuellen Dokument abgespeichert
(This Document)
Alle Dokumente (Normal.dot)
(All Documents)
Das Makro wird im File Normal.dot abgespeichert und steht somit allen
Dokumenten zur Verfügung
In der nachfolgenden Aufstellung werden die Einsatzgebiete und die Vor und Nachteile von Makros
und VBA-Modulen aufgelistet.
Typ
Makro
Einsatzgebiete
Vorteile
Automatisierung von einfachen einfache Aufzeichnung von
Aufgaben
Befehlsfolgen mittels MakroRekorder
keine Programmierkentnisse
notwendig
VBAModul
Erstellung eigener Funktionen
Ausführen von -Aktionen auf
Systemebene (Datenzugriff, DDE,
DLL's)
Anpassung
aufgezeichneter
Makros
Copyright »
Fehlerbehandlung
dynamische
Parameterübergabe
Nachteile
schwierige Fehlersuche (Ein
Fehler führt sofort zum
Abbruch des Makros)
keine Fehlerbehandlung
möglich
keine dynamiscxhe
Parameterübergabe möglich
kein externenr Datezugriff
möglich
Kenntniss der VBA
Programmiersprache nötig
Daten- und Systemzugriff
2000 by Robert Bonomo, Lerchenberg 9, 8046 Zürich
http://surf.to/bonomo
10
.XUVXQWHUODJHQ
3&6XSSRUWHU6,=
/HKUJDQJ3&6XSSRUWHU6,=
9,68$/%$6,&)25$33/,&$7,2169%$
Prozeduren werden in VBA mit dem Keywort Sub und mit folgender Syntax programmiert:
[Private | Public] [Static] Sub name [(arglist)]
[statements]
[Exit Sub]
[statements]
End Sub
Funktionen sind analogwerden in VBA mit dem Keywort Function und mit folgender Syntax
programmiert. Funktionen können einen Rückgabewert zurückliefern.
[Public | Private] [Static] Function name [(arglist)] [As type]
[statements]
[name = Expression]
[Exit Function]
[statements]
[name = Expression]
End Function
Copyright »
2000 by Robert Bonomo, Lerchenberg 9, 8046 Zürich
http://surf.to/bonomo
11
.XUVXQWHUODJHQ
3&6XSSRUWHU6,=
/HKUJDQJ3&6XSSRUWHU6,=
9,68$/%$6,&)25$33/,&$7,2169%$
2.3 Objekthierarchie
Für die effiziente Programmierung der Microsoft-Office Produkte mittels VBA muss man die
Objekthierarchie der einzelnen Applikationen kennen. Am Beispiel von Microsoft Excel wird in
diesem Kapitel die Objekthierarchie erklärt. Das oberste Objekt in der Hierarchie ist immer das
Application Objekt, es repräsentiert die gesamte Microsoft-Excel Anwendung. Jedes Objekt besitzt
also einen Objekt-Pfad ausgehend vom Application Objekt.
Über den Menüpunkt View/Object Browser bzw. Ansicht / Objektkatalog oder über die
Funktionstaste F2 kann man im Visual-Basic Editor auf den Objektkatalog zugreifen, welcher eine
Auflistung aller verfügbaren Objekte inklusive deren Methoden, Eigenschaften und
programmierbaren Ereignissen enthält.
Methoden
Vordefinierte Funktionen, die die
Objektdaten oder das Objekt
manipulieren
Application.ActivateMicrosoftApp
(xlMicrosoftPowerPoint)
Eigenschaften Bestimmung des Verhaltens und
des Aussehens eines Objekts
application.Path (Read-Only)
Vordefinierte Ereignisse, die mit
Ereignisse
Funktionalität (VBA Code)
hinterlegt werden können
Sub Workbook_BeforeClose(C as
Boolean)
If Me.Saved = False Then Me.Save
End Sub
Copyright »
2000 by Robert Bonomo, Lerchenberg 9, 8046 Zürich
http://surf.to/bonomo
12
.XUVXQWHUODJHQ
3&6XSSRUWHU6,=
/HKUJDQJ3&6XSSRUWHU6,=
9,68$/%$6,&)25$33/,&$7,2169%$
2.4 Fehlerbehandlung
Ein wichtiger Aspekt bei der Programmierung von Automatismen und Anwendungen ist eine
saubere und effiziente Fehlerbehandlung. Werden mögliche und unerwartete Fehlerzustände nicht
abgefangen, so stoppt das Prgramm beim Auftreten des Fehlers und alle bereits eingegebenen Daten
sind möglicherweise verloren. Man unterscheidet grundsätzlich 3 Fehlertypen:
Typ
Syntaxfehler
Semantikfehler
Typ
Laufzeitfehler
Beschreibung
Die formalen Regeln der Programmiersprache wurden nicht eingehalten. Ein
Syntaxfehler tritt auf, wenn ein Programmieranweisung falsch geschrieben wurde.
Eine Syntaxprüfung wird während der Eingabe beim Verlassen der Programmzeile
automatisch durchgeführt
Die Definition der Beziehung zwischen den einzelnen Sprachwörtern und deren
Bedeutung (Programmlogik, grammatikalische Regeln) wurde nicht berücksichtigt.
Ein Semantikfehler tritt erst während der Laufzeit eines Programmes auf (z.B.
wenn nach einem If das End if vergessen wurde)
Beschreibung
Ein Laufzeitfehler tritt während des Programmablaufs ab und führt zum sofortigen
Programmabbruch unter Angabe des Fehlertyps (z.B. nicht kompatible
Datentypen, Division durch 0)
Visual Basic for Applications bietet zur Fehlerbehandlung während der Laufzeit von Programmen
folgenden Befehl an:
On Error [Aktion]
Innerhalb einer Routine kann also ein beliebiger Laufzeitfehler mit dieser Anweisung abgefangen
werden und mit einer Aktion behandelt werden. Es gibt hier wiederum 2 Möglichkeiten:
On Error Resume Next
Der Fehler wird ignoriert und die nächste Zeile der Funktion wird ausgeführt. Dieses Vorgehen ist
sehr gefährlich, insbesondere dann, wenn die einzelnen Befehle innerhalb einer Funktion auf den
Resultaten der vorgängigen Befehle aufbauen.
On Error Goto [Sprungziel]
Mit dieser Art der Fehlerbehandlung springt man im Fehlerfall auf eine vordefinierte
Fehlerbehandlungsroutine (Sprungziel), die mit einer Sprungmarke innerhalb der Funktion definiert
wurde. (TIP: Vergessen Sie nie vor der Fehlerroutine die Funktion mit Exit Sub zu verlassen).
Jeder Fehler wird innerhalb der VBA Umgebun im Objekt Err abgespeichert. Eine interessante
Eigenschaft des Err Objektes ist Description, welche die Beschreibung des aufgetretenen Fehlers
enthält
Sub MyFunction()
On Error Goto ERRORHANDLER
Dim a as integer
a="abc"
Exit Sub
Copyright »
2000 by Robert Bonomo, Lerchenberg 9, 8046 Zürich
http://surf.to/bonomo
12
.XUVXQWHUODJHQ
3&6XSSRUWHU6,=
/HKUJDQJ3&6XSSRUWHU6,=
9,68$/%$6,&)25$33/,&$7,2169%$
ERRORHANDLER:
Msgbox Err.Description
End Sub
Copyright »
2000 by Robert Bonomo, Lerchenberg 9, 8046 Zürich
http://surf.to/bonomo
13
.XUVXQWHUODJHQ
3&6XSSRUWHU6,=
/HKUJDQJ3&6XSSRUWHU6,=
9,68$/%$6,&)25$33/,&$7,2169%$
2.5 Dialoge
Mittels sogenannten Dialogen kann eine Interaktion bzw. Kommunikation mit dem Benutzer
stattfinden. Man unterscheidet verschiedene Arten von Dialogen:
2.5.1 Vordefinierte Dialogboxen (VBA)
Unter vordefinierten Dialogboxen versteht man durch VBA vordefinierte Funktionen, welche eine
einfach konfigurierbare Ein- und Ausgabe zur Verfügung stellen.
Msgbox
(Anweisung)
Syntax
Beispiel
Msgbox (Funktion)
Syntax
Beispiel
Beschreibung
Die Anweisung Msgbox ist die einfachste Art, mit dem Benutzer zu
kommunizieren. Die Anweisung zeigt eine Meldung an, welche der Benutzer mit
OK bestätigen muss. Es wird kein Rückgabewert geliefert.
msgbox Meldung
msgbox "Beispiel einer einfachen msgbox ohne Rückgabewert"
Beschreibung
Die Funktion Msgbox zeigt eine Meldung in einer konfigurierbaren Meldungsbox
und liefert einen Integer als Rückgabewert., dessen Wert dem durch den Benutzer
gedrückten Button entspricht.
Rückgabewert=MsgBox(Meldung[, buttons] [, title] [, helpfile, context])
response=msgbox("Programm beenden",vbOKCancel,"My Program")
Beschreibung
Inputbox (Funktion) Die Funktion Inputbox zeigt eine Meldung in einer konfigurierbaren Meldungsbox
die ein zusätzliches Eingabefeld besitzt, und liefert einen String als Rückgabewert.,
dessen Wert dem Inhalt der Textbox entspricht
Antwort=InputBox(Meldung[, title] [, default] [, xpos] [, ypos] [, helpfile, context])
Syntax
antwort=inputbox("Wie alt sind Sie ?","Altersabfrage",1967)
Beispiel
2.5.2 benutzerdefinierte Systemdialoge (User-Forms)
VBA bietet die Möglichkeit benutzerdefinierte Dialoge zu erstellen. Dies geschieht im Visual Basic
Editor indem man eine User-Form hinzufügt und die benötigten Konstruktionselemente in der Form
plaziert. Jedes Konstruktionselement besitzt vordefinierte Eigenschaften, welche mit VBA Code
hinterlegt werden können.
Copyright »
2000 by Robert Bonomo, Lerchenberg 9, 8046 Zürich
http://surf.to/bonomo
14
.XUVXQWHUODJHQ
3&6XSSRUWHU6,=
/HKUJDQJ3&6XSSRUWHU6,=
9,68$/%$6,&)25$33/,&$7,2169%$
2.5.3 integrierte Systemdialoge
Integrierte Systemdialoge werden auf Systemebene über das Microsoft Common Dialog Control
COMDLG32.ocx zur Verfügung gestellt.
Um mit diesen Systemdialogen arbeiten zu können, muss wie folgt vorgegangen werden.
Im Visual Basic Editor wird über das Menü "Insert" bzw. "Einfügen" eine Neue "User-Form" bzw.
"Benutzer-Form" hinzugefügt. In der erscheinenden Toolbox kann man über die rechte Maustaste
zusätzliche Controls ("additional Controls") hinzufügen. (siehe obenstehende Abbildung). In der
Auswahlbox ist "Microsoft Common Dialog Control auszuwählen und mit OK zu bestätigen. In der
Toolbox erscheint dadurch ein neues Konstruktionselement, welches man mit Drag und Drop auf
die User-Form hineinzieht und fallenlässt. Es sind die folgenden Standard-Dialoge programmierbar:
Aufruf (Code)
CommonDialog1.DialogTitle = "Speichern"
CommonDialog1.InitDir = "c:\temp"
CommonDialog1.Filter = "Text Files | *.txt"
CommonDialog1.ShowSave
CommonDialog1.DialogTitle = "File laden"
File öffnen
CommonDialog1.InitDir = "c:\temp"
CommonDialog1.Filter = "Robert Bonomo's Files | *.rb"
CommonDialog1.ShowOpen
Printer-Dialog CommonDialog1.ShowPrinter
Dialogart
File speichern
Font-Dialog
CommonDialog1.ShowFont
Farbe
auswählen
CommonDialog1.ShowColor
Copyright »
2000 by Robert Bonomo, Lerchenberg 9, 8046 Zürich
Rückgabewert (Code)
CommonDialog1.FileName
CommonDialog1.FileName
CommonDialog1.Orientation
CommonDialog1.Copies
CommonDialog1.FromPage
CommonDialog1.ToPage
CommonDialog1.FontBold
CommonDialog1.FontName
CommonDialog1.FontSize
CommonDialog1.Color
http://surf.to/bonomo
15
.XUVXQWHUODJHQ
3&6XSSRUWHU6,=
/HKUJDQJ3&6XSSRUWHU6,=
9,68$/%$6,&)25$33/,&$7,2169%$
Microsoft Excel verfügt zuzsätzlich über eine grosse Anzahl an vordefinierten Dialogen, welche
über folgenden Objektpfad aufgerufen werden können: Application.Dialogs(Dialogtyp). Der
Rückgabewert ist entweder True oder falsch, je nachdem ob der Benutzer im Dialog OK oder
Abbrechen gedrückt hat. Die Manipulationen, die sich aus den Dialogen ergeben, werden immer
mit dem Application Object durchgeführt.
In der nachfolgenden Tabelle finden Sie einige Besipiele für die Verwendung dieser Dialoge:
Dialogtyp
xlDialogDisplay
xlDialogFont
xlDialogOpen
Copyright »
Beschreibung
Setzt die Excel Display Optionen
Font-Einstellungen
Öffnet ein Excel File
Aufruf (Code)
Application.Dialogs(xlDialogDisplay).Show
Application.Dialogs(xlDialogFont).Show
Application.Dialogs(xlDialogOpen).Show
2000 by Robert Bonomo, Lerchenberg 9, 8046 Zürich
http://surf.to/bonomo
16
.XUVXQWHUODJHQ
3&6XSSRUWHU6,=
/HKUJDQJ3&6XSSRUWHU6,=
9,68$/%$6,&)25$33/,&$7,2169%$
2.6 VBA in Microsoft Excel
2.6.1 Das Microsoft Excel Objektmodell
In der obenstehenden Abbildung sind die wesentlichsten Objekte aus dem Microsoft Excel
Objektmodell abgebildet. In den nachfolgenden Tabellen werden exemplarisch einige wichtige
Methoden, Eigenschaften und Ereignisse dieser Objekte beschrieben.
Das Application Object
Application.Run
Application.Run("Mappe1.xls!Makro1")
Application.Quit
Microsoft Excel beenden
Application.SaveWorkspace
Save
Application.Checkspelling
Application.Checkspelling("Feler")
Liefert true oder false zurück
Application.Username
Application.Username = "Micky Mouse"
Application.StatusBar
Application.StatusBar="Excel macht Spass"
Application.Version
msgbox Application.Version (Read Only!)
Application.Name
msgbox Application.Name (Read Only!)
Application.Caption
Application.Caption="Mein eigenes Excel"
Application.Visible
True falls die Applikation sichtbar ist
Application.WorkbookBeforeClose
Application. WorkbookBeforeSave
Application. WorkbookOpen
Application. WorkbookNewSheet
Application.Caption
Ereignisse müssen über den Visual
Basic Editor programmiert werden,
bzw. die Ereignisse sind ohne
Aktion bereits im Objektcode
enthalten. Eine Übersicht aller
Applkikationsereignisse
ist
im
Objektbrowser (F2) ersichtlich
Um auf die Ereignisse des Application Objektes zugreifen zu können muss ein neues Klassenmodul
erstellt werden, dass die Applikation instanziert und die vordefinierten Events abfängt.
Copyright »
2000 by Robert Bonomo, Lerchenberg 9, 8046 Zürich
http://surf.to/bonomo
17
.XUVXQWHUODJHQ
3&6XSSRUWHU6,=
/HKUJDQJ3&6XSSRUWHU6,=
9,68$/%$6,&)25$33/,&$7,2169%$
Das Workbooks Object
Workbooks.Add
neue Arbeitsmappe hinzufügen
Workbooks.Open
FileName:="C:\bonomo.xls"
öffnet eine bestehende Arbeitsmappe
Workbooks.Count
Anzahl Arbeitsmappen ausgeben
Worksheets.Add
neue Tabelle hinzufügen
Worksheets.Count
Anzahl Tabellen in der Arbeitsmappe ausgeben
Worksheets("Name").Activate
Tabelle mit Namen "Name" aktivieren
Worksheets("Name").Delete
Tabelle mit Namen "Name" löschen
Worksheets("Name").Protect
Tabelle mit Namen "Name" schützen
Worksheets(Index).Name
Name der Tabelle mit Index Index (Start bei 1)
Worksheets(Index).Visible
Tabelle verstecken oder zeigen (true oder false)
Das Worksheets Object
Das Worksheet Object
Worksheets("Name").SelectionChange
Worksheets("Name").Activate
Ereignisse müssen über den Visual
Basic Editor programmiert werden,
bzw. die Ereignisse sind ohne
Aktion bereits im Objektcode
enthalten. Eine Übersicht aller
Applkikationsereignisse
ist
im
Objektbrowser (F2) ersichtlich
Um die vordefinierten Ereignisse des Worksheet Objektes mit Aktionen (Code) erweitern zu
können muss im Visual-Basic Editor (Alt-F11) ein Doppelclick auf dem Workbook durchgeführt
werden. Im erscheinenden Code-Fenster ist links das Worlbook Objekt auszuwählen, dann werden
rechts die Ereignisse angezeigt.
Copyright »
2000 by Robert Bonomo, Lerchenberg 9, 8046 Zürich
http://surf.to/bonomo
18
.XUVXQWHUODJHQ
3&6XSSRUWHU6,=
/HKUJDQJ3&6XSSRUWHU6,=
9,68$/%$6,&)25$33/,&$7,2169%$
Das Range Object (Cells und Ranges)
Range("A1").Activate
aktiviert Zelle A1
Range(“A1:B2“).Delete
löscht den Range A1-B2
Cells(1,2).Select
selektiert Zelle B1
Range(“A1:B2“).Formula
gibt die Feldformel zurück (Wert falls keine
Formel da)
Range(“A1:C2“).Value
setzt den Wert der Zellen im Range
Range(“A1:C2“).Offset(x,y)
Navigation weg vom Range
Cells(1,1).FormulaR1C1
gibt die Feldformel zurück (Wert falls keine
Formel da)
ActiveCell.Value
Wert der aktuellen Zelle
Das Range Objekt besitzt keine Ereignisse
2.6.2 Absolute und relative Zellreferenzen
Wird bei der Makroaufzeichnung mit Zellpositionen und Formeln gearbeitet, so ist besonders auf
die Referenzierung der Zellen zu achten. Man unterscheidet zwischen absoluter und relativer
Referenzierung, wobei die absolute Referenzierung der Default ist. Bei der absoluten
Referenzierung werden die Zellreferenzen im Makro absolut (Indexreferenz) abgespeichert, bei der
relativen Referenzierung werden die Zellreferenzen abweichend von der vor der Makroausführung
aktiven Zelle berechnet. Formeln werden bei der Makroaufzeichnung aber immer mittels der R1C1
Style-Notation in relativer Referenzierung abgespeichert. Wird bei der Makroaufzeichnung die
aktive Zelle (Selektion) manipuliert, so wird diese ebenfalls immer relativ referenziert.
Die Art der Referenzierung lässt sich über das Icon Relative Reference, das in der Toolbar Stop
Recording enthalten ist, einstellen.
Copyright »
2000 by Robert Bonomo, Lerchenberg 9, 8046 Zürich
http://surf.to/bonomo
19
.XUVXQWHUODJHQ
3&6XSSRUWHU6,=
/HKUJDQJ3&6XSSRUWHU6,=
9,68$/%$6,&)25$33/,&$7,2169%$
In der untenstehenden Tabelle sollen die möglichen Aufzeichnungsarten und die Auswirkungen der
absolten und relativen Aufnahme in Abhängigkeit zum Benutzerverhalten beispielhaft dargestellt
werden.
Vorgehen
Was
Selektion
vorgängige Selektion
Start Recording
Manipulation der Selektion
Stop Recording
ú
ú
ú
ú
Das Makro wird bei der Ausführung immer auf die aktuelle Selektion angewendet (Range)
absolute
ú
vorgängige Selektion
Aufzeichnung
ú
Start Recording
neue Selektion vornehmen (Repositionierung im Arbeitsblatt)
Manipulation der Selektion
Stop Recording
ú
ú
ú
Das Makro wird bei der Ausführung immer auf die selbe Zelle(n) angewendet
absolute
ú
vorgängige Selektion
Aufzeichnung
ú
relative Aufzeichnung aktivieren (Symbolleiste Stop Recording)
ú
ú
Start Recording
neue Selektion vornehmen (Repositionierung im Arbeitsblatt)
und/oder Manipulation der Selektion oder des Umfeldes der
Selektion
Stop Recording
ú
Das Makro wird bei der Ausführung immer relativ zur aktuellen Selektion angewendet
Copyright »
2000 by Robert Bonomo, Lerchenberg 9, 8046 Zürich
http://surf.to/bonomo
20
.XUVXQWHUODJHQ
3&6XSSRUWHU6,=
/HKUJDQJ3&6XSSRUWHU6,=
9,68$/%$6,&)25$33/,&$7,2169%$
2.6.3 Die Active Properties
Direkt unter dem Application Object sind die Active-Properties angesiedelt. Mittels diesen
Eigenschaften kann man aktuelle selektierte oder aktive Bereich der Applikation herausfinden.
Beispiele können Sie nachfolgender Tabelle entnehmen
Name
ActiveWindow
ActiveWorkbook
ActiveSheet
ActiveCell
Beschreibung
Returniert ein Objekt, das das aktive Window
enthält.
Returniert ein Objekt, das das aktive
Workbook enthält..
Returniert ein Objekt, das das aktive Sheet im
Workbook ebthält
Returniert
einen
Range
der
aktiven
(selektierten) Zellen
Beispiel
ActiveWindow.WindowState
ActiveWorkbook.Name
ActiveSheet.Name
ActiveCell.Value
2.6.4 Zellen und Ranges
Mit den Zellen und Ranges Objekten kann man sich in einem Worksheet positionieren, eine
Auswahl treffen, Manipulationen durchführen und Berechnungen durchführen. Die nachfolgende
Tabelle soll gebräuchlichste Problemstellungen veranschaulichen
Stichwort
Selection
Offset
Beschreibung
Beispiel-Code
Mittels der Selektion Eigenschaft können alle For each cell in Selection
gewählten Zellen durchlaufen und abgefragt
msgbox cell.value
bzw. manipuliert werden
Next
Mit der Offset Methode können Zellen relativ ActiveCell.offset(x, y).Value = 22
zui einer Selektion manipuliert werden
In der nebenstehenden Grafik ist die Orange
Zelle die selektierte und aktive Zelle. Die
Navigation mit der Offset Methode kann
anhand der x- und y-Achsen Werte angeleitet
werden.
Stichwort
Range
Beschreibung
Einen
bestimmten
Bereich
in
einem
Arbeitsblatt
ansprechen
Stichwort
Cells
Beschreibung
Beispiel-Code
Zellen
über
eine Worksheets("Sheet1").Cells(6, 1).Value = 10
Indexnummer
ansprechen
Copyright »
Beispiel-Code
Range("A1")
Zelle A1
Range("A1:B5")
Zellen A1 bis B5
Range("C5:D9,G9:H16")
A multiple-area selection
Range("A:A")
Spalte (Column) A
Range("1:1")
Zeile (Row) Eins
Range("A:C")
Spalten A bis C
Range("1:5")
Zeilen Eins bis Fünf
Range("1:1,3:3,8:8")
Zeilen Eins, Drei, Acht
Range("A:A,C:C,F:F")
Spalten A, C, and F
2000 by Robert Bonomo, Lerchenberg 9, 8046 Zürich
http://surf.to/bonomo
21
.XUVXQWHUODJHQ
3&6XSSRUWHU6,=
/HKUJDQJ3&6XSSRUWHU6,=
9,68$/%$6,&)25$33/,&$7,2169%$
In der nebenstehenden Grafik ist die
Navigation im Excel über die Indexnummer
im Cells Objekr ersichtlich
Stichwort
FormulaR1C1
Beschreibung
Diese Eigenschaft gibt
über
einen
vewendeten Range die
Feldformeln oder falls
keine Formeld definiert
sind den Inhalt der
Zellen zurück.
Beispiel-Code
Worksheets("Sheet1").Range("B1").FormulaR1C1 =
"=SUM(R[1]C:R[9]C)"
(Relative Navigation gem. Darstellung)
Worksheets("Sheet1").Range("B1").FormulaR1C1 =
"=SQRT(R1C1)"
(Absolute Navigation zu Row1 / Column1 = A1)
In der nebenstehenden Grafik ist die R1C1
Stil-Navigation
im
Excel
grafisch
übersichtlich aufbereit. Die Navigation kann
sowohl absolut (analog zur Cells Indexierung)
mit den X- und Y Werten direkt hinter R
(Rows)und C (Columns) Schlüsseln als auch
relativ (durch die Angabe der Werte in
eckigen Klammern []) zur aktiven Zelle
durchgeführt werden. Die R1C1 Notation
wird nur in Formeln angewendet.
2.6.4 Microsoft Excel Objektlandkarte
Copyright »
2000 by Robert Bonomo, Lerchenberg 9, 8046 Zürich
http://surf.to/bonomo
22
.XUVXQWHUODJHQ
3&6XSSRUWHU6,=
/HKUJDQJ3&6XSSRUWHU6,=
9,68$/%$6,&)25$33/,&$7,2169%$
2.7 VBA in Microsoft Winword
2.7.1 Das Microsoft Winword Objektmodell
In der obenstehenden Abbildung sind die wesentlichsten Objekte aus dem Microsoft Winword
Objektmodell abgebildet. In den nachfolgenden Tabellen werden exemplarisch einige wichtige
Methoden, Eigenschaften und Ereignisse dieser Objekte beschrieben.
Das Application Object
Application.Run
Application.Run("Module1.Makro1")
Application.Quit
Microsoft Winword beenden
Application.NewWindow
Neues Fenster des aktiven Dokumentes öffnen
Application.PrintOut
application.PrintOut
FileName:="c:\temp\MeinDokument.doc"
Application.Username
Application.Username = "Micky Mouse"
Application.StatusBar
Application.StatusBar="Excel macht Spass"
Application.Version
msgbox Application.Version (Read Only!)
Application.Name
msgbox Application.Name (Read Only!)
Application.Caption
Application.Caption="Mein eigenes Excel"
application.DisplayStatusBar
application.DisplayStatusBar=False
Application.Visible
True falls die Applikation sichtbar ist
Application.DocumentChange
Application.Quit
Copyright »
2000 by Robert Bonomo, Lerchenberg 9, 8046 Zürich
Ereignisse müssen über den Visual
Basic Editor programmiert werden,
bzw. die Ereignisse sind ohne
Aktion bereits im Objektcode
enthalten. Eine Übersicht aller
Applkikationsereignisse
ist
im
Objektbrowser (F2) ersichtlich
http://surf.to/bonomo
23
.XUVXQWHUODJHQ
3&6XSSRUWHU6,=
/HKUJDQJ3&6XSSRUWHU6,=
9,68$/%$6,&)25$33/,&$7,2169%$
Das Documents Object
Documents.Add
Fügt ein neues Dokument hinzu
Documents.Close
Schliesst alle geöffneten Dokumente
Documents.Open
Documents.Save
Documents.Open("C:\temp\Doc1.doc")
Documents.Count
Zeigt die Anzahl geöffneter Dokumente
Speicher alle geöffneten Dokumente
Das Document Object
Documents(1)Activate
Aktiviert Dokument mit Indexnummer 1
Documents("Doc1.doc").Activate
Aktiviert Dokument mit Namen Doc1.doc
Documents(1).Bookmarks.Count
Liefert die Anzahl definierter Bookmarks
Documents(1).Characters.Count
Liefert die Anzahl Charakter
Documents(1).Words.Count
Liefert die Anzahl Wörter im Dokument
Documents(1).CheckSpelling
Überprüft die Rechtschreibung im Dokument
Documents(1).CheckGrammar
Überprüft die Grammatik im Dokument
Documents("Doc1.doc").PrintOut
Druckt das Dokument Doc1.doc
Documents(1).Fullname
Zeigt den Dokumentnamen inklusive Pfad
Documents(1).Path
Zeigt den Speicherort (Pfad) des Dokuments
Documents(1).HasPassword
Liefert true zurück, falls Passwort gesetzt
New
Close
Open
Copyright »
2000 by Robert Bonomo, Lerchenberg 9, 8046 Zürich
Ereignisse müssen über den Visual
Basic Editor programmiert werden,
bzw. die Ereignisse sind ohne
Aktion bereits im Objektcode
enthalten. Eine Übersicht aller
Applkikationsereignisse
ist
im
Objektbrowser (F2) ersichtlich
http://surf.to/bonomo
24
.XUVXQWHUODJHQ
3&6XSSRUWHU6,=
/HKUJDQJ3&6XSSRUWHU6,=
9,68$/%$6,&)25$33/,&$7,2169%$
Das Selection Object
Selection.Delete
Löscht die Selektion
selection.GoTo
What:=wdGoToBookmark,
Name:="gaga"
Springt zum Bookmark mit Namen gaga
Selection.typetext "R. Bonono"
Schreibt Text in ein Dokument
Selection.MoveDown
Unit:=wdLine, Count:=6
Der Cursor
verschoben
Selection.MoveLeft
Unit:=wdCharacter, Count:=1
Der Cursor wird eine Stelle nach rechts
verschoben
Selection.MoveRight
Unit:=wdCharacter, Count:=8,
Extend:=wdExtend
Die Selektion wird um 8 Charakter erweitert
Selection.Characters.Count
Liefert die Anzahl selektierter Charakter
Selection.Type
Liefert den Typ der Selektion zurück:
wdNoSelection, wdSelectionBlock,
wdSelectionColumn, wdSelectionFrame,
wdSelectionInlineShape, wdSelectionIP,
wdSelectionNormal, wdSelectionRow, or
wdSelectionShape.
wird
6
Linien
nach
unten
Das Selection Objekt besitzt keine Ereignisse
Copyright »
2000 by Robert Bonomo, Lerchenberg 9, 8046 Zürich
http://surf.to/bonomo
25
.XUVXQWHUODJHQ
3&6XSSRUWHU6,=
/HKUJDQJ3&6XSSRUWHU6,=
9,68$/%$6,&)25$33/,&$7,2169%$
2.7.2 Microsoft Winword Objektlandkarte
Copyright »
2000 by Robert Bonomo, Lerchenberg 9, 8046 Zürich
http://surf.to/bonomo
26
.XUVXQWHUODJHQ
3&6XSSRUWHU6,=
/HKUJDQJ3&6XSSRUWHU6,=
9,68$/%$6,&)25$33/,&$7,2169%$
2.8 Interaktion zwischen Microsoft Office Anwendungen
2.8.1 Erstellung, Referenzierung und Dereferenzierung von ActiveX Objekten
Mit Microsoft Visual Basic for Applications besteht die Möglichkeit, ActiveX Objekte zu erstellen
und zu manipulieren. Im Befehlssatz von VBA verwendet man für die Erstellung von ActiveX
Objekten das Kommando CreateObject. Das nachfolgende Beispiel zeigt, wie man ein ActiveX
Objekt einer Microsoft Office Applikation erstellt.
Zuerst muss eine Objektvariable deklariert werden, welche die Objektreferenz auf das zu
erstellende ActiveX Objekt aufnehmen kann. Dies geschieht mit der Dim Anweisung
Dim xlApp As Object
Nun kann mit dem Set Kommando und der CreateObject Funktion das neue ActiveX Objekt erzeugt
werden. Durch die nachfolgenden Statements werden neue Instanzen der definierten Microsoft
Applikationen gestartet und in den Hauptspeicher geladen
Set xlApp = CreateObject("Excel.Application")
Set wwApp = CreateObject("Word.Application")
Set ppApp = CreateObject("Powerpoint.Application")
Set acApp = CreateObject("Access.Application")
Set olApp = CreateObject("Outlook.Application")
Die Applikation wird unsichtbar gestartet, ist jedoch bereits voll funktionsfähig. Um die
Applikation sichtbar zu machen, muss die Visible Eingenschaft des Applikationsobjektes (siehe
Kapitel 2.6.1 / 2.7.1) auf true gesetzt werden
xlApp.Visible=true
Um die Applikation zu beenden wird die Quit Methode des Applikationsobjektes verwendet
Applikationsobjektes (siehe Kapitel 2.6.1 / 2.7.1).
xlApp.Quit
Um die bestehende Referenz auf das ActiveX Objekt ebenfalls zu entfernen (Dereferenzierung),
wird folgender Befehl verwendet.
Set xlApp = Nothing
Copyright »
2000 by Robert Bonomo, Lerchenberg 9, 8046 Zürich
http://surf.to/bonomo
27
.XUVXQWHUODJHQ
3&6XSSRUWHU6,=
/HKUJDQJ3&6XSSRUWHU6,=
9,68$/%$6,&)25$33/,&$7,2169%$
2.8.2 Referenzierung von bestehenden ActiveX Objekten
Mit der GetObjekt Methode lassen sich bereits laufende Instanzen von Microsoft Anwendungen
auffinden.. Um festzustellen, ob Microsoft Excel bereits läuft kann nachfolgender Code verwendet
werden.
Dim xlApp As Object
Dim ExcelWasNotRunning As Boolean
On Error Resume Next
Set xlApp = Getobject(, "Excel.Application")
If Err.Number <> 0 Then ExcelWasNotRunning = True
Err.Clear
Über die Applikationseigenschaft des soeben referenzierten Objektes kann nun auf das
Objektmodell vom Microsoft Excel zugegriffen werden. Damit die neu instanzierte Microsoft
Applikation auch im COM Modell ansprechbar ist, muss diese in der Microsoft Windows
Systemtabelle der aktiven Objekte eingetragen werden (Running Object Table). Dies geschieht mit
der nachfolgenden Prozedur.
Sub DetectExcel()
Const WM_USER = 1024
Dim hWnd As Long
hWnd = FindWindow("XLMAIN", 0) 'Dieser API Aufruf sucht eine Instanz von Excel
If hWnd = 0 Then Falls der Rückgabewert <> 0 ist, wurde eine Excel Instanz gefunden
Exit Sub
Else
SendMessage hWnd, WM_USER + 18, 0, 0 'Eintrag in der Running Objekt Tabelle
End If
End Sub
Damit die oben aufgeführte Prozedur auf die Windows-API Funktionen FindWindow und
SendMessage zugreifen kann, müssen diese im Deklarationsteil eines Modules wie folgt deklariert
werden.
Declare Function FindWindow Lib "user32" Alias _
"FindWindowA" (ByVal lpClassName as String, _
ByVal lpWindowName As Long) As Long
Declare Function SendMessage Lib "user32" Alias _
"SendMessageA" (ByVal hWnd as Long,ByVal wMsg as Long _
ByVal wParam as Long _
ByVal lParam As Long) As Long
Copyright »
2000 by Robert Bonomo, Lerchenberg 9, 8046 Zürich
http://surf.to/bonomo
28
.XUVXQWHUODJHQ
3&6XSSRUWHU6,=
/HKUJDQJ3&6XSSRUWHU6,=
9,68$/%$6,&)25$33/,&$7,2169%$
2.8.3 Kommunikation zwischen Office Anwendungen über ActiveX Objekte
Mittels der in Kapitel 2.8.1 beschriebene Methode, ActiveX Objekte aller Microsoft Applikationen
unter VBA zu erstellen, besteht nun eine einfache Möglichkeit, auf gespeicherte und aktuell
bearbeitete Daten aus anderen Microsoft Applikationen zuzugreifen.
Über referenzierte ActiveX Objekte sind sämtliche Eigenschaften und Methoden der
entsprechenden Applikation abrufbar (siehe Kapitel 2.6.1 / 2.7.1).
Copyright »
2000 by Robert Bonomo, Lerchenberg 9, 8046 Zürich
http://surf.to/bonomo
29
/HKUJDQJ3&6XSSRUWHU6,=
.XUVXQWHUODJHQ
3&6XSSRUWHU6,=
9,68$/%$6,&)25$33/,&$7,2169%$
3.0 Übungen
3.1 Einführung in die Programmierung
Antwort
Frage
Was ist ein Programm ?
Was ist eine Programmiersprache ?
Durch
was
wird
Programmiersprache definiert ?
eine
Nennen Sie zwei Einsatzgebiete für
VBA ?
Was muss vor der eigentlichen
Programmierung gemacht werden ?
Was unterscheidet einen Compiler von
einem Interpreter ?
Was ist ein Modul ?
Was ist eine Variable und welche
verschiedenen Datentypen kann diese
aufnehmen ?
Wie
muss
man
eine
Variable
deklarieren,
damit
diese
allen
Funktionen innerhalb eines Moduls zur
Verfügung steht ?
Copyright »
2000 by Robert Bonomo, Lerchenberg 9, 8046 Zürich
http://surf.to/bonomo
30
/HKUJDQJ3&6XSSRUWHU6,=
.XUVXQWHUODJHQ
3&6XSSRUWHU6,=
9,68$/%$6,&)25$33/,&$7,2169%$
3.2 Datentypen
Welchen Datentypen können die folgenden Werte zugeordnet werden:
Prüfen Sie dabei, ob eine Zuweisung möglich ist ohne dass sich der Wert verändert.
Legende
Wert
100
„
î
Datentyp
Integer
keine Zuweisung möglich(Error)
Zuweisung möglich, die Daten werden jedoch verändert
Zuweisung möglich
Long
Single
Double
Currency
String
Date
Variant
Boolean
-8540
1000000
abc
123abc
123456789
123456789012345
12345678901234567
123456789012345.12
1.1
1.123456
1.1234567890
%&/123
3.147512234
50%
Dies ist ein Text
a/2=b
1.1.2000
1. Januar 2000
Copyright »
2000 by Robert Bonomo, Lerchenberg 9, 8046 Zürich
http://surf.to/bonomo
31
.XUVXQWHUODJHQ
3&6XSSRUWHU6,=
/HKUJDQJ3&6XSSRUWHU6,=
9,68$/%$6,&)25$33/,&$7,2169%$
3.3 Programmablaufpläne
Stelle den nachfolgend beschriebenen Ablauf als Programmablaufplan das. Verwende dabei die
Notation des Flussdiagrammes nach DIN 6601PA.
Peters wöchentliche Einkaufstour:
Peter fährt mit dem Auto zum Migros, nimmt im Parkhaus den Parkschein entgegennehmen und
parkiert dann sein Auto in der Garage. Er schnappt sich einen Einkaufskorb und betritt den Migros.
Zuerst kommt er bei der Milch vorbei und nimmt immer 2 Liter in den Einkaufskorb. Falls das
Ablaufdatum der Milch grösser als 4 Wochen in der Zukunft ist, nimmt er nochmals 2 Liter in den
Einkaufskorb. Falls es frischen Zopf hat, kauft er 1 kg Zopf. An der Kasse bezahlt er nur mit der
Kreditkarte, falls sein Bargeld nicht ausreicht. An der Parkkasse bezahlt er seinen Parkschein
entweder mit Münz, mit Cash oder mit Banknoten. Danach fährt er nach Hause.
Copyright »
2000 by Robert Bonomo, Lerchenberg 9, 8046 Zürich
http://surf.to/bonomo
32
.XUVXQWHUODJHQ
3&6XSSRUWHU6,=
/HKUJDQJ3&6XSSRUWHU6,=
9,68$/%$6,&)25$33/,&$7,2169%$
3.4 VBA
ú
Erstelle unter Microsoft Excel ein Programm, dass folgende Funktionalitäten abdeckt:
ú
ú
Das Programm soll über ein eigenes Menü "Mein Menü" mit Menü-Eintrag "Wochentag" und
mit der Tastenkombination Ctrl-C aufgerufen werden können
Die selektiere Zelle(n) soll hellblau eingefärb werden
Der Datumswert der Zelle soll überprüft werden und der Wochentag als Meldungsbox
ausgegeben werden
zusätzlich
ú
Sicherstellung, dass mindestens eine Zelle selektiert ist, sonst Programmabruch
ú
Der Wert der aktiven Zelle soll überprüft werden. Ist der Wert NULL (d.h. die Zelle ist leer), so
soll das Programm abgebrochen werden
ú
Im Fehlerfall soll eine Fehlerroutine angesprochen werden und eine Fehlermeldung ausgegeben
werden
ú
Falls kein Wert in der Zelle steht, so wird dieser abgefragt (Inputbox) und die die aktive Zelle
geschrieben
3.5 Programm-Analyse
VBA Aufgabe der PC-Supporter Prüfung 1999-1:
Das Makro ZeilenLöschen soll Zellen in einem angegebenen Bereich prüfen. Wenn eine leere Zelle
gefunden wird, soll die ganze entsprechende Zeile gelöscht werden.
1
2
3
4
5
6
7
8
9
10
11
12
13
Sub ZeilenLöschen()
anzahl2=0
anzahl=Selection.Rows.Count
For zähler=1 to anzahl
If ActiveCell="" then
Selection.EntireRow.Delete
Else
ActiveCell.Offset(1,0).Select
End If
Next zähler
anzahl2=anzahl2+1
Msgbox(anzahl2 & " Zeilen wurden gelöscht")
End Sub
Für das oben dargestellte Makro trifft zu:
a
P
A
ˆ
B
ˆ
C
D
ˆ
ˆ
Copyright »
O
ˆ
ˆ
ˆ
ˆ
Das Makro arbeitet nur richtig, wenn der Bereich mit den Daten, in dem
sich die leeren Zeilen befinden, markiert ist
Das Makro muss für jede Spalte separat gestartet werden
Die Anweisung in Zeile 11 ist im Makro falsch plaziert
Mit diesem Makro können maximal 256 Zeilen abgesucht werden.
2000 by Robert Bonomo, Lerchenberg 9, 8046 Zürich
http://surf.to/bonomo
33
.XUVXQWHUODJHQ
3&6XSSRUWHU6,=
/HKUJDQJ3&6XSSRUWHU6,=
9,68$/%$6,&)25$33/,&$7,2169%$
VBA Aufgaben der PC-Supporter Prüfung 1998-2:
In der Tabelle "AB" ist der Dokumentenschutz aktiviert. Über eine Befehlsschaltfläche ist in diesem
Dokument ein Makro zugeordnet, welches einen Eingabe Dialog öffnet, in dem ein Wert
eingegeben werden kann. Dieser Wert soll durch das Makro in die Zelle C2 eingefügt werden, in
dem zuerst der Dokumentenschutz aufgehoben wird, die Zahl eingetragen und anschliessend der
Dokumentenschutz wieder aktiviert wird. Das Modul der Tabelle "AB" ist untenstehend abgebildet.
1
2
3
4
5
6
7
Sub dauer()
dau=Inputbox("Geben Sie den Wert ein:")
Range("C2").Select
ActiveCell.FormulaR1C1=dau
entsperren
sperren
End Sub
8
9
10
11
Sub entsperren()
Sheets("AB").Select
ActiveSheet.Unprotect
End Sub
12
13
14
Sub sperren()
Sheets("AB").Select
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, _
Scenarios:=True
End Sub
15
Damit die Nutzungsdauer mit Hilfe des Makros in die Tabelle eingetragen werden kann, müsste:
b
A
B
C
D
P
ˆ
ˆ
ˆ
ˆ
O
ˆ
ˆ
ˆ
ˆ
Zeile 5 und 6 vertauscht werden
Zeile 4 und 5 vertauscht werden
Zeile 4 zwischen Zeilen 6 und 7 eingeschoben werden
Zeile 5 zwischen Zeilen 2 und 3 eingeschoben werden
Für die Makros in diesem Modul gilt:
c
P
A
ˆ
B
ˆ
C
ˆ
D
ˆ
Copyright »
O
ˆ
ˆ
ˆ
ˆ
In Zeile 14 genügt die Anweisung "ActiveSheet.Protect". Der Rest ist
für die Funktion dieses Makros überflüssig
In Zeile 4 muss die Variable dau in Anführungszeichen gesetzt werden
Die Makros "sperren" und "entsperren" können als Tastatur-Makros
aufgezeichnet werden
Statt die Makros "sperren" und "entsperren" über ihre Namen
aufzurufen, könnte ihr Inhalt auch direkt in das Makro "dauer"
geschrieben werden
2000 by Robert Bonomo, Lerchenberg 9, 8046 Zürich
http://surf.to/bonomo
34
.XUVXQWHUODJHQ
3&6XSSRUWHU6,=
/HKUJDQJ3&6XSSRUWHU6,=
9,68$/%$6,&)25$33/,&$7,2169%$
Makros können in Excel auf verschiedene Arten gestartet werden; zum Bespiel durch
d
P
A
ˆ
B
ˆ
C
D
ˆ
ˆ
O
ˆ
ˆ
ˆ
ˆ
Kombination der Alt-Taste und einer Buchstabentaste, ausgenommen
Umlaute
eines eigens definierbaren Befehls im Menü "Extras"
Klick auf eine Befehlsschaltfläche, der das Makro zugewiesen ist
Aufruf aus einem anderen Makro
Der Cursor befindet sich vor der Ausführung des untenstehenden Makros in der Zelle C10. Für das
Makro "versetzen" trifft zu:
1
2
7
Sub versetzen()
ActiveCell.Offset(3,-2).Range("A1).Select
End Sub
e
P
A
ˆ
B
ˆ
C
ˆ
D
ˆ
Copyright »
O
ˆ
ˆ
Nach der Ausführung des Makros befindet sich der Cursor in der Zelle
G12
Das Makro wurde relativ aufgezeichnet
ˆ
Nach dem Sprung zur neuen Zielzelle wird zusätzlich die Zelle A1
markiert.
Das Makro enthält einen Syntaxfehler
ˆ
2000 by Robert Bonomo, Lerchenberg 9, 8046 Zürich
http://surf.to/bonomo
35
.XUVXQWHUODJHQ
3&6XSSRUWHU6,=
/HKUJDQJ3&6XSSRUWHU6,=
9,68$/%$6,&)25$33/,&$7,2169%$
3.6 ActiveX Objekte
ú
Erstelle unter Microsoft Winword ein Programm, dass folgende Funktionalitäten abdeckt:
ú
ú
ú
Das Programm soll über ein eigenes Menü "Datenabgleich" mit Menü-Eintrag "Daten aus
Excel" aufgerufen werden können (Tastenkombination Ctrl-E)
Beim Start des Programms wird ein neues Winword Dokument eröffnet
Ein Titel "Monatszahlen" und <<aktueller Monat>> soll eingefügt werden (Schriftgrösse 20)
Unter dem Titel wird aus einem abgespeicherten Excel File (siehe untenstehender Screenshot)
mit dem Speicherpfad "c:\monate.xls" der folgende Inhalt für den aktuellen Monat
übernommen:
ú
ú
: Umsatz Bereich Beratung <<Beratung>>
: Umsatz Bereich Programmierung <<Programmierung>>
: Umsatz Monats-Total <<Monatstotal>>
: Bisheriger durchschnittlicher Monatsumsatz <<durchMonat>>
: Bisheriger Jahresumsatz <<JahresUmsatz>>
Das Excel File und die Excel Applikation werden im Anschluss auf die Datenübernahme
geschlossen
Das Winwordfile wird unter dem Filenamen "C:\Monat<<Monat>>.doc" abgespeichert.
Copyright »
2000 by Robert Bonomo, Lerchenberg 9, 8046 Zürich
http://surf.to/bonomo
36
/HKUJDQJ3&6XSSRUWHU6,=
.XUVXQWHUODJHQ
3&6XSSRUWHU6,=
9,68$/%$6,&)25$33/,&$7,2169%$
4.0 Lösungen
4.1 Einführung in die Programmierung
Antwort
Ein Programm ist eine aus Deklarationen,Anweisungen und
Funktionen zusammengesetzte Software, die ein definiertes
Aufgabengebiet unterstützt bzw. automatisiert oder eine
Problemstellung löst.
Eine Programmiersprache ist ein Hilfsmittel, um mit dem
Computer
zu
kommunizieren.
Mittels
einer
Programmiersprache können Programme erstellt werden.
Frage
Was ist ein Programm ?
Was ist eine Programmiersprache ?
Durch
was
wird
Programmiersprache definiert ?
eine Eine Programmiersprache wird durch die Semantik und die
Syntax vollumfänglich definiert.
Nennen Sie zwei Einsatzgebiete für ú
VBA ?
ú
ú
Automatisation von Teilaufgaben
Implementation neuer Funktionen
Kommunikation mit anderen Applikationen
Lesen und Schreiben von Files
ú
Was muss vor der eigentlichen Bevor man mit der Programmierung beginnt, sollte man sich
die zu lösende Aufgabe abstrakt aufzeichnen und die zur
Programmierung gemacht werden ?
Lösung nötigen Teilschritte und Entscheidungen aufzeichnen.
Was unterscheidet einen Compiler von Ein Compiler verwandelt Sourcecode in selbständig
ausführbare Dateien (Executables, Programmfiles)
einem Interpreter ?
Ein Interpreter arbeitet Zeile für Zeile eines Source-Code files
ab.
Was ist ein Modul ?
Ein Modul ist ein in sich abgeschlossenes Programmteil, das
Deklarationen, Funktionen und Anweisungen enthält..
Objektorientiert eProgrammiersprachen sind immer modular
aufgebaut.
Was ist eine Variable und welche Eine Variable ist ein Objekt, dass eine Daten eines
verschiedenen Datentypen kann diese vordefinierten Datentyps aufnehmen kann.
aufnehmen ?
Wie
muss
man
eine
Variable public
deklarieren,
damit
diese
allen
Funktionen innerhalb eines Moduls zur
Verfügung steht ?
Copyright »
2000 by Robert Bonomo, Lerchenberg 9, 8046 Zürich
http://surf.to/bonomo
37
/HKUJDQJ3&6XSSRUWHU6,=
.XUVXQWHUODJHQ
3&6XSSRUWHU6,=
9,68$/%$6,&)25$33/,&$7,2169%$
4.2 Datentypen
Legende
Wert
100
keine Zuweisung möglich(Error)
„
î
Datentyp
Integer
Long
Single
Double
Currency
String
Date
Variant
Boolean
î
î
î
î
î
î
„
î
„
î
î
î
î
î
„
î
„
î
-8540
Zuweisung möglich, die Daten werden jedoch verändert
Zuweisung möglich
1000000
î
î
î
î
abc
123abc
î
123456789
123456789012345
12345678901234567
123456789012345.12
1.1
„
„
„
„
„
1.123456
1.1234567890
%&/123
3.147512234
„
„
„
„
î
„
„
„
„
î
î
î
„
î
î
„
î
î
î
50%
Dies ist ein Text
a/2=b
î
î
„
„
„
î
1. Januar 2000
2000 by Robert Bonomo, Lerchenberg 9, 8046 Zürich
„
î
î
î
„
î
„
î
„
î
î
î
î
î
î
î
„
„
î
„
î
„
î
„
î
„
î
„
î
„
î
î
î
î
î
î
î
1.1.2000
Copyright »
î
î
î
î
î
î
„
î
î
î
http://surf.to/bonomo
î
î
î
38
/HKUJDQJ3&6XSSRUWHU6,=
.XUVXQWHUODJHQ
3&6XSSRUWHU6,=
9,68$/%$6,&)25$33/,&$7,2169%$
4.3 Programmablaufpläne
Zum Migros fahren
Parkschein
entgegennehmen und parkieren.
Einkaufskorb nehmen und
Migros betreten
2 Liter Milch in den
Einkaufskorb nehmen
ja
ja
Total Milch
< 4 Liter
Ablaufdatum
> 4 Wochen
nein
Frischer
Zopf ?
nein
nein
ja
Kaufe 1 kg frischen Zopf
Bargeld
ausreichend
?
nein
An der Migroskasse mit
Kreditkarte bezahlen
ja
An der Migroskasse mit
Bargeld bezahlen
Parkschein
Bezahlen
ja
Münz
Cash
Bank
Münz
einwerfen
Kreditkarte
benutzen
Banknoten
einschieben
noten
Nach Hause fahren
Copyright »
2000 by Robert Bonomo, Lerchenberg 9, 8046 Zürich
http://surf.to/bonomo
39
.XUVXQWHUODJHQ
3&6XSSRUWHU6,=
/HKUJDQJ3&6XSSRUWHU6,=
9,68$/%$6,&)25$33/,&$7,2169%$
4.4 VBA
Nr
1
2
3
4
Teilbereich
Lösungsweg
Das Programm soll über ein eigenes Menü Im Exel via Menü "Tools/Customize" im Tab
Menü"
mit
Menü-Eintrag "Commands" unter Categories den Punkt "New
"Mein
"Wochentag" aufgerufen werden können Menü" anwählen. Im Commands-Fenster "New
Menü" in die Menüleiste ziehen. Mit der rechten
Maustaste kann der Name geändert werden.
Danach die Kategorie "Macros" auswählen und im
Commands-Fenster "Custom Menü Item" anwählen
und in das vorgängig erstellte Menü ziehen, Mit der
rechten Maustaste ebenfalls den Namen anpassen.
(Fortsetzung bei Nr. 4)
Die selektierte Zelle(n) soll hellblau Eine Zelle vorgängig selektieren, den MakroRekorder starten und die Zelle blau einfärben. Das
eingefärb werden.
… die Tastenkombination soll Ctrl-C Makro soll im Personal Makro Workbook
abgespeichert werden und den Namen
sein
mcrCOLORIZE tragen. Der Shortcut Ctrl-C ist im
Record-Makro Dialog einzutragen. Den
Makrorecorder beenden
Der Datumswert der Zelle soll überprüft Den Visual-Basic Editor starten. In einem Modul
werden
und
der
Wochentag
als folgende Prozedur erstellen:
Meldungsbox ausgegeben werden
Sub Wochentag()
Dim Wt As Date
Wt = ActiveCell.Value
MsgBox Format(Wt, "dddd")
End Sub
… Das Programm soll über ein eigenes Dem unter Punkt 1 erstellten Menü-Eintrag das
Menü "Mein Menü" mit Menü-Eintrag Makro PERSONAL.XLS!mcrCOLORIZE zuordnen.
"Wochentag" aufgerufen werden können Im Makro PERSONAL.XLS!mcrCOLORIZE über den
VisualBasic Editor einen Verweis in die Prozedur
Wochentag einbauen:
5
Sicherstellung, dass mindestens eine Zelle
selektiert ist, sonst Programmabruch
6
Der Wert der aktiven Zelle soll überprüft
werden. Ist der Wert NULL (d.h. die Zelle
ist
leer),
so
soll
das
Programm
abgebrochen werden
Copyright »
Sub mcrCOLORIZE()
'
' mcrCOLORIZE Macro
' Macro recorded 27.05.2000 by Robert Bonomo
'
' Keyboard Shortcut: Ctrl+c
'
With Selection.Interior
.ColorIndex = 34
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
Wochentag
End Sub
in der Prozedur PERSONAL.XLS!mcrCOLORIZE die
folgende Zeile als erste Zeile einfügen:
If Selection.Count = 0 Then Exit Sub
in der Prozedur PERSONAL.XLS!mcrCOLORIZE die
folgende Zeile als erste Zeile einfügen:
If Selection.value= "" Then Exit Sub
2000 by Robert Bonomo, Lerchenberg 9, 8046 Zürich
http://surf.to/bonomo
40
.XUVXQWHUODJHQ
3&6XSSRUWHU6,=
Nr
7
/HKUJDQJ3&6XSSRUWHU6,=
9,68$/%$6,&)25$33/,&$7,2169%$
Teilbereich
Lösungsweg
Im Fehlerfall soll eine Fehlerroutine In der Prozedur PERSONAL.XLS!mcrCOLORIZE über
angesprochen
werden
und
eine den VisualBasic Editor folgende Zeilen einbauen:
Fehlermeldung ausgegeben werden
Sub mcrCOLORIZE()
'
' mcrCOLORIZE Macro
' Macro recorded 27.05.2000 by Robert Bonomo
'
' Keyboard Shortcut: Ctrl+c
'
On Error GoTo ERRORHANDLER
If Selection.Count = 0 Then Exit Sub
If Selection.Value = "" Then Exit Sub
With Selection.Interior
.ColorIndex = 34
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
Wochentag
Exit Sub
ERRORHANDLER:
MsgBox Err.Description
8
End Sub
Falls kein Wert in der Zelle steht, so wird In der Prozedur PERSONAL.XLS!mcrCOLORIZE über
dieser abgefragt (Inputbox) und die die den VisualBasic Editor die unter Punkt 5 erstellte
Zeile durch den folgenden Code ersetzen
aktive Zelle geschrieben
If Selection.Value = "" Then
Selection.Value = InputBox("Bitte Datum
eingeben:", , "11.02.1967")
End If
4.5 Programm Analyse
VBA Aufgabe der PC-Supporter Prüfung 1999-1:
:
a
A
B
C
D
P
‡
‡
‡
ˆ
Copyright »
O
ˆ
ˆ
ˆ
‡
Ja, sonst werden keine Zeilen gelöscht
Ja
Ja, die Anweisung muss unmittelbar nach der Zeile 6 plaziert werden
Nein, es werden maximal 1,7 * 10 308 (15 Stellige Zahl) Zeilen abgesucht.. Da Die
Variable anzahl nicht deklariert wurde, nimmt diese automatisch den Datentyp
Variant an, welcher oben genannte Beschränkung hat.
2000 by Robert Bonomo, Lerchenberg 9, 8046 Zürich
http://surf.to/bonomo
41
.XUVXQWHUODJHQ
3&6XSSRUWHU6,=
/HKUJDQJ3&6XSSRUWHU6,=
9,68$/%$6,&)25$33/,&$7,2169%$
VBA Aufgaben der PC-Supporter Prüfung 1998-2:
b
A
B
C
P
ˆ
‡
ˆ
D
‡
c
P
A
‡
O
‡
ˆ
‡
ˆ
ˆ
ˆ
C
‡
ˆ
D
‡
ˆ
d
P
O
ˆ
B
‡
C
‡
D
‡
e
P
A
B
C
D
ˆ
‡
ˆ
ˆ
Copyright »
ja
Nein, denn in Zeile 6 wird das Sheet ja gesperrt
ja
O
B
A
Nein, denn die Manipulation des Zellwertes wird mit Zeile 5 vollzogen
‡
‡
ˆ
ˆ
ˆ
ja
Nein, sonst wird die Variable nicht aufgelöst und die Zelle C2 erhält die
Wertzuweisung "dau"
ja: Makrorekorder starten und via Menüpunkt "Tools/Protection" das
Dokument sperren bzw. entsperren
ja
Nein, eine Kombination der Alt-Taste und einer Buchstabentaste inkl.
Umlaute ist die richtige Antwort
ja ("Tools/Customize/Commands")
ja
ja
O
‡
ˆ
‡
‡
Nein, der Cursor befindet sich danach in der Zelle A13
ja (offset!)
Nein
Obwohl die Anweisung keinen Sinn macht, ist sie von der Syntax her
korrekt formuliert. Die Range("A1") Anweisung wird aber ignoriert.
2000 by Robert Bonomo, Lerchenberg 9, 8046 Zürich
http://surf.to/bonomo
42
.XUVXQWHUODJHQ
3&6XSSRUWHU6,=
/HKUJDQJ3&6XSSRUWHU6,=
9,68$/%$6,&)25$33/,&$7,2169%$
4.6 ActiveX Objekte
Nr
1
2
Teilbereich
Lösungsweg
Das Programm soll über ein eigenes Menü "Datenabgleich" mit Menü-Eintrag "Daten aus
Excel" aufgerufen werden können >>
Im Winword via Menü "Tools/Customize" im Tab "Commands" unter Categories den Punkt "New
Menü" anwählen. Im Commands-Fenster "New Menü" in die Menüleiste ziehen. Mit der rechten
Maustaste kann der Name geändert werden.
Danach die Kategorie "Macros" auswählen und im Commands-Fenster "Custom Menü Item"
anwählen und in das vorgängig erstellte Menü ziehen, Mit der rechten Maustaste ebenfalls den
Namen anpassen.
Beim Start des Programms wird ein neues Winword Dokument eröffnet
Den Visual-Basic Editor starten. In einem Modul folgende Prozedur erstellen:
Sub DatenAustauschExcel()
application.documents.add
End Sub.
3
Ein Titel "Monatszahlen" und <<aktueller Monat>> soll eingefügt werden (Schriftgrösse 20)
Die bestehende Prozedur DatenAustauschExcel() wird um folgenden Code erweitert::
Selection.TypeText Text:="Monatskennzahlen "
Selection.TypeText Text:= format(now,"mm/yyyy")
4
Selection.HomeKey Unit:=wdLine, Extend:=wdExtend
Selection.Font.Size = 20
Selection.EndKey Unit:=wdLine
Selection.TypeParagraph
Selection.EndKey Unit:=wdLine, Extend:=wdExtend
Selection.Font.Size = 12
Unter dem Titel wird aus einem abgespeicherten Excel File (siehe untenstehender
Screenshot) mit dem Speicherpfad "c:\monate.xls" der folgende Inhalt für den aktuellen
Monat übernommen
Die bestehende Prozedur DatenAustauschExcel() wird um folgenden Code erweitert::
Dim xlApp as Object
Set xlApp = CreateObject("Excel.Application")
xlApp.Workbooks.Open FileName:="C:\monate.xls"
5
Umsatz Bereich Beratung <<Beratung>>
Die bestehende Prozedur DatenAustauschExcel() wird um folgenden Code erweitert::
Selection.TypeText Text:="Umsatz Bereich Beratung "
MonatCell = 4 + format(now,"mm")
Beratung = xlApp. _
Workbooks(1).WorkSheets(1).Cells(MonatCell,3).Value
Selection.TypeText Text:= Beratung
Selection.TypeParagraph
Copyright »
2000 by Robert Bonomo, Lerchenberg 9, 8046 Zürich
http://surf.to/bonomo
43
.XUVXQWHUODJHQ
3&6XSSRUWHU6,=
Nr
6
/HKUJDQJ3&6XSSRUWHU6,=
9,68$/%$6,&)25$33/,&$7,2169%$
Teilbereich
Lösungsweg
Umsatz Bereich Programmierung <<Programmierung>>
Die bestehende Prozedur DatenAustauschExcel() wird um folgenden Code erweitert::
7
Selection.TypeText Text:="Umsatz Bereich Programm. "
MonatCell = 4 + format(now,"mm")
Prog = xlApp.Workbooks(1).WorkSheets(1).cells(MonatCell,4).Value
Selection.TypeText Text:= Prog
Selection.TypeParagraph
Umsatz Monats-Total <<Monatstotal>>
Die bestehende Prozedur DatenAustauschExcel() wird um folgenden Code erweitert::
8
Selection.TypeText Text:="Umsatz Bereich Programm. "
MonatCell = 4 + format(now,"mm")
Prog = xlApp.Workbooks(1).WorkSheets(1).cells(MonatCell,5).Value
Selection.TypeText Text:= Prog
Selection.TypeParagraph
Bisheriger durchschnittlicher Monatsumsatz <<durchMonat>>
Die bestehende Prozedur DatenAustauschExcel() wird um folgenden Code erweitert::
9
Dim I as Integer
Dim Monatswert as Integer
For I = 1 to Format(now,"mm")
Monatswert = Monatswert + xlApp.Workbooks(1).WorkSheets(1).cells(4+I,5).Value
Next I
Monatswert= Monatswert / I
Selection.TypeText Text:="bisheriges Durchschnittstotal pro Monat:"
Selection.TypeText Text:= Monatswert
Selection.TypeParagraph
Bisheriger Jahresumsatz <<JahresUmsatz>>
Die bestehende Prozedur DatenAustauschExcel() wird um folgenden Code erweitert::
Dim a as Integer
Dim MonatsTotal as Integer
For a = 1 to Format(now,"mm")
MonatsTotal= Monatstotal + xlApp.Workbooks(1).WorkSheets(1).cells(4+a,5).Value
Next a
Selection.TypeText Text:="Monatstotal aufkumuliert :"
Selection.TypeText Text:= MonatsTotal
Selection.TypeParagraph
10 Das Excel File und die Excel Applikation werden im Anschluss auf die Datenübernahme
geschlossen
Die bestehende Prozedur DatenAustauschExcel() wird um folgenden Code erweitert::
xlApp.Quit
Set xlApp = Nothing
11 Das Winwordfile wird unter dem Filenamen "C:\Monat<<Monat>>.doc" abgespeichert.
Die bestehende Prozedur DatenAustauschExcel() wird um folgenden Code erweitert::
SaveFileName="c:\Monat" & format(now,"mm") & ".doc"
ActiveDocument.SaveAs FileName:= SaveFileName
:
Copyright »
2000 by Robert Bonomo, Lerchenberg 9, 8046 Zürich
http://surf.to/bonomo
44
.XUVXQWHUODJHQ
3&6XSSRUWHU6,=
Copyright »
/HKUJDQJ3&6XSSRUWHU6,=
9,68$/%$6,&)25$33/,&$7,2169%$
2000 by Robert Bonomo, Lerchenberg 9, 8046 Zürich
http://surf.to/bonomo
45
.XUVXQWHUODJHQ
3&6XSSRUWHU6,=
/HKUJDQJ3&6XSSRUWHU6,=
9,68$/%$6,&)25$33/,&$7,2169%$
Stichwortverzeichnis
Copyright »
2000 by Robert Bonomo, Lerchenberg 9, 8046 Zürich
http://surf.to/bonomo
46
.XUVXQWHUODJHQ
3&6XSSRUWHU6,=
/HKUJDQJ3&6XSSRUWHU6,=
9,68$/%$6,&)25$33/,&$7,2169%$
$
Auswahl, 5
Auto_Close, 8
Auto_Open, 8
0
Makro, 9
Makro-Rekorder, 8
Module, 2
%
Boolean, 3
&
2
Objekthierarchie, 10
Objektkatalog, 10
Offset, 11
Operatoren, 7
3
Compiler, 1
Currency, 3
'
date, 3
Datentypen, 3
Deklarationsteil, 2
Dialogboxen, 14
Dialoge, 14
Dim, 4
double, 3
private, 4
Programmiersprachen, 2
Prozeduren, 2
public, 4
5
Ranges, 11
(
Error, 13
EVA-Prinzip, 1
)
Folge, 5
FormulaR1C1, 12
Funktionen, 2
*
global, 4
6
Selection, 11
Semantik, 1
Semantikfehler, 12
single, 3
static, 4
string, 3
Syntax, 1
Syntaxfehler, 12
Systemdialoge, 15
9
Variant, 3
VBA-Modul, 9
,
Integer, 3
Interpreter, 1
Iteration, 6
:
Wiederholung, 6
.
Kontrollkonstrukte, 5
=
Zellen, 11
Zellreferenzen, 9
/
Laufzeitfehler, 13
Long, 3
Copyright »
2000 by Robert Bonomo, Lerchenberg 9, 8046 Zürich
http://surf.to/bonomo
47
.XUVXQWHUODJHQ
3&6XSSRUWHU6,=
Copyright »
/HKUJDQJ3&6XSSRUWHU6,=
9,68$/%$6,&)25$33/,&$7,2169%$
2000 by Robert Bonomo, Lerchenberg 9, 8046 Zürich
http://surf.to/bonomo
48