Macro's programmeren in Excel: de complete stapsgewijze handleiding

Informatic Digital » programmering » Macro's programmeren in Excel: de complete stapsgewijze handleiding

Als u taken in Excel steeds opnieuw herhaalt, zijn macro's uw beste bondgenoot om tijdverspilling te voorkomen. automatiseer complete reeksen acties (meer informatie over VBA-programmering), van opmaak tot het importeren van gegevens of het uitvoeren van berekeningen, en u kunt ze op elk gewenst moment met een klik of een sneltoets activeren.

Wanneer u een macro maakt, registreert Excel uw klikken en toetsaanslagen en voert deze getrouw uit. U kunt de code vervolgens aanpassen om de uitvoering te verfijnen en te raadplegen. bronnen voor ExcelHet is gebruikelijk om te beginnen met de recorder en dan met VBA aan te passen, meer controle, flexibiliteit en geen fouten door onzorgvuldigheid.

Wat is een macro in Excel precies?

Een macro is een lijst met statements die Excel uitvoert in de gedefinieerde volgordeHet kan zo simpel zijn als het toepassen van een percentagestijl of zo complex als een rapport voorbereiden met draaitabellen, formatteer het, maak spreadsheets en verstuur een e-mail in Outlook. Macro's worden opgeslagen in uw werkmap en u kunt ze uitvoeren wanneer u ze nodig hebt.

Je hoeft niet te kunnen programmeren om te beginnen: de recorder vertaalt je acties naar VBA-code. Als je echter wat VBA-kennis hebt, kun je... Maak aangepaste functies, formulieren en zelfs applets binnen Excel.

Waarom macro's gebruiken? Echte voordelen

Het hoofddoel van een macro is vermijd repetitief werk en minimaliseer foutenAls u elke maand hetzelfde rapport opstelt, bespaart een macro u de mechanische stappen en kunt u zich concentreren op wat belangrijk is.

Tot de voordelen behoren tijdsbesparing, consistente uitvoering (zonder te vergeten) en de mogelijkheid om snelkoppelingen en knoppen om processen te versnellenAls u Excel slechts af en toe gebruikt, is het misschien niet de moeite waard. Maar als u het dagelijks gebruikt, zult u vanaf dag één het verschil merken.

Het tabblad Ontwikkelaar inschakelen (Windows en Mac)

Het lint voor ontwikkelaars is standaard verborgen. Klik in Windows met de rechtermuisknop op het lint en selecteer 'Lint aanpassen'. Selecteer 'Ontwikkelaars' en accepteer. Je ziet het tabblad Ontwikkelaar naast Vista.

Ga op een Mac naar Excel > Voorkeuren > Werkbalken en lint. Selecteer onder 'Hoofdtabbladen' de optie 'Ontwikkelaars' en sla de wijzigingen op. Dit geeft je toegang tot de werkbalk. de recorder, toegang tot VBA en bedieningselementenAls je net begint, bekijk dan de basis Excel-verwerking om u te begeleiden met het lint en de opties.

Hoe maak je macro's met de Recorder

De recorder is een prima plek om te beginnen. Geef onder Programmeur > Macro opnemen (of Alt+T+M+R in Windows) een beschrijvende naam. Let op: de naam moet met een letter beginnen. zonder spaties of symbolen.

Kies waar u het wilt opslaan: "Deze werkmap" is in de meeste gevallen voldoende. Wilt u het altijd beschikbaar hebben, selecteer dan "Persoonlijke macrowerkmap" en Excel maakt/slaat het op. Persoonlijk.xlsb, die verborgen wordt geopend met Excel.

Voeg optioneel een sneltoets toe. Om conflicten (bijvoorbeeld het verliezen van Ctrl+Z) te voorkomen, is het raadzaam om combinaties met Ctrl+Shift te gebruiken. Je kunt ook een sneltoets typen. korte en duidelijke beschrijving (erg handig als je veel macro's hebt).

Klik op OK en voer de acties uit die u wilt vastleggen. Selecteer bijvoorbeeld A1 en pas de notatie 'Percentage' toe. Vermijd onnodige klikken of selectiewijzigingen die geen deel uitmaken van het beoogde proces, omdat de recorder neemt bijna alles op.

Stop de opname als je klaar bent. Om de opname uit te voeren, ga je naar Ontwikkelaar > Macro's (of Alt+F8), kies je je macro en druk je op Uitvoeren. Je zult zien hoe Excel werkt. herhaal de stappen in exact dezelfde volgorde.

Voer uw macro's uit, bewerk ze en beheer ze

Via Programmeur > Macro's (Alt+F8) kunt u macro's uitvoeren, bewerken of verwijderen. Als u Bewerken kiest, wordt de Visual Basic Editor geopend met de opgenomen code, die u vervolgens kunt bewerken. opschonen en optimaliseren (bevat meestal overbodige stappen).

Het toewijzen van een macro aan een knop of vorm is erg handig: voeg een vorm in, klik met de rechtermuisknop > Macro toewijzen, selecteer de macro en klaar. Wanneer u op de vorm klikt, wordt onmiddellijk uitgevoerd.

Om het altijd bij de hand te hebben in de werkbalk Snelle toegang, opent u het menu van de balk (pijltje naar beneden) > Meer opdrachten > onder 'Beschikbare opdrachten in' kiest u 'Macro's', voegt u uw eigen macro toe en wijzigt u het pictogram naar wens. Nu hebt u een permanente knop zelfs als je niet in Programmeur zit.

Als u de macro in een andere werkmap wilt hergebruiken, kunt u de module kopiëren vanuit de Visual Basic Editor. Open VBE (Alt+F11), sleep de module naar een andere geopende werkmap of gebruik .bas-bestand exporteren/importeren.

Macro's programmeren in Excel met de Visual Basic (VBA) Editor

Om verder te gaan dan de recorder, opent u de editor met Alt+F11. Voeg een module in (Invoegen > Module) en maak uw eerste subroutine. Het basispatroon is Subnaam() … Einde Sub.

Om bijvoorbeeld een bericht weer te geven en een waarde naar A1 te schrijven, kunt u met iets eenvoudigs beginnen. Zo begrijpt u de structuur en hoe u de macro vanuit Excel aanroept (Alt+F8) of aan een knop toewijst, met heel weinig regels:

Sub Primera_Macro()
    Range("A1").Value = "Esto de las macros mola."
    MsgBox "Hola, querido amigo."
End Sub

Nog een snel voorbeeld om de percentagestijl toe te passen op de huidige selectie. Deze macro repliceer wat je met de recorder hebt gedaan, maar dan direct geschreven:

Sub FormatoPorcentaje()
    Selection.Style = "Percent"
End Sub

De ideale combinatie is: neem op om het proces te versnellen en open vervolgens de VBE om onnodige stappen op te schonen, variabelen, voorwaarden of lussen toe te voegen. Zo maak je van een letterlijke opname een macro. robuust en herbruikbaar.

Absolute versus relatieve referenties

Standaard maakt de recorder macro's in de absolute modus: deze werkt altijd op dezelfde cellen (bijv. B3), ongeacht waar u zich bevindt tijdens de uitvoering. Als de gegevens van positie veranderen in uw proces, kan dit niet zijn wat je nodig hebt.

Met 'Relatieve verwijzingen gebruiken' werkt de macro relatief ten opzichte van de actieve cel wanneer deze wordt uitgevoerd. Als u bijvoorbeeld bent begonnen met opnemen in B3 en 'Voornaam', 'Achternaam' enz. hebt getypt, en u naar rechts beweegt, wordt dit bij uitvoering vanuit C4 weggeschreven van C4, D4, E4… relatief.

Een typisch voorbeeld: het geven van een titel aan een tabel die soms begint in B3 en soms in C5. In de absolute modus zou je altijd in B3 schrijven. In de relatieve modus zal het zich aanpassen aan de geselecteerde startcel.

Veelvoorkomende fouten bij het vastleggen van processen

Een veelvoorkomende valkuil is het opnemen van een formulesleep voor bijvoorbeeld drie rijen, en denken dat dit ook voor langere tabellen zal werken. De recorder zal iets opnemen dat gelijk is aan "vul drie cellen naar beneden", niet "tot aan het einde van de tabel."

De oplossing is om de aanpak te veranderen: gebruik dynamische bereiken (bijvoorbeeld de laatste rij detecteren), lussen of de CurrentRegion-methode, of de macro in VBA aanpassen nadat u deze hebt opgenomen, zodat schaalt naar elke maat.

Goede praktijken bij het opnemen

Voer het handmatige proces een paar keer uit voordat u gaat opnemen om het te perfectioneren. kort en specifiek in plaats van één grote; dit maakt ze gemakkelijker te onderhouden en te combineren.

Vermijd onnodig klikken, onnodig scrollen en onbelangrijke selecties. Houd de macronaam en -beschrijving duidelijk, zodat jij en je teamgenoten de macro kunnen gebruiken. je herkent het doel ervan direct.

Macro's op veel manieren uitvoeren

Naast het vak Macro's (Alt+F8) kunt u macro's starten met een vorm, een knop op de werkbalk Snelle toegang of wanneer u de werkmap opent. U kunt Excel de macro zelfs laten uitvoeren vanuit andere Office-apps, zodat u... automatiseer taken met meerdere applicaties (bijvoorbeeld een tabel bijwerken en een e-mail verzenden via Outlook).

Denk goed na over sneltoetsen: als u Ctrl+Z aan een macro toewijst, verliest u de mogelijkheid om ongedaan te maken terwijl de werkmap geopend is. Daarom is het raadzaam om combinaties te gebruiken met Ctrl + Shift om conflicten te vermijden.

Werkmappen opslaan met macro's

Wanneer een bestand VBA bevat, sla het dan op als .xlsm zodat de code behouden blijft en kan worden uitgevoerd. Als u het opslaat als .xlsx, je verliest het VBA-projectAls u globale macro's wilt, gebruikt u de persoonlijke macrowerkmap (Personal.xlsb).

Beveiliging en het inschakelen van macro's

Macro's kunnen worden geblokkeerd, afhankelijk van uw beveiligingsinstellingen. Controleer het Vertrouwenscentrum om ze selectief in te schakelen en activeer nooit macro's van onbekende bronnen. Het kennen van uw beveiligingsniveau helpt u balans tussen bescherming en productiviteit.

Macro's toewijzen aan knoppen, vormen en besturingselementen

Om een ​​gebruiker ze zonder nadenken te laten uitvoeren, wijst u een macro toe aan een knop, vorm of besturingselement. Klik met de rechtermuisknop op het object > Macro toewijzen > selecteren. U kunt ook een pictogram op het lint of de sneltoegangsbalk plaatsen en koppel het aan de macro.

Als u formulieren of ActiveX-besturingselementen gebruikt, kunt u er macro's of gebeurtenisprocedures aan toewijzen (bijvoorbeeld bij klikken). Dit maakt het mogelijk gebruiksvriendelijkere interfaces en begeleide processen.

Nuttige macrovoorbeelden (VBA)

Deze voorbeelden illustreren veelvoorkomende taken. U kunt ze in een module plakken (Alt+F11 > Invoegen > Module) en aanpassen. Vergeet niet de boeken te ondertekenen of de beveiliging aan te passen als uw organisatie dit vereist. ze zonder waarschuwingen kunnen uitvoeren.

Kopieer de geselecteerde inhoud naar een andere locatie op hetzelfde werkblad: Selectie.Kopieerbestemming

Sub CopiarSeleccion()
    If TypeName(Selection) = "Range" Then
        Selection.Copy Destination:=Selection.Offset(0, 2)
    Else
        MsgBox "Selecciona un rango de celdas primero."
    End If
End Sub

Druk het actieve blad af: ActiveSheet.PrintOut

Sub ImprimirHojaActual()
    ActiveSheet.PrintOut
End Sub

Sla het bestand op als een werkmap met macro's (.xlsm): ActieveWerkmap.OpslaanAls

Sub GuardarComoXlsm()
    Dim ruta As String
    ruta = ThisWorkbook.Path & Application.PathSeparator & "LibroConMacros.xlsm"
    ActiveWorkbook.SaveAs Filename:=ruta, FileFormat:=xlOpenXMLWorkbookMacroEnabled
End Sub

Formatteer een bereik (pas het bereik aan uw behoeften aan): Met bereik(…)

Sub FormatoRango()
    With Range("A1:D10")
        .Font.Bold = True
        .Interior.Color = RGB(230, 230, 250)
        .Borders.LineStyle = xlContinuous
    End With
End Sub

Zoek een waarde in een bereik en geef de eerste overeenkomst weer: Bereik(…).Zoeken

Sub BuscarValor()
    Dim celda As Range
    Dim valor As String
    valor = InputBox("¿Qué valor quieres buscar?")
    If valor = "" Then Exit Sub
    Set celda = Range("A1:D1000").Find(What:=valor, LookIn:=xlValues, LookAt:=xlPart)
    If Not celda Is Nothing Then
        MsgBox "Encontrado en: " & celda.Address
        celda.Select
    Else
        MsgBox "No se encontró el valor especificado."
    End If
End Sub

Praktijkvoorbeeld: opmaak als percentage

Opname: Start de opname, geef deze een naam, kies waar u deze wilt opslaan en wijs eventueel een snelkoppeling en beschrijving toe. Selecteer de cellen en pas 'Percentage' toe via het menu Start. Stop de opname en start deze vervolgens met Alt+F8. Als u de opname met minimalistische code wilt afspelen, gebruikt u Selectiestijl = "Percentage" in VBA.

Tip: Verander niet zomaar van cel tijdens de opname. Deze bewegingen worden geregistreerd en kunnen worden opgeslagen. vertraag en vervuil de macro.

Automatiseer lange taken: kleine macro's zijn beter

Als je een proces met veel stappen hebt, overweeg dan om het op te splitsen in meerdere kleinere macro's en deze in een keten aan te roepen. Dit maakt onderhoud eenvoudiger en stelt je in staat om hergebruik blokken en vereenvoudigt het debuggen.

Bijvoorbeeld één macro om gegevens voor te bereiden, een andere om te formatteren, een andere om een ​​afdrukblad te genereren en nog een andere om te exporteren naar PDF of mailU kunt ze één voor één aanroepen, of allemaal tegelijk.

Bewerk en ruim de opgenomen code op

De recorder legt "alles" vast: selecties, klikken en tab-wijzigingen. Wanneer u de VBE opent, worden onnodige selecties (Selecteren/Selectie) verwijderd en vervangen door directe verwijzingen (bijv. Bereik("A1").Waarde = …) en groepeert formaten in With/End With-blokken.

Het voegt ook variabelen en basisfoutverwerking toe. Een paar if-statements en eenvoudige controles voorkomen fouten wanneer het bereik leeg is of de selectie is niet wat verwacht werd.

Veel gestelde vragen

Hoe maak ik een formulier (UserForm) in Excel? Open de Visual Basic Editor (Alt+F11), ga naar Invoegen > Gebruikersformulier en voeg besturingselementen toe (tekstvakken, knoppen, enz.). Je kunt ze weergeven met een macro:

Sub MostrarFormulario()
    UserForm1.Show
End Sub

Wijs vanaf daar procedures toe aan de gebeurtenissen van de besturingselementen (bijvoorbeeld Klik op een knop), zodat voer de logica uit die u nodig hebt.

Hoe kan ik een werkblad met macro's beveiligen/de beveiliging ervan opheffen? Gebruik deze routines om je wachtwoord naar wens te wijzigen. Ze stellen je in staat om: Activeer of verwijder de beveiliging met één klik:

Sub ProtegerHoja()
    ActiveSheet.Protect Password:="segura", AllowFiltering:=True
End Sub

Sub DesprotegerHoja()
    ActiveSheet.Unprotect Password:="segura"
End Sub

Hoe verwijder ik een macro? Ga naar Programmeur > Macro's, selecteer de macro en druk op Delete. Als u alle code in een module wilt verwijderen, opent u de VBE (Alt+F11), zoekt u de module in het project, klikt u met de rechtermuisknop en selecteert u Verwijderen. Module verwijderen (U kunt het eerst exporteren als u het wilt opslaan).

Nog een laatste praktische opmerking: als u een maandelijks rapport opmaakt waarin klanten met schulden rood en vetgedrukt moeten zijn, registreert u de volgorde één keer, past u de VBA-code aan om deze op het juiste bereik toe te passen (met behulp van detectie van de laatste rij of filters) en slaat u de macro op in uw persoonlijke macrowerkmap. De volgende keer is een snelkoppeling of knop voldoende om de opmaak direct toe te passen. geen storingen en binnen enkele seconden.