VBA für Excel 24.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 24.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

mailto:[email protected]



Ü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



VBA für Excel

Fragen zum gestrigen Tag?



Lernziele erreicht?

Idealerweise sollten Sie seit dem gestrigen Tag

• wissen, daß Excel ein Objektmodell anbietet;

• mit den Excel-Objekten umgehen können;

• das Objektmodell mithilfe von Objektkatalog und eingebau- ter Hilfe inspizieren können;

• erste Automatisierungsmakros programmieren können.



Lernziele des heutigen Tages

Idealerweise sollten Sie am Ende des heutigen Tages

• wissen, was “Ereignis-gesteuerte Programmierung” ist;

• den Unterschied zwischen Formular- und ActiveX- Steuerelementen kennen;

• Formular- und ActiveX-Steuerelemente einsetzen und programmieren können;



VBA für Excel

Teil III: Ereignis-gesteuerte Programmierung



Ereignis-gesteuerte Programmierung

es werden laufend „Ereignisse“ generiert, z.B. • Mausbewegungen • Tastendrücke • Systemereignisse (CD/SD einlegen, USB-Gerät wechseln) • abgeleitete Ereignisse (grafische Benutzeroberfläche)

Ereignisse werden in Warteschlange (event queue) abgelegt und der Reihe nach bearbeitet

Ereignisbehandlungsroutinen (event handler) müssen/sollten zügig terminieren, da sonst u.U. das System blockiert

unter VBA: Abbruch mit “Esc” bzw. “Alt-Break”



Ereignis-gesteuerte Programmierung (Fortsetzung)

z.B. Mausklicks



Ereignis-gesteuerte Programmierung (Fortsetzung)

Benutzer-definiert

bearbeiten

Excel-intern

bearbeiten

Ereignis der Warte-

schlange entnehmen

Während einer Ereignis-

behandlung ist Excel

blockiert!



Quellen für Ereignisse in Excel

Excel-eigene Objekte • Application • Workbook • Worksheet • (Chart) • QueryTable

Steuerelemente • Excel-interne Steuerelemente • ActiveX Controls



Welche Ereignisse erzeugt ein bestimmtes Objekt?

Nutzen Sie den Code Editor und die Hilfe



Wie schreibt man eine Ereignisroutine?

Nutzen Sie den Code Editor

der automatisch erzeugte „Rumpf“ enthält ggfs. eine Referenz auf die auslösende Komponente



Interessante Ereignisse des Application-Objektes

WorkbookBeforeSave vor dem Speichern einer Mappe WorkbookBeforePrint vor dem Ausdrucken einer Mappe WorkbookBeforeClose vor dem Schließen einer Mappe

SheetFollowHyperlink beim Anklicken eines Hyperlinks

SheetChange nach Änderung eines Blattes



Interessante Ereignisse des Workbook-Objektes

Open nach dem Öffnen Activate nach dem Aktivieren

BeforeSave vor dem Speichern BeforePrint vor dem Ausdrucken BeforeClose vor dem Schließen

SheetFollowHyperlink beim Anklicken eines Hyperlinks

NewSheet nach Anlegen eines Blattes SheetChange nach Änderung eines Blattes



Interessante Ereignisse des Worksheet-Objektes

Activate nach dem Aktivieren Deactivate nach Deaktivierung

Calculate nach Neuberechnungen Change nach Änderungen

FollowHyperlink nach Anklicken eines Hyperlinks

SelectionChange nach Änderung der Selektion



Interessante Ereignisse des QueryTable-Objektes

BeforeRefresh vor Aktualisierung einer Abfrage

AfterRefresh nach Aktualisierung einer Abfrage



VBA für Excel

Teil III: Steuerelemente & Eingabeformulare



Zwei Varianten von Steuerelementen (Controls)

Formular-Steuerelemente

• kleine, nicht erweiterbare Auswahl geringer Funktionalität

• in Excel integriert, dadurch stets vorhanden

• vermutlich Plattform-unabhängig (bitte stets vorher testen)

• keine Unterscheidung Entwurfsmodus<->Arbeitsmodus

ActiveX-Steuerelemente

• beliebig erweiterbare Auswahl (Drittanbieter)

• nicht nur für Excel, für alle (ActiveX-fähigen) Programme

• sehr vielseitig konfigurierbar, viele mögliche Events



Formular-Steuerelemente (Überblick)

Bezeichnungen (Label) Schaltflächen (Button) Drehfelder (SpinBox) Kontrollkästchen (Checkbox) Optionsfelder (Radiobutton) Gruppenfelder (Group) Listenfelder (Listbox) Kombinationsfelder (Combobox) Rollbalken (Scrollbar)



Formular-Steuerelemente (allg. Arbeitsschritte)

Anlegen eines Steuerelementes • Steuerelement im Menü wählen • mit linker Maustaste auf dem Tabellenblatt Umriß zeichnen • im Falle einer Schaltfläche: Makro wählen

Bearbeiten eines Steuerelementes • mit rechter Maustaste selektieren, danach • z.B. Verschieben oder Größe ändern • Text bearbeiten • Makro zuweisen • Steuerelement formatieren



Formular-Steuerelemente: Schaltflächen

rufen beim Anklicken das zugewiesene Makro auf

Änderung des Erscheinungs-

bildes mittels “Steuerelement

formatieren...”



Formular-Steuerelemente: Drehfelder

erlauben die Auswahl aus einem Zahlenbereich

rufen bei Wertänderung

das zugewiesene Makro

auf

aktueller Wert kann mit

Tabellenzelle verknüpft

werden (blenden Sie

diese Zelle ggfs. aus!)



Formular-Steuerelemente: Rollbalken

erlauben die Auswahl aus einem Zahlenbereich



auf



werden (blenden Sie




Formular-Steuerelemente: Kontrollkästchen

erlauben das An- und Abwählen



auf



werden (blenden Sie




Formular-Steuerelemente: Optionsfelder

erlauben das An- und Abwählen eines von mehreren Werten



auf



werden (blenden Sie


ggfs. gruppieren!



Formular-Steuerelemente: Gruppenfelder

dienen dem “Gruppieren“ anderer Steuerelemente

entweder aus optischen

Gründen oder

damit Optionsfelder

wissen, daß Sie zu-

sammengehören!



Formular-Steuerelemente: Kombinationsfelder

erlauben die Auswahl aus einer Werteliste



auf



werden (blenden Sie




Formular-Steuerelemente: Listenfelder

erlauben die Auswahl eines/(mehrerer) Werte aus einer Liste



auf



werden (blenden Sie




ActiveX-Steuerelemente (Überblick)

Bezeichnungen (Label) Schaltflächen (CommandButton) Umschaltflächen (ToggleButton) Drehfelder (SpinButton) Texteingabefelder (Textbox) ein- und mehrzeilig Kontrollkästchen (Checkbox) Optionsfelder (Radiobutton) Listenfelder (Listbox) Kombinationsfelder (Combobox) Grafikanzeigen (Image) Rollbalken (Scrollbar)

und beliebig mehr...



ActiveX-Steuerelemente (allg. Arbeitsschritte)

Anlegen eines oder mehrerer Steuerelemente • Entwurfsmodus wählen(!) • Steuerelement im Menü wählen • mit linker Maustaste auf dem Tabellenblatt Umriß zeichnen • evtl. weitere Steuerelemente anlegen

Bearbeiten eines Steuerelementes • z.B. mittels Doppelklick in den Code-Editor wechseln • im Eigenschaftenfenster Änderungen vornehmen • im Codefenster ggfs. Steuerelement wählen • gewünschtes Ereignis wählen und Routine schreiben

Verwenden eines Steuerelementes • Entwurfsmodus abwählen(!)



ActiveX-Steuerelemente in Excel

besitzen ein paar zusätzliche Eigenschaften, u.a. • LinkedCell Zelle, die aktuellen Wert aufnimmt • ListFillRange Zellen, die Listeninhalte vorgeben

manche Excel-Methoden sind in Ereignis-Routinen nicht verfügbar: Private Sub CommandButton1.Click Range("a1:a10").Sort Key1:=Range("a1") End Sub

Abhilfe: je nach Bedarf Tabellenzelle oder ActiveX-Steuer- element explizit aktivieren: Private Sub CommandButton1.Click Range("a1").Activate Range("a1:a10").Sort Key1:=Range("a1") CommandButton1.Activate End Sub



ActiveX-Steuerelemente in Excel

ActiveX-Steuerelemente per Programm hinzufügen ActiveSheet.OLEObjects.Add "Forms.CommandButton.1", _ Left:=10, Top:=10, Height:=20, Width:=100

ActiveX-Steuerelemente sind Bestandteil des Tabellenblattes: ActiveSheet.CommandButton1.Caption = "Click Me”

und zugleich OLE-Objekte ActiveSheet.OLEObjects(“CommandButton1”).Caption = "Click Me”



ActiveX-Steuerelemente: Labels

dienen der (statischen) Anzeige von

Texten (und Bildern), können aber

auch auf Mausklicks reagieren

wichtige Eigenschaften

• Name

• Caption, evtl. Picture

• Autosize

• Visible, evtl. BackStyle



ActiveX-Steuerelemente: Befehlsschaltflächen

dienen dem Anstoßen einer Aktion

wichtige Eigenschaften:

• Name


• Enabled



ActiveX-Steuerelemente: Umschaltflächen

springen nach dem Anklicken nicht

sofort wieder zurück


• Name


• Enabled

• Value, LinkedCell



ActiveX-Steuerelemente: Drehfelder

Auswahl eines Wertes aus

einem numerischen Bereich


• Name

• Orientation

• Enabled




ActiveX-Steuerelemente: Texteingabefelder

für ein- oder mehrzeilige Text-

eingabe – manchmal auch Anzeige


• Name

• MultiLine, Scrollbars

• Enabled, TextAlign, WordWrap

• Value oder Text, LinkedCell

• MaxLength, PasswordChar



für einfache Markierungen


• Name

• Caption, TextAlign, evtl. WordWrap

• Enabled, evtl. Picture, Alignment

• evtl. TripleState


ActiveX-Steuerelemente: Kontrollkästchen



für Auswahl aus einer statischen

Menge, keine Mehrfachauswahl


wie Kontrollkästchen, zusätzlich

• GroupName

ActiveX-Steuerelemente: Optionsfelder



Anzeige/Auswahl aus einer Liste

evtl. Mehrfachauswahl


• Name

• ListStyle, MultiSelect, ListFillRange

• Enabled, IntegralHeight

• Text oder Value, LinkedCell

ActiveX-Steuerelemente: Listenfelder



Füllen eines Listenfeldes

• aus Tabelle (ListFillRange)

• per Programm: ListBox.AddItem “neuer Eintrag”

Ermitteln der selektierten Werte

• bei Einfachselektion: ListBox.Value

• bei Mehrfachselektion:

for i = 0 to ListBox.ListCount – 1

if ListBox.Selected(i) then ...

next

Umgang mit Listenfeldern



Löschen von Elementen eines Listenfeldes

ListBox.RemoveItem (index)

ListBox.Clear

Anzahl der Elemente eines Listenfeldes

ListBox.Count

Zugriff auf einzelne Listenelemente

ListBox.List (index)

Umgang mit Listenfeldern (Fortsetzung)



Aufklappmenü (ggfs. editierbar!),

Einfachauswahl


• Name

• Style(!)

• ListFillRange

• Text oder Value, LinkedCell

ActiveX-Steuerelemente: Kombinationsfelder



ActiveX-Steuerelemente: Grafiken

Anzeige einer (einfachen) Grafik


• Name

• Picture, PictureAlignment,

• PictureSizeMode, PictureTiling

• SpecialEffect



ActiveX-Steuerelemente: Rollbalken

für (kontinuierliche) Auswahl

aus einem Zahlenbereich


• Name

• Orientation

• Min/Max

• SmallChange/LargeChange




VBA für Excel

Übungen



Übungen

programmieren Sie eine Endlosschleife, starten Sie sie und brechen Sie sie ab

Nutzen Sie für die nächsten Übungen Formular-Steuerelemente

erstellen Sie ein Drehfeld für Monatsnamen verknüpfen Sie das Steuerelement dazu mit einem (unsichtba- ren) Tabellenfeld und reagieren Sie auf Benutzereingaben, in- dem Sie das Feld auslesen und den zugehörigen Monatsnamen in einem anderen Feld anzeigen

erstellen Sie einen Rollbalken (mit passendem Wertebereich) und verwenden Sie den vom Benutzer gewählten Wert als Palettenindex für die Hintergrundfarbe



Übungen (Fortsetzung)

erstellen Sie ein Kontrollkästchen und lesen Sie den Wert aus

erstellen Sie mindestens 2 Optionsfelder, verknüpfen Sie diese mit demselben Tabellenfeld und lesen Sie den Wert aus

erstellen Sie ein Ausklappmenü für die Tage einer Woche

erstellen Sie eine (Auswahl-)Liste für die Monate eines Jahres




Ab sofort sollten Sie ActiveX-Steuerelemente verwenden - achtenSie auf den Entwurfsmodus!

erstellen Sie einen Label und weisen Sie ihm per Programm einen Wert zu: schreiben Sie zu diesem Zweck eine Ereignis- behandlungsroutine, die bei jedem Klick auf den Label einen Zähler erhöht und diesen im Label selbst anzeigt

erstellen Sie ein Drehfeld für Monatsnamen: lesen Sie in der Ereignisbehandlungsroutine den aktuellen Wert aus und zeigen Sie den zugehörigen Monatsnamen an




erstellen Sie einen Rollbalken (mit passendem Wertebereich) und verwenden Sie den vom Benutzer gewählten Wert als Palettenindex für die Hintergrundfarbe

erstellen Sie eine Umschaltfläche und setzen Sie diese nach zwei Sekunden per Programm zurück. Das Kommando für die Wartezeit lautet:

Application.Wait now() + TimeValue(„0:0:2“)




erstellen Sie ein Kontrollkästchen und zeigen Sie dessen Wert in der Ereignisbehandlungsroutine an

erstellen Sie mindestens zwei Optionsfelder, weisen Sie diesen denselben Gruppennamen zu und finden Sie heraus, welches Optionsfeld zu einem gegebenen Zeitpunkt gesetzt ist

erstellen Sie ein Listenfeld mit Einfachauswahl, laden Sie dieses aus einer Tabellenzeile oder -spalte und zeigen Sie das jeweils selektierte Element an

erstellen Sie ein Listenfeld mit Mehrfachauswahl und füllen Sie dieses in Ihrem Programm, zeigen Sie die Selektion an




erstellen Sie ein Aufklappmenü, füllen Sie dieses aus der Tabelle und zeigen Sie das ausgewählte Element an

erstellen Sie ein Texteingabefeld für max. 10 Zeichen und zeigen Sie den eingegebenen Text an

machen Sie daraus ein Feld für Paßworteingaben

beschränken Sie die eingebbaren Zeichen auf Ziffern

erstellen Sie ein mehrzeiliges Texteingabefeld und belegen Sie dieses mit „Bitte geben Sie hier Ihren Text ein“ vor. Zählen Sie die eingegebenen Zeichen




erstellen Sie ein Bildanzeigefeld und simulieren Sie damit (op- tisch) eine Umschaltfläche

programmieren Sie „Vanity rückwärts“: lassen Sie einen Text eingeben (nur Buchstaben und Leerzeichen) und geben Sie auf Knopfdruck aus, welche Ziffern auf einer Telefontaste gedrückt werden müßten, um die betreffenden Zeichen zu erhalten

1 = 2 = ABC 3 = DEF4 = GHI 5 = JKL 6 = MNO7 = PQRS 8 = TUV 9 = WXYZ

0 = (Leerzeichen)

Documents

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