VBA für Excel

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

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

3. VBA für Excel 26.02.2010 VBA für Excel Fragen zum gestrigen Tag? Andreas Rozek HyMeSys Software & Consulting

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

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

6. VBA für Excel 26.02.2010 VBA für Excel Teil V: Dateien und Dateisysteme Andreas Rozek HyMeSys Software & Consulting

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

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

9. VBA für Excel 26.02.2010 Excel-eigene Funktionen (Fortsetzung) • Umgebungsvariable auslesen ... = Environ("path") Andreas Rozek HyMeSys Software & Consulting

10. VBA für Excel 26.02.2010 VBA für Excel Teil V: Verwendung externer Komponenten Andreas Rozek HyMeSys Software & Consulting

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

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

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

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

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

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

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

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

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

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

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

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

23. VBA für Excel 26.02.2010 VBA für Excel Teil V: Fehlerbehandlungen Andreas Rozek HyMeSys Software & Consulting

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

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

26. VBA für Excel 26.02.2010 VBA für Excel Teil V: Emails verschicken Andreas Rozek HyMeSys Software & Consulting

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

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

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

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

31. VBA für Excel 26.02.2010 VBA für Excel Teil V: Web-Abfragen Andreas Rozek HyMeSys Software & Consulting

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

33. VBA für Excel 26.02.2010 Web-Abfragen manuell erzeugen Andreas Rozek HyMeSys Software & Consulting

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

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

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

37. VBA für Excel 26.02.2010 VBA für Excel Noch ein Schmankerl zum Schluß: Sudoku Andreas Rozek HyMeSys Software & Consulting

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

39. VBA für Excel 26.02.2010 Sudoku Makrozelle (Mikro)zelle Andreas Rozek HyMeSys Software & Consulting

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

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

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

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

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

45. VBA für Excel 26.02.2010 VBA für Excel Übungen Andreas Rozek HyMeSys Software & Consulting

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

47. VBA für Excel 26.02.2010 Übungen (Fortsetzung) • siehe Sudoku-Folien im Vorlesungsbereich Andreas Rozek HyMeSys Software & Consulting