Abfragen von globalen Ereignissen mit Klassenmodulen

In diesem Artikel erfahren Sie, wie globale Ereignisse unter der Verwendung von Klassenmodulen verarbeitet werden können. Ausgehend von theoretischen Ansätzen wird das Thema anhand von Beispielen vertieft.

Wie Sie sicherlich wissen, bietet Excel Methoden an, mit denen verschiedene Ereignisse abgefangen, also verwaltet werden können. Beispielsweise kann abhängig vom Ereignis Worksheet_Change eine bestimmter Code abgearbeitet werden. Der entsprechende Code wird nur dann ausgeführt, wenn in einer beliebigen Zelle eine Veränderung stattfindet.

Sollen bei der Ausführung bestimmter Excel-Grundfunktionen, wie beispielsweise der Erstellung eines neuen Tabellenblattes oder dem Speichern einer vorhandenen Arbeitsmappe bestimmte Makros ausgeführt werden, ohne das der entsprechende Code in der Arbeitsmappe selbst hinterlegt wäre, dann könnte dies über so genannte Klassenmodule realisiert werden.

Ein Klassenmodul ist nichts anderes als ein Container, der Programm-Code genauso aufnehmen kann wie ein herkömmliches Modul-Code-Blatt. Im Gegensatz zu „normalen“ Modulen kann ein Klassenmodul jedoch Ereignisse selbst auslösen und verarbeiten. Um also globale Ereignisse in Excel abzufangen, bietet es sich an, Klassenmodule zu verwenden. Dabei wird das vordefinierte Objekt Application im eigenen Programmcode eingebunden und kann so Ereignisse die für die Anwendung, in diesem Beispiel eben Excel, bestimmt sind, auf eigene Programme umleiten und somit verarbeiten.

 

Damit das Klassenmodul ständig aktiv ist, bietet es sich an, den Code zur Initialisierung der entsprechenden Klasse in ein Add-In zu integrieren. Um ein Klassenmodul zu erzeugen, starten Sie zuerst den VBA-Editor und fügen über das Menü Einfügen / Klassenmodul ein neues Klassenmodul ein. Benennen Sie dieses Modul in clsBeispielKlasse um. Die Präfix cls wir im allgemeinen für Klassenmodule verwendet.

Auf den ersten Blick unterscheiden sich Klassenmodule nicht von „normalen“ Modul-Blättern. Der Code kann genauso erfasst und editiert werden. Mit dem Einfügen des Klassenmoduls wurde der formale Rahmen für die Programmierung einer globalen Prozedur geschaffen. Im nächsten Schritt müssen die entsprechenden Prozeduren erstellt werden.

 

Um die Klasse zu definieren, fügen Sie im allgemeinen Deklarationsteil des Klassenmoduls folgenden Code ein: Public WithEvents App As Application

Diese Anweisung definiert, dass die Ereignisse der Anwendung, also die Ereignisse von Excel selbst verwaltet werden sollen. Im nächsten Schritt muss Excel angewiesen werden, eine Verbindung zwischen den Ereignissen von Excel und dem Kassenmodul clsBeispielKlasse herzustellen. Denn erst dann können die globalen Ereignisse auch verarbeitet werden.

Diese Verbindung wird am besten im Modul DieseArbeitsmappe realisiert, indem das Ereignis Workbook_Open() genutzt wird. Erfassen Sie dazu den Code aus Listing 1 im Modul DieseArbeitsmappe.

Listing 1:

Option Explicit
'** Erzeugung einer neuen Objektvariable
Dim AppObject As New clsBeispielKlasse
Private Sub Workbook_Open()
'** Zuweisung der Objektvariable
Set AppObject.App = Application
End Sub

Zuerst wird mittels der Dim-Anweisung die Objekt-Variable AppObjects erzeugt, über welche die Ereignisse aus dem Klassenmodul clsBeispielKlasse gesteuert werden. Anschließend muss noch dafür Sorge getragen werden, dass Excel alle Ereignisse der Anwendung mit dem neuen Klassenmodul verbindet. Dies wird realisiert, indem mittels der Anweisung Set der Objekt-Variable AppObjects das entsprechende Objekt zugewiesen wird. Mit diesen Programmzeilen haben Sie bereits die Grundlagen für die eigenen globalen Ereignisprozeduren geschaffen. Damit diese Zuweisung immer zur Verfügung steht, speichern Sie diese Datei als Add-In ab und binden es über den Add-In-Manager ein. Bei jedem Start von Excel wird diese Prozedur ausgeführt und initialisiert somit die Verarbeitung der globalen Ereignisse.

Umgang mit globalen Ereignissen

Die globalen Ereignisse von Excel können Sie wie gewohnt abfragen. Starten Sie dazu den VBA-Editor und öffnen das Klassenmodul clsBeispielKlasse. Wie gewohnt können Sie mittels des DropDown-Menüs die gewünschten Ereignisse auswählen, siehe Abbildung 1.



Nachdem Sie im linken DropDown-Menü den Eintrag App ausgewählt haben, stehen im rechten DropDown-Menü alle globalen Ereignisse zur Verfügung.

Übersicht der wichtigsten globalen Excel-Ereignisse

App_NewWorkbook(ByVal Wb As Workbook)

 - Eine neue Arbeitsmappe wurde eingefügt

App_SheetActivate(ByVal Sh As Object)

 - Ein andere Tabellenblatt wurde aktiviert (Blattwechsel)

App_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)

 - Ein Doppelklick wurde ausgeführt

App_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)

 - Ein Klick mit der rechten Maustaste wurde durchgeführt

App_SheetCalculate(ByVal Sh As Object)

 - Neuberechnung eines Tabellenblattes

App_SheetChange(ByVal Sh As Object, ByVal Target As Range)

 - Zelleninhalt eines Tabellenblattes wurde verändert

App_SheetDeactivate(ByVal Sh As Object)

 - Ein Tabellenblatt wurde verlassen (Blattwechsel)

App_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)

 - Zellenmarkierung eines Tabellenblattes wurde geändert

App_WindowActivate(ByVal Wb As Workbook, ByVal Wn As Window)

 - Ein neues Fenster wurde aktiviert (Fensterwechsel)

App_WindowDeactivate(ByVal Wb As Workbook, ByVal Wn As Window)

 - Ein Fenster wurde verlassen (Fensterwechsel)

App_WindowResize(ByVal Wb As Workbook, ByVal Wn As Window)

- Die Größe eines Fensters wurde geändert

App_WorkbookActivate(ByVal Wb As Workbook)

 - Eine neue Arbeitsmappe wurde aktiviert (Arbeitsmappenwechsel

App_WorkbookAddinInstall(ByVal Wb As Workbook)

 - Ein Add-In wurde installiert

App_WorkbookAddinUninstall(ByVal Wb As Workbook)

 - Ein Add-In wurde deinstalliert

App_WorkbookBeforeClose(ByVal Wb As Workbook, Cancel As Boolean)

 - Eine Arbeitsmappe soll geschlossen werden

App_WorkbookBeforePrint(ByVal Wb As Workbook, Cancel As Boolean)

 - Eine Arbeitsmappe soll gedruckt werden

App_WorkbookBeforeSave(ByVal Wb As Workbook, ByVal SaveAsUI As Boolean, Cancel As Boolean)

 - Eine Arbeitsmappe soll geschlossen werden

App_WorkbookDeactivate(ByVal Wb As Workbook)

 - Eine Arbeitsmappe wurde verlassen (Arbeitsmappenwechsel)

App_WorkbookNewSheet(ByVal Wb As Workbook, ByVal Sh As Object)

 - In eine Arbeitsmappe wurde ein neues Tabellenblatt eingefügt

App_WorkbookOpen(ByVal Wb As Workbook)

 - Eine neue Arbeitsmappe wurde geöffnet

Wenn Sie aus der Liste das entsprechende Ereignis auswählen, legt der VBA-Editor daraufhin automatisch die passende leere Prozedur für das Ereignis an. Auf diese Weise können Sie alle globalen Ereignisse von Excel abfangen und auf das eigene Programm umleiten. Die unterschiedlichen Ereigniss-Prozeduren arbeiten mit fest definierten Parametern. Dabei stellt der Parameter Wb die aktive Arbeitsmappe dar. Sh steht für das aktive Tabellenblatt, Target bezieht sich auf den aktiven Zellbereich und die Variable Wn bezeichnet das aktive Fenster. Der Parameter Cancel dient dazu, eine Information an Excel zurückzugeben. Dabei kann Cancel den Wert True oder False annehmen.

Praxisbeispiele

Nach der grauen Theorie folgt nun die Praxis. Anhand der nachfolgenden Beispiele soll der Umgang und die Funktionsweise von Klassenmodulen näher dargestellt werden.

Beispiel 1:

Im ersten Beispiel soll beim aktivieren eines Tabellenblattes eine MessageBox ausgeführt werden, die den Namen des aktivierten Tabellenblattes ausgibt. Erfassen Sie dazu den Code aus Listing 2 im Klassenmodul clsBeispielKlasse.

Listing 2: Ausgabe des aktivierten Tabellenblattnamens

Option Explicit
Public WithEvents app As Application
Private Sub App_SheetActivate(ByVal Sh As Object)
'** Bezeichnung des aktivierten Tabellenblattes
MsgBox "Es wurde das Sheet " & """" & Sh.Name & _
    """" & " ausgewählt!"
End Sub

Sobald ein Blattwechsel in einer beliebigen Excel-Datei stattfindet, wird der Name ausgelesen indem das Ereignis SheetActivate abgefragt wird. Die Eigenschaft Name, welche auf die Variable Sh angewendet wird, gibt die Bezeichnung des gerade aktivierten Tabellenblattes zurück.

Beispiel 2:

Im nächsten Beispiel soll bei jeder Änderung einer Zelle die Zelladresse sowie der Zelleninhalt ausgegeben werden, siehe Listing 3.

Listing 3: Ausgabe Zelladresse und Zellinhalt

Option Explicit
Public WithEvents app As Application
Private Sub App_SheetChange(ByVal Sh As Object, _
    ByVal Target As Range)
'** Ausgabe der geänderten Zelle
On Error Resume Next
MsgBox "Der Inhalt der Zelle " & Target.Address(False, _
    False) & " lautet " & Target.Value
End Sub

Dabei wird die Zelladresse über den Parameter Target.Adress angesprochen. Der Zellinhalt wird mittels der Anweisung Target.Value ausgelesen. Die so ermittelten Informationen werden über eine MessageBox am Bildschirm ausgegeben.

Beispiel 3:

Dieses Beispiel zeigt, wie verhindert werden kann, dass eine Excel-Datei unter einem bestimmten Dateinamen gespeichert werden kann. Damit kann zum Beispiel das überschreiben von sensiblen Daten wirksam verhindert werden. Den Code dafür sehen Sie in Listing 4.

Listing 4: Speichern bestimmter Arbeitsmappen verhindern

Option Explicit
Public WithEvents app As Application
Private Sub App_WorkbookBeforeSave(ByVal Wb As Workbook, _
    ByVal SaveAsUI As Boolean, Cancel As Boolean)
'** Dimensionierung der Variablen
Dim answer As String
'** Abfrage des Dateinamens
If UCase(Wb.Name) = "SICHERUNG.XLSM" Then
    answer = MsgBox("Diese Datei kann nicht" & _
        "gespeichert werden!", vbCritical, "Hinweis")
    Cancel = True
End If
End Sub

Die Datei kann ein einziges Mal unter dem Namen Sicherung.xlsm abgespeichert werden. Alle weitern Speicherversuche werden mit einer entsprechenden Meldung quittiert. Dieses Makro setzt das Ereignis WorkbookBevoreSave() ein und prüft, ob das in der Variablen Wb von Excel übergebene Objekt eine Arbeitsmappe ist und Sicherung.xlsm heißt. Ist dies der Fall, wird ein entsprechender Hinweis ausgegeben und der Rückgabewert Cancel auf den Wert True gesetzt. Dies bewirkt, dass die Datei nicht gespeichert wird. Damit sämtliche Schreibweisen des Dateinamens berücksichtigt werden, erfolgt zuerst die Umwandlung des Dateinamens in Großbuchstaben. Verwendet wird dazu die Anweisung UCase(Wb.Name).

Beispiel 4:

Das folgende Beispiel zeigt eine Möglichkeit, alle mit Excel geöffneten Dateien in einer eigenen Textdatei zu protokollieren. Selbstverständlich wird auch hier wieder ein globales Ereignis abgefragt. Diesmal heißt das Ereignis WorkbookOpen(). Tragen Sie auch den Code aus Listing 5 in das Klassenmodul clsBeipielKlasse ein, damit das Ereignis ausgewertet werden kann.

Listing 5: Öffnen von Excel-Dateien protokollieren

Option Explicit
Public WithEvents app As Application
Private Sub app_WorkbookOpen(ByVal WBook As Excel.Workbook)
'** Dimensionierung der Variablen
Dim strBenutzer As String
Dim datDatum As Date
Dim datUhrzeit As Date
Dim strDateiname As String
'** Definition der Vorgabewerte
strBenutzer = Application.UserName
datDatum = Format(Now, "dd.mm.yyyy")
datUhrzeit = Format(Now, "HH:MM:SS")
strDateiname = WBook.FullName
   
'** Ausgabe der Infos in eine Text-Datei
Open ThisWorkbook.Path & "\excel-zugriffe.txt" For Append As #1
Print #1, strBenutzer & vbTab & datDatum & vbTab _
    & datUhrzeit & vbTab & strDateiname
Close #1
End Sub

Zuerst erfolgt die Deklaration aller Variablen. Anschließend werden den entsprechenden Variablen die Informationen der gerade geöffneten Datei wie Benutzername, Datum, Uhrzeit und Dateiname übergeben. Bevor die Daten mittels der Anweisung Print # in die Datei excel-zugriffe.txt geschrieben werden, muss diese geöffnet werden. Dazu wird die Open-Anweisung verwendet. In diesem Beispiel wird die Datei c:\excel-zugriffe.txt im Append-Modus geöffnet. Dies bewirkt, dass die Daten einfach an das Text-File angehängt werden. Sofern die Datei beim ersten Schreibversuch nicht vorhanden ist, wird diese automatisch angelegt. Nach dem Schreibvorgang wird die Textdatei wieder geschlossen. Die Protokollierung aller in Excel geöffneter Dateien wird solange durchgeführt, so lange die Datei mit dem Code aus diesem Beispiel geöffnet ist.

Zusammenfassung

An diesen Beispielen können Sie sehen, dass sich Klassenmodule zur Abfrage von globalen Ereignissen sehr vielfältig einsetzen lassen. Verschiedene Ereignisse lassen sich abfangen, ohne dass die entsprechenden Mappen oder Tabellen VBA-Code enthalten müssen. Denken Sie daran, dass das Umleiten von globalen Ereignissen auf Ihr Klassenmodul erst dann funktioniert, wenn die entsprechende Auto-Open-Prozedur, vergleiche Listing 1, ausgeführt wurde.