VBA für Excel

VBA für Excel 23.02.2010

Andreas Rozek HyMeSys Software & Consulting

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 RozekHyMeSys Software & ConsultingBrunnenstraße 30/271032 Böblingen

Telefon: (07031) 436 5784Email: [email protected]: www.Rozek.de

mailto:[email protected]



Überblick über den Kurs

Montag Grundlagen (Syntax & Semantik von VBA)

Freitag weiterführende Themen (Email, Web, usw.)Verwendung externer Objekte, Sudoku

Donnerstag Eingabeformulare, Programmentwicklung(Anmeldeformular, Zahlen-Memory)

Mittwoch Ereignis-gesteuerte ProgrammierungFormular- und ActiveX-Steuerelemente

Dienstag Das Excel-Objektmodell



VBA für Excel

Fragen zum gestrigen Tag?



VBA für Excel

Teil II: das Excel-Objektmodell



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



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.



Das

Exc

el O

bje

ktm

od

ell



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)



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



Nu

tzen

Sie

den

Ob

jekt

-Kat

alo

g!

...u

nd

die

ein

geb

aute

Hilf

e!



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



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



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)



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



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



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



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.



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“



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



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



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



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




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




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




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)



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



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



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. enthält Excel-interne Funktionen



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()”



Vor den Übungen...

Befehlsschaltflächen • einsetzen • beschriften • Makro zuweisen

Diagramme manuell anlegen • Datenquelle zuweisen • beschriften und formatieren



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”



Dieses Beispiel sollten Sie jetzt verstehen...



Hier ist der Quelltext...



VBA für Excel

Übungen



Ü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)



Ü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)




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”)




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” _ )




erstellen Sie ein Programm zur Übersetzung von Excel-Formeln

(Tip: denken Sie an Formula und FormulaLocal)

Documents

VBA für Excel