Inhoud
Zelfs als je niet weet hoe je moet programmeren, zijn er veel plaatsen (boeken, websites, forums) waar je kant-en-klare VBA-macrocode kunt vinden voor een groot aantal typische taken in Excel. In mijn ervaring verzamelen de meeste gebruikers vroeg of laat hun persoonlijke verzameling macro's om routineprocessen te automatiseren, of het nu gaat om het vertalen van formules in waarden, het weergeven van sommen in woorden of het optellen van cellen op kleur. En hier ontstaat het probleem - de macrocode in Visual Basic moet ergens worden opgeslagen om later in het werk te kunnen worden gebruikt.
De eenvoudigste optie is om de macrocode direct in het werkbestand op te slaan door naar de Visual Basic-editor te gaan met de sneltoets anders+F11 en een nieuwe lege module toevoegen via het menu Invoegen – Module:
Er zijn echter verschillende nadelen aan deze methode:
- Als er veel werkbestanden zijn en overal een macro nodig is, zoals een macro om formules om te zetten in waarden, dan moet je de code kopiëren in elk boek.
- Mag niet worden vergeten bestand opslaan in macro-enabled formaat (xlsm) of in binair boekformaat (xlsb).
- Bij het openen van zo'n bestand macro bescherming zal elke keer een waarschuwing geven die moet worden bevestigd (nou ja, of de beveiliging volledig uitschakelen, wat niet altijd wenselijk is).
Een elegantere oplossing zou zijn om te creëren uw eigen invoegtoepassing (Excel-invoegtoepassing) – een apart bestand met een speciaal formaat (xlam) met al je “favoriete” macro's. De voordelen van deze aanpak:
- Het zal genoeg zijn add-on één keer verbinden in Excel - en u kunt de VBA-procedures en -functies in elk bestand op deze computer gebruiken. Het opnieuw opslaan van uw werkbestanden in xlsm- en xlsb-formaten is dus niet nodig, omdat. de broncode wordt niet daarin opgeslagen, maar in het add-in-bestand.
- Bescherming u zult ook geen last hebben van macro's. add-ons zijn per definitie vertrouwde bronnen.
- Kan doen apart tabblad op het Excel-lint met leuke knoppen om invoegmacro's uit te voeren.
- De invoegtoepassing is een apart bestand. Zijn gemakkelijk te dragen van computer naar computer, deel het met collega's of verkoop het zelfs 😉
Laten we het hele proces van het maken van uw eigen Microsoft Excel-invoegtoepassing stap voor stap doornemen.
Stap 1. Maak een invoegtoepassingsbestand
Open Microsoft Excel met een lege werkmap en sla deze op onder een geschikte naam (bijvoorbeeld: MijnExcelAddin) in add-in formaat met de opdracht Bestand – Opslaan als of sleutels F12, met vermelding van het bestandstype Excel-invoegtoepassing:
Houd er rekening mee dat Excel standaard invoegtoepassingen opslaat in de map C:UsersYour_nameAppDataRoamingMicrosoftAddIns, maar in principe kunt u elke andere map opgeven die voor u handig is.
Stap 2. We koppelen de gemaakte invoegtoepassing
Nu de invoegtoepassing die we in de laatste stap hebben gemaakt MijnExcelAddin moet worden aangesloten op Excel. Ga hiervoor naar het menu Bestand – Opties – Add-ons (Bestand — Opties — Invoegtoepassingen), klik op de knop Over (Gaan) onderaan het venster. Klik in het geopende venster op de knop Beoordeling (Bladeren) en specificeer de locatie van ons invoegtoepassingsbestand.
Als je alles goed hebt gedaan, dan is onze MijnExcelAddin zou moeten verschijnen in de lijst met beschikbare add-ons:
Stap 3. Voeg macro's toe aan de invoegtoepassing
Onze invoegtoepassing is verbonden met Excel en werkt met succes, maar er zit nog geen enkele macro in. Laten we het vullen. Open hiervoor de Visual Basic-editor met de sneltoets anders+F11 of met de knop Visual Basic tab ontwikkelaar (Ontwikkelaar). Als tabbladen ontwikkelaar niet zichtbaar, kan worden weergegeven via Bestand – Opties – Lint instellen (Bestand — Opties — Lint aanpassen).
Er zou een venster moeten zijn in de linkerbovenhoek van de editor Project (als het niet zichtbaar is, zet het dan aan via het menu) Bekijken — Projectverkenner):
In dit venster worden alle geopende werkmappen en actieve Microsoft Excel-invoegtoepassingen weergegeven, inclusief de onze. VBA-project (MijnExcelAddin.xlam) Selecteer het met de muis en voeg er een nieuwe module aan toe via het menu Invoegen – Module. In deze module slaan we de VBA-code van onze invoegmacro's op.
Je kunt de code helemaal opnieuw typen (als je weet hoe je moet programmeren), of hem ergens kant-en-klaar kopiëren (wat veel gemakkelijker is). Laten we, om te testen, de code van een eenvoudige maar nuttige macro invoeren in de toegevoegde lege module:
Vergeet na het invoeren van de code niet op de knop Opslaan (diskette) in de linkerbovenhoek te klikken.
Onze macro FormulesNaarWaarden, zoals u zich gemakkelijk kunt voorstellen, converteert formules naar waarden in een vooraf geselecteerd bereik. Soms worden deze macro's ook wel procedures. Om het uit te voeren, moet je cellen met formules selecteren en een speciaal dialoogvenster openen Macro's van het tabblad ontwikkelaar (Ontwikkelaar — Macro's) of sneltoets anders+F8. Normaal gesproken toont dit venster beschikbare macro's van alle geopende werkmappen, maar invoegmacro's zijn hier niet zichtbaar. Desondanks kunnen we de naam van onze procedure in het veld invoeren macronaam (Macronaam)en klik vervolgens op de knop lopen (rennen) – en onze macro zal werken:
| |
Hier kun je ook een sneltoets toewijzen om snel een macro te starten - de knop is hiervoor verantwoordelijk parameters (Opties) in het vorige venster Macro:
Houd er bij het toewijzen van toetsen rekening mee dat ze hoofdlettergevoelig en toetsenbordindelinggevoelig zijn. Dus als je een combinatie toewijst zoals Ctrl+Й, then, in fact, in the future you will have to make sure that you have the layout turned on and press additionally Shiftom de hoofdletter te krijgen.
Voor het gemak kunnen we ook een knop voor onze macro toevoegen aan de werkbalk voor snelle toegang in de linkerbovenhoek van het venster. Selecteer hiervoor Bestand – Opties – Werkbalk Snelle toegang (Bestand — Opties — Werkbalk Snelle toegang aanpassen), en vervolgens in de vervolgkeuzelijst bovenaan het venster de optie Macro's. Daarna onze macro FormulesNaarWaarden kan op het paneel worden geplaatst met de knop Toevoegen (Toevoegen) en selecteer er een icoon voor met de knop Veranderen (Edit):
Stap 4. Functies toevoegen aan de invoegtoepassing
Maar macro-procedures, er zijn ook functiemacro's of zoals ze worden genoemd UDF (Door gebruiker gedefinieerde functie = door gebruiker gedefinieerde functie). Laten we een aparte module maken in onze add-on (menuopdracht Invoegen – Module) en plak daar de code van de volgende functie:
Het is gemakkelijk in te zien dat deze functie nodig is om btw af te trekken van het bedrag inclusief btw. Niet de binomiaal van Newton natuurlijk, maar het zal ons als voorbeeld dienen om de basisprincipes te laten zien.
Merk op dat de syntaxis van een functie verschilt van een procedure:
- constructie wordt gebruikt Functie …. Functie beëindigen verkrijgen in plaats daarvan Sub ... Einde Sub
- na de naam van de functie worden de argumenten tussen haakjes aangegeven
- in de hoofdtekst van de functie worden de nodige berekeningen uitgevoerd en vervolgens wordt het resultaat toegewezen aan een variabele met de naam van de functie
Merk ook op dat deze functie niet nodig is en dat het onmogelijk is om zoals de vorige macroprocedure door het dialoogvenster te lopen Macro's en knop lopen. Een dergelijke macrofunctie moet worden gebruikt als een standaard werkbladfunctie (SOM, ALS, VERT.ZOEKEN...), dwz voer gewoon in een cel in en geef de waarde van het bedrag met btw als argument op:
... of voer via het standaarddialoogvenster in voor het invoegen van een functie (knop fx in de formulebalk), een categorie selecteren User Defined (Gebruiker gedefinieerde):
Het enige onaangename moment hier is de afwezigheid van de gebruikelijke beschrijving van de functie onderaan het venster. Om het toe te voegen, moet u het volgende doen:
- Open de Visual Basic Editor met een sneltoets anders+F11
- Selecteer de invoegtoepassing in het deelvenster Project en druk op de toets F2om het venster Objectbrowser te openen
- Selecteer uw invoegtoepassingsproject in de vervolgkeuzelijst bovenaan het venster
- Klik met de rechtermuisknop op de functie die verschijnt en selecteer opdracht Properties.
- Voer een beschrijving van de functie in het venster in Omschrijving
- Sla het invoegbestand op en start Excel opnieuw.
Na het opnieuw opstarten zou de functie de beschrijving moeten weergeven die we hebben ingevoerd:
Stap 5. Maak een add-on-tabblad in de interface
De laatste, hoewel niet verplichte, maar prettige bijkomstigheid is het maken van een apart tabblad met een knop om onze macro uit te voeren, die in de Excel-interface zal verschijnen nadat onze invoegtoepassing is aangesloten.
Informatie over de tabbladen die standaard worden weergegeven, bevindt zich in het boek en moet worden opgemaakt in een speciale XML-code. De eenvoudigste manier om dergelijke code te schrijven en te bewerken, is met behulp van speciale programma's - XML-editors. Een van de handigste (en gratis) is het programma van Maxim Novikov Lint XML-editor.
Het algoritme om ermee te werken is als volgt:
- Sluit alle Excel-vensters zodat er geen bestandsconflict is wanneer we de invoegtoepassing XML-code bewerken.
- Start het Ribbon XML Editor-programma en open ons MyExcelAddin.xlam-bestand erin
- Met knoop tabbladen voeg in de linkerbovenhoek het codefragment voor het nieuwe tabblad toe:
- U moet lege aanhalingstekens plaatsen id onze tab en groep (alle unieke identifiers), en in label – de namen van ons tabblad en een groep knoppen erop:
- Met knoop voeg in het linkerdeelvenster een blanco code toe voor de knop en voeg er tags aan toe:
- label is de tekst op de knop
— afbeeldingMso — dit is de voorwaardelijke naam van de afbeelding op de knop. Ik gebruikte een rood knoppictogram genaamd AnimationCustomAddExitDialog. De namen van alle beschikbare knoppen (en er zijn er enkele honderden!) zijn te vinden op een groot aantal sites op internet als u zoekt op de trefwoorden "imageMso". Om te beginnen kun je hier terecht.
- opActie – dit is de naam van de callback-procedure – een speciale korte macro die onze hoofdmacro zal uitvoeren FormulesNaarWaarden. Je kunt deze procedure noemen zoals je wilt. We zullen het iets later toevoegen.
- U kunt de juistheid van alles controleren met behulp van de knop met een groen vinkje bovenaan de werkbalk. Klik op dezelfde plaats op de knop met een diskette om alle wijzigingen op te slaan.
- Sluit de Ribbon XML Editor
- Open Excel, ga naar de Visual Basic-editor en voeg een callback-procedure toe aan onze macro KillFormuleszodat het onze hoofdmacro uitvoert voor het vervangen van formules door waarden.
- We slaan de wijzigingen op en, terugkerend naar Excel, controleren we het resultaat:
Dat is alles: de invoegtoepassing is klaar voor gebruik. Vul het met je eigen procedures en functies, voeg mooie knoppen toe - en het wordt veel gemakkelijker om macro's in je werk te gebruiken.
- Wat zijn macro's, hoe u ze in uw werk kunt gebruiken, waar u macrocode kunt krijgen in Visual Basic.
- Een opstartscherm maken bij het openen van een werkmap in Excel
- Wat is een persoonlijk macroboek en hoe gebruik je het?