Wie speichert und verwendet man VBA-Makros, sodass sie in allen Excel-Arbeitsmappen zur Verfügung stehen?
In vielen Situationen müssen Sie dasselbe VBA-Makro wiederholt in verschiedenen Excel-Arbeitsmappen einsetzen – beispielsweise zur Automatisierung repetitiver Berechnungen, zur Datenformatierung oder für benutzerdefinierte Funktionen wie die Umwandlung von Zahlen in Wörter. Eine häufige Herausforderung dabei: Makros werden standardmäßig nur in der Arbeitsmappe gespeichert, in der sie erstellt wurden, und sind daher in neuen Dokumenten nicht ohne Weiteres verfügbar oder wiederverwendbar. Excel bietet jedoch mehrere flexible Methoden, um ein VBA-Makro global nutzbar zu machen und so das lästige wiederholte Kopieren des Codes bei jeder neuen Arbeitsmappe zu vermeiden. Dieses Tutorial liefert umfassende Anleitungen zu verschiedenen Ansätzen, damit Ihre VBA-Makros problemlos in allen Arbeitsmappen zur Verfügung stehen und Ihren Arbeitsablauf sowie Ihre Produktivität spürbar verbessern.
VBA-Code in allen Arbeitsmappen speichern und verwenden
Methode mit der persönlichen Makroarbeitsmappe
Angenommen, Sie möchten Zahlen mithilfe eines benutzerdefinierten VBA-Codes jederzeit in ihre englischen Wortäquivalente umwandeln – ganz gleich, in welcher Arbeitsmappe Sie gerade arbeiten. Mit dem richtigen Ansatz speichern Sie Ihre VBA-Module so, dass sie bei Bedarf jederzeit in Excel wiederverwendbar sind. Das ist besonders praktisch für benutzerdefinierte Funktionen oder Automatisierungen, die Ihnen stets zur Verfügung stehen sollen, ohne den Code in mehreren Dateien duplizieren zu müssen.
Dazu können Sie Ihren VBA-Code als benutzerdefiniertes Excel-Add-in verpacken, das sich in Excel aktivieren lässt und Ihre individuelle Funktionalität als global verfügbare Funktion bereitstellt.
Gehen Sie wie folgt vor:
1. Drücken Sie in Excel Alt + F11, um das Fenster „Microsoft Visual Basic for Applications“ zu öffnen.
2. Klicken Sie im VBA-Editor auf Einfügen > Modul und fügen Sie den folgenden Makrocode in das neu erstellte Modulfenster ein.
VBA-Code: Umwandlung von In Wörter umwandeln
Function NumberstoWords(ByVal MyNumber)
'Update by ExtendofficeDim xStr As StringDim xFNum As IntegerDim xStrPointDim xStrNumberDim xPoint As StringDim xNumber As StringDim xP() As VariantDim xDPDim xCnt As IntegerDim xResult, xT As StringDim xLen As IntegerOn Error Resume NextxP = Array("", "Thousand ", "Million ", "Billion ", "Trillion ", " ", " ", " ", " ")
xNumber = Trim(Str(MyNumber))
xDP = InStr(xNumber, ".")
xPoint = ""
xStrNumber = ""
If xDP >0 ThenxPoint = " point "
xStr = Mid(xNumber, xDP +1)
xStrPoint = Left(xStr, Len(xNumber) - xDP)
For xFNum =1 To Len(xStrPoint)
xStr = Mid(xStrPoint, xFNum,1)
xPoint = xPoint & GetDigits(xStr) & " "
Next xFNumxNumber = Trim(Left(xNumber, xDP -1))
End IfxCnt =0xResult = ""
xT = ""
xLen =0xLen = Int(Len(Str(xNumber)) /3)
If (Len(Str(xNumber)) Mod3) =0 Then xLen = xLen -1Do While xNumber <> ""
If xLen = xCnt ThenxT = GetHundredsDigits(Right(xNumber,3), False)
ElseIf xCnt =0 ThenxT = GetHundredsDigits(Right(xNumber,3), True)
ElsexT = GetHundredsDigits(Right(xNumber,3), False)
End IfEnd IfIf xT <> "" ThenxResult = xT & xP(xCnt) & xResultEnd IfIf Len(xNumber) >3 ThenxNumber = Left(xNumber, Len(xNumber) -3)
ElsexNumber = ""
End IfxCnt = xCnt +1LoopxResult = xResult & xPointNumberstoWords = xResultEnd FunctionFunction GetHundredsDigits(xHDgt, xB As Boolean)
Dim xRStr As StringDim xStrNum As StringDim xStr As StringDim xI As IntegerDim xBB As BooleanxStrNum = xHDgtxRStr = ""
On Error Resume NextxBB = TrueIf Val(xStrNum) =0 Then Exit FunctionxStrNum = Right("000" & xStrNum,3)
xStr = Mid(xStrNum,1,1)
If xStr <> "0" ThenxRStr = GetDigits(Mid(xStrNum,1,1)) & "Hundred "
ElseIf xB ThenxRStr = "and "
xBB = FalseElsexRStr = " "
xBB = FalseEnd IfEnd IfIf Mid(xStrNum,2,2) <> "00" ThenxRStr = xRStr & GetTenDigits(Mid(xStrNum,2,2), xBB)
End IfGetHundredsDigits = xRStrEnd FunctionFunction GetTenDigits(xTDgt, xB As Boolean)
Dim xStr As StringDim xI As IntegerDim xArr_1() As VariantDim xArr_2() As VariantDim xT As BooleanxArr_1 = Array("Ten ", "Eleven ", "Twelve ", "Thirteen ", "Fourteen ", "Fifteen ", "Sixteen ", "Seventeen ", "Eighteen ", "Nineteen ")
xArr_2 = Array("", "", "Twenty ", "Thirty ", "Forty ", "Fifty ", "Sixty ", "Seventy ", "Eighty ", "Ninety ")
xStr = ""
xT = TrueOn Error Resume NextIf Val(Left(xTDgt,1)) =1 ThenxI = Val(Right(xTDgt,1))
If xB Then xStr = "and "
xStr = xStr & xArr_1(xI)
ElsexI = Val(Left(xTDgt,1))
If Val(Left(xTDgt,1)) >1 ThenIf xB Then xStr = "and "
xStr = xStr & xArr_2(Val(Left(xTDgt,1)))
xT = FalseEnd IfIf xStr = "" ThenIf xB ThenxStr = "and "
End IfEnd IfIf Right(xTDgt,1) <> "0" ThenxStr = xStr & GetDigits(Right(xTDgt,1))
End IfEnd IfGetTenDigits = xStrEnd FunctionFunction GetDigits(xDgt)
Dim xStr As StringDim xArr_1() As VariantxArr_1 = Array("Zero ", "One ", "Two ", "Three ", "Four ", "Five ", "Six ", "Seven ", "Eight ", "Nine ")
xStr = ""
On Error Resume NextxStr = xArr_1(Val(xDgt))
GetDigits = xStrEnd Function3. Klicken Sie nun auf das Symbol „Speichern“ oben links im Fenster oder drücken Sie einfach Strg + S, um den Dialog „Speichern unter“ zu öffnen.
4. Geben Sie im Dialog „Speichern unter“ im Feld „Dateiname“ den gewünschten Namen ein. Wählen Sie in der Dropdown-Liste „Speichern im Format“ unbedingt Excel-Add-in (*.xlam) aus.
5. Klicken Sie auf „Speichern“, um Ihre Arbeitsmappe als Excel-Add-in-Datei zu speichern – so erstellen Sie ein wiederverwendbares Add-in, das jederzeit in jeder beliebigen Arbeitsmappe aktiviert werden kann.
6. Schließen Sie die Arbeitsmappe in Excel, nachdem Sie sie als Add-in gespeichert haben.
7. Öffnen Sie eine neue oder vorhandene Arbeitsmappe, in der Sie Ihr Makro nutzen möchten, und geben Sie die benutzerdefinierte Formel in die gewünschte Zelle ein (z. B. B2):
=NumberstoWords(A2)
Wechseln Sie zur Registerkarte Entwicklertools und klicken Sie in der Gruppe „Add-Ins“ auf die Schaltfläche Excel-Add-Ins.
9. Wählen Sie im erscheinenden Add-Ins-Dialog Durchsuchen.
10. Suchen Sie die zuvor gespeicherte Add-in-Datei, wählen Sie sie aus und klicken Sie auf OK.
11. Ihr benutzerdefiniertes Add-in, z. B. das „Zahl-in-Wörter-Umwandlungs-Add-in“, sollte nun in der Add-in-Liste erscheinen. Aktivieren Sie das Kontrollkästchen und klicken Sie auf OK, um es zu aktivieren.
12. Geben Sie nun erneut die benutzerdefinierte Funktion in die Zielzelle ein (z. B. B2) und drücken Sie Enter. Die Formel liefert jetzt die korrekten englischen Wörter für die Zahl.
=NumberstoWords(A2)13. Um die Umwandlungsformel schnell auf mehrere Zahlen anzuwenden, ziehen Sie am Ausfüllkästchen der Zelle nach unten und kopieren so die Formel in weitere Zellen.
Tipps & Hinweise:
- Indem Sie Ihr Makro als Add-in speichern, nutzen Sie dieselben benutzerdefinierten Funktionen, denselben Code oder dieselben Automatisierungen in allen Arbeitsmappen – und sparen so Zeit bei gleichzeitig verbesserter Konsistenz.
- Wenn Excel geschlossen wird oder das Add-in später deaktiviert ist, zeigen dessen Funktionen möglicherweise vorübergehend „#NAME?“ an, bis das Add-in erneut geladen wird. Um Verwirrung zu vermeiden, stellen Sie sicher, dass das Add-in im Add-in-Manager stets aktiviert bleibt, sobald es benötigt wird.
- Einige Benutzer sehen die Registerkarte „Entwicklertools“ standardmäßig nicht. Um sie einzublenden, klicken Sie mit der rechten Maustaste auf das Menüband, wählen „Menüband anpassen“ und aktivieren die Option „Entwicklertools“.
- Speichern Sie Add-ins am besten in einem dauerhaften Ordner, um fehlende Verweise zu vermeiden – selbst wenn Dateien verschoben oder umbenannt werden.
Wenn Sie den Code lieber manuell ausführen möchten, ist dies ebenfalls möglich und manchmal hilfreich beim Debuggen oder für spontane Anwendungen:
- Sie können einem Makro eine Schaltfläche in der Symbolleiste für den Schnellzugriff zuweisen, um es mit einem Klick in jeder sichtbaren Arbeitsmappe auszuführen. Klicken Sie dazu mit der rechten Maustaste auf die Symbolleiste für den Schnellzugriff, wählen Sie „Symbolleiste für den Schnellzugriff anpassen“ und fügen Sie Ihr Makro hinzu.
- Sie können auch Alt + F11 drücken, um den VBA-Editor zu öffnen, Ihr Makro manuell auszuwählen und anschließend F5 drücken, um den Code bei Bedarf auszuführen.
Vorteile: Mit dieser Lösung erstellen und teilen Sie umfangreiche, wiederverwendbare Makrofunktionen, die zuverlässig funktionieren – solange das Add-in aktiviert ist.
Nachteile: Benutzer müssen daran denken, das Add-in zu laden, und beim Teilen von Arbeitsmappen zusätzlich die Add-in-Datei sowie Details zu den Funktionen weitergeben. Zudem sind Add-ins in Excel Online nicht verwendbar.
Eine weitere äußerst praktische Möglichkeit, Ihre bevorzugten oder am häufigsten genutzten Makros in jeder Excel-Sitzung sofort griffbereit zu haben – unabhängig davon, welche Arbeitsmappe geöffnet ist –, ist die Verwendung der persönlichen Makroarbeitsmappe (PERSONAL.XLSB). Dabei handelt es sich um eine spezielle, ausgeblendete Excel-Datei, die beim Start von Excel automatisch geladen wird und sicherstellt, dass alle darin gespeicherten Makros in sämtlichen geöffneten Arbeitsmappen zur Verfügung stehen.
Anwendungsszenarien: Ideal für persönliche Automatisierungen, routinemäßige Formatierungsskripte oder Hilfsfunktionen, die Sie nicht als formale Excel-Add-Ins weitergeben müssen. Makros in PERSONAL.XLSB stehen auf Ihrem Computer zur Verfügung – unabhängig davon, welche Datei Sie gerade geöffnet haben.
Vorteile: Ihre Makros stehen global für Ihr lokales Excel-Profil zur Verfügung – ohne dass Sie Add-Ins oder zusätzliche Dateien installieren müssen.
Nachteile: Auf diese Weise gespeicherte Makros funktionieren nur auf dem Computer und mit dem Benutzerkonto, unter dem die Datei PERSONAL.XLSB vorhanden ist. Um sie mit anderen zu teilen, müssen Sie die Module manuell exportieren und importieren.
- Um diese Methode nutzen zu können, nehmen Sie zunächst ein Makro auf oder erstellen Sie eines und stellen sicher, dass es in der persönlichen Makroarbeitsmappe gespeichert wird.
Gehen Sie wie folgt vor:
- Öffnen Sie Excel. Klicken Sie auf der Registerkarte Ansicht auf Makros und anschließend auf Makro aufzeichnen.
- Wählen Sie im Dialogfeld unter „Makro speichern in“ die Option Persönliche Makroarbeitsmappe. Beenden Sie die Aufzeichnung (Sie können sie sofort beenden, falls nicht benötigt).
- Drücken Sie Alt + F11, um den VBA-Editor zu öffnen. Dort sehen Sie ein Projekt mit dem Namen PERSONAL.XLSB – fügen Sie hier ein neues Modul ein oder Ihren gewünschten Makrocode.
- Speichern Sie Ihre Änderungen – Excel erstellt und verwaltet die Arbeitsmappe PERSONAL.XLSB automatisch im Startordner.
- Makros in PERSONAL.XLSB können anschließend über das Makros-Dialogfeld ()Alt + F8) ausgeführt, Menüband- oder Symbolleisten-Schaltflächen zugewiesen oder aus VBA aufgerufen werden.
Fehlerbehebung und Wartung: Wenn Makros in der Datei PERSONAL.XLSB nicht verfügbar sind, prüfen Sie, ob Excel im abgesicherten Modus gestartet wurde oder ob die Makrosicherheitseinstellungen auf „Alle Makros deaktivieren“ stehen. Beachten Sie außerdem: Die Datei PERSONAL.XLSB ist standardmäßig ausgeblendet. Sollten Sie sie versehentlich ohne Speichern schließen oder löschen, müssen Sie möglicherweise ein Makro erneut aufzeichnen, um die Datei neu zu erstellen.
C:\Users\[YourUserName]\AppData\Roaming\Microsoft\Excel\XLSTART\PERSONAL.XLSBEin VBA-Code zum Auflisten aller Add-Ins in Excel
In Excel können Sie verschiedene Add-Ins hinzufügen, um Ihre Daten noch effizienter zu verarbeiten. Sicher wissen Sie bereits, dass Sie über das Optionsfenster alle Add-Ins anzeigen lassen können – aber wussten Sie auch, dass sich alle Add-Ins direkt in einem Arbeitsblatt auflisten lassen? In diesem Tutorial stellen wir Ihnen einen praktischen VBA-Code bereit, der genau das für Sie erledigt.
Wie führt man ein VBA-Makro beim Öffnen oder Schließen einer Arbeitsmappe aus?
In diesem Artikel erfahren Sie, wie Sie Ihren VBA-Code automatisch jedes Mal beim Öffnen oder Schließen der Arbeitsmappe ausführen können.
Wie schützt bzw. sperrt man VBA-Code in Excel?
Genau wie Sie Arbeitsmappen und Arbeitsblätter mit einem Passwort schützen können, lässt sich auch ein Kennwort zum Schutz Ihrer Makros in Excel festlegen.
Wie verwendet man eine Zeitverzögerung nach dem Ausführen eines VBA-Makros in Excel?
In bestimmten Fällen benötigen Sie möglicherweise eine Zeitverzögerung, bevor ein VBA-Makro in Excel ausgeführt wird. Beispielsweise soll beim Klicken auf ein bestimmtes Makro dessen Wirkung erst nach 10 Sekunden eintreten. Dieser Artikel zeigt Ihnen eine einfache Methode, wie Sie das umsetzen können.
Beste Office-Produktivitätstools
Verbessern Sie Ihre Excel-Kenntnisse mit Kutools für Excel und erleben Sie Effizienz wie nie zuvor.Kutools für Excel bietet über 300 erweiterte Funktionen zur Steigerung der Produktivität und Zeit sparen.Klicken Sie hier, um die Funktion zu erhalten, die Sie am dringendsten benötigen...
Office Tab bringt eine tabbasierte Oberfläche in Office und macht Ihre Arbeit viel einfacher
- Aktivieren Sie tabbasiertes Bearbeiten und Lesen in Word, Excel, PowerPoint, Publisher, Access, Visio und Project.
- Öffnen und erstellen Sie mehrere Dokumente in neuen Registerkarten desselben Fensters – statt jedes in einem separaten Fenster zu öffnen.
- Steigert Ihre Produktivität um 50 % und erspart Ihnen täglich Hunderte von Mausklicks!
Alle Kutools-Add-Ins – ein Installationsprogramm
Kutools for Office-Paket bündelt Add-Ins für Excel, Word, Outlook und PowerPoint sowie Office Tab Pro – ideal für Teams, die mit mehreren Office-Anwendungen arbeiten.
- Alles-in-einem-Paket— Add-Ins für Excel, Word, Outlook & PowerPoint sowie Office Tab Pro
- Ein Installationsprogramm, eine Lizenz— innerhalb weniger Minuten eingerichtet (MSI-fähig)
- Funktioniert besser zusammen— optimierte Produktivität über alle Office-Anwendungen hinweg
- 30-tägige Vollversion zum Testen— keine Registrierung, keine Kreditkarte erforderlich
- Bestes Preis-Leistungs-Verhältnis— sparen Sie im Vergleich zum Kauf einzelner Add-Ins