Autor: Beate Schmitz  --- Aus Excel VBA - Gruppe: Grundlagen

Personl.xls - Die persönliche Makromappe

Autor: Beate Schmitz - Erstellt: 2006-03      - Letzte Revision: 2011-10
Persönliche Makroarbeitsmappe - personl.xls (bzw. personal.xlsb ab Excel2007)

Die Datei personl.xls nimmt unter den Excel-Dateien mit der Endung *.xls eine Sonderstellung ein, da sie ausgeblendet bei jedem Excelstart geöffnet wird. In ihr können Sie Makros sowie benutzerdefinierte Funktionen speichern und aus jeder Excel-Datei jederzeit auf diese zugreifen, sie ist sozusagen ein "Code-Behälter". Sie ist nicht automatisch nach der Excel-Installation vorhanden, sie muss unter Beachtung bestimmter Regeln zunächst erstellt werden.

Die Tabellenblätter sind üblicherweise leer - die Datei dient der excelweiten zur Verfügung-Stellung von Code. Die Tabellenblätter präsentieren sich also optisch wie eine neue leere Datei und deshalb ist es sinnvoll, sie ausgeblendet einzusetzen, damit man sich nicht irrtümlich an ihr vergreift.

Bedenken Sie bei der Weitergabe Ihrer Dateien, wenn darin Makros oder benutzerdefinierte Funktionen aus der personl.xls herangezogen werden, dass diese auf anderen Computern nicht laufen werden, da sie dort nicht zur Verfügung stehen. Wenn Sie Weitergabe planen, sollten Sie den Code entweder in der Datei selbst haben oder diesen in ein Add-In einbinden, welches Sie zusammen mit der Datei weitergeben.

Sie sollten also nur Code in die personl.xls einbinden, der nur für Sie von persönlichem Interesse ist und den Sie gerne aus verschiedenen Dateien heraus nutzen möchten. Gerne werden z.B. Codes zur Formatierung, Seiteneinrichtung, Selektierung oder automatischen Ausfüllung dort hinterlegt. Somit hat die Datei einen "sprechenden Namen" bezüglich ihres Sinns.

Folgende Grundlagen sollten Sie kennen: Hier zunächst eine Gegenüberstellung von personl.xls und Add-In, damit Sie für sich entscheiden, welches der passende Weg für Ihren Code ist:
 
 Vergleich
 personl.xlsAdd-In
Zweck"Behälter" zum Abruf persönlicher Makros und persönlicher benutzerdefinierter Funktionen"Behälter" zum Abruf allgemeiner Makros und allgemeiner benutzerdefinierter Funktionen
Übertragbarkeitstarke Bindung an den PCkann leicht weitergegeben werden - bessere Übertragbarkeit an andere Anwender
Funktionsaufrufbei Aufruf von Funktionen umständlichere Formel, z.B. =PERSONL.XLS!Gesperrt(B2)bei Aufruf von Funktionen einfachere Formel, der Mappenname braucht nicht vorangestellt zu werden, z.B. =Gesperrt(B2)
excelweite Verfügbarkeitda die Datei automatisch bei jedem Excelstart mitgeöffnet wird, ist der enthaltene Code immer verfügbarein Add-In ist nur verfügbar, wenn es installiert und aktiviert ist
Deaktivierungdazu muss bei geschlossenem Excel die Datei personl.xls vorübergehend aus dem Verzeichnis XLStart in ein anderes Verzeichnis verschoben werdeneinfach durch abhaken des entsprechenden Add-Ins unter Menü > Extras > Add-Ins…
Dateiendung*.xls*.xla
Speicherortim aktuellen Verzeichnis XLStartfrei definierbar - empfehlenswert ist jedoch ein Sammelverzeichnis AddIn, um Dateien bei Installation nicht suchen zu müssen
AusführungMakros werden angezeigt unter Menü > Extras > Makro > Makros....  und können von dort aus auch aufgerufen werdenMakros werden nicht angezeigt unter Menü > Extras > Makro > Makros....  von daher können sie nicht auf diesem Weg aufgerufen werden
Tempokein messbarer Unterschied
Offene Dateienwird von Excel immer mitgezählt beim Zählen offener Dateien, auch wenn ausgeblendetwird von Excel nicht mitgezählt beim Zählen offener Dateien; standardmäßig ausgeblendet
 


Zum Blattanfang

Wie erstellt man die Datei personl.xls?

Wie zu Beginn erwähnt, bringt Excel die Datei personl.xls nicht von Hause aus mit, Sie müssen sie selbst anlegen.

Damit Sie die personl.xls sicher im richtigen Verzeichnis XLStart anlegen, empfiehlt es sich, diese durch Aufzeichnung eines kleinen Makros zu erstellen. Dieses Makro kann ja später aus der Datei gelöscht werden.

Das gehe ich jetzt hier Schritt für Schritt mit Ihnen durch, am Beispiel eines simplen und ungefährlichen Makros:
  • öffnen Sie eine neue Datei
  • gehen Sie über Menü > Extras > Makro > Aufzeichnen....
  • die Dialogbox Makro aufzeichnen springt auf
  • geben Sie im Dialogfeld Makroname ihrem Makro einen aussagekräftigen Namen, z.B. blaue_Zelle, die anderen Voreinstellungen können Sie unverändert so beibehalten
  • wählen Sie dort im Dropdown aus, dass Ihr Makro in der Persönlichen Makroarbeitsmappe gespeichert werden soll
  • die Makroaufzeichnung starten Sie mit Klick auf die Schaltfläche OK
  • klicken Sie auf das Füllfarbeicon und wählen blau aus
  • danach stoppen Sie die Aufzeichnung durch Klick auf das Aufzeichnen-Beenden-Kästchen links auf der gleichnamigen Symbolleiste
  • schließen Sie Excel - dabei springt die Rückfrage auf, ob die Änderungen der persönlichen Makroarbeitsmappe gespeichert werden sollen?
  • bestätigen Sie diese mit Ja und ab nun haben Sie eine personl.xls, auch wenn Sie diese nicht sehen, da sie ausgeblendet ist. Excel vergibt den Dateinamen personl.xls automatisch und speichert ebenso automatisch in das Verzeichnis XLStart

Zum Blattanfang

Wo ist die Datei personl.xls gespeichert?

Die personl.xls muss zwingend im Verzeichnis XLStart gespeichert sein, sonst wird sie nicht beim Excel-Start mitgeöffnet und die enthaltenen Makros sowie benutzerdefinierten Funktionen stehen nicht zur Verfügung.

Je nach Windows-Version kann das XLStart-Verzeichnis an verschiedenen Stellen liegen. Haben Sie früher auf Ihrem System andere Excelversionen installiert gehabt, kann es auch sein, dass das XLStart-Verzeichnis mehrfach vorkommt. Es gibt aber jedenfalls nur ein aktuell Verwendetes und das müssen sie finden.

Suchen Sie direkt im Explorer nach der Datei personl.xls und lassen Sie sicherheitshalber den ganzen Arbeitsplatz mit Unterverzeichnissen durchsuchen:
  • wenn diese nicht gefunden wird, existiert sie noch nicht und Sie müssen sie anlegen
  • finden sich mehrere Dateien dieses Namens, dann wird die mit dem jüngsten Datum die aktuelle personl.xls im aktuell von Excel herangezogenen XLStart-Verzeichnis sein
Dies hier als Beispiel, warum Suche nach der aktuellen Version wichtig ist:
Bei mir selbst (Excel 2002) ist die aktuelle Datei in diesem Ordner gespeichert:
C:\Dokumente und Einstellungen\Schmitz\Application Data\Microsoft\Excel\XLStart
In anderen Versionen kann sie aber auch im Office-Verzeichnis liegen, so findet sich auch noch diese Altlast auf meinem System:
C:\Programme\Microsoft Office\Office\XLStart
Nun verstehen Sie wohl, wie wichtig es ist, die Aktuelle herauszufinden, denn nur die springt beim Excelstart an.

Zum Blattanfang

Pfad des aktuellen XLStart-Verzeichnisses per Makro ermitteln

Beide Codes gehören in ein Modul:

Möglichkeit 1: Ausgabe in einer MsgBox:

Sub ZeigePfadMeinesStartordners()
  MsgBox Application.StartupPath
End Sub

Möglichkeit 2: Ausgabe im Direktfenster; praktisch, wenn Sie ihn rauskopieren möchten:

Sub PfadMeinesStartordners_ins_Direktfenster()
  Debug.Print Application.StartupPath
End Sub


Zum Blattanfang

Abhilfe: Trotz korrektem Speicherort startet die Datei personl.xls nicht

Problem: Ihre personl.xls startet nicht beim Excelstart und Sie sind sich sicher, dass die Datei im aktuellen XLStart-Verzeichnis gespeichert ist.

Grund: Dann ist die Datei personl.xls von Excel deaktiviert worden, weil sie ein Problem verursacht hat.

Abhilfe: So können Sie die personl.xls (oder auch andere deaktivierte Elemente) reaktivieren: Guckst Du: Deaktivierte Elemente aktivieren

Zum Blattanfang

Wie blendet man die Datei personl.xls ein oder aus?

Wenn die personl.xls nicht gerade bearbeitet wird (dazu muss sie auch nicht zwingend eingeblendet ein, siehe hier), sollte sie ausgeblendet sein. Dann wird sie Ihnen auch nicht in der Liste der geöffneten Dateien aufgeführt. Zur Verfügung stehen die Makros und benutzerdefinierten Funktion auch im ausgeblendeten Zustand. Sollten Sie aus irgendeinem Grund die Datei eingeblendet haben, blenden sie diese nach Speichern Ihrer Änderungen auch bitte wieder aus. Beim Schließen von Excel werden Sie gefragt, ob Sie die Änderungen an der personl.xls speichern möchten, bestätigen Sie dies, weil diese Rückfrage sich auf den ausgeblendeten Zustand bezieht, den Inhalt haben Sie ja bereits gespeichert.

Um die personl.xls einzublenden gehen Sie über Menü > Fenster > Einblenden.... . Dann wählen Sie mit der Maus im aufspringenden Dialogfenster die Datei personl.xls aus und verlassen den Dialog über die Schaltfläche OK.

Um die personl.xls auszublenden aktivieren Sie die Datei personl.xls und gehen über Menü > Fenster > Ausblenden....

Zum Blattanfang

Wie kann man die personl.xls nachträglich bearbeiten, ergänzen oder Code löschen?

Möglichkeit 1:
  • dafür müssen Sie bei geöffnetem Excel die personl.xls wie hier beschrieben einblenden
  • nun können Sie in die Module Code durch kopieren einfügen oder Code nachbearbeiten oder auch löschen. Dies funktioniert wie in jeder anderen Exceldatei; bei Bedarf siehe: Wie und wo fügt man ein Makro bzw. Code ein
  • nach den gewünschten Änderungen speichern Sie die personl.xls und blenden sie wie hier beschrieben aus
Möglichkeit 2:

Wenn Sie sich in der Entwicklungsumgebung für VBA befinden (dahin gelangen Sie über die Tastenkombination Alt + F11), können Sie auch von dort aus direkt auf die Module der personl.xls zugreifen und deren Inhalt bearbeiten. Dann ist Ein- und Ausblenden nicht nötig. Denken Sie aber ans Speichern Ihrer Änderungen.

Möglichkeit 3:

Sie können natürlich auch per Aufnahme Makros der personl.xls zufügen, wenn Sie zu Beginn der Aufnahme im Dropdown ausgewählt haben, dass Ihr Makro in der Persönlichen Makroarbeitsmappe gespeichert werden soll. Dabei entfallen die Arbeitsschritte ein- und ausblenden.

Zum Blattanfang

Wie löscht man die Datei personl.xls?

Löschen können Sie die aktuelle personl.xls nur bei geschlossenem Excel über den Explorer. Dies nur für den Fall, falls Ihre derzeitige nur unrelevanten Code enthält, der nicht im Einsatz ist. Dann können Sie sich schneller eine neue personl.xls anlegen, als die Bestehende umfangreich aufzuräumen.

Finden können Sie im Explorer die aktuelle personl.xls auf diesem Weg.

Also: Vorsicht mit diesem Schritt, wenn Sie unsicher sind, ob Code aus der aktuellen personl.xls irgendwo im Einsatz ist!

Zum Blattanfang

Wie überträgt man die Datei personl.xls auf einen anderen PC?

Lesen Sie diesen Absatz bitte vor Durchführung komplett, weil Gefahren lauern!

Sie können diese Datei wie jede andere Datei kopieren und per Mail oder Datenträger auf einem anderen PC übertragen. Auf dem Quell- sowie dem Zielcomputer finden Sie im Explorer die aktuelle personl.xls bzw. das aktuelle XLStart-Verzeichnis auf diesem Weg.

Wenn auf dem Zielcomputer keine personl.xls existiert, legen Sie dort eine neue personl.xls an, um alle Unsicherheiten über den korrekten Pfad auszuräumen. Die so entstandene neue personl.xls können Sie dann an der richtigen Stelle über den Explorer mit Ihrer Version der Datei überschreiben.

Existiert auf dem Zielcomputer bereits eine aktuelle personl.xls ist Vorsicht geboten!!! Eventuell greifen Dateien auf Code dieser Datei zu, die nach Überschreiben der personl.xls durch Ihre Version ins Leere liefen. Dann benennen Sie lieber Ihre Datei bis Sicherheit besteht in personl2.xls um und fügen Sie danach erst ins aktuelle XLStart-Verzeichnis ein. Dann stehen Ihnen ab dem nächsten Excelstart ausgeblendet beide Dateien mit ihrem Codeangebot zur Verfügung.

Zum Blattanfang

Wie ruft man ein Makro der personl.xls aus einer anderen Datei heraus auf?

Berücksichtigen Sie grundsätzlich:
Die Routine muss eine Sub sein, darf nicht als Private definiert sein und darf keine Parameter besitzen, sonst geht es nicht.

Möglichkeit 1: Per Tastenkombination

Sie weisen dem Makro der personl.xls eine Tastenkombination zu. Dann springt das Makro aus jeder Excel-Datei heraus an, wenn Sie diese Tastenkombination drücken (also in Kombination mit Strg oder Strg+Umschalt). Da man sich nur beschränkt Tastenkombinationen merken kann, sollte man diesen Weg nur bei sehr häufig genutzten Makros wählen. Nachträglich können Sie eine Tastenkombination wie folgt zuweisen:
  • gehen Sie über Menü > Extras > Makro > Makros....
  • wählen Sie im Dropdown unter Makros in die Personl.xls aus
  • markieren Sie mit der Maus das gewünschte Makro, so dass es im Feld Makroname erscheint
  • klicken Sie auf die Schaltfläche Optionen...
  • ein weiteres Dialogfenster springt auf und Sie können dort die gewünschte Tastenkombination eingeben
  • berücksichtigen Sie, welche Tastenkombinationen Sie bereits vergeben haben bzw. welche Tastenkombinationen von Excel selbst vorbelegt sind
  • schließen Sie das Dialogfenster über die Schaltfläche OK
  • verlassen Sie das nun noch offene Dialogfenster Makro über das Schließenkreuz

Möglichkeit 2: Aus einem Makro heraus

Über ein Makro in der aufrufenden Datei, so wird z.B. unser oben erstelltes Makro blaue_Zelle aufgerufen:

Sub Aufruf_blaue_Zelle()
Application.Run ("Personl.xls!Blaue_Zelle")
End Sub


Auch in längeren Code der Ausgangsdatei kann diese Codezeile an der passenden Stelle eingebunden werden.


Möglichkeit 3: Über eine Schaltfläche auf einer Symbolleiste

Für häufig genutzte Makros der personl.xls lohnt sich eventuell der Einbau einer entsprechenden Schaltfläche, der Sie das Makro zuweisen. Das geht so:
  • gehen Sie über Menü > Ansicht > Symbolleisten > Anpassen.... > Registerblatt: Befehle
  • wählen Sie unter Kategorien: Makros aus
  • unter Befehle sehen sie ein Smiley-Symbol (das ist austauschbar, aber wir nehmen das für unser Beispiel), dieses markieren Sie durch anklicken
  • ziehen Sie das Symbol mit der Maus an die gewünschte Stelle in der gewünschten Symbolleiste
  • klicken Sie nun das neu erstellte Symbol mit der rechten Maustaste an und wählen dann aus: Makro zuweisen...
  • das Dialogfenster Makro zuweisen springt auf
  • wählen Sie im Dropdown unter Makros in die Personl.xls aus
  • markieren Sie mit der Maus das gewünschte Makro, so dass es im Feld Makroname erscheint
  • verlassen Sie das Dialogfenster über die Schaltfläche OK
  • schliessen Sie nun das Dialogfenster Anpassen über die Schaltfläche Schließen
Ab nun startet Ihr Makro bei Klick auf die Schaltfläche. Diese Änderung wirkt sich auf Ihre *.xlb-Datei aus, in der Ihre Menüeinstellungen und Symbolleisten hinterlegt sind.

Das Icon können Sie auch wieder löschen, indem Sie
  • über Menü > Ansicht > Symbolleisten > Anpassen.... > Registerblatt: Befehle gehen
  • wenn das Dialogfenster aktiv ist, können Sie das Icon einfach aus der Symbolleiste mit der Maus auf das Dialogfenster ziehen
  • dieses dann über die Schaltfläche Schließen beenden

Möglichkeit 4: Zu Fuß

  • gehen Sie über Menü > Extras > Makro > Makros....
  • die Dialogbox Makro springt auf
  • wählen Sie im Dropdown unter Makros in die Personl.xls aus
  • markieren Sie mit der Maus das gewünschte Makro, so dass es im Feld Makroname erscheint
  • klicken Sie auf die Schaltfläche Ausführen

Zum Blattanfang

Wie benutzt man eine benutzerdefinierte Funktion der personl.xls in einer anderen Datei?

Wenn Sie eine benutzerdefinierte Funktion in der personl.xls haben, können Sie diese Funktion aus jeder Exceldatei heraus einsetzen. Sie müssen allerdings in der Formel bzw. im Code angeben, dass sie sich in der personl.xls befindet.

Möglichkeit 1: Als Funktion in einer Zelle

Beispiel: Angenommen, Sie haben diese benutzerdefinierte Funktion Gesperrt in ihrer personl.xls:

Function Gesperrt(ByVal Text As String) As String
Dim n As Integer
   If Text <> "" Then
      For n = 1 To Len(Text)
      Gesperrt = Gesperrt & Mid(Text, n, 1) & " "
      Next
   End If
End Function

Dann muss die Formel in einer Zelle (hier C2) so lauten, damit sie ausgeführt wird:
 
 ABCD
1    
2 Anton GimpelA n t o n   G i m p e l  
3    
Formeln der Tabelle
C2 : =PERSONL.XLS!Gesperrt(B2)
 

Anmerkung: Wäre der gleiche Code in einem Add-In würde diese Formel ausreichen: =Gesperrt(B2)


Möglichkeit 2: Funktion der personl.xls in ein Makro einer anderen Datei einbinden - ohne Parameter

Wie schon oben erwähnt, muss die Routine eine Sub sein, darf nicht als Private definiert sein und darf keine Parameter besitzen. Deshalb müssen wir hier einen Umweg gehen.

Zusätzlich zu obiger Funktion Gesperrt müssen wir in die personl.xls folgendes Makro einfügen:

Sub Aufruf_Gesperrt()
   MsgBox Gesperrt(ActiveCell)
End Sub

Und in die aufrufende Datei dann folgendes Makro (dieses Makro hier gibt dann den Inhalt der aktiven Zelle in einer MsgBox in gesperrter Schrift wieder):

Sub Aufruf_Gesperrt2()
   Application.Run ("Personl.xls!Aufruf_Gesperrt")
End Sub


Möglichkeit 3: Funktion der personl.xls in ein Makro einer anderen Datei einbinden - mit Parametern

Wenn Sie der Run-Methode Parameter mitgeben wollen, müssen Sie folgendes beachten:
  • rufen Sie eine Sub oder eine Funktion ohne Rückgabewert auf, dann darf der Ausdruck nicht in Klammern stehen
  • rufen Sie eine Funktion mit Rückgabewert auf, dann muss der Ausdruck in Klammern stehen.
Dafür verändern wir unsere bisherige benutzerdefinierte Funktion Gesperrt wie folgt in der personl.xls:

Public Function Gesperrt3(ByVal sText As String, _
    ByRef iBlanks As Integer) As String
    Dim iCount As Integer
    For iCount = 1 To Len(sText) - 1
        Gesperrt3 = Gesperrt3 & Mid$(sText, iCount, 1) & _
            String(iBlanks, vbKeySpace)
    Next
    Gesperrt3 = Gesperrt3 & Right$(sText, 1)
End Function

Aufruf aus einer anderen Datei ohne Rückgabewert ohne Klammern:

Sub Aufruf_Gesperrt3()
    Application.Run "Personl.xls!Gesperrt3", ActiveCell.Text, 2
End Sub

Aufruf aus einer anderen Datei mit Rückgabewert mit Klammern:

Sub Aufruf_Gesperrt4()
    MsgBox Application.Run("Personl.xls!Gesperrt3", ActiveCell.Text, 4)
End Sub


Zum Blattanfang

Wie schützt man die Datei personl.xls vor Virenattacken oder ungewollter Veränderung?

Grund für folgenden Tipp ist, dass Viren häufig so programmiert werden, dass sie sich in genau diese Datei einschleichen.

Nachdem Sie Ihre wichtigen Codes in die Datei eingefügt haben, sollten Sie überlegen, ob Sie diese Datei nicht schreibschützen wollen. Finden können Sie im Explorer die aktuelle personl.xls auf diesem Weg. Das Setzen des Schreibschutzes funktioniert so:
  • Rechtsklick im Explorer auf die Datei personl.xls
  • Eigenschaften anklicken
  • im Registerblatt Allgemein haken Sie unter Dateiattribute: "Schreibgeschützt" an
  • und verlassen dann das Dialogfenster über die Schaltfläche OK
Dieser Weg schützt Ihre Datei weitmöglichst auch vor ungewollter und unbemerkter Veränderung durch Sie selbst oder Mitbenutzer, neben dem "kleinen" Schutz, dass sie normalerweise ausgeblendet ist.

Natürlich können Sie die Datei trotzdem später noch verändern, indem Sie vorübergehend dann den Schreibschutz wieder aufheben.


Weitere Artikel der Gruppe: Grundlagen Aus Excel VBA
Nach oben
rechte seite