Wie speichert und verwendet man seine VBA-Makros in allen Arbeitsmappen in Excel?

Es gibt viele Situationen, in denen Sie dasselbe VBA-Makro wiederholt in verschiedenen Excel-Arbeitsmappen für Aufgaben wie die Automatisierung wiederholter Berechnungen, Datenformatierung oder benutzerdefinierte Funktionen wie das Umwandeln von Zahlen in Wörter verwenden müssen. Eine häufige Herausforderung ist, dass Makros standardmäßig nur in der Arbeitsmappe gespeichert werden, in der sie erstellt wurden, was bedeutet, dass Sie nicht einfach auf sie zugreifen oder sie in neuen Dokumenten wiederverwenden können. Excel bietet jedoch mehrere flexible Methoden, um ein VBA-Makro global verfügbar zu machen und so zu vermeiden, dass Sie den Code jedes Mal neu kopieren müssen, wenn Sie eine neue Arbeitsmappe beginnen. Dieses Tutorial bietet umfassende Anweisungen für verschiedene Ansätze, um sicherzustellen, dass Ihre VBA-Makros leicht in allen Arbeitsmappen zugänglich sind und so Ihre Produktivität und Ihren Workflow verbessern.

VBA-Code in allen Arbeitsmappen speichern und verwenden
Persönliche Makroarbeitsmappe Methode

Angenommen, Sie möchten beispielsweise in der Lage sein, Zahlen mit einem benutzerdefinierten VBA-Code in ihre äquivalenten englischen Wörter umzuwandeln und sicherstellen, dass diese Funktion immer verfügbar ist, unabhängig davon, in welcher Arbeitsmappe Sie arbeiten. Mit dem richtigen Ansatz können Sie Ihre VBA-Module so speichern, dass sie immer dann wiederverwendet werden können, wenn Sie sie in Excel benötigen. Dies ist besonders hilfreich für benutzerdefinierte Funktionen oder Automatisierungen, die Sie immer verfügbar haben möchten, ohne Code in mehreren Dateien zu duplizieren.

Um dies zu tun, können Sie Ihr VBA-Code als benutzerdefiniertes Excel-Add-In verpacken. Dieses Add-In kann in Excel aktiviert werden und macht Ihre benutzerdefinierten Funktionen global verfügbar.

Führen Sie die folgenden Schritte aus:

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 das folgende Makro in das neu erstellte Modulfenster ein.

VBA-Code: Konvertiere Zahlen in Wörter

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 Function

3. Klicken Sie nun auf das Symbol „Speichern“, das sich in der oberen linken Ecke des Fensters befindet, oder drücken Sie einfach Strg + S um das Dialogfeld „Speichern unter“ zu öffnen.

4. Geben Sie im Fenster „Speichern unter“ den gewünschten Dateinamen in das Feld „Dateiname“ ein. Wählen Sie im Dropdown-Menü „Speichern als Typ“ unbedingt Excel-Add-In (*.xlam).

5. Klicken Sie auf die Schaltfläche „Speichern“, um Ihre Arbeitsmappe als Excel-Add-In-Datei zu speichern. Dadurch wird ein wiederverwendbares Add-In erstellt, das jederzeit für jede Arbeitsmappe aktiviert werden kann.

6. Sobald es gespeichert ist, kehren Sie zu Excel zurück und schließen Sie die Arbeitsmappe, die Sie gerade in ein Add-In konvertiert haben.

7. Öffnen Sie eine neue oder vorhandene Arbeitsmappe, in der Sie Ihr Makro verwenden möchten. Geben Sie die benutzerdefinierte Formel in die entsprechende Zelle ein (z.B. in B2):

=NumberstoWords(A2)

8. Gehen Sie zur Registerkarte Entwickler und klicken Sie auf die Schaltfläche Excel-Add-Ins in der Gruppe Add-Ins.

9. Wählen Sie im erscheinenden Add-Ins-Dialogfeld Durchsuchen.

10. Suchen und wählen Sie die zuvor gespeicherte Add-In-Datei aus, und klicken Sie auf OK.

11. Ihr benutzerdefiniertes Add-In, wie z.B. "Convert Number To Words Add-in", sollte jetzt in der Add-Ins-Liste erscheinen. Stellen Sie sicher, dass es aktiviert ist, und klicken Sie auf OK um es zu aktivieren.

12. Geben Sie nun die benutzerdefinierte Funktion erneut in die Zielzelle (z.B. B2) ein und drücken Sie Enter. Sie sollten sehen, dass die Formel die korrekten englischen Wörter für die Zahl zurückgibt.

=NumberstoWords(A2)

13. Um die Konvertierungsformel schnell auf mehrere Zahlen anzuwenden, ziehen Sie den Autoausfüllgriff der Zelle nach unten, um die Funktion in andere Zellen zu kopieren.

1. Sie können ein Makro der Schnellzugriffsleiste zuweisen, um es mit einem Klick in jeder sichtbaren Arbeitsmappe auszuführen. Klicken Sie dazu mit der rechten Maustaste auf die Schnellzugriffsleiste, wählen Sie "Schnellzugriffsleiste anpassen" und fügen Sie Ihr Makro hinzu.
   
2. Sie können auch Alt + F11 drücken, um den VBA-Editor zu öffnen, Ihr Makro manuell auswählen und F5 drücken, um den Code bei Bedarf auszuführen.

Eine weitere sehr praktische Möglichkeit, um sicherzustellen, dass Ihre Lieblings- oder am häufigsten verwendeten Makros in jeder Excel-Sitzung bereit sind, unabhängig davon, welche Arbeitsmappe geöffnet ist, ist die Verwendung der persönlichen Makroarbeitsmappe (PERSONAL.XLSB). Dies ist eine spezielle, ausgeblendete Excel-Datei, die jedes Mal automatisch geladen wird, wenn Excel gestartet wird, sodass jedes in ihr gespeicherte Makro in allen geöffneten Arbeitsmappen verfügbar ist.

Anwendbare Szenarien: Ideal für persönliche Automatisierung, Routine-Formatierungsskripte oder Dienstprogrammfunktionen, die Sie nicht als formale Excel-Add-Ins freigeben müssen. Makros in PERSONAL.XLSB sind auf Ihrem Computer verfügbar, unabhängig davon, welche Datei geöffnet ist.

Vorteile: Makros sind global für Ihr lokales Excel-Profil verfügbar und erfordern keine Installation von Add-Ins oder zusätzlichen Dateien.
Nachteile: Auf diese Weise gespeicherte Makros können nur auf dem Computer und dem Konto verwendet werden, auf dem PERSONAL.XLSB existiert. Um sie mit anderen zu teilen, müssen Module manuell exportiert und importiert werden.

• Um diese Methode zu verwenden, müssen Sie zuerst ein Makro aufzeichnen oder erstellen und sicherstellen, dass es in der persönlichen Makroarbeitsmappe gespeichert wird.

Führen Sie die folgenden Schritte aus:

1. Öffnen Sie Excel. Klicken Sie auf der Registerkarte Ansicht auf Makros und dann auf Makro aufzeichnen.
2. Wählen Sie im Dialogfeld unter "Makro speichern in" die persönliche Makroarbeitsmappe aus. Beenden Sie die Aufzeichnung (Sie können sofort stoppen, falls nicht benötigt).
3. Drücken Sie Alt + F11, um den VBA-Editor zu öffnen, wo Sie ein Projekt für PERSONAL.XLSB sehen werden. Fügen Sie hier ein neues Modul ein oder fügen Sie Ihren gewünschten Makrocode ein.
4. Speichern Sie Ihre Änderungen. Excel erstellt und pflegt automatisch die PERSONAL.XLSB-Arbeitsmappe in ihrem Startordner.
5. Makros in PERSONAL.XLSB können dann über das Makros-Dialogfeld (Alt + F8), über Schaltflächen im Menüband oder Symbolleisten oder über VBA aufgerufen werden.

Problembehebung & Wartung: Wenn Makros in PERSONAL.XLSB nicht verfügbar sind, überprüfen Sie, ob Excel im abgesicherten Modus geöffnet wird oder ob die Makro-Sicherheitseinstellungen auf "Alle Makros deaktivieren" gesetzt sind. Darüber hinaus ist PERSONAL.XLSB standardmäßig ausgeblendet; wenn Sie versehentlich ohne Speichern schließen oder es löschen, müssen Sie möglicherweise ein Makro neu aufzeichnen, um es neu zu generieren.