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 verwenden müssen, zum Beispiel für die Automatisierung wiederholter Berechnungen, Datenformatierungen oder benutzerdefinierter Funktionen wie das Konvertieren von Zahlen in Wörter. Eine häufige Herausforderung ist, dass Makros standardmäßig nur in der Arbeitsmappe gespeichert werden, in der sie erstellt wurden, was bedeutet, dass Sie sie nicht ohne weiteres in neuen Dokumenten wiederverwenden können. Excel bietet jedoch mehrere flexible Methoden, um ein VBA-Makro global verfügbar zu machen, sodass Sie den Code nicht jedes Mal neu kopieren müssen, wenn Sie eine neue Arbeitsmappe starten. 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.

Speichern und Verwenden des VBA-Codes in allen Arbeitsmappen
Methode der persönlichen Makroarbeitsmappe

Angenommen, Sie möchten in der Lage sein, Zahlen mit einem benutzerdefinierten VBA-Code in ihre entsprechenden 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 zur Verfügung haben möchten, ohne Code in mehreren Dateien zu duplizieren.

Um dies zu tun, können Sie Ihren VBA-Code als benutzerdefiniertes Excel-Add-In packen. Dieses Add-In kann in Excel aktiviert werden und stellt Ihre benutzerdefinierte Funktionalität als global verfügbare Funktion zur Verfügung.

Folgen Sie diesen Schritten:

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: Zahlen 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 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 im 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. Dies erstellt ein wiederverwendbares Add-In, das jederzeit für jede Arbeitsmappe aktiviert werden kann.

6. Sobald es gespeichert ist, kehren Sie zu Excel zurück und schließen 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 Entwickler Registerkarte und klicken Sie auf die Excel-Add-Ins Schaltfläche 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 OK.

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

12. Geben Sie nun die benutzerdefinierte Funktion erneut in die Zielzelle (z. B. B2) ein und drücken Sie die Eingabetaste. 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üllbereich der Zelle nach unten, um die Funktion auf 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 äußerst praktische Möglichkeit, 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 automatisch jedes Mal geladen wird, wenn Excel gestartet wird, sodass jedes darin 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 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.

Folgen Sie diesen Schritten:

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, wenn es nicht benötigt wird).
3. Drücken Sie Alt + F11, um den VBA-Editor zu öffnen, wo Sie ein Projekt für PERSONAL.XLSB sehen werden. Hier können Sie ein neues Modul einfügen oder Ihren gewünschten Makrocode einfügen.
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) ausgeführt, der Menüleiste oder Symbolleisten zugeordnet oder aus VBA aufgerufen werden.

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