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
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
Andreas Rozek
weiterführende Themen (Email, Web, usw.)
Verwendung externer Objekte, Sudoku
HyMeSys Software & Consulting
VBA für Excel
23.02.2010
VBA für Excel
Fragen zum gestrigen Tag?
Andreas Rozek
HyMeSys Software & Consulting
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
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 Anwendungsfall 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
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 eingebauter Hilfe inspizieren können;
• erste Automatisierungsmakros programmieren können.
Andreas Rozek
HyMeSys Software & Consulting
23.02.2010
Das Excel Objektmodell
VBA für Excel
Andreas Rozek
HyMeSys Software & Consulting
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
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
Andreas Rozek
23.02.2010
...und die eingebaute Hilfe!
Nutzen Sie den Objekt-Katalog!
VBA für Excel
HyMeSys Software & Consulting
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
23.02.2010
Erste Experimente...

Anlegen einer neuen Arbeitsmappe
Workbooks.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
23.02.2010
Ups – was war denn das?

Explizites Benennen von Argumenten
newBook.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 separieren
WorkBooks.Open FileName:=“Hurra.xls“, ReadOnly:=True

allerdings: einmal benannt, immer benannt (im selben Aufruf)
Andreas Rozek
HyMeSys Software & Consulting
VBA für Excel
23.02.2010
Weitere wichtige Workbook-Methoden

“Aktivieren” einer Arbeitsmappe
Workbooks(...).Activate

Drucken einer Arbeitsmappe
Workbooks(...).Printout

Sichern einer Arbeitsmappe
Workbooks(...).Save
Workbooks(...).SaveAs „c:\Hurra.xls“

Schließen einer Arbeitsmappe
Workbooks(...).Close
Andreas Rozek
HyMeSys Software & Consulting
VBA für Excel
23.02.2010
Weitere wichtige Workbook-Methoden (Fortsetzung)

Exportieren einer Arbeitsmappe
ActiveWorkbook.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
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
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 Fehlersuche usw.
Andreas Rozek
HyMeSys Software & Consulting
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
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
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
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).Insert
Shift:=xlShiftDown
Andreas Rozek
HyMeSys Software & Consulting
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).Delete
Shift:=xlShiftUp
Andreas Rozek
HyMeSys Software & Consulting
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
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”).Select
immer 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
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.Selection
liefert 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
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
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
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.FormulaR1C1Local
WorksheetFunction.
Andreas Rozek
enthält Excel-interne Funktionen
HyMeSys Software & Consulting
VBA für Excel
23.02.2010
Noch ein paar Bemerkungen zu „Value“

zuweisbare Werte (achten Sie auf das erste Zeichen)
aCell.Value
aCell.Value
aCell.Value
aCell.Value
Andreas Rozek
=
=
=
=
1234e56
“1234e56”
“'1234e56”
“=now()”
HyMeSys Software & Consulting
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
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
23.02.2010
Dieses Beispiel sollten Sie jetzt verstehen...
Andreas Rozek
HyMeSys Software & Consulting
VBA für Excel
23.02.2010
Hier ist der Quelltext...
Andreas Rozek
HyMeSys Software & Consulting
VBA für Excel
23.02.2010
VBA für Excel
Übungen
Andreas Rozek
HyMeSys Software & Consulting
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
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 zunächst manuell an und erzeugen Sie die zugehörigen Werte
automatisch)
Andreas Rozek
HyMeSys Software & Consulting
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
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
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

VBA für Excel - auf den Web