Hoe VBA-macros op te slaan en gebruiken in alle werkboeken in Excel?
Er zijn veel situaties waarin je mogelijk dezelfde VBA-macro herhaaldelijk moet gebruiken in verschillende Excel-werkboeken voor taken zoals het automatiseren van repetitieve berekeningen, gegevensopmaak of aangepaste functies zoals het omzetten van getallen in woorden. Een veelvoorkomend probleem is dat macros standaard alleen worden opgeslagen in het werkboek waarin ze zijn gemaakt, wat betekent dat je ze niet gemakkelijk kunt openen of hergebruiken in nieuwe documenten. Gelukkig biedt Excel verschillende flexibele methoden om een VBA-macro globaal beschikbaar te maken, zodat je de code niet telkens opnieuw hoeft te kopiëren wanneer je een nieuw werkboek start. Deze handleiding biedt uitgebreide instructies voor verschillende benaderingen om ervoor te zorgen dat je VBA-macros eenvoudig toegankelijk zijn in alle werkboeken, waardoor je productiviteit en workflow verbeteren.
Sla de VBA-code op en gebruik deze in alle werkboeken
Persoonlijke Macro-werkmap methode
Stel bijvoorbeeld dat je getallen wilt kunnen omzetten naar hun equivalente Engelse woorden met behulp van een aangepaste VBA-code en ervoor wilt zorgen dat deze functie altijd beschikbaar is, ongeacht in welk werkboek je werkt. Met de juiste aanpak kun je je VBA-modules opslaan zodat ze herbruikbaar zijn wanneer je ze nodig hebt in Excel. Dit is vooral handig voor aangepaste functies of automatisering die je elke keer wilt gebruiken, zonder code te dupliceren in meerdere bestanden.
Om dit te doen, kun je je VBA-code inpakken als een aangepaste Excel-invoegtoepassing. Deze invoegtoepassing kan worden ingeschakeld in Excel en zal je aangepaste functionaliteit blootleggen als een functie die wereldwijd beschikbaar is.
Volg deze stappen:
1. Druk in Excel op Alt + F11 om het venster "Microsoft Visual Basic for Applications" te openen.
2. Klik in de VBA-editor op Invoegen > Module en plak de volgende macro in het zojuist gemaakte modulevenster.
VBA-code: Converteer getallen naar woorden
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. Klik nu op het pictogram "Opslaan" linksboven in het venster of druk gewoon op Ctrl + S om het dialoogvenster "Opslaan als" te openen.
4. Voer in het venster "Opslaan als" de gewenste bestandsnaam in het veld "Bestandsnaam" in. Zorg er bij de vervolgkeuzelijst "Opslaan als type" voor dat je Excel-invoegtoepassing (*.xlam).
5. Klik op de knop "Opslaan" om je werkboek op te slaan als een Excel-invoegtoepassingsbestand. Hiermee maak je een herbruikbare invoegtoepassing die op elk moment kan worden ingeschakeld voor elk werkboek.
6. Wanneer deze is opgeslagen, ga terug naar Excel en sluit het werkboek dat je zojuist hebt omgezet naar een invoegtoepassing.
7. Open een nieuw of bestaand werkboek waarin je je macro wilt gebruiken. Voer de aangepaste formule in de juiste cel in (bijvoorbeeld in B2):
=NumberstoWords(A2)
8. Ga naar de Ontwikkelaar tab en klik op de Excel-invoegtoepassingen knop in de groep Invoegtoepassingen.
9. Selecteer in het dialoogvenster Invoegtoepassingen dat verschijnt Bladeren.
10. Zoek en selecteer het eerder opgeslagen invoegtoepassingsbestand en klik daarna op OK.
11. Je aangepaste invoegtoepassing, zoals "Converteer Getal Naar Woorden Invoegtoepassing", zou nu moeten verschijnen in de lijst met invoegtoepassingen. Zorg ervoor dat deze is aangevinkt en klik op OK om deze in te schakelen.
12. Voer nu de aangepaste functie opnieuw in de doelcel in (zoals B2) en druk op Enter. Je zou moeten zien dat de formule de juiste Engelse woorden retourneert voor het getal.
=NumberstoWords(A2)13. Om snel de conversieformule toe te passen op meerdere getallen, sleep je de autofill-hendel van de cel naar beneden om de functie naar andere cellen te kopiëren.
Tips & Notities:
- Het opslaan van je macro als een invoegtoepassing laat je toe om dezelfde aangepaste functies, code of automatiseringen in al je werkboeken te gebruiken, wat tijd bespaart en consistentie verbetert.
- Als Excel wordt gesloten of de invoegtoepassing later wordt uitgeschakeld, kunnen functies uit de invoegtoepassing tijdelijk "#NAAM?" weergeven tot de invoegtoepassing opnieuw is geladen. Om verwarring te voorkomen, zorg ervoor dat de invoegtoepassing altijd is ingeschakeld in de invoegtoepassingsbeheerder wanneer nodig.
- Sommige gebruikers zien mogelijk niet standaard de tab Ontwikkelaar. Om deze in te schakelen, klik met de rechtermuisknop op de lint en kies "Lint aanpassen", en vink de optie "Ontwikkelaar" aan.
- Het is een goede praktijk om invoegtoepassingen in een permanente map op te slaan om ontbrekende verwijzingen te voorkomen als bestanden worden verplaatst of hernoemd.
Als je liever code handmatig uitvoert, is dat ook mogelijk en soms handig bij het debuggen of voor ad hoc-gebruik:
- Je kunt een macro toewijzen aan de Sneltoegangswerkbalk voor één-klik-uitvoering in elk zichtbaar werkboek. Doe dit door met de rechtermuisknop op de Sneltoegangswerkbalk te klikken, "Sneltoegangswerkbalk aanpassen" te selecteren en vervolgens je macro toe te voegen.
- Je kunt ook op Alt + F11 drukken om de VBA-editor te openen, handmatig je macro selecteren en op F5 drukken om de code indien nodig uit te voeren.
Voordelen: Deze oplossing laat je toe om rijke, herbruikbare macrofunctionaliteit te creëren en delen die altijd werkt zolang de invoegtoepassing is ingeschakeld.
Nadelen: Gebruikers moeten zich herinneren om de invoegtoepassing te laden en, bij het delen van werkboeken, ook het invoegtoepassingsbestand en functiedetails te delen. Ook kunnen invoegtoepassingen niet worden gebruikt in Excel Online.
Een andere zeer praktische manier om ervoor te zorgen dat je favoriete of meest gebruikte macro's klaar zijn in elke Excel-sessie, ongeacht welk werkboek open is, is door de Persoonlijke Macro-werkmap (PERSONAL.XLSB) te gebruiken. Dit is een speciaal verborgen Excel-bestand dat automatisch wordt geladen elke keer dat Excel wordt gestart, waardoor elke macro die erin is opgeslagen toegankelijk is in alle open werkboeken.
Toepasselijke scenario's: Ideaal voor persoonlijke automatisering, routine-opmaakscripts of hulpmiddelenfuncties die je niet als formele Excel-invoegtoepassingen hoeft te delen. Macros in PERSONAL.XLSB zijn beschikbaar op je computer, ongeacht welk bestand open is.
Voordelen: Macros zijn wereldwijd beschikbaar voor je lokale Excel-profiel en vereisen geen installatie van invoegtoepassingen of extra bestanden.
Nadelen: Macros die op deze manier worden opgeslagen, kunnen alleen worden gebruikt op de computer en het account waar PERSONAL.XLSB bestaat. Delen met anderen vereist het handmatig exporteren en importeren van modules.
- Om deze methode te gebruiken, moet je eerst een macro opnemen of maken en ervoor zorgen dat deze wordt opgeslagen in de Persoonlijke Macro-werkmap.
Volg deze stappen:
- Open Excel. Klik op de tab Weergave, klik op Macros en selecteer dan Macro opnemen.
- Selecteer in het dialoogvenster onder "Macro opslaan in" Persoonlijke Macro-werkmap. Voltooi het opnemen (je kunt meteen stoppen als dit niet nodig is).
- Druk op Alt + F11 om de VBA-editor te openen, waar je een project voor PERSONAL.XLSB zult zien. Voeg hier een nieuwe module in of plak je gewenste macrocode.
- Sla je wijzigingen op. Excel maakt automatisch de werkmap PERSONAL.XLSB aan en onderhoudt deze in de opstartmap.
- Macros in PERSONAL.XLSB kunnen dan worden uitgevoerd via het dialoogvenster Macros (Alt + F8), toegewezen aan lint- of werkbalkknoppen, of aangeroepen vanuit VBA.
Problemen oplossen & Onderhoud: Als macros in PERSONAL.XLSB niet beschikbaar zijn, controleer dan of Excel in veilige modus wordt geopend of of de macro-beveiligingsinstellingen zijn ingesteld op “Alle macros uitschakelen.” Daarnaast is PERSONAL.XLSB standaard verborgen; als je per ongeluk zonder opslaan afsluit of deze verwijdert, moet je mogelijk een macro opnieuw opnemen om deze te regenereren.
C:\Users\[YourUserName]\AppData\Roaming\Microsoft\Excel\XLSTART\PERSONAL.XLSBEen VBA-code om alle invoegtoepassingen in Excel weer te geven
In Excel kun je mogelijk wat invoegtoepassingen toevoegen of invoegen voor een betere omgang met gegevens. Zoals we weten, kunnen we naar het Optiesvenster gaan om alle invoegtoepassingen te bekijken, maar is er een manier om alle invoegtoepassingen in een blad weer te geven? Nu biedt deze handleiding een VBA-code aan om alle invoegtoepassingen in Excel weer te geven.
Hoe een VBA-macro uit te voeren bij het openen of sluiten van een werkboek?
In dit artikel leg ik uit hoe je de VBA-code kunt uitvoeren terwijl je het werkboek elke keer opent of sluit.
Hoe VBA-code in Excel te beveiligen / vergrendelen?
Net zoals je een wachtwoord kunt gebruiken om werkboeken en werkbladen te beveiligen, kun je ook een wachtwoord instellen om de macro's in Excel te beschermen.
Hoe een tijdvertraging te gebruiken na het uitvoeren van een VBA-macro in Excel?
In sommige gevallen heb je mogelijk een timer-vertraging nodig om een VBA-macro in Excel te activeren. Bijvoorbeeld, wanneer je klikt om een specifieke macro uit te voeren, zal deze pas na 10 seconden effect hebben. Dit artikel laat je een methode zien om dit te bereiken.
Beste productiviteitstools voor Office
Verbeter je Excel-vaardigheden met Kutools voor Excel en ervaar ongeëvenaarde efficiëntie. Kutools voor Excel biedt meer dan300 geavanceerde functies om je productiviteit te verhogen en tijd te besparen. Klik hier om de functie te kiezen die je het meest nodig hebt...
Office Tab brengt een tabbladinterface naar Office en maakt je werk veel eenvoudiger
- Activeer tabbladbewerking en -lezen in Word, Excel, PowerPoint, Publisher, Access, Visio en Project.
- Open en maak meerdere documenten in nieuwe tabbladen van hetzelfde venster, in plaats van in nieuwe vensters.
- Verhoog je productiviteit met50% en bespaar dagelijks honderden muisklikken!
Alle Kutools-invoegtoepassingen. Eén installatieprogramma
Kutools for Office-suite bundelt invoegtoepassingen voor Excel, Word, Outlook & PowerPoint plus Office Tab Pro, ideaal voor teams die werken met Office-toepassingen.
- Alles-in-één suite — invoegtoepassingen voor Excel, Word, Outlook & PowerPoint + Office Tab Pro
- Eén installatieprogramma, één licentie — in enkele minuten geïnstalleerd (MSI-ready)
- Werkt beter samen — gestroomlijnde productiviteit over meerdere Office-toepassingen
- 30 dagen volledige proef — geen registratie, geen creditcard nodig
- Beste prijs — bespaar ten opzichte van losse aanschaf van invoegtoepassingen