VBA für Excel 22.02.2010 Andreas RozekHyMeSys Software & Consulting VBA für Excel eine Einführung in das Programmieren mit Visual Basic for Applications

VBA für Excel 22.02.2010

Andreas Rozek HyMeSys Software & Consulting

VBA für Excel

eine Einführung in das Programmieren mit „Visual Basic for Applications“ speziell (aber nicht nur) für Excel unter Windows

Andreas RozekHyMeSys Software & ConsultingBrunnenstraße 30/271032 Böblingen

Telefon: (07031) 436 5784Email: [email protected]: www.Rozek.de



Organisatorisches

• Theorie: von 900 Uhr bis 1200 Uhr

• Praxis: von 1300 Uhr bis 1700 Uhr

• Anwesenheitsliste → Teilnahmebestätigung

• begleitende Literatur

Christian Friedrich„Einstieg in VBA mit Excel“Galileo ComputingISBN 3-89842-647-5 ca. 2490 €



Zum Einstieg...



Zum Einstieg...



Überblick über den Kurs

Montag Grundlagen (Syntax & Semantik von VBA)

Freitag weiterführende Themen (Email, Web, usw.)Verwendung externer Objekte, Sudoku

Donnerstag Eingabeformulare, Programmentwicklung(Anmeldeformular, Zahlen-Memory)

Mittwoch Ereignis-gesteuerte ProgrammierungFormular- und ActiveX-Steuerelemente

Dienstag Das Excel-Objektmodell



Lernziele

Idealerweise sollten Sie am Ende dieses (Crash-)Kurses

• wissen, daß man die Funktionalität von Excel mit VBA erheblich erweitern kann;

• einen ungefähren Eindruck von den Möglichkeiten und Grenzen von VBA haben;

• in der Lage sein, eigene VBA-Makros zu schreiben und auszuführen;

• eigene Benutzeroberflächen (UserForms) erstellen und programmieren können.



Kursmaterialien

• Microsoft Excel: 60-Tage Testversion http://trial.trymicrosoftoffice.com/trialgermany/default.aspx

• Excel VBA Language Reference http://www.microsoft.com/technet/scriptcenter/topcis/office/vba.mspx

• Windows Script 5.6 Documentation http://www.microsoft.com/DOWNLOADS/details.aspx?familyid=01592C48-207D-4BE1-8A76-1C4099D7BBB9&displaylang=en

• OpenOffice.org Version 3.x http://de.openoffice.org/product/info.html



VBA für Excel

Teil I: Grundlagen



Ein paar Worte zu Excel

• Tabellen-artige Darstellung von Zahlen und Texten(!)

• Verarbeitung der Tabelleninhalte mithilfe von Formeln

• Visualisierung der Tabelleninhalte mithilfe von Diagrammen

Grenzen • komplexe Berechnungen, Textverarbeitung

• Zugriff auf Dateien, Datenbanken, das WWW (Data Mining)

• „Steuerung des Benutzers“ durch Benutzeroberflächen



Grenzen sprengen durch Makro-Programmierung

Macros

Interpreter

Application

System

Macros

Interpreter

Application

System

DLLs, ActiveX

• vorhandene Funktionalität durch (meist einfache) „Makros“ erweitern (sofern die Anwendung dies zuläßt)

• durch Integration externer Komponenten (z.B. DLLs oder

ActiveX Controls) zusätzliche Funktionalitäten möglich

• das Gros der Funktionalitäten kommt weiterhin von der

ursprgl. „Application“ (Wirtsprogramm)



Was ist „VisualBasic for Applications (VBA)“?

• VisualBasic

• für Anwendungen, Steuerelemente, Bibliotheken

• Übersetzung in Maschinensprache (bis Version 6)

• VBScript

• interpretierte Scriptsprache, z.B. für WSH, HTTP-Server, ...

• keine echten Datentypen, ActiveX-Steuerung

• VisualBasic for Applications

• interpretierte „Automatisierungssprache“ in Wirtsprogramm

• Zugriff auf Objektmodell des Wirtsprogrammes

• VisualBasic „Classic“ > VBA > VBScript



Wo und wie wird VBA (in Excel) eingesetzt?

• exploratives Prototyping (Anforderungen erkennen) • experimentelles Prototyping (Lösungsmöglichkeiten suchen)

• „Einmal“-Programme

(Konvertierung von Datenbeständen, Analysen, Studien)

• kleinere (häufig Firmen-interne) Projekte

• kleinere Anwendungen

Treibender Faktor ist stets: • Zeitersparnis (es ist fast schon alles vorhanden)



Einmal lernen – mehrmals nutzen

• (Visual)Basic-Syntax ist stets gleich

• die Objekt-Modelle ähneln sich, die Konzepte ohnehin

• Ereignis-orientierte Programmierung ist omnipräsent

VBA ist direkt einsetzbar in

• Microsoft's Office-Anwendungen unter Windows

• z.T. Microsoft's Office-Anwendungen unter MacOS X

• manchen Anwendungen von Drittanbietern

• OpenOffice 3.0 (evtl. auch unter MacOS X und Linux)



VBA für Excel

Excel als VBA-Entwicklungsumgebung(im Vergleich zu Java)



Excel als Entwicklungsumgebung

• Entwicklerwerkzeuge einblenden

• Sicherheitseinstellungen anpassen

• Makro-Rekorder (wird hier nicht behandelt)

• Persönliche Makroarbeitsmappe

c:\Dokumente und Einstellungen\benutzer\Anwendungsdaten\ Microsoft\Excel\XLSTART\Personal.xlsb



Java-Übersetzungseinheiten

• Klassen (eine Klasse pro Datei)

• Pakete (packages, als Verzeichnis oder JAR-Archiv)

• z.B. unter Eclipse: Projekte und zusätzliche Dateien

VBA (in Excel, Projekt-Explorer)

• „Projekte“ (Arbeitsmappen, persönliche Makroarbeitsmappe)

• „Excel-Objekte“ (Tabellenblätter u.a.) *.cls

• Formulare (UserForms) *.frm

• Standardmodule *.bas

• Klassenmodule *.cls



Excel als Entwicklungsumgebung

• VBA-Editor

• Eigenschaftenfenster

• Direktbereich

• Editor-Einstellungen

• Kontext-sensitive Hilfe (F1), IntelliSense

• Objektkatalog (F2)

• Modul-Ebene, Prozedur-Ebene, Code-Ebene



Excel als Entwicklungsumgebung (Fortsetzung)

• Ausführen einer Prozedur (F5)

• Haltepunkt setzen/löschen (F9)

• Einzelschritt (F8), Prozedurschritt (Shift-F8)

• Variablenwerte als Tool-Tip

• Lokalfenster: Variablen-Sichtung und -Verwaltung

• Überwachungsfenster: Überwachen von Ausdrücken

• Debug.print expression



VBA für Excel

Syntax und Semantik



Grundlegendes zur Syntax

• eine Anweisung pro Zeile (Zeilenende = Anweisungsende)

• : als Trenner zwischen zwei Anweisungen

• _ als (Zeilen-)Fortsetzungszeichen

• Groß-/Kleinschreibung ist nicht signifikant

• Code-Editor paßt Schreibweise automatisch an

• ' als Kommentarzeichen (für Zeilenkommentare)

• Code-Editor (vgl. Eclipse)

• beherrscht „IntelliSense“, „Syntax Colorization“

• gibt häufig bereits den Rahmen für eine Prozedur vor



Namen („Bezeichner“)

• 1...255 Zeichen

• erstes Zeichen muß ein Buchstabe sein

• danach sind Buchstaben, Ziffern und Unterstriche erlaubt

• Schlüsselworte sind zu meiden

• Groß-/Kleinschreibung wird nicht unterschieden

• ISO 8859-1 ist zulässig (Unicode?)



Datentypen

• Boolean 2 Bytes true oder false

• Byte 1 Byte 0...255

• Integer 2 Bytes -32768...+32767

• Long 4 Bytes -2147483648...+2147483647

• Single 4 Bytes ±3,402823e38...±1,401298e-45

• Double 8 Bytes ±1,79769313486231e308...

±4,94065645841247e-324

• Currency 8 Bytes -922337203685477,5808...

+922337203685477,5807



Datentypen (Fortsetzung)

• Decimal 14 Bytes (groß, Untertyp von Variant)

• Date 8 Bytes 01.01.100...31.12.9999

• Object 4 Bytes Referenz auf ein Objekt

• String 10+n Bytes variable Länge < 2147483648 Zeichen

• String n Bytes feste Länge 1...65535 Zeichen

• Variant 16 Bytes für Zahlen

• Variant 22+n Bytes für Zeichenketten



Datentypen-Suffixe

• Integer %

• Long &

• Single !

• Double #

• Currency @

• String $

Suffixe sind nicht Bestandteil des Variablennamens



Namenskonventionen

• vor allem für Variablen und Konstanten

• machen (Variablen-)Eigenschaften namentlich sichtbar

• allgemeine Form

[prefix]kind[name]

• Präfixe

• s = lokale, statische Variablen

• m = Modul-spezifische Variablen und Konstanten

• g = globale Variablen und Konstanten



Namenskonventionen (Fortsetzung)

• Variablen-Arten für Excel-Blätter

• wks Worksheet (Arbeitsblatt) sht

• cht Chart (Diagramm)

• frm Form (Dialog)

• bas Basic (Standardmodul) mdl

• cls Class (Klassenmodul)

• xl4 Excel4 (Makroblatt)

• im Falle einer Liste wird der Art ein „s“ angehängt




• Variablen-Arten für die verschiedenen Datentypen

• bln Boolean • sng Single

• cur Currency • str String

• dat Date • typ Type (Benutzerdefiniert)

• dbl Double • var Variant

• int Integer

• lng Long

• obj Object




• Variablen-Arten für die verschiedenen Steuerelemente

• lbl Label • mpg MultiPage

• txt Textbox • spn SpinButton

• cbo Combobox • scr Scrollbar

• lst Listbox • img Image

• chk Checkbox • ref RefEdit

• opt OptionButton • trv TreeView

• tgl ToggleButton • lsv ListView

• cmd CommandButton • cal Calendar

• tab TabStrip



Deklaration von Variablen

• implizit (durch Verwendung in einer Zuweisung) varValue = „implizit deklarierte Variable“

strValue$ = „implizit deklarierte String-Variable“

• explizit (irgendwo, aber vor der ersten Verwendung)

dim varValue dim strValue as string

dim|public|static name [as type] [, name [as type]]

• Verbieten impliziter Deklarationen

option explicit



Deklaration von Datenfeldern (Arrays)

• stets explizit

dim varArray() dim intArray(4) as Integer dim strArray(-3 to 3) as String

dim|public|static name (n | n to m [,...])[as type][,...]

• bis zu 60(!) Dimensionen möglich

• Änderung des impliziten Start-Index

option base 1

• Änderung der Feldgröße zur Laufzeit

redim varArray(3) redim preserve intArray(15)



Gültigkeitsbereich von Variablen und Konstanten

• Deklaration auf Modulebene (public)

• innerhalb des gesamten Modules sichtbar

• Variablenwert bleibt erhalten

• Deklaration auf Prozedurebene (dim)

• nur innerhalb der Prozedur sichtbar

• auf Modulebene deklarierte Variablen dürfen überdeckt werden

• statische Variablen behalten ihren Wert



Literale

• Zahlen 1234 1234e-56

• Zeichenketten „dies ist ein Text“

• Datum und Uhrzeit #21/07/2008 9:15:35#

Konstanten

const conValue = „Zeichenkette“const conValue = 1234const conValue = 1234e-56const conValue = #21/07/2008 9:15:35#

const conValue as String = „Zeichenkette“const conValue as Integer = 1234

const name [as type] = value



Arithmetische Operatoren

• Potenzierung ^ a ^ b

• Grundrechenarten + - * / a + b

• Ganzzahldivision \ a \ b

• Modulo-Division mod a mod b

• Negation - -a

• Potenzierung negativer Zahlen nur für ganzzahlige Exponenten

• Ganzzahldivision wandelt Argumente in Byte/Integer/Long

• Modulo-Division liefert Divisionsrest



Vergleichsoperatoren

• Vergleich < <= >= > a < b

• Gleichheit = a = b

• Ungleichheit <> a <> b

• Referenzvergleich is a is b

• Ähnlichkeit like a like pattern

• Zeichenkettenvergleich vergleicht ASCII-Codes

• like-Operator erlaubt Platzhalter (? *) im Vergleichsmuster



Logische Operatoren

ab: 00 10 01 11

• Konjunktion and a and b 0 0 0 1

• Äquivalenz eqv a eqv b 1 0 0 1

• Implikation imp a imp b 1 0 1 1

• Negation not not a

• Disjunktion or a or b 0 1 1 1

• Antivalenz xor a xor b 0 1 1 0



Verkettungsoperatoren

• Verkettung & a & b

• Verkettung + a + b

Operator-Hierarchie

• ^ • - • * / • \ • mod • + - • & • = <> < > <= >= is like • not and or xor eqv imp



Fallunterscheidungen

• if ... then ... else

if condition then [...] [else [...]]

if condition then ... [elseif condition then ...] [else ...] end if

• verschachtelbar



Fallunterscheidungen (Fortsetzung)

• select case

select case expression [case expression [,...] ...] [case n to m [,...] ...] [case is < | <= | > | >= | = | <> expression ...] [case else ...] end select

• kein break erforderlich!



Schleifen

• for ... next

for counter = first to last [step delta]

... [exit for]

next [counter]

• Zählvariable außerhalb der Schleife deklarieren

• Zählvariable innerhalb der Schleife nicht verändern!

• Vorsicht mit Zählvariablen vom Typ single oder double



Schleifen (Fortsetzung)

• for each

for each element in group

... [exit for]

next [element]

• „Gruppen“ können Arrays oder Collections sein

• auch mehrdimensionale Arrays zulässig

(niedrige Indices werden zuerst durchlaufen)




• do

do [while | until condition]

... [exit do]

loop

do

... [exit do]

loop [while | until condition]

• auch Endlosschleifen möglich




• while

while condition

...

wend

• bitte nicht mehr verwenden („deprecated“)

• kein Verlassen der Schleife möglich



Goto und Sprungmarken

• goto

goto line | label

• Sprungmarken

number label:

• alphanumerische Sprungmarken:

• erstes Zeichen muß ein Buchstabe sein

• danach können Buchstaben oder Ziffern folgen

• für on error goto von Bedeutung



Prozeduren: Subroutinen und Funktionen

• Subroutinen [private | public][static] sub name [(params)] ... [exit sub] end sub

• Parameterlisten [optional][byVal | byRef][ParamArray] name[()][as type][=def]

im Normalfall

[byVal | byRef] name[()][as type]

static läßt alle inneren Variablen einen Prozedur-Aufruf überdauern



Prozeduren (Fortsetzung)

• Funktionen [private | public ][static] function name [(params)][as type] ... [exit function] ... name = expression end function

• Sichtbarkeit von Prozeduren

• public von allen Modulen aller Projekte aus aufrufbar

• private nur aus dem eigenen Modul aus aufrufbar

(static hat nichts mit der Sichtbarkeit zu tun)



Aufruf von Prozeduren

Subroutinen [call] name [(] [argument [, argument]] [)]

Funktionen ... = name ([argument [, argument]])

Übergabe von Argumenten

byValue für Variablen und Ausdrücke, Übergabe einer Kopie

byRef nur für Variablen, Übergabe einer Referenz



Konvertierungsfunktionen

• CBool (expression) → Boolean • CByte (expression) → Byte • CCur (expression) → Currency • CDate (expression) → Date • CDbl (expression) → Double • CDec (expression) → Decimal • CInt (expression) → Integer • CLng (expression) → Long • CSng (expression) → Single • CVar (expression) → Variant • CStr (expression) → String



Konvertierungsfunktionen (Fortsetzung)

• Val (string-expression) → Zahl • Str (number-expression) → String • Hex (number-expression) → Hexadezimal-Darstellung • Oct (number-expression) → Oktal-Darstellung

• Asc (string-expression) → Zeichencode des ersten Zeichens • Chr (byte-expression) → Zeichen mit ggb. Zeichencode



Datentypen (o.ä.) überprüfen oder anzeigen

• IsArray (variable) ist Variable ein Feld? • IsDate (expression) ist Ausdruck vom Typ „Date“? • IsEmpty (expression) ist Ausdruck initialisiert? • IsError (expression) enthält Ausdruck ein Fehler-Objekt? • IsMissing (expression) ist optionales Argument vorhanden? • IsNull (expression) enthält Ausdruck nur Null-Werte? • IsNumeric (expression) ist Ausdruck numerisch? • IsObject (variable) enthält Variable eine Objekt-Referenz?

• TypeName (expression) liefert den Datentyp des Ausdruckes



Klassen

Instanzieren ja, Vererbung nein

Anlegen und Verwalten über den Code-Editor in eigenem Klassenmodul

nur Instanzen-Methoden und -Eigenschaften static bedeutet: alle Prozedur-internen Variablen überdauern einen Aufruf (erspart viele einzelne "static"s)

Selbst-Referenz me



Eigenschaftsprozeduren

Auslesen einer Eigenschaft [public | private] [static] property get name [(params)] as type ... [exit property] ... [name = expression] end property

Zuweisen eines Wertes an eine Eigenschaft [public | private] [static] property let name ([params,] value) ... [exit property] end property

Zuweisen einer Referenz an eine Eigenschaft [public | private] [static] property set name ([params,] reference) ... [exit property] end property



Konstruktor und Destruktor

Konstruktor private sub Class_initialize () end sub

Destruktor private sub Class_terminate () end sub

Selbst-Referenz me



Verwenden von Klassen und Objekten

Zuweisung von Referenzen dim name as Object set name = reference

Instanzieren einer Klasse set name = new class

Adressieren von Eigenschaften ... = instance.property instance.property = ...



Verwenden von Klassen und Objekten

Null-Referenz set name = nothing if expression is nothing then ...

with ... end with with instance ... end with

Vorsicht bei Schachtelungen!



Funktionen für die Ein- und Ausgabe

Ausgabe im Direktbereich debug.print argument

Meldungsfenster ... = MsgBox(prompt [, buttons] [, title]) Konstanten für „buttons“ und Rückgabewerte siehe Objektkatalog

Eingabefenster ... = InputBox(prompt [,title] [, default]) gibt immer eine Zeichenkette zurück



Mathematische Funktionen

abs (expression) Betrag atn (expression) Arcustangens cos (expression) Cosinus exp (expression) Exponentialfunktion fix (expression) ganzzahliger Anteil (Rundung gegen 0) int (expression) ganzzahliger Anteil (Rundung gegen -infty) log (expression) natürlicher Logarithmus rnd ([seed]) Zufallszahl sng (expression) Vorzeichenfunktion -1,0,+1 sin (expression) Sinus sqr (expression) Quadratwurzel tan (expression) Tangens



Zeichenketten-Funktionen

liegen z.T. in Byte- und Wort-Varianten vor (Unicode)

Zeichenkettenlänge Entfernen von Weißzeichen ... = len (str) ... = trim (str)

Zeichenkettenausschnitte ... = left(str,len) ... = mid(str,start,len) ... = right(str,len)

Zeichenkettenumwandlungen ... = lcase (str) ... = ucase (str)



Datum und Uhrzeit

• aktuelles Datum/aktuelle Uhrzeit

Date() Time() Now()

• Bestandteile eines Datums/einer Uhrzeit Day(date) Month(date) Year(date) Hour(time) Minute(time) Second(time)

• konkretes Datum/konkrete Uhrzeit DateSerial(year, month, day) TimeSerial(hour, minute, second)

• Wochentage und Monate Weekday(date [, firstdayofweek]) WeekdayName(date , abbreviated, firstdayofweek) MonthName(month [, abbreviated])



Übungen

spielen sich heute im Code-Editor und im Direktbereich ab

öffnen Sie Excel, nehmen Sie die Entwicklungseinstellungen

vor und öffnen Sie den Code-Editor

im Direktbereich: debug.print „Hello, World!“

debug.print sind zuviele Zeichen? schreiben Sie eine Sub-

routine namens „say“, die einen Zeichenketten-Parameter

erwartet und diesen mittels „debug.print“ ausgibt



Übungen (Fortsetzung)

machen Sie den Parameter optional (Default ist „“)

entfernen Sie den Default und prüfen Sie stattdessen in Ihrer

Prozedur, ob ein Argument übergeben wurde – falls nicht,

geben Sie „(null)“ aus

ersetzen Sie den einzelnen Parameter durch ein

„ParamArray“

und geben Sie alle Elemente dieses Feldes per debug.print aus

jetzt geben Sie alle Elemente in einer Zeile aus

als nächstes puffern Sie die übergebenen Texte zwischen und

implementieren einen Zeilenumbruch nach 80 Zeichen




legen Sie eine neue Klasse „Console“ an – diese wird gleich die

Konsolenausgabe vereinfachen

legen Sie eine private Klassen-Variable „Buffer“ an, die die je-

weilige Ausgbezeile zwischenspeichert, initialisieren Sie mit „“

legen Sie eine statische Methode „print“ an, die wie zuvor „say“

zunächst alle Ausgaben zwischenspeichert und nach max. 80

Zeichen per debug.print ausgibt (nicht aber, wenn die Zeile noch

nicht voll ist)

legen Sie eine weitere Methode „println“ an, die sich wie „print“

verhält, aber am Ende den Puffer-Inhalt ausschreibt




programmieren Sie einen elektronischen Würfel

versuchen Sie, das Würfelergebnis „quasi-grafisch“ in den

Direktbereich auszugeben

programmieren Sie einen Lottozahlengenerator

können Sie auch dessen Ausgabe „quasi-grafisch“ (wie auf

einem Lottoschein) anzeigen?




ermitteln Sie für ein gegebenes Datum die aktuelle Kalender-

woche – tragen Sie diese Funktion als zusätzliche Funktion in

Excel ein

ermitteln Sie für einen ggb. Monat die Anzahl der Arbeitstage

(zunächst noch ohne Feiertage)

legen Sie ein Feld von Feiertagen an (für ein ggb. Jahr) und

berücksichtigen Sie dieses bei der Ermittelung der Arbeitstage

wie spät ist es?

wie lange dauert es noch bis zum Ende dieses Kurses?




geben Sie mittels MsgBox eine Nachricht aus – suchen Sie

zuvor die Liste der Konstanten für die „buttons“ heraus

welchen Button hat der Benutzer gedrückt? Zeigen Sie ihn an

lassen Sie den Benutzer mittels InputBox einen Text eingeben

und zeigen Sie diesen an – was passiert, wenn der Benutzer

die Eingabe abbricht?

Documents

VBA für Excel 22.02.2010 Andreas RozekHyMeSys Software & Consulting VBA für Excel eine Einführung in das Programmieren mit Visual Basic for Applications