Workshop 7: VBA-Programmierung mit MS Excel

1 Workshop 7: VBA-Programmierung mit MS Excel 2011, Dr. Volker Thorm hlen Seite 1 von 12 1 Mit Tabellenbl ttern arbeiten .. 1 Auf Bl tter zugreifen .. 1 Gemeinsame Eigenschaften des Worksheet und 2 Spezielle Eigenschaften des Worksheet-Objekts .. 3 Spezielle Eigenschaften des Chart-Objekts .. 3 Wichtige Methoden von Worksheet- und Chart-Objekts .. 4 Wichtige Eigenschaften und Methoden der 6 Tabellenbl tter ausw hlen .. 6 Neues Tabellenblatt hinzuf gen .. 6 Tabellenbl tter kopieren / verschieben .. 7 Tabellenbl tter benennen .. 7 Tabellenbl tter l schen .. 8 Tabellenbl tter aus- und einblenden.

2 8 Kopf- und Fu zeilen anlegen .. 8 Tabellenbl tter ausdrucken .. 9 Tabellenblatt als E-Mail versenden .. 10 2 Ereignisse f r das Tabellenblatt .. 11 Allgemeine Vorgehensweise .. 11 Spaltenbreite automatisch anpassen .. 11 Tabellenname aus Zelle 11 Aktive Zelle hervorheben .. 12 Kontextmen deaktivieren .. 12 Werterh hung bei Doppelklick .. 12 1 Mit Tabellenbl ttern arbeiten Arbeitsmappen (Workbooks) enthalten Bl tter (Sheets), die entweder Arbeitsbl tter (Worksheet) oder Diagrammbl tter (Chart) darstellen. Auf Bl tter zugreifen Die Sheets- auflistung enth lt alle Arbeits und Diagrammbl tter einer Arbeitsmappe.

3 Nur ber diese auflistung k nnen neue Bl tter angelegt werden, die dann in die auflistung eingef gt werden. Damit k nnen Aktionen durchgef hrt werden, die sich allgemein auf die Verwaltung von Arbeits- und Diagrammbl ttern beziehen. ActiveSheet verweist auf das aktuelle Arbeitsblatt unabh ngig vom Typ des Blatts. Achtung: Ein ActiveWorksheet gibt es nicht! ActiveChart erm glicht den Zugriff auf das aktive Diagrammblatt. Workshop 7: VBA-Programmierung mit MS Excel 2011, Dr. Volker Thorm hlen Seite 2 von 12 ber ein Element der Sheets- auflistung wird auf ein konkretes Blatt verwiesen.

4 Dabei erfolgt der Zugriff ber einen Index oder ber den Namen des Blattes: Beispiel: o Sheets(2) o Sheets("Name") Der Index entspricht der Reihenfolge der Bl tter. Der Zugriff auf ein konkretes Blatt kann auch mit einer Objektvariablen vom Typ Worksheet oder Chart erfolgen. Beispiel: Dim objSht As Worksheet Dim objCht as Chart Wichtige Eigenschaften und Methoden der Sheets- auflistung : o : Diese Eigenschaft gibt einen Long-Wert zur ck, der die Anzahl der Objekte in der auflistung darstellt. o : Diese Methode erstellt ein neues Arbeitsblatt, Diagramm oder Makroblatt. Das neue Arbeitsblatt wird zum aktiven Blatt.

5 O Sheets(2).Select: Diese Methode markiert das angegebene Objekt. o (2): Diese Eigenschaft gibt das angegebene Blatt der auflistung zur ck Das folgende Beispiel veranschaulicht das Arbeiten mit einer Objektvariablen vom Typ WorkSheet: Sub AufEinBlattVerweisen() Dim objSht As Worksheet ' Objektvariable deklarieren ' Objektvariable setzen Set objSht = ("Mappe1"). ("Tabelle1") With objSht ' Objekt benennen .Name = "Termine" End With End Sub Gemeinsame Eigenschaften des Worksheet und Chart-Objekts Eigenschaft Bedeutung Name Der Name eines Blattes kann ermittelt oder bestimmt werden.

6 Next N chsten Blatt aktivieren (vorw rts springen) Previous Vorheriges Blatt aktivieren (r ckw rts springen) Workshop 7: VBA-Programmierung mit MS Excel 2011, Dr. Volker Thorm hlen Seite 3 von 12 Spezielle Eigenschaften des Worksheet-Objekts Eigenschaft Bedeutung Cells Der Name eines Blattes kann ermittelt oder bestimmt werden. Beispiel: MsgBox (1, 1).Value Columns Gibt eine Range-Objekt zur ck, dass alle Spalten eines Arbeitsblatts enth lt. Beispiel: Rows Gibt eine Range-Objekt zur ck, dass alle Zeilen eines Arbeitsblatts enth lt. Beispiel: FilterMode Ruft einen Wert ab, der angibt, ob sich das Arbeitsblatt im Filtermodus befindet.

7 Beispiel: Function IstFilter(objSht As Worksheet) As Boolean IstFilter = End Functiont. Type Gibt den Arbeitsblatttyp zur ck. Beispiel: Sub ArbeitsblattTyp() Select Case ("Tabelle1").Type Case MsgBox "Dies ist ein Arbeitsblatt." Case MsgBox "Dies ist ein Dialogblatt." Case MsgBox "Dies ist ein Diagrammblatt." End Select End Sub Spezielle Eigenschaften des Chart-Objekts Eigenschaft Bedeutung ChartType Gibt den Diagrammtyp zur ck oder legt ihn fest. = xlXYScatterLines ChartTitle Gibt den Titel des angegebenen Diagramms zur ck oder legt ihn fest.

8 With Charts("Chart1") .HasTitle = True . = "Umsatz im ersten Quartal" End With DataTable Gibt die Datentabelle eines Diagramms zur ck. Workshop 7: VBA-Programmierung mit MS Excel 2011, Dr. Volker Thorm hlen Seite 4 von 12 Wichtige Methoden von Worksheet- und Chart-Objekts Methode Bedeutung Activate Das angegebene Arbeits- oder Diagrammblatt wird in den Vordergrund gebracht. (1).Activate Copy Das Blatt wird an eine andere Stelle der Arbeitsmappe kopiert. After:=Sheets( ) Delete L scht das angegebene Arbeits- oder Diagrammblatt. Sub BlattLoeschen() = False Sheets(4).Delete = True End Sub Move Verschiebt das angegebene Arbeits-oder Diagrammblatt an eine andere Stelle in der Arbeitsmappe.

9 Sub BlattVerschieben() Const conShtName As String = "VerschiebMich" With ThisWorkbook .Sheets(1).Name = conShtName MsgBox "Pos. vorher: " & .Sheets(conShtName).Index .Sheets(1).Move After:=.Worksheets(. ) MsgBox "Pos. nachher: " & Sheets(conShtName).Index End With End Sub Protect Sch tzt ein Arbeits-oder Diagrammblatt, damit es nicht ge ndert werden kann. Sub BlattSchuetzen() Const conPwd As String = "xyz_123" Password:=conPwd End Sub SaveAs Speichert nderungen am Diagramm oder Arbeitsblatt in einer anderen Datei. Siehe Prozedur AktuellesBlattSpeicher PrintOut Druckt das Arbeits- oder Diagrammblatt.

10 Unprotect Hebt den Blattschutz wieder auf (vgl. Protect) Password:=conPwd Die Anwendung der Methode SaveAs wird in der folgenden Prozedur mit dem Namen AktuellesBlattSpeichernUnter gezeigt. In der Prozedur wird die Excel -Version abgefragt, so dass sie f r alle neueren Versionen ablauff hig ist. Workshop 7: VBA-Programmierung mit MS Excel 2011, Dr. Volker Thorm hlen Seite 5 von 12 Sub AktuellesBlattSpeichernUnter() ' In Anlehnung an Ron de Bruin, Use VBA SaveAs in Excel 2007-2010, ' Januar 2010, ' G ltig f r Excel 2000-2010 Dim varFileName As Variant ' Dateityp Dim objWkbNew As Workbook ' Ziel-Arbeitsmappe Dim lngFormatVal As Long ' Nummer des Dateityps ' Excel -Version pr fen If Val( ) < 9 Then Exit Sub If Val( ) < 12 Then ' Zeigt das integrierte Dialogfeld 'Speichern unter' an ' und gibt als Dateiformat Excel 2000-2003 vor.