1 / 39

VBA für Excel

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

conway
Download Presentation

VBA für Excel

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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. VBA für Excel 23.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 23.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 23.02.2010 VBA für Excel Fragen zum gestrigen Tag? Andreas Rozek HyMeSys Software & Consulting

  4. VBA für Excel 23.02.2010 VBA für Excel Teil II: das Excel-Objektmodell Andreas Rozek HyMeSys Software & Consulting

  5. VBA für Excel 23.02.2010 VBA ist eine Automatisierungssprache • was kann automatisiert werden? VBA „lebt“ in einem Wirtsprogramm • wie kann automatisiert werden? das Wirtsprogramm stellt seine Bestandteile (bzw. die seiner Dokumente) in Form von (hierarchisch organisierten) Objekten zur Verfügung • warum soll automatisiert werden? • Anwendungsentwickler müssen/können nicht jeden Anwen- dungsfall voraussehen • Benutzeroberfläche muß nicht mit unnötigen Funktionen überladen werden • soll der Anwender doch selber etwas beitragen Andreas Rozek HyMeSys Software & Consulting

  6. VBA für Excel 23.02.2010 Lernziele Idealerweise sollten Sie am Ende des heutigen Tages • 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

  7. VBA für Excel 23.02.2010 Das Excel Objektmodell Andreas Rozek HyMeSys Software & Consulting

  8. VBA für Excel 23.02.2010 Objektmodelle allgemein • Document Object Model (DOM) • HTML-Dokumente (vulgo: “Web-Seiten”) • XML-Dokumente • u.v.a. (Open Document Format) • Object Model wenn nicht nur Dokumente beschrieben werden • sind heutzutage allgegenwärtig (merken Sie sich den Begriff – es steckt nichts „magisches“ dahinter) Andreas Rozek HyMeSys Software & Consulting

  9. VBA für Excel 23.02.2010 Das Excel Objektmodell • Application Excel selbst • Workbook(s) Arbeitsmappen • Worksheet(s) Tabellenblätter • Row(s) Tabellenzeilen • Column(s) Tabellenspalten • Range(s), Cell(s) Tabellenbereiche, einzelne Zellen • Objekte sind vorhanden und müssen nur benutzt werden! • alle Objekte haben Eigenschaften und Methoden Andreas Rozek HyMeSys Software & Consulting

  10. VBA für Excel 23.02.2010 Nutzen Sie den Objekt-Katalog! ...und die eingebaute Hilfe! Andreas Rozek HyMeSys Software & Consulting

  11. VBA für Excel 23.02.2010 Grundlagen des Objektmodells • Application • ist der Ausgangspunkt aller Referenzen und... • ...muß deshalb häufig nicht explizit notiert werden • [Application.]ActiveWorkbook referenziert die derzeit aktive Arbeitsmappe • [Application.]ThisWorkbook referenziert die Arbeitsmappe mit dem derzeit ausgeführten VBA-Makro Andreas Rozek HyMeSys Software & Consulting

  12. VBA für Excel 23.02.2010 Erste Experimente... • Anlegen einer neuen ArbeitsmappeWorkbooks.Add (Aufruf einer Methode) • Zugriff auf das erzeugte Objekt (u.a. Eigenschaften setzen)Set newBook = Workbooks.Add With newBook .Title = "mein Titel" .Subject = "meine Beschreibung" .SaveAs Filename:="Hurra.xls" End With Andreas Rozek HyMeSys Software & Consulting

  13. VBA für Excel 23.02.2010 Ups – was war denn das? • Explizites Benennen von ArgumentennewBook.SaveAs Filename:="Hurra.xls" • Parameterreihenfolge nicht mehr relevant • nicht anzugebene Parameter einfach ignorieren • Parameternamen siehe IntelliSense oder eingebaute Hilfe • Parameterlisten weiterhin wie gewohnt per Komma separierenWorkBooks.Open FileName:=“Hurra.xls“, ReadOnly:=True • allerdings: einmal benannt, immer benannt (im selben Aufruf) Andreas Rozek HyMeSys Software & Consulting

  14. VBA für Excel 23.02.2010 Weitere wichtige Workbook-Methoden • “Aktivieren” einer ArbeitsmappeWorkbooks(...).Activate • Drucken einer ArbeitsmappeWorkbooks(...).Printout • Sichern einer ArbeitsmappeWorkbooks(...).SaveWorkbooks(...).SaveAs „c:\Hurra.xls“ • Schließen einer ArbeitsmappeWorkbooks(...).Close Andreas Rozek HyMeSys Software & Consulting

  15. VBA für Excel 23.02.2010 Weitere wichtige Workbook-Methoden (Fortsetzung) • Exportieren einer ArbeitsmappeActiveWorkbook.SaveAs Filename:="c:\Hurra.html", _ FileFormat:=xlHTML • mit Optionen (und Sicherung auf HTTP-Server) With ActiveWorkbook With .WebOptions .AllowPNG = True .PixelsPerInch = 96 End With With .PublishObjects(1) .FileName = "http://example.server.de/Hurra.html" .Publish End With End With Andreas Rozek HyMeSys Software & Consulting

  16. VBA für Excel 23.02.2010 Iterieren durch eine Auflistung • mit for-next-Schleife dim i as integer for i = 1 to Application.Workbooks.Count debug.print Application.Workbooks(i).Name next • mit for-each-Schleife dim Item as Workbook for each Item in Application.Workbooks debug.print Item.Name next Andreas Rozek HyMeSys Software & Consulting

  17. VBA für Excel 23.02.2010 Unterdrücken von Warnmeldungen (Vorsicht!) • Sichern und Schließen einer Arbeitsmappe ActiveWorkbook.Close SaveChanges:=True • Verwerfen von Änderungen & Schließen einer Arbeitsmappe Application.DisplayAlerts = False ActiveWorkbook.Close Application.DisplayAlerts = True • Vorsicht! vergessen Sie nie, DisplayAlerts wieder zu aktivieren! Vorsicht bei Programmabbrüchen, während der Fehler- suche usw. Andreas Rozek HyMeSys Software & Consulting

  18. VBA für Excel 23.02.2010 Tabellenblätter • Aktivierung (nicht nur von Tabellenblättern) ActiveWorkbook.Worksheets(...).Activate ActiveWorkbook.Sheets(...).Activate • Anlegen eines neuen Tabellenblattes ActiveWorkbook.Worksheets.Add ActiveWorkbook.Worksheets.Add After:=WorkSheets(...) ActiveWorkbook.Worksheets.Add Before:=WorkSheets(...) • Löschen eines Tabellenblattes ActiveWorkbook.Worksheets(...).Delete • Anzahl Tabellenblätter ActiveWorkbook.Worksheets.Count • Tabellenblätter umbenennen ActiveWorkbook.Worksheets(...).Name = „Hurra“ Andreas Rozek HyMeSys Software & Consulting

  19. VBA für Excel 23.02.2010 Tabellenblätter (Fortsetzung) • “Codenamen” (nicht nur von Tabellenblättern) ActiveWorkbook.Worksheets(...).Codename = “meineWelt” meineWelt.Activate • Selektieren (nicht nur) eines Tabellenblattes ActiveWorkbook.Worksheets(...).Select • Selektieren (nicht nur) mehrerer Tabellenblätter ActiveWorkbook.Worksheets(Array(1,2,3)).Select • Selektieren (nicht nur) aller Tabellenblätter ActiveWorkbook.Worksheets.Select • Liste selektierter Tabellenblätter ActiveWindow.SelectedSheets • Tabellenblätter ein-/ausblenden ActiveWorkbook.Worksheets(...).Visible = False ActiveWorkbook.Worksheets(...).Visible = xlVeryHidden Andreas Rozek HyMeSys Software & Consulting

  20. VBA für Excel 23.02.2010 Tabellenblätter (Fortsetzung) • Tabellenblätter kopieren ActiveWorkbook.Worksheets(...).Copy After:= _ ActiveWorkbook.Worksheets(...) ActiveWorkbook.Worksheets(...).Copy Before:= _ ActiveWorkbook.Worksheets(...)Probieren Sie den Aufruf 'mal ohne Before/After • Tabellenblätter verschieben ActiveWorkbook.Worksheets(...).Move After:= _ ActiveWorkbook.Worksheets(...) ActiveWorkbook.Worksheets(...).Move Before:= _ ActiveWorkbook.Worksheets(...) • Tabellenblätter ausdrucken Application.Worksheets(...).Printout Application.Worksheets.Printout Copies:=1 Andreas Rozek HyMeSys Software & Consulting

  21. VBA für Excel 23.02.2010 Zellen und Zellbereiche • Zeilen und Spalten ActiveSheet.Columns(1) ActiveSheet.Columns(“a”) ActiveSheet.Rows(1) ActiveSheet.Rows(“7”) • Zeilen und Spalten einfügen ActiveSheet.Columns(“f”).Insert ActiveSheet.Rows(5).Insert ActiveSheet.Columns(“f”).Insert Shift:=xlShiftToRight ActiveSheet.Rows(5).InsertShift:=xlShiftDown Andreas Rozek HyMeSys Software & Consulting

  22. VBA für Excel 23.02.2010 Zellen und Zellbereiche (Fortsetzung) • mehrere Zeilen und Spalten einfügen ActiveSheet.Columns(“c:g”).Insert ActiveSheet.Rows(“1:5”).Insert • Zeilen und Spalten löschen ActiveSheet.Columns(“h”).Delete ActiveSheet.Rows(7).Delete ActiveSheet.Columns(“f”).Delete Shift:=xlShiftToLeft ActiveSheet.Rows(5).DeleteShift:=xlShiftUp Andreas Rozek HyMeSys Software & Consulting

  23. VBA für Excel 23.02.2010 Zellen und Zellbereiche (Fortsetzung) • Zeilen und Spalten ein- und ausblenden ActiveSheet.Columns(“a:f”).Hidden = True ActiveSheet.Rows(“4:5”).Hidden = False • alle Zeilen und Spalten ein- und ausblenden ActiveSheet.Columns.Hidden = False ActiveSheet.Rows.Hidden = True Andreas Rozek HyMeSys Software & Consulting

  24. VBA für Excel 23.02.2010 Zellen und Zellbereiche (Fortsetzung) • Zeilen und Spalten selektieren ActiveSheet.Columns(“g”).Select ActiveSheet.Rows(“1:3”).Select • nicht zusammenhängende Bereiche selektieren ActiveSheet.Range(“a:a,d:d,e:g”).Select ActiveSheet.Range(“2:2,4:4,7:9”).Selectimmer A1-Bezugsart ActiveSheet.Range(“a3”).Select ActiveSheet.Range(“a3:g7”).Select ActiveSheet.Range(“a3,d4,g7”).Select ActiveSheet.Range(“a3:d5,e4:g5”).Select Andreas Rozek HyMeSys Software & Consulting

  25. VBA für Excel 23.02.2010 Zellen und Zellbereiche (Fortsetzung) • alternative Adressierungsformen ActiveSheet.Range(“a3”,”d5”).Select ActiveSheet.Range(Cells(3,1),Cells(5,4)).Select • Selektion abfragen ActiveSheet.Selectionliefert Range-Objekt mit allen selektierten Bereichen • spezielle Zellen ermitteln ActiveSheet.Cells.SpecialCells(xlCellTypeFormula) ActiveSheet.Cells.SpecialCells(xlCellTypeBlank) Andreas Rozek HyMeSys Software & Consulting

  26. VBA für Excel 23.02.2010 Zellformate • Vorbemerkung dim aCell as Range Set aCell = ActiveSheet.Range(“a3”) • Hintergrundfarben und Muster aCell.Interior.ColorIndex = 1-56 aCell.Interior.Color = RGB(r,g,b) aCell.Interior.Pattern = 1-18 aCell.Interior.PatternColorIndex = 1-56 • Rahmen aCell.Borders.ColorIndex = 1-56 aCell.Borders.LineStyle = xlContinuous aCell.Borders.Weight = xlThin Andreas Rozek HyMeSys Software & Consulting

  27. VBA für Excel 23.02.2010 Textformate (von Zelleninhalten) • Vorbemerkung dim aFont as Font Set aFont = ActiveSheet.Range(“a3”).Font • Font-Eigenschaften aFont.Name = “Arial” aFont.Size = 18 aFont.Bold = True aFont.Italic = True aFont.Underline = True aFont.Strikethrough = True aFont.Shadow = True aFont.Subscript = True aFont.Superscript = True Andreas Rozek HyMeSys Software & Consulting

  28. VBA für Excel 23.02.2010 Zelleninhalte • Inhalte von einer oder mehreren Zellen ActiveSheet.Range(“a3”).Value = “z.B. Text” ActiveSheet.Range(“a3:b6”).Value • Existenz von Zelleninhalten prüfen isEmpty(ActiveSheet.Range(“a3”).Value)nur für eine einzelne Zelle! • Formelinhalte (aCell.hasFormula) aCell.Formula aCell.FormulaR1C1 aCell.FormulaLocal aCell.FormulaR1C1LocalWorksheetFunction. enthält Excel-interne Funktionen Andreas Rozek HyMeSys Software & Consulting

  29. VBA für Excel 23.02.2010 Noch ein paar Bemerkungen zu „Value“ • zuweisbare Werte (achten Sie auf das erste Zeichen) aCell.Value = 1234e56 aCell.Value = “1234e56” aCell.Value = “'1234e56” aCell.Value = “=now()” Andreas Rozek HyMeSys Software & Consulting

  30. VBA für Excel 23.02.2010 Vor den Übungen... • Befehlsschaltflächen • einsetzen • beschriften • Makro zuweisen • Diagramme manuell anlegen • Datenquelle zuweisen • beschriften und formatieren Andreas Rozek HyMeSys Software & Consulting

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

  32. VBA für Excel 23.02.2010 Dieses Beispiel sollten Sie jetzt verstehen... Andreas Rozek HyMeSys Software & Consulting

  33. VBA für Excel 23.02.2010 Hier ist der Quelltext... Andreas Rozek HyMeSys Software & Consulting

  34. VBA für Excel 23.02.2010 VBA für Excel Übungen Andreas Rozek HyMeSys Software & Consulting

  35. VBA für Excel 23.02.2010 Übungen • legen Sie für jede Übung ein neues Tabellenblatt an • legen Sie eine Befehlsschaltfläche auf dieses Blatt und be- nutzen Sie dieses, um Ihr Makro auszuführen • erstellen Sie eine Übersicht über die zu den einzelnen (Farb-) Indices gehörenden Farben (durch Einfärben von Zellen in 4 Reihen à 14 Spalten) • erstellen Sie eine Übersicht über die eingebauten Muster (z.B. in 2 Reihen à 9 Spalten) Andreas Rozek HyMeSys Software & Consulting

  36. VBA für Excel 23.02.2010 Übungen (Fortsetzung) • nehmen Sie den Würfel von gestern und stellen Sie das Ergebnis auf einer Excel-Tabelle grafisch dar (z.B. durch farbiges Markieren passender quadratischer Excel-Zellen) • nehmen Sie das Lotto-Programm von gestern und stellen Sie den Lottoschein auf einer Excel-Tabelle dar • prüfen Sie die Gleichverteilung des Excel-Zufallsgenerators (nehmen Sie z.B. 100 Intervalle, legen Sie das Diagramm zu- nächst manuell an und erzeugen Sie die zugehörigen Werte automatisch) Andreas Rozek HyMeSys Software & Consulting

  37. VBA für Excel 23.02.2010 Übungen (Fortsetzung) • legen Sie einen Jahreskalender an (12 Spalten mit je bis zu 31 Zeilen) – tragen Sie nach dem Monatsdatum jeweils noch den Wochentag ein und markieren Sie Samstag und Sonntag extra • legen Sie zusätzlich ein Feld von Feiertagen an und heben Sie diese im Kalender farbig hervor • dim Holidays as Variant Holidays = Array(“24/12/2009”,“25/12/2009”,“26/12/2009”) Andreas Rozek HyMeSys Software & Consulting

  38. VBA für Excel 23.02.2010 Übungen (Fortsetzung) • erweitern Sie die Feiertagsanzeige um die Namen der Feiertage (und tragen Sie diese in den Kalender ein) • dim Holidays as Variant Holidays = Array( _ “24/12/2008”,”Heilig Abend”, _ “25/12/2008”,”Weihnachten” _ “26/12/2008”,”Weihnachten” _ ) Andreas Rozek HyMeSys Software & Consulting

  39. VBA für Excel 23.02.2010 Übungen (Fortsetzung) • erstellen Sie ein Programm zur Übersetzung von Excel-Formeln (Tip: denken Sie an Formula und FormulaLocal) Andreas Rozek HyMeSys Software & Consulting

More Related