Programmierungen am Visual Basic Editor

Der folgende Beitrag stammt von einem meiner US-MVP-Kollegen: Chip Pearson
Er hat mir erlaubt, einen seiner veröffentlichten Beiträge zu übersetzen und hier zu publizieren.
Ein herzliches Dankeschön!

Den englische Originaltext findest Du hier:
http://www.cpearson.com/excel/vbe.htm

Übersetzt am: 5. September 2004 - Monika Weber - http://www.jumper.ch

Inhaltsverzeichnis

1.) Einführung
2.) VBE Objekte
3.) Ein Objekt referenzieren
4.) Ein Modul in eine Mappe einfügen
5.) Ein Modul aus einer Mappe entfernen
6.) Eine Prozedur in ein Modul einfügen
7.) Eine Ereignis-Prozedur erzeugen
8.) Eine Prozedur aus einem Modul entfernen
9.) Alle Prozeduren aus einem Modul entfernen
10.) Alle Module einer Mappe auflisten
11.) Alle Prozeduren eines Moduls auflisten
12.) Alle Module in ein Projekt exportieren
13.) Sämtlichen VBA-Code eines Projekts löschen
14.) Module zwischen Projekten kopieren
15.) Prüfen, ob ein Modul oder eine Prozedur existiert

1.) Einführung

Der Visual Basic Editor (VBE) wird verwendet, um Visual Basic for Applications (VBA) Prozeduren und Module zu erstellen, verändern und verwalten. VBA gibt Dir die Möglichkeit, Mappen und Tabellenblätter zu verändern, indem Excel als Schnittstelle dient. VBA erlaubt zudem, VBA Komponenten und Code Module zu bearbeiten, indem VBE das Interface darstellt. Diese Seite bezieht sich lediglich auf Excel 97 und höhere Versionen. Ältere Versionen werden nicht unterstützt.

Diese Seite beschreibt einige Objekt, Methoden und Eigenschaften von VBE, die mittels VBA manipuliert werden können. In Excel 97 sind diese Objekte, Methoden und Eigenschaften nicht in der VBA-Hilfe-Datei beschrieben. Es muss die Datei VEENOB3.hlp verwendet werden. Diese Datei wurde möglicherweise nicht auf Deinem System installiert, als Du Office 97 installiert hast. Ab Office 2000 sind diese Themen in der Standard VBA Hilfe enthalten.

Bevor Du mit dem Programmieren durch die VBE beginnen kannst, musst Du den Verweis auf die Bibliothek "Extensibility" setzen. Du findest Sie im VBA Editor unter dem Menü "Extras/Verweise". Aktiviere das Kontrollkästchen "Microsoft Visual Basic for Applications Extensibility". Dies ermöglicht VBA die Definitionen der Objekte zu finden. Wenn Excel 97 verwendet wird, erscheint die Bibliothek in der Referenzliste ohne Versionsnummer. Wenn Du mit einer neueren Version arbeitest, ist es die Version 5.3 ("Microsoft Visual Basic for Applications Extensibility 5.3"). Es ist sehr wichtig, dass die richtige Bibliothek referenziert wird. Wenn die Falsche oder gar keine verwendet wird, wird ein Fehler beim Ausführen der nachfolgenden Codes erzeugt.

Informationen zur Programmierung von VBE Menüs findest Du unter Adding Menus To The VBA Editor (englisch).

Hinweis: In der Version Excel 2002 wurde ein zusätzlicher Sicherheitslevel eingeführt. Um VBE Projekt Objekte zu manipulieren, so wie es hier beschrieben ist, müsst Du Deine Sicherheitseinstellungen ändern. Verwende in Excel das Menü "Extras/Makro/Sicherheit". Aktiviere das Register "Vertrauenswürdige Quellen". Aktiviere darin das Kontrollkästchen "Zugriff auf Visual Basic-Projekt vertrauen".

Hinweis: Für alle Excel-Versionen gilt, dass das Projekt nicht geschützt werden darf. Wenn dies der Fall ist, schlägt die Prozedur fehl.

Zusätzliche Fehler können entstehen, wenn versucht wird aus einem Modul her Veränderungen im selben Modul vorzunehmen. Damit ist der Zugriff von z.B. Modul1 auf Modul1 gemeint. Es ist zu empfehlen, dies nicht zu tun.

Zurück zum Anfang

2.) VBE-Objekte

Wir werden drei Objekte in unseren Codes verwenden:

VBProject
Das ist der gesamte Satz von VBA Modulen und Referenzen, die mit einer Mappe verbunden sind.

VBComponent
Das sind die individuellen Komponenten innerhalb von VBProject. Zum Beispiel ein UserForm und ein Standard-Modul sind VBComponenten. Die VBComponents Kollektion enthält alle existierenden VBComponent Objekte.

CodeModule
Dieses Objekt repräsentiert den aktuellen Code innerhalb von VBComponent. Wenn Du beispielsweise einen Code in Modul1 eintippst, gibst Du den Code in das CodeModule Objekt ein in der VBComponenten mit dem Namen "Modul1".

Wir werden programmiertechnisch durch das Workbook Objekt zu diesen Komponenten "navigieren". Du kannst diese Komponenten auch über den Application.VBE Objekt-Pfad ansteuern, aber das werden wir hier nicht tun.

Es gibt verschiedene Typen an VBComponents, identifiziert durch die Typen-Eigenschaft des Objekt VBComponent.

Zurück zum Anfang

3.) Ein Objekt referenzieren

Der erste Schritt in der Programmierung mit VBE ist, das Objekt, das für die Arbeit benötigt wird, zu referenzieren.

VBProject
Dim VBProj As VBProject
Set VBProj = ThisWorkbook.VBProject

VBComponent
Dim VBComp As VBComponent
Set VBComp = ThisWorkbook.VBProject.VBComponents("Modul1")

CodeModule
Dim VBCodeMod As CodeModule
Set VBCodeMod = ThisWorkbook.VBProject.VBComponents("Modul1").CodeModule

In allen Beispielen auf dieser Seite werden wir mit dem ThisWorkbook Objekt arbeiten -- das bedeutet, wir werden die VBA Komponenten über die Mappe ansprechen. Natürlich können jegliche Arten an offenen Mappen verwendet werden AktiveWorkbook oder Workbooks(IrgendeineMappe.xls).

Zurück zum Anfang

4.) Ein Modul in eine Mappe einfügen

Die untenstehende Prozedur fügt ein neues Modul mit dem Namen "NeuesModul" in ThisWorkbook ein.

Sub AddModule()
   Dim VBComp As VBComponent
   Set VBComp = ThisWorkbook.VBProject.VBComponents. _
                Add(vbext_ct_StdModule)

   VBComp.Name = "NeuesModul"
   Application.Visible = True
End Sub

Wenn dieser Code von Excel her ausgeführt wird, während der VBE offen ist, wirst Du direkt zum neuen Modul geführt und die Prozedur wird beendet. Wenn der Code ausgeführt wird, wenn der VBE geschlossen ist, wird der Fokus an die Excel Applikation zurückgegeben. Der VBE wird nicht geöffnet.

Zurück zum Anfang

5.) Ein Modul aus einer Mappe entfernen

Die folgende Prozedur löscht das Modul "NeuesModul" aus ThisWorkbook.

Sub DeleteModule()
   Dim VBComp As VBComponent
   Set VBComp = ThisWorkbook.VBProject. _
                VBComponents("NeuesModul")

   ThisWorkbook.VBProject.VBComponents.Remove VBComp
End Sub

Ein ThisWorkbook Code Modul oder ein Sheet Code Modul oder ein Chart Code Modul kann nicht gelöscht werden.

Zurück zum Anfang

6.) Eine Prozedur in ein Modul einfügen

Die nächste Prozedur fügt eine neue Prozedur mit dem Namen "MeineNeueProzedur" in das Modul mit dem Namen "NeuesModul" in ThisWorkbook ein. Das Modul "NeuesModul" muss vorhanden sein, ansonsten entsteht eine Fehlermeldung.

Sub AddProcedure()
   Dim VBCodeMod As CodeModule
   Dim LineNum As Long

   Set VBCodeMod = ThisWorkbook.VBProject. _
                   VBComponents("NeuesModul").CodeModule

   With VBCodeMod
       LineNum = .CountOfLines + 1
       .InsertLines LineNum, _
   "Sub MeineNeueProzedur()" & Chr(13) & _
   " Msgbox ""Hier ist die neue Prozedur"" " & Chr(13) & _
   "End Sub"
   End With

   Application.Run "MeineNeueProzedur"
End Sub

Beachte den Weg, wie die .InsertLines Methode verwendet wird. Die gesamte Prozedur wird als ein Argument aufbereitet. Mit dem Chr(13) werden die Zeilenumbrüche erzeugt. Die Anweisung

Application.Run "MeineNeueProzedur"

bewirkt, dass die Prozedur gleich gestartet wird. Anstatt die Prozedur direkt aufzurufen (Call), musst Du die Anweisung Application.Run verwenden. Damit kann ein eventueller Compile-Time Error vermieden werden. Die Methode Call wird nur funktionieren, wenn Du Code in ein anderes Code Modul einfügst. Wenn Code im selben Modul eingefügt wird, musst Du ein Application.OnTime einsetzen. Auf diese Weise wird die Kontrolle an Excel zurückgegeben und das Modul kann kompiliert und geladen werden. Mit der Benutzung von Application.OnTime können unter Umständen Synchronisierungsprobleme entstehen. Du solltest vermeiden, eine Prozedur aufzurufen, die gerade erst ins selbe Modul eingefügt wurde ohne, dass zuvor alle VBA Prozeduren die Möglichkeit hatten beendet zu werden.

Application.OnTime Now, "NeueProzedurName"

Zurück zum Anfang

7.) Eine Ereignis-Prozedur erzeugen

Das CodeModul Objekt verfügt über eine Methode mit dem Namen CreateEventProc, die benutzt werden kann, um eine Prozedur in einem Document-Modul zu erstellen. Z.B. Ein Tabellenmodul, oder das DieseArbeitsmappe-Modul. Der Vorteil der Benutzung von CreateEventProc über InsertLines besteht darin, dass CreateEventProc automatisch die gesamte Prozedur Deklaration, inklusive aller korrekten Parameter einfügt.

CreateEventProc gibt die Zeilennummer zurück, in welcher die Prozedur beginnt. Wenn Du einem CreateEventProc aufrufst, addierte eins zum Ergebnis und verwende dies mit InsertLines um den Kern der Ereignisprozedur einzufügen. Zum Beispiel: der untenstehende Code erzeugt eine Workbook_Open-Prozedur mit einer MsgBox. Der Code wird in DieseArbeitsmappe eingefügt.

Sub CreateEvent()
   Dim StartLine As Long

   With ActiveWorkbook.VBProject. _
        VBComponents("DieseArbeitsmappe").CodeModule

       StartLine = .CreateEventProc("Open", "Workbook") + 1
       .InsertLines StartLine, _
       "Msgbox ""Hallo Welt"",vbOkOnly"
   End With
End Sub

Zurück zum Anfang

8.) Eine Prozedur aus einem Modul entfernen

Die nachfolgende Prozedur löscht die Prozedur mit dem Namen "MeineNeueProzedur" aus dem Modul "NeuesModul" in ThisWorkbook.

Sub DeleteProcedure()
   Dim VBCodeMod As CodeModule
   Dim StartLine As Long
   Dim HowManyLines As Long

   Set VBCodeMod = ThisWorkbook.VBProject. _
                   VBComponents("NeuesModul").CodeModule
  
   With VBCodeMod
       StartLine = .ProcStartLine("MeineNeueProzedur", vbext_pk_Proc)
       HowManyLines = .ProcCountLines("MeineNeueProzedur", vbext_pk_Proc)
       .DeleteLines StartLine, HowManyLines
   End With
End Sub

Zurück zum Anfang

9.) Alle Prozeduren aus einem Modul entfernen

Die untenstehende Prozedur löscht sämtlichen Code aus dem Modul mit dem Namen "NeuesModul".

Sub DeleteAllCodeInModule()
   Dim VBCodeMod As CodeModule
   Dim StartLine As Long
   Dim HowManyLines As Long  

   Set VBCodeMod = ThisWorkbook.VBProject. _
                   VBComponents("NeuesModul"). _
                   CodeModule

   With VBCodeMod
       StartLine = 1
       HowManyLines = .CountOfLines
      .DeleteLines StartLine, HowManyLines
   End With
End Sub

Zurück zum Anfang

10.) Alle Module einer Mappe auflisten

Die nächste Prozedur listet, in einer MsgBox, alle Module in ThisWorkbook auf. Es wird eine Funktion mit dem Namen CompTypeToName verwendet, um eine Textbeschreibung vom Typ des Modul zu erhalten. Die Funktion ist ebenfalls nachfolgend zu finden.

Sub ListModules()
   Dim VBComp As VBComponent
   Dim Msg As String

   For Each VBComp In ThisWorkbook.VBProject.VBComponents
       Msg = Msg & VBComp.Name & " Type: " & _
             CompTypeToName(VBComp) & Chr(13)
   Next VBComp
   MsgBox Msg
End Sub

Function CompTypeToName(VBComp As VBComponent) As String
   Select Case VBComp.Type
       Case vbext_ct_ActiveXDesigner
           CompTypeToName = "ActiveX Designer"
       Case vbext_ct_ClassModule
           CompTypeToName = "Class Module"
       Case vbext_ct_Document
           CompTypeToName = "Document"
       Case vbext_ct_MSForm
           CompTypeToName = "MS Form"
       Case vbext_ct_StdModule
           CompTypeToName = "Standard Module"
       Case Else
   End Select
End Function

Zurück zum Anfang

11.) Alle Prozeduren eines Moduls auflisten

Die nächste Prozedur listet, in einer MsgBox, alle Prozeduren eines Standard Moduls auf. Das Standard Modul hat den Namen "NeuesModul" in ThisWorkbook. Die Prozeduren werden in der Reihenfolge aufgelistet, in der sie im Modul enthalten sind.

Sub ListProcedures()
   Dim VBCodeMod As CodeModule
   Dim StartLine As Long
   Dim Msg As String
   Dim ProcName As String

   Set VBCodeMod = ThisWorkbook.VBProject. _
                   VBComponents("NeuesModul").CodeModule

   With VBCodeMod
       StartLine = .CountOfDeclarationLines + 1
       Do Until StartLine >= .CountOfLines
           Msg = Msg & .ProcOfLine(StartLine, vbext_pk_Proc) & _
                 Chr(13)
                 StartLine = StartLine + _
                 .ProcCountLines(.ProcOfLine _
                 (StartLine, vbext_pk_Proc), _
                 vbext_pk_Proc)
       Loop
   End With
   MsgBox Msg
End Sub

Beachte den folgenden Hyperlink für mehr Informationen zu CodeName Eigenschaften von VBComponents: Code Modules And Code Names (englisch).

Zurück zum Anfang

12.) Alle Module in ein Projekt exportieren

Die nachfolgende Prozedur exportiert alle Module in den Ordner, in dem die Mappe abgespeichert ist. Damit kann eine schnelle Sicherung der VBA Prozeduren erfolgen, ohne dass jedes einzelne Modul exportiert werden muss.

Sub ExportAllVBA()
   Dim VBComp As VBIDE.VBComponent
   Dim Sfx As String

   For Each VBComp In ActiveWorkbook.VBProject.VBComponents
       Select Case VBComp.Type
           Case vbext_ct_ClassModule, vbext_ct_Document
              Sfx = ".cls"
           Case vbext_ct_MSForm
              Sfx = ".frm"
           Case vbext_ct_StdModule
              Sfx = ".bas"
           Case Else
              Sfx = ""
       End Select

       If Sfx <> "" Then
          VBComp.Export _
             Filename:=ActiveWorkbook.Path & "\" & _
             VBComp.Name & Sfx
       End If
   Next VBComp
End Sub

Zurück zum Anfang

13.) Sämtlichen VBA-Code eines Projekts löschen

Die folgende Prozedur löscht sämtliche Codezeilen aus dem gesamten Projekt. Du solltest diese Prozedur mit Vorsicht genießen, da der Code nicht wieder hergestellt werden kann. Es werden Standardmodule, UserForms und Klassenmodule entfernt. Ebenso wird der Code in ThisWorkbook-Modulen gelöscht. Zur Sicherheit könntest Du den vorangegangenen Code verwenden, um vor dem Löschen eine Sicherungskopie der Codes zu erstellen.

Sub DeleteAllVBA()
   Dim VBComp As VBIDE.VBComponent
   Dim VBComps As VBIDE.VBComponents

   Set VBComps = ActiveWorkbook.VBProject.VBComponents

   For Each VBComp In VBComps
      Select Case VBComp.Type
         Case vbext_ct_StdModule, vbext_ct_MSForm, _
              vbext_ct_ClassModule
              VBComps.Remove VBComp
         Case Else
            With VBComp.CodeModule
               .DeleteLines 1, .CountOfLines
            End With
      End Select
   Next VBComp
End Sub

  Zurück zum Anfang

14.) Module zwischen Projekten kopieren

Es gibt nicht einfach eine Methode, um Module zwischen Projekten zu kopieren. Der Code eines Projektes muss zuerst exportiert werden. Danach kann dieser in ein anderes Projekt importiert werden. Die folgende Prozedur exportiert das "Modul1" von der "Mappe2" in die "Mappe1".

Sub CopyOneModule()
   Dim FName As String

   With Workbooks("Mappe2")
       FName = .Path & "\code.txt"
       .VBProject.VBComponents("Modul1").Export FName
   End With

   Workbooks("Mappe1").VBProject.VBComponents.Import FName
End Sub

Ändere lediglich "Modul1" in den Namen des Moduls, das Du tatsächlich kopieren möchtest. Wenn Du alle Module kopieren möchtest (mit Ausnahme von DieseArbeitsmappe und den Tabellenmodulen), kannst Du den folgenden Code benutzen.

Sub CopyAllModules()
   Dim FName As String
   Dim VBComp As VBIDE.VBComponent

   With Workbooks("Mappe2")
       FName = .Path & "\code.txt"
       If Dir(FName) <> "" Then
           Kill FName
       End If

       For Each VBComp In .VBProject.VBComponents
           If VBComp.Type <> vbext_ct_Document Then
              VBComp.Export FName
              Workbooks("Mappe1").VBProject.VBComponents. _
                 Import FName
              Kill FName
           End If
       Next VBComp
   End With
End Sub

  Zurück zum Anfang

15.) Prüfen, ob ein Modul oder eine Prozedur existiert

Du kannst das VBA Extensibility Tool nutzen, um zu prüfen, ob ein Modul existiert, oder ob eine bestimmte Prozedur in einem Modul.

Function ProcedureExists(ProcedureName As String, _
       ModuleName As String) As Boolean

   On Error Resume Next
   If ModuleExists(ModuleName) = True Then
       ProcedureExists = ThisWorkbook.VBProject. _
                         VBComponents(ModuleName) _
          .CodeModule.ProcStartLine _
           (ProcedureName, vbext_pk_Proc) <> 0
   End If
End Function

Zurück zum Anfang

Dear Chip,

many thans for allowing me to share this great article with German speaking users.
We appreciate it.

Greetings,
Monika

PS: I see it as a birthday present ;-)