Artikel 1 | Januar 2011 | Excel VBA

Module und Auslagern



Die Prozedurzuordnung in Excel

Die Zuordnung die Excel beim Erstellen einer Prozedur vorschlägt, folgt einer einfachen Regel: Prozeduren kommen immer dort hin, wo das Ereignis ausgelöst wird. Bspw. wird die Prozedur für ein Steuerelement wie einem CommandButton auf Tabelle1

VBA Project (Mappe1) Tabelle1 (Tabelle1)

Private Sub CommandButton1_Click()

End Sub

grundsätzlich auch auf Tabellenblatt 1 im VBA-Editor abgelegt. Jede Funktion, die ausgelöst werden soll (hier bspw. ein Klick), wird in die Prozedur geschrieben.

Warum Auslagern?

Das Skripten in einer Prozedur ist teilweise so vielfätig, dass die Euphorie des Anfängers alsbald ein jähes Ende findet. Denn sechsfach verschachtelte Schleifen nur beim Eintippen richtig cool (nicht aber beim Überprüfen) und außerdem wird jeder Traum einer "Super-Prozedur", mit maximal 64 KB begrenzt.

Fehler: Prozedur zu groß.

Abb. 1:

Wenn es zu lang wird,

streikt Excel.

Man steht wieder am Anfang. Man schwört sich, jetzt das erste Kapitel des teuer erstandenen VBA-für-Einsteiger-Buchs zu beherzigen. Prinzipiell kann jede noch so lange Prozedur gerettet werden, indem man genügend Ballast auslagert, Doppelungen und Mehrfachschleifen vereinfacht. Obwohl das eine Mehrarbeit erfordert, überwiegen die Vorteile. Das nächste Kapitel zeigt, was Module können.

Globale Module

Module sind, wenn man sie entsprechend definiert, wie übergeordnete Tabellenbletter, auf die man, im Gegensatz zu herkömmlichen Prozeduren, von überall zugreifen kann. Der Sinn eines Modules ist, Teile einer Prozedur eines Tabellenblattes aufzunehmen und somit die Prozedur zu entlasten. Weiterhin kann eine Prodzedur in einem Modul auch von anderen Tabellenblättern aufgerufen werden. Das Ziel ist es, ein Modul zu skripten, welches universal einsetzbar ist.

Module werden im VBA-Editor wie herkömmliche Prozeduren eingefügt.

Microsoft Visual Basic for Applications

Einfügen » Modul

Der Aufbau eines Modulfensters entspricht dem einer Prozedur auf einem Tabellenblatt. Um eine Prozedur global, also von jedem Tabellenblatt aus verfügbar zu machen, wird folgender Code eingegeben.

Modul1

Public Sub

End Sub

Innerhalb dieser globalen Prozedur im Modul wird nun der Teil der Prozedur auf dem Tabellenblatt eingefügt, der ausgelagert werden soll.

Aufgerufen wird die globale Prozedur durch

Prozedur Tabellenblatt

Call globale Prozedur

Excel springt aus der Prozedur im Tabellenblatt bei Call in die globale Prozedur und arbeitet diese Zeile für Zeile ab. Ist die globale Prozedur beendet, springt Excel wieder zurück und arbeitet an der nächsten Zeile nach Call weiter.

Beispiel

Im Tabellenblatt 1 befindet sich ein CommandButton1 und ein CommandButton2. Bei Klick auf einen dieser Steuerelemente soll eine MessageBox ausgegeben werden, mit dem Inhalt "CommandButton geklickt". Dabei soll der Befehl zum Einblenden der MessageBox nicht direkt in jeder Prozedur aufgerufen werden, sondern beide Prozeduren greifen auf eine globale Prozedur mit dem Namen MessageBox zu.

Tabelle1

Private Sub CommandButton1_Click()

Call MessageBox

End Sub

Private Sub CommandButton2_Click()

Call MessageBox

End Sub

Modul1

Public Sub MessageBox()

MsgBox "CommandButton geklickt"

End Sub

Globale Variablen

Die in einer Prozedur mit Dim ... As deklarierten Variablen werden nicht mit in die globale Prozedur übernommen. Diese Variablen sind für die globale Prozedur unbekannt, weil sie nur für die Prozedur gelten, in der sie deklariert wurden. Variablen, die universell in verschiedenen Prozeduren angewandt werden, sollten im Modul ganz oben deklariert werden.

Modul1

Public [Variable] As [Typ]

Beispiel

Es wird das Beispiel von oben aufgegriffen. Im Tabellenblatt 1 befinden sich die zwei CommandButton. Ein Klick auf einen CommandButton blendet eine MessageBox ein, die den Namen des CommandButton ausgibt. Der Name des CommandButton wird in der Prozedur des jeweiligen CommandButton in eine Variable übergeben. Die Variable wird im Modul als global deklariert. Beide Ereignisse, die von CommandButton1 und CommandButton2 werden durch Aufruf der globalen Prozedur ausgelöst.

Tabelle1

Private Sub CommandButton1_Click()

CBName = "CommandButton1"

Call MessageBox

End Sub

Private Sub CommandButton2_Click()

CBName = "CommandButton1"

Call MessageBox

End Sub

Modul1

Public CBName As String

Public Sub MessageBox()

MsgBox CBName

End Sub

Vorteile des Auslagerns

Artikel 1 | Januar 2011 | Excel VBA

Ziel

Aufzeigen des grundsätzlichen Problems beim drauflos Skripten. Erläuterung der Vorteile von ausgelagerten Prozedurteilen.

Navigation

Schnellanleitung

Prozeduren in Tabellenblättern werden mit Private Sub eingeleitet. Globale Prozeduren, auf die man von jedem Tabellenblatt aus zugreifen kann, werden in Modulen eingefügt. Module erzeugt man über Einfügen, Module. Globale Prozeduren deklariert man mit Public Sub im Modul. Zum Aufrufen der globalen Prozedur verwendet man Call + Prozedurname. Zur Übernahme von Variablen von einer zur anderen Prozedur deklariert man globale Variablen. Die Deklaration erfolgt im oberen Teil eines Moduls anstatt wie üblich Dim ... As ... mit Public ... As ....