VBA für Excel
This presentation is the property of its rightful owner.
Sponsored Links
1 / 39

VBA für Excel PowerPoint PPT Presentation


  • 68 Views
  • Uploaded on
  • Presentation posted in: General

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

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


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

Email:[email protected]

URL:www.Rozek.de

Andreas Rozek

HyMeSys Software & Consulting


Vba f r excel

VBA für Excel

23.02.2010

Überblick über den Kurs

MontagGrundlagen (Syntax & Semantik von VBA)

DienstagDas Excel-Objektmodell

MittwochEreignis-gesteuerte ProgrammierungFormular- und ActiveX-Steuerelemente

DonnerstagEingabeformulare, Programmentwicklung

(Anmeldeformular, Zahlen-Memory)

Freitagweiterführende Themen (Email, Web, usw.)

Verwendung externer Objekte, Sudoku

Andreas Rozek

HyMeSys Software & Consulting


Vba f r excel

VBA für Excel

23.02.2010

VBA für Excel

Fragen zum gestrigen Tag?

Andreas Rozek

HyMeSys Software & Consulting


Vba f r excel

VBA für Excel

23.02.2010

VBA für Excel

Teil II: das Excel-Objektmodell

Andreas Rozek

HyMeSys Software & Consulting


Vba f r excel

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


Vba f r excel

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


Vba f r excel

VBA für Excel

23.02.2010

Das Excel Objektmodell

Andreas Rozek

HyMeSys Software & Consulting


Vba f r excel

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


Vba f r excel

VBA für Excel

23.02.2010

Das Excel Objektmodell

  • ApplicationExcel 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


Vba f r excel

VBA für Excel

23.02.2010

Nutzen Sie den Objekt-Katalog!

...und die eingebaute Hilfe!

Andreas Rozek

HyMeSys Software & Consulting


Vba f r excel

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


Vba f r excel

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


Vba f r excel

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


Vba f r excel

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


Vba f r excel

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


Vba f r excel

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


Vba f r excel

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


Vba f r excel

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


Vba f r excel

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


Vba f r excel

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


Vba f r excel

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


Vba f r excel

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


Vba f r excel

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


Vba f r excel

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


Vba f r excel

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


Vba f r excel

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


Vba f r excel

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


Vba f r excel

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


Vba f r excel

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


Vba f r excel

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


Vba f r excel

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


Vba f r excel

VBA für Excel

23.02.2010

Dieses Beispiel sollten Sie jetzt verstehen...

Andreas Rozek

HyMeSys Software & Consulting


Vba f r excel

VBA für Excel

23.02.2010

Hier ist der Quelltext...

Andreas Rozek

HyMeSys Software & Consulting


Vba f r excel

VBA für Excel

23.02.2010

VBA für Excel

Übungen

Andreas Rozek

HyMeSys Software & Consulting


Vba f r excel

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


Vba f r excel

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


Vba f r excel

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


Vba f r excel

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


Vba f r excel

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


  • Login