slide1 l.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
VBA für Excel PowerPoint Presentation
Download Presentation
VBA für Excel

Loading in 2 Seconds...

play fullscreen
1 / 53

VBA für Excel - PowerPoint PPT Presentation


  • 263 Views
  • Uploaded on

VBA für Excel. 24.02.2010. 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 Rozek HyMeSys Software & Consulting Brunnenstraße 30/2 71032 Böblingen Telefon: (07031) 436 5784

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'VBA für Excel' - albert


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
slide1

VBA für Excel

24.02.2010

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 Rozek

HyMeSys Software & Consulting

Brunnenstraße 30/2

71032 Böblingen

Telefon: (07031) 436 5784

Email: A.Rozek@gmx.de

URL: www.Rozek.de

Andreas Rozek

HyMeSys Software & Consulting

slide2

VBA für Excel

24.02.2010

Überblick über den Kurs

Montag Grundlagen (Syntax & Semantik von VBA)

Dienstag Das Excel-Objektmodell

Mittwoch Ereignis-gesteuerte Programmierung Formular- und ActiveX-Steuerelemente

Donnerstag Eingabeformulare, Programmentwicklung

(Anmeldeformular, Zahlen-Memory)

Freitag weiterführende Themen (Email, Web, usw.)

Verwendung externer Objekte, Sudoku

Andreas Rozek

HyMeSys Software & Consulting

slide3

VBA für Excel

24.02.2010

VBA für Excel

Fragen zum gestrigen Tag?

Andreas Rozek

HyMeSys Software & Consulting

slide4

VBA für Excel

24.02.2010

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.

Andreas Rozek

HyMeSys Software & Consulting

slide5

VBA für Excel

24.02.2010

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;

Andreas Rozek

HyMeSys Software & Consulting

slide6

VBA für Excel

24.02.2010

VBA für Excel

Teil III: Ereignis-gesteuerte Programmierung

Andreas Rozek

HyMeSys Software & Consulting

slide7

VBA für Excel

24.02.2010

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”

Andreas Rozek

HyMeSys Software & Consulting

slide8

VBA für Excel

24.02.2010

Ereignis-gesteuerte Programmierung (Fortsetzung)

z.B. Mausklicks

Andreas Rozek

HyMeSys Software & Consulting

slide9

VBA für Excel

24.02.2010

Ereignis-gesteuerte Programmierung (Fortsetzung)

Ereignis der Warte-schlange entnehmen

Excel-internbearbeiten

Benutzer-definiertbearbeiten

Während einer Ereignis-behandlung ist Excel blockiert!

Andreas Rozek

HyMeSys Software & Consulting

slide10

VBA für Excel

24.02.2010

Quellen für Ereignisse in Excel

  • Excel-eigene Objekte • Application • Workbook • Worksheet • (Chart) • QueryTable
  • Steuerelemente • Excel-interne Steuerelemente • ActiveX Controls

Andreas Rozek

HyMeSys Software & Consulting

slide11

VBA für Excel

24.02.2010

Welche Ereignisse erzeugt ein bestimmtes Objekt?

  • Nutzen Sie den Code Editor und die Hilfe

Andreas Rozek

HyMeSys Software & Consulting

slide12

VBA für Excel

24.02.2010

Wie schreibt man eine Ereignisroutine?

  • Nutzen Sie den Code Editor der automatisch erzeugte „Rumpf“ enthält ggfs. eine Referenz auf die auslösende Komponente

Andreas Rozek

HyMeSys Software & Consulting

slide13

VBA für Excel

24.02.2010

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

Andreas Rozek

HyMeSys Software & Consulting

slide14

VBA für Excel

24.02.2010

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

Andreas Rozek

HyMeSys Software & Consulting

slide15

VBA für Excel

24.02.2010

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

Andreas Rozek

HyMeSys Software & Consulting

slide16

VBA für Excel

24.02.2010

Interessante Ereignisse des QueryTable-Objektes

  • BeforeRefresh vor Aktualisierung einer Abfrage
  • AfterRefresh nach Aktualisierung einer Abfrage

Andreas Rozek

HyMeSys Software & Consulting

slide17

VBA für Excel

24.02.2010

VBA für Excel

Teil III: Steuerelemente & Eingabeformulare

Andreas Rozek

HyMeSys Software & Consulting

slide18

VBA für Excel

24.02.2010

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

Andreas Rozek

HyMeSys Software & Consulting

slide19

VBA für Excel

24.02.2010

Formular-Steuerelemente (Überblick)

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

Andreas Rozek

HyMeSys Software & Consulting

slide20

VBA für Excel

24.02.2010

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

Andreas Rozek

HyMeSys Software & Consulting

slide21

VBA für Excel

24.02.2010

Formular-Steuerelemente: Schaltflächen

  • rufen beim Anklicken das zugewiesene Makro auf
  • Änderung des Erscheinungs- bildes mittels “Steuerelement formatieren...”

Andreas Rozek

HyMeSys Software & Consulting

slide22

VBA für Excel

24.02.2010

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!)

Andreas Rozek

HyMeSys Software & Consulting

slide23

VBA für Excel

24.02.2010

Formular-Steuerelemente: Rollbalken

  • 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!)

Andreas Rozek

HyMeSys Software & Consulting

slide24

VBA für Excel

24.02.2010

Formular-Steuerelemente: Kontrollkästchen

  • erlauben das An- und Abwählen
  • rufen bei Wertänderung das zugewiesene Makro auf
  • aktueller Wert kann mit Tabellenzelle verknüpft werden (blenden Sie diese Zelle ggfs. aus!)

Andreas Rozek

HyMeSys Software & Consulting

slide25

VBA für Excel

24.02.2010

Formular-Steuerelemente: Optionsfelder

  • erlauben das An- und Abwählen eines von mehreren Werten
  • rufen bei Wertänderung das zugewiesene Makro auf
  • aktueller Wert kann mit Tabellenzelle verknüpft werden (blenden Sie diese Zelle ggfs. aus!)
  • ggfs. gruppieren!

Andreas Rozek

HyMeSys Software & Consulting

slide26

VBA für Excel

24.02.2010

Formular-Steuerelemente: Gruppenfelder

  • dienen dem “Gruppieren“ anderer Steuerelemente
  • entweder aus optischen Gründen oder
  • damit Optionsfelder wissen, daß Sie zu- sammengehören!

Andreas Rozek

HyMeSys Software & Consulting

slide27

VBA für Excel

24.02.2010

Formular-Steuerelemente: Kombinationsfelder

  • erlauben die Auswahl aus einer Werteliste
  • rufen bei Wertänderung das zugewiesene Makro auf
  • aktueller Wert kann mit Tabellenzelle verknüpft werden (blenden Sie diese Zelle ggfs. aus!)

Andreas Rozek

HyMeSys Software & Consulting

slide28

VBA für Excel

24.02.2010

Formular-Steuerelemente: Listenfelder

  • erlauben die Auswahl eines/(mehrerer) Werte aus einer Liste
  • rufen bei Wertänderung das zugewiesene Makro auf
  • aktueller Wert kann mit Tabellenzelle verknüpft werden (blenden Sie diese Zelle ggfs. aus!)

Andreas Rozek

HyMeSys Software & Consulting

slide29

VBA für Excel

24.02.2010

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...

Andreas Rozek

HyMeSys Software & Consulting

slide30

VBA für Excel

24.02.2010

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(!)

Andreas Rozek

HyMeSys Software & Consulting

slide31

VBA für Excel

24.02.2010

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

Andreas Rozek

HyMeSys Software & Consulting

slide32

VBA für Excel

24.02.2010

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”

Andreas Rozek

HyMeSys Software & Consulting

slide33

VBA für Excel

24.02.2010

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

Andreas Rozek

HyMeSys Software & Consulting

slide34

VBA für Excel

24.02.2010

ActiveX-Steuerelemente: Befehlsschaltflächen

  • dienen dem Anstoßen einer Aktion
  • wichtige Eigenschaften: • Name • Caption, evtl. Picture • Enabled

Andreas Rozek

HyMeSys Software & Consulting

slide35

VBA für Excel

24.02.2010

ActiveX-Steuerelemente: Umschaltflächen

  • springen nach dem Anklicken nicht sofort wieder zurück
  • wichtige Eigenschaften: • Name • Caption, evtl. Picture • Enabled • Value, LinkedCell

Andreas Rozek

HyMeSys Software & Consulting

slide36

VBA für Excel

24.02.2010

ActiveX-Steuerelemente: Drehfelder

  • Auswahl eines Wertes aus einem numerischen Bereich
  • wichtige Eigenschaften: • Name • Orientation • Enabled • Value, LinkedCell

Andreas Rozek

HyMeSys Software & Consulting

slide37

VBA für Excel

24.02.2010

ActiveX-Steuerelemente: Texteingabefelder

  • für ein- oder mehrzeilige Text- eingabe – manchmal auch Anzeige
  • wichtige Eigenschaften: • Name • MultiLine, Scrollbars • Enabled, TextAlign, WordWrap • Value oder Text, LinkedCell • MaxLength, PasswordChar

Andreas Rozek

HyMeSys Software & Consulting

slide38

VBA für Excel

24.02.2010

ActiveX-Steuerelemente: Kontrollkästchen

  • für einfache Markierungen
  • wichtige Eigenschaften: • Name • Caption, TextAlign, evtl. WordWrap • Enabled, evtl. Picture, Alignment • evtl. TripleState • Value, LinkedCell

Andreas Rozek

HyMeSys Software & Consulting

slide39

VBA für Excel

24.02.2010

ActiveX-Steuerelemente: Optionsfelder

  • für Auswahl aus einer statischen Menge, keine Mehrfachauswahl
  • wichtige Eigenschaften: wie Kontrollkästchen, zusätzlich • GroupName

Andreas Rozek

HyMeSys Software & Consulting

slide40

VBA für Excel

24.02.2010

ActiveX-Steuerelemente: Listenfelder

  • Anzeige/Auswahl aus einer Liste evtl. Mehrfachauswahl
  • wichtige Eigenschaften: • Name • ListStyle, MultiSelect, ListFillRange • Enabled, IntegralHeight • Text oder Value, LinkedCell

Andreas Rozek

HyMeSys Software & Consulting

slide41

VBA für Excel

24.02.2010

Umgang mit Listenfeldern

  • 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

Andreas Rozek

HyMeSys Software & Consulting

slide42

VBA für Excel

24.02.2010

Umgang mit Listenfeldern (Fortsetzung)

  • Löschen von Elementen eines ListenfeldesListBox.RemoveItem (index)ListBox.Clear
  • Anzahl der Elemente eines ListenfeldesListBox.Count
  • Zugriff auf einzelne ListenelementeListBox.List (index)

Andreas Rozek

HyMeSys Software & Consulting

slide43

VBA für Excel

24.02.2010

ActiveX-Steuerelemente: Kombinationsfelder

  • Aufklappmenü (ggfs. editierbar!), Einfachauswahl
  • wichtige Eigenschaften: • Name • Style(!) • ListFillRange • Text oder Value, LinkedCell

Andreas Rozek

HyMeSys Software & Consulting

slide44

VBA für Excel

24.02.2010

ActiveX-Steuerelemente: Grafiken

  • Anzeige einer (einfachen) Grafik
  • wichtige Eigenschaften: • Name • Picture, PictureAlignment, • PictureSizeMode, PictureTiling • SpecialEffect

Andreas Rozek

HyMeSys Software & Consulting

slide45

VBA für Excel

24.02.2010

ActiveX-Steuerelemente: Rollbalken

  • für (kontinuierliche) Auswahl aus einem Zahlenbereich
  • wichtige Eigenschaften: • Name • Orientation • Min/Max • SmallChange/LargeChange • Value, LinkedCell

Andreas Rozek

HyMeSys Software & Consulting

slide46

VBA für Excel

24.02.2010

VBA für Excel

Übungen

Andreas Rozek

HyMeSys Software & Consulting

slide47

VBA für Excel

24.02.2010

Übungen

  • programmieren Sie eine Endlosschleife, starten Sie sie und brechen Sie sie abNutzen 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

Andreas Rozek

HyMeSys Software & Consulting

slide48

VBA für Excel

24.02.2010

Ü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

Andreas Rozek

HyMeSys Software & Consulting

slide49

VBA für Excel

24.02.2010

Übungen (Fortsetzung)

  • 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

Andreas Rozek

HyMeSys Software & Consulting

slide50

VBA für Excel

24.02.2010

Übungen (Fortsetzung)

  • 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“)

Andreas Rozek

HyMeSys Software & Consulting

slide51

VBA für Excel

24.02.2010

Übungen (Fortsetzung)

  • 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

Andreas Rozek

HyMeSys Software & Consulting

slide52

VBA für Excel

24.02.2010

Übungen (Fortsetzung)

  • 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

Andreas Rozek

HyMeSys Software & Consulting

slide53

VBA für Excel

24.02.2010

Übungen (Fortsetzung)

  • 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 = DEF 4 = GHI 5 = JKL 6 = MNO 7 = PQRS 8 = TUV 9 = WXYZ 0 = (Leerzeichen)

Andreas Rozek

HyMeSys Software & Consulting