slide1
Download
Skip this Video
Download Presentation
VBA für Excel

Loading in 2 Seconds...

play fullscreen
1 / 39

VBA für Excel - PowerPoint PPT Presentation


  • 140 Views
  • Uploaded on

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

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' - conway


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

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: [email protected]

URL: www.Rozek.de

Andreas Rozek

HyMeSys Software & Consulting

slide2
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

slide3
VBA für Excel

23.02.2010

VBA für Excel

Fragen zum gestrigen Tag?

Andreas Rozek

HyMeSys Software & Consulting

slide4
VBA für Excel

23.02.2010

VBA für Excel

Teil II: das Excel-Objektmodell

Andreas Rozek

HyMeSys Software & Consulting

slide5
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

slide6
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

slide7
VBA für Excel

23.02.2010

Das Excel Objektmodell

Andreas Rozek

HyMeSys Software & Consulting

slide8
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

slide9
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

slide10
VBA für Excel

23.02.2010

Nutzen Sie den Objekt-Katalog!

...und die eingebaute Hilfe!

Andreas Rozek

HyMeSys Software & Consulting

slide11
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

slide12
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

slide13
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

slide14
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

slide15
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

slide16
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

slide17
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

slide18
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

slide19
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

slide20
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

slide21
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

slide22
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

slide23
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

slide24
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

slide25
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

slide26
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

slide27
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

slide28
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

slide29
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

slide30
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

slide31
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

slide32
VBA für Excel

23.02.2010

Dieses Beispiel sollten Sie jetzt verstehen...

Andreas Rozek

HyMeSys Software & Consulting

slide33
VBA für Excel

23.02.2010

Hier ist der Quelltext...

Andreas Rozek

HyMeSys Software & Consulting

slide34
VBA für Excel

23.02.2010

VBA für Excel

Übungen

Andreas Rozek

HyMeSys Software & Consulting

slide35
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

slide36
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

slide37
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

slide38
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

slide39
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

ad