Een macro opnemen in Excel - TechTV-artikelen

Dit zijn mijn shownotities van mijn optreden in aflevering 33 van Call for Help op TechTV Canada.

VBA-macrorecorder

Elk exemplaar van Excel dat sinds 1995 is verkocht, bevat de ongelooflijk krachtige Visual Basic for Applications (VBA) die zich achter de cellen verschuilt. Als je Excel hebt, heb je VBA.

Met VBA kunt u alles automatiseren dat u in Excel kunt doen, en meer dingen doen die over het algemeen niet mogelijk zijn in Excel.

Gelukkig voor ons heeft Microsoft een macro-recorder met Excel meegeleverd. Hiermee kunt u VBA-code schrijven zonder een programmeur te zijn. Helaas is de Macro Recorder gebrekkig. Het zal geen code produceren die elke keer zal werken. Met de macrorecorder kom je dichtbij, maar in veel gevallen moet je de code een beetje corrigeren om hem betrouwbaar te laten werken. In andere gevallen moet u weten wanneer u Relative Recording moet gebruiken om de macro te laten werken.

Tracy Syrstad en ik hebben VBA en Macros Microsoft Excel 2016 geschreven om u te helpen de beperkingen van opgenomen code te begrijpen en om u te leren hoe u opgenomen code kunt repareren in iets dat altijd werkt.

Op de show heb ik het proces van het opnemen van een macro gedemonstreerd dat perfect zal werken als je weet hoe je de Relative Reference-knop moet gebruiken. Iemand had me een Excel-werkblad gegeven met honderden namen en adressen. Ze wilden adresetiketten. Als u ooit de functie Afdruk samenvoegen van Microsoft Word hebt gebruikt, weet u dat u elk veld in een nieuwe kolom in het werkblad nodig hebt. In plaats daarvan had dit specifieke werkblad de gegevens in kolom A.

Het proces om een enkel label te repareren is behoorlijk vervelend.

Begin met de celaanwijzer op de naam in kolom A.
Druk op de pijl omlaag om naar het adres te gaan
Ctrl + x om te knippen
Pijl-omhoog, pijl-rechts om naar kolom B naast de naam te gaan
Ctrl + v om te plakken
Pijl-omlaag tweemaal, linkerpijl eenmaal om naar de stad te gaan
Ctrl + x om te knippen
Pijl omhoog tweemaal, pijl naar rechts driemaal
Ctrl + v om te plakken
Twee keer pijl-links, één keer pijl-omlaag om naar de nu lege rij te gaan.
Houd de Shift-toets ingedrukt terwijl u twee keer op de pijl-omlaag drukt
Alt + edr om de lege rijen te verwijderen
Pijl-omhoog en pijl-omlaag om alleen de naam opnieuw te selecteren.

Hier is de animatie met deze stappen:

Excel instellen om macro's toe te staan

Hoewel elk exemplaar van Excel VBA bevat, schakelt Microsoft standaard de mogelijkheid uit om macro's uit te voeren. U moet een eenmalige aanpassing uitvoeren om macro's te kunnen uitvoeren. Open Excel. Selecteer in het menu Extra> Macro> Beveiliging. Als uw macrobeveiligingsniveau momenteel te hoog is ingesteld, wijzigt u dit in Gemiddeld.

Voorbereiden om de macro op te nemen

Denk na over de stappen die nodig zijn om de taak te volbrengen. U wilt er zeker van zijn dat u begint met de celaanwijzer op een naam en daarmee eindigt op de volgende naam. Hierdoor kunt u de macro meerdere keren herhaaldelijk uitvoeren. Schakel de macrorecorder in wanneer u de stappen klaar heeft staan. Selecteer in het menu Extra> Macro> Nieuwe macro opnemen.

Als u deze macro meerdere dagen gebruikt, moet u de macro een nuttige naam geven. Als het een eenmalige macro is om een eenmalige taak te automatiseren, dan is het waarschijnlijk prima om de Macronaam Macro1 te laten staan. Het belangrijke veld hier is het sneltoetsveld. Als u een macro voor eenmalig gebruik maakt, wijst u de macro toe aan een sneltoets zoals Ctrl + a - het is vrij eenvoudig om Ctrl + a herhaaldelijk in te drukken omdat de toetsen zich dicht bij elkaar bevinden. Als je een macro gaat maken die je elke dag gaat gebruiken, dan wil je de macro toewijzen aan een toets die niet al een belangrijke sneltoetscombinatie is. Ctrl + j of Ctrl + k zijn goede keuzes.

Macro's voor eenmalig gebruik moeten in ThisWorkbook worden opgeslagen. Als u de macro herhaaldelijk in veel verschillende werkmappen moet gebruiken, kunt u de macro opslaan in de Persoonlijke macrowerkmap.

U krijgt nu de kleinste werkbalk in heel Excel te zien; de werkbalk Opname stoppen. Het heeft slechts twee pictogrammen en ziet er als volgt uit:

Als deze werkbalk u in de weg zit, druk dan niet op de X om deze te sluiten. Pak in plaats daarvan de blauwe balk en sleep de werkbalk naar een nieuwe locatie. Het verplaatsen van de werkbalk wordt niet in de macro vastgelegd. Als u de werkbalk per ongeluk sluit, haalt u deze terug met Beeld> Werkbalken> Opname stoppen. Deze actie wordt echter geregistreerd.

De eerste knop op de werkbalk is de knop "Opname stoppen". Dit spreekt voor zich. Als u klaar bent met het opnemen van de macro, drukt u op de werkbalk Opname stoppen.

De belangrijkste (en zelden begrepen) knop is de knop "Relatieve referentie".

In ons huidige voorbeeld moet u op de knop Relative Reference drukken voordat u begint. Als u een macro opneemt met relatieve verwijzingen ingeschakeld, registreert Excel de volgende stappen:

Verplaats een cel naar beneden
Snijd de huidige cel
Verplaats een cel omhoog
Verplaats een cel naar rechts
Plakken
enzovoort.

Als u in plaats daarvan de macro zou opnemen zonder relatieve verwijzingen, zou de macro deze stappen opnemen:

Ga naar cel A4
Snijd cel A4
Ga naar cel A3
Ga naar cel B3
Plak in cel B3
enzovoort.

Dit zou vreselijk verkeerd zijn - we hebben de macro nodig om te werken aan cellen die 1 en 2 cellen zijn vanaf het beginpunt van de macro. Terwijl u de macro steeds opnieuw uitvoert, is het startpunt rij 4, rij 5, rij 6, enz. Als u de macro opneemt zonder dat relatieve verwijzingen zijn ingeschakeld, wordt de macro altijd op rij 3 als het startpunt uitgevoerd.

Volg deze stappen:

Kies de relatieve referentieknop op de werkbalk Opname stoppen
De celaanwijzer zou al op een naam in kolom A moeten staan.
Druk op de pijl omlaag om naar het adres te gaan
Ctrl + x om te knippen
Pijl-omhoog, pijl-rechts om naar kolom B naast de naam te gaan
Ctrl + v om te plakken
Pijl-omlaag tweemaal, linkerpijl eenmaal om naar de stad te gaan
Ctrl + x om te knippen
Pijl omhoog tweemaal, pijl naar rechts driemaal
Ctrl + v om te plakken
Twee keer pijl-links, één keer pijl-omlaag om naar de nu lege rij te gaan.
Houd de Shift-toets ingedrukt terwijl u twee keer op de pijl-omlaag drukt
Alt + edr om de lege rijen te verwijderen
Pijl-omhoog en pijl-omlaag om de volgende naam in de lijst te selecteren.
Klik op de werkbalk Opname stoppen
Sla de werkmap op
Test de macro door op de hierboven toegewezen sneltoets te drukken. Het zou perfect de volgende naam in de lijst moeten corrigeren

Zodra u ziet dat de macro naar wens werkt, kunt u Ctrl + a ingedrukt houden om snel een paar namen per seconde vast te leggen. De volledige lijst met 500 namen kan binnen een minuut of twee worden hersteld.

Bonustip - niet op de show

U kunt de macro gemakkelijk in een simpele lus omwikkelen om hem alle 500 namen te laten repareren zonder dat u een paar honderd keer op Ctrl + hoeft te drukken.

Druk op de Alt + F11-toets om de macro-editor te openen.

Als u het Project - VBAProject-paneel niet ziet, drukt u op Ctrl + r.

Klik met de rechtermuisknop op Module1 en kies Bekijk code. U ziet een code die er als volgt uitziet:

Nu hoeft u niet te begrijpen wat deze code doet, maar u weet wel dat we alles in die macro één keer willen uitvoeren voor elke naam die we hebben. Als u weet dat er 462 namen zijn, kunt u 2 regels toevoegen om de code 462 keer uit te voeren. Voeg bovenaan de macro een nieuwe regel in met de woorden " For i = 1 to 462". Voeg onder aan de macro een regel in met de tekst " Next i". Dit vertelt VBA om de code 462 keer uit te voeren.

Conclusie

Na deze simpele macro liet ik een voorbeeld zien van een zeer complexe macro. Deze macro heeft draaitabellen en grafieken gemaakt voor 46 afdelingen van een bedrijf. Dit rapport kostte vroeger 40 uur per maand. De VBA-macro wordt nu uitgevoerd en maakt alle 46 rapporten in minder dan 2 minuten.

Het boek VBA en macro's Microsoft Excel 2016 helpt u bij het leren, zodat u van het opnemen van eenvoudige macro's zoals deze tot het uitvoeren van zeer complexe rapporten kunt komen die u honderden uren per jaar kunnen besparen. Als u geen VBA wilt leren, huur dan een Excel Consultant in om een rapport voor u te ontwerpen.