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 / 47

VBA für Excel - PowerPoint PPT Presentation


  • 230 Views
  • Uploaded on

VBA für Excel. 26.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' - jana


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

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

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

26.02.2010

VBA für Excel

Fragen zum gestrigen Tag?

Andreas Rozek

HyMeSys Software & Consulting

slide4

VBA für Excel

26.02.2010

Lernziele erreicht?

Idealerweise sollten Sie seit dem gestrigen Tag

• wissen, was ein “Eingabe-Formular” (UserForm) ist;

• UserForms erstellen und benutzen können;

• auch nicht-triviale Aufgaben wohldurchdacht lösen können; • sich bei der Erstellung eines “Produktes” nicht selbst im Wege stehen.

Andreas Rozek

HyMeSys Software & Consulting

slide5

VBA für Excel

26.02.2010

Lernziele des heutigen Tages

Idealerweise sollten Sie am Ende des heutigen Tages

• wissen, wie man mit externen Objekten umgeht;

• auf Dateisysteme und Dateien zugreifen können;

• Emails verschicken und Web-Abfragen bearbeiten können;

• in der Lage sein, zur Laufzeit auftretende Fehler korrekt

zu behandeln.

Andreas Rozek

HyMeSys Software & Consulting

slide6

VBA für Excel

26.02.2010

VBA für Excel

Teil V: Dateien und Dateisysteme

Andreas Rozek

HyMeSys Software & Consulting

slide7

VBA für Excel

26.02.2010

Excel-eigene Funktionen für Dateien & Dateisysteme

  • aktuelles Verzeichnis anzeigen ... = CurDir
  • Verzeichnis/Laufwerk wechseln (evtl. beides erforderlich!)ChDir "D:\Files\Excel"ChDrive "D:"
  • Präsenz einer Datei/eines Verzeichnisses prüfen ... = Dir("D:\Files\Excel" [, vbDirectory])
  • Dateigröße und -Änderungsdatum ermitteln ... = FileLen("D:\Files\Excel\Test.xls") ... = FileDateTime("D:\Files\Excel\Test.xls")

Andreas Rozek

HyMeSys Software & Consulting

slide8

VBA für Excel

26.02.2010

Excel-eigene Funktionen (Fortsetzung)

  • neues Verzeichnis anlegenMkDir "D:\Files\Excel"
  • bestehendes Verzeichnis löschenRmDir "D:\Files\Excel"
  • Datei kopierenFileCopy "D:\Files\orig.txt", "D:\Files\copy.txt"
  • Datei umbenennenName "D:\Files\old.txt" as "D:\Files\new.txt"
  • Datei löschenKill "D:\Files\obsolete.txt"

Andreas Rozek

HyMeSys Software & Consulting

slide9

VBA für Excel

26.02.2010

Excel-eigene Funktionen (Fortsetzung)

  • Umgebungsvariable auslesen ... = Environ("path")

Andreas Rozek

HyMeSys Software & Consulting

slide10

VBA für Excel

26.02.2010

VBA für Excel

Teil V: Verwendung externer Komponenten

Andreas Rozek

HyMeSys Software & Consulting

slide11

VBA für Excel

26.02.2010

Externe Objekte...

  • ...erweitern die Funktionalität von Excel durch vorgefertigte (und getestete) Funktionen
  • ...müssen explizit erzeugt (und später evtl. wieder vernichtet) werden
  • ...müssen auf dem Rechner, auf dem das Arbeitsblatt verwen- det werden soll, vorhanden sein!die vermutlich wichtigste Objekt-Bibliothek: Microsoft Scripting Runtime (“scripting.object”)(unter Windows XP stets vorhanden)

Andreas Rozek

HyMeSys Software & Consulting

slide12

VBA für Excel

26.02.2010

Verwaltung externer Objekte

  • externes Objekt erzeugendim FSO as Object set FSO = CreateObject("Scripting.FileSystemObject")
  • Präsenz des externen Objektes prüfen if (FSO is nothing) then ...
  • externes Objekt verwenden if (FSO.FolderExists("c:\Test")) then ...
  • externes Objekt vernichten (lassen) set FSO = nothing

Andreas Rozek

HyMeSys Software & Consulting

slide13

VBA für Excel

26.02.2010

Microsoft Scripting Runtime

  • Beschreibung siehe Windows Script 5.6 Documentationhttp://www.microsoft.com/DOWNLOADS/details.aspx?familyid=01592C48- 207D-4BE1-8A76-1C4099D7BBB9&displaylang=en
  • eigentlich für den WSH gedacht (VBScript/JScript) aber auch unter Excel/VBA nutzbringend einsetzbar
  • wichtige verfügbare Objekte • Dictionary assoziative Felder (“Hashtable”) • FileSystemObject Zugriff auf Dateisysteme und Dateien

Andreas Rozek

HyMeSys Software & Consulting

slide14

VBA für Excel

26.02.2010

Microsoft Scripting Runtime: Dictionary

  • Anlegen eines neuen Dictionarydim Table as Object set Table = CreateObject("Scripting.Dictionary")
  • wichtige Eigenschaften eines Dictionary • Table.Count Anzahl Tabelleneinträge (r/o) • Table.Item(key) Eintrag unter ggb. Schlüssel (r/w!)
  • wichtige Methoden eines Dictionary • Table.add(key,item) Eintrag hinzufügen/ändern • ... = Table.exists(key) Präsenz eines Eintrags prüfen • ... = Table.Items() Array mit allen Einträgen • ... = Table.Keys() Array mit allen Schlüsseln • Table.remove(key) Eintrag löschen • Table.removeAll() Dictionary leeren

Andreas Rozek

HyMeSys Software & Consulting

slide15

VBA für Excel

26.02.2010

Microsoft Scripting Runtime: FileSystemObject

  • wichtige vom FileSystemObject erzeugte Objekte • Drive repräsentiert ein Laufwerk (lokal / im Netzwerk) • Drives Liste aller verfügbaren Laufwerke • File repräsentiert eine Datei • Files Liste aller Dateien in einem Verzeichnis • Folder repräsentiert ein Verzeichnis • Folders alle Unterverzeichnisse in einem Verzeichnis • TextStream dient dem Lesen/Schreiben einer Datei

Andreas Rozek

HyMeSys Software & Consulting

slide16

VBA für Excel

26.02.2010

FileSystemObject: Grundfunktionen

  • auf existierende Laufwerke/Verzeichnisse/Dateien zugreifen set ... = FSO.getDrive("c") set ... = FSO.getFolder("c:\Dateien\Excel") set ... = FSO.getFile("c:\Dateien\Excel\Übungen.xls")
  • Verzeichnis anlegen set newFolder = FSO.createFolder("c:\Dateien\Excel")
  • auf Eigenschaften des Objektes zugreifen ... = FSO.getFile("c:\AutoExec.bat").DateLastModified

Andreas Rozek

HyMeSys Software & Consulting

slide17

VBA für Excel

26.02.2010

FileSystemObject: Drive

  • auf Laufwerk zugreifen set ... = FSO.getDrive(FSO.getDriveName("c:\Programme"))
  • verfügbarer Speicherplatz ... = DriveObject.TotalSize ... = DriveObject.AvailableSpace ... = DriveObject.FreeSpace
  • Art des Laufwerkes ... = DriveObject.DriveType
  • ist Laufwerk verfügbar? if (DriveObject.isReady) then ...

Andreas Rozek

HyMeSys Software & Consulting

slide18

VBA für Excel

26.02.2010

FileSystemObject: Verzeichnisse

  • Verzeichnis anlegen set ... = FSO.CreateFolder("c:\Excel")
  • Verzeichnis löschen FolderObject.Delete FSO.DeleteFolder("c:\Excel")
  • Verzeichnis verschieben FolderObject.Move("c:\newExcel") FSO.MoveFolder("c:\Excel","c:\newExcel")
  • Verzeichnis kopieren FolderObject.Copy("c:\newExcel") FSO.CopyFolder("c:\Excel","c:\newExcel")

Andreas Rozek

HyMeSys Software & Consulting

slide19

VBA für Excel

26.02.2010

FileSystemObject: Verzeichnisse (Fortsetzung)

  • existiert ein Verzeichnis? If (FSO.FolderExists("c:\Excel")) then ...
  • auf übergeordnetes Verzeichnis zugreifen set ... = FolderObject.ParentFolder()
  • Verzeichnisname ... = FolderObject.Name

Andreas Rozek

HyMeSys Software & Consulting

slide20

VBA für Excel

26.02.2010

FileSystemObject: Dateien

  • Datei löschen FileObject.Delete FSO.DeleteFile("c:\Excel\Übungen.xls")
  • Datei verschieben FileObject.Move("c:\newExcel\Übungen.xls") FSO.MoveFile("c:\Excel\Übung.xls","c:\newExcel\Übung.xls")
  • Datei kopieren FileObject.Copy("c:\newExcel\Übungen.xls") FSO.CopyFile("c:\Excel\Übung.xls","c:\newExcel\Übung.xls")
  • Datei umbenennen FileObject.Name = "newÜbung.xls"

Andreas Rozek

HyMeSys Software & Consulting

slide21

VBA für Excel

26.02.2010

FileSystemObject: Dateien schreiben

  • Datei anlegen bzw. öffnen set StreamObject = FSO.CreateTextFile("Output.txt") set StreamObject = FSO.OpenTextFile("Output.txt", 2)
  • Schreiben der Datei StreamObject.Write (...) ohne Zeilenvorschub StreamObject.WriteLine (...) mit Zeilenvorschub

Andreas Rozek

HyMeSys Software & Consulting

slide22

VBA für Excel

26.02.2010

FileSystemObject: Dateien lesen

  • Datei öffnen set StreamObject = FSO.OpenTextFile("Output.txt", 1)
  • Lesen der Datei ... = StreamObject.Read (n) n Zeichen lesen ... = StreamObject.ReadLine () bis Zeilenende ... = StreamObject.ReadAll ganze Datei auf einmal

Andreas Rozek

HyMeSys Software & Consulting

slide23

VBA für Excel

26.02.2010

VBA für Excel

Teil V: Fehlerbehandlungen

Andreas Rozek

HyMeSys Software & Consulting

slide24

VBA für Excel

26.02.2010

Fehlerbehandlung allgemein

  • Benutzereingaben müssen grundsätzlich kontrolliert werden: unerwartete Eingaben führen häufig zu Problemen
  • manchmal sind Eingaben zustandsabhängig, ggfs. Texteingabefelder sperren (Entry.enabled = false)
  • im Falle eines Laufzeitfehlers bricht ein Makro ohne weitere Vorkehrungen ab

Andreas Rozek

HyMeSys Software & Consulting

slide25

VBA für Excel

26.02.2010

Laufzeitfehler

  • kein try-catch-finally
  • on error resume ignoriert den Fehler und setzt die Makroausführung fort
  • on error goto ... springt im Falle eines Fehlers zu der angegebenen Marke
  • on error goto 0 schaltet Fehlerbehandlung wieder ab

Andreas Rozek

HyMeSys Software & Consulting

slide26

VBA für Excel

26.02.2010

VBA für Excel

Teil V: Emails verschicken

Andreas Rozek

HyMeSys Software & Consulting

slide27

VBA für Excel

26.02.2010

Emails versenden (Vorbemerkungen)

  • (externe) Voraussetzungen für den Versand von EMails • Outlook muß verfügbar sein (Outlook übernimmt Versand) • MailServer muß konfiguriert und erreichbar sein

Andreas Rozek

HyMeSys Software & Consulting

slide28

VBA für Excel

26.02.2010

Emails versenden (ohne Anhang)

dim Outlook as Outlook.Applicationdim Mail as Outlook.MailItem

set Outlook = Outlook.Application

set Mail = Outlook.createItem(olMailItem)

with Mail

.to = "addressat@xyz.de; noch@jemand.de"

.cc = "irgendwer@irgendwo.de"

.bcc = "spion@work.de"

.subject = "der Grund für die Mail"

.body = "hier kommt die eigentliche Nachricht"

.ReadReceiptRequested = true

.display .send

end with

Andreas Rozek

HyMeSys Software & Consulting

slide29

VBA für Excel

26.02.2010

Emails versenden (mit Anhang)

set Outlook = Outlook.Application

set Mail = Outlook.createItem(olMailItem)

with Mail

.to = "addressat@xyz.de; noch@jemand.de"

.subject = "der Grund für die Mail"

.body = "hier kommt die eigentliche Nachricht"

with .Attachments

.add ThisWorkbook.FullName

.add "c:\irgendeineDatei.txt"

end with

.send

end with

Andreas Rozek

HyMeSys Software & Consulting

slide30

VBA für Excel

26.02.2010

Emails versenden (als HTML-Seite)

set Outlook = Outlook.Application

set Mail = Outlook.createItem(olMailItem)

with Mail

.to = "addressat@xyz.de; noch@jemand.de"

.subject = "der Grund für die Mail"

.BodyFormat = olFormatHTML .HTMLBody = "<html><body>Dies ist HTML</body></html>"

.send

end with

Andreas Rozek

HyMeSys Software & Consulting

slide31

VBA für Excel

26.02.2010

VBA für Excel

Teil V: Web-Abfragen

Andreas Rozek

HyMeSys Software & Consulting

slide32

VBA für Excel

26.02.2010

Web-Abfragen (Vorbemerkungen)

  • ...holen sich eine per URL spezifizierte HTML-Datei
  • extrahieren eine/mehrere zuvor ausgewählte (HTML-)Tabellen
  • extrahieren die Zellen dieser (HTML-)Tabellen und plazieren sie in der ursprgl. Anordnung auf einer zuvor gewählten Excel-Tabelle
  • können automatisch aktualisiert werden

Andreas Rozek

HyMeSys Software & Consulting

slide33

VBA für Excel

26.02.2010

Web-Abfragen

manuell erzeugen

Andreas Rozek

HyMeSys Software & Consulting

slide34

VBA für Excel

26.02.2010

Web-Abfragen manuell erzeugen

  • gewünschte Web-Seite muß Tabellen enthalten, benötigte Tabelle(n) im Fenster auswählen
  • Ziel und Ausgabe-/Aktualisierungs- parameter festlegen
  • Excel merkt sich, daß die Zellen aus einer Web-Abfrage gefüllt wurden => Aktualisieren über Kontextmenü

Andreas Rozek

HyMeSys Software & Consulting

slide35

VBA für Excel

26.02.2010

Web-Abfragen per Makro erzeugen

with ActiveSheet.QueryTables.Add _

Connection:="URL;http://...",Destination:=Range("B2")

.Name = "Testabfrage"

.RefreshOnFileOpen = false

.BackgroundQuery = true

.RefreshStyle = xlInsertDeleteCells

.SavePassword = true

.SaveData = true

.RefreshPeriod = 0

.WebTables = "2,3"

.refresh BackgroundQuery:=false

end with

Andreas Rozek

HyMeSys Software & Consulting

slide36

VBA für Excel

26.02.2010

Web-Abfragen aktualisieren/löschen

  • bereits erstellte Abfrage aktualisieren on error goto onError ActiveSheet.Range("B2").QueryTable.Refresh exit sub onError: ...
  • alle Abfragen aktualisierenActiveWorkbook.refreshAll
  • Abfrage löschen Range("B2").currentRegion.delete Range("B2").QueryTable.delete

Andreas Rozek

HyMeSys Software & Consulting

slide37

VBA für Excel

26.02.2010

VBA für Excel

Noch ein Schmankerl zum Schluß: Sudoku

Andreas Rozek

HyMeSys Software & Consulting

slide38

VBA für Excel

26.02.2010

Sudoku

“...ist es das Ziel, ein 9x9-Gitter mit den Ziffern 1 bis 9 so zu füllen, dass jede Ziffer in einer Spalte, in einer Reihe und in einem Block (3x3-Unterquadrat) nur einmal vorkommt. Ausgangspunkt ist ein Gitter, in dem bereits mehrere Ziffern vorgegeben sind.”

(aus: Wikipedia)

Andreas Rozek

HyMeSys Software & Consulting

slide39

VBA für Excel

26.02.2010

Sudoku

Makrozelle

(Mikro)zelle

Andreas Rozek

HyMeSys Software & Consulting

slide40

VBA für Excel

26.02.2010

Sudoku

  • erstellen Sie die Bedienoberfläche für das Spiel und denken Sie sich eine Numerierung für die verschiedenen Zellentypen aus
  • schreiben Sie die Methode zum Löschen aller Mikrozellen (Löschen Sie dabei auch die Hintergrundfarbe)

Andreas Rozek

HyMeSys Software & Consulting

slide41

VBA für Excel

26.02.2010

Backtracking

  • systematisches Durchforsten eines Lösungsbaumes
  • vgl. hilfsmittelfreies Wandern durch ein Labyrinth ohne Zyklen
  • etwas formaler • Durchforsten eines zyklenfreien gerichteten Graphen • bei Erfolg: vorwärts! • bei Mißerfolg: zurück und nächste Alternative • wird meist rekursiv implementiert • merken Sie sich den zurückgelegten “Weg”

Andreas Rozek

HyMeSys Software & Consulting

slide42

VBA für Excel

26.02.2010

Sudoku: Prüfen des Wertes einer Mikrozelle

  • bauen Sie eine Liste von (in der aktuellen Zeile/Spalte/Makro- zelle) verwendeten Ziffern auf
  • sobald eine Ziffer in dieser Liste doppelt auftaucht, markieren Sie die Mikrozelle rot
  • nutzen Sie Ihre Erfahrung aus dem Memory-Spiel!
  • ein ausgefülltes Sudoku-Spiel finden Sie z.B. In Wikipedia

Andreas Rozek

HyMeSys Software & Consulting

slide43

VBA für Excel

26.02.2010

Sudoku: Füllen eines leeren Spielfeldes

  • erstellen Sie für jede Mikrozelle eine zufällige Permutation der Ziffernfolge 1,2,...9 – dies sind die prinzipiell möglichen Werte für diese Zelle
  • füllen Sie das Spielfeld von links oben nach rechts unten • entfernen Sie alle in der aktuellen Zeile/Spalte/Makrozelle bereits verwendeten Ziffern aus der o.g. Liste • aus den verbleibenden Möglichkeiten dürfen Sie wählen

Andreas Rozek

HyMeSys Software & Consulting

slide44

VBA für Excel

26.02.2010

Sudoku: Komplettieren eines Spielfeldes

  • wie zuvor, nur daß • die vorgegebenen Ziffern beim Ausfüllen grundsätzlich berücksichtigt werden müssen • die vorgegebenen Zellen unangetastet bleiben

Andreas Rozek

HyMeSys Software & Consulting

slide45

VBA für Excel

26.02.2010

VBA für Excel

Übungen

Andreas Rozek

HyMeSys Software & Consulting

slide46

VBA für Excel

26.02.2010

Übungen

  • testen Sie folgende in Excel eingebaute Dateifunktionen Arbeitsmappenpfad: thisWorkbook.Path Arbeitsmappenname: thisWorkbook.Name

Andreas Rozek

HyMeSys Software & Consulting

slide47

VBA für Excel

26.02.2010

Übungen (Fortsetzung)

  • siehe Sudoku-Folien im Vorlesungsbereich

Andreas Rozek

HyMeSys Software & Consulting