Record wijzigen Excel-macro uitvoeren - Excel-tips

Dit is de 19e wekelijkse Excel-tip op.com. Veel van de Excel-tips bevatten een soort macrotruc. Deze week bied ik voor de Excel-gebruikers die nog nooit een macro hebben geschreven, een inleiding over het opnemen en vervolgens aanpassen van een nuttige Excel-macro.

Voorbeeld adresgegevens

Stel dat u 400 rijen adresgegevens heeft, zoals weergegeven in de bovenste figuur aan de linkerkant. Het naamveld staat in kolom A, het adres in kolom B en de stad in kolom C.

Uw doel is om de gegevens om te zetten naar een enkele kolom zoals weergegeven in de tweede afbeelding.

Dit eenvoudige probleem wordt gebruikt om te illustreren hoe u een eenvoudige macro opneemt, wijzigt en vervolgens uitvoert.

Voor Excel 95-gebruikers: na het opnemen van de macro, plaatst Excel uw macro op een blad met de naam Module1 in uw werkmap. U kunt gewoon op het blad klikken om toegang te krijgen tot de macro.

Hoewel er 400 records in dit werkblad staan, wil ik een klein stukje van de macro opnemen die alleen voor het eerste adres zorgt. De macro gaat ervan uit dat de celwijzer op de voornaam staat. Het zal drie lege rijen invoegen. Het kopieert de cel rechts van de originele cel naar de cel onder de originele cel. Het kopieert de stadscel naar de cel 2 rijen onder de originele cel. Vervolgens moet de celaanwijzer naar beneden worden verplaatst, zodat deze op de volgende naam staat.

De sleutel is om dit proces uit te denken voordat u het opneemt. U wilt niet veel fouten maken bij het opnemen van de macro.

Dus plaats je celaanwijzer in cel A1. Ga naar het menu en selecteer Extra> Macro> Nieuwe macro opnemen. Het dialoogvenster Macro opnemen stelt de naam Macro1 voor. Dit is prima, dus druk op OK.

De Excel-macrorecorder heeft een heel domme standaardinstelling die je absoluut moet wijzigen om deze macro te laten werken. Ga in Excel 95 naar Extra> Macro> Relatieve verwijzingen gebruiken. Klik in Excel 97-2003 op het tweede pictogram op de werkbalk Opname stoppen. Het pictogram ziet eruit als een klein werkblad. Een rode cel in C3 wijst naar een andere rode cel in A3. Het pictogram heet Relatieve referentie. Als dit pictogram 'aan' is, is er wat kleur rondom het pictogram. Het pictogram onthoudt de laatste instelling van de huidige Excel-sessie, dus u moet er mogelijk een paar keer op klikken om erachter te komen welke methode al dan niet is ingeschakeld. Gebruik in Excel 2007 Beeld - Macro's - Relatieve verwijzingen gebruiken.

OK, we zijn klaar om te vertrekken. Volg deze stappen:

  • Druk eenmaal op de pijl-omlaag om naar cel B1 te gaan.
  • Houd de Shift-toets ingedrukt en druk twee keer op de pijl-omlaag om rijen 2, 3 en 4 te selecteren
  • Selecteer in het menu Invoegen en vervolgens Rijen om drie lege rijen in te voegen.
  • Druk op de pijl omhoog en vervolgens op de pijl naar rechts om naar cel B2 te gaan.
  • Druk op Ctrl X om cel B2 te knippen.
  • Druk op de pijl-omlaag, de linkerpijl en vervolgens Ctrl V om in cel A2 te plakken.
  • Druk op pijl-omhoog, pijl-rechts, pijl-rechts, Ctrl X, pijl-links, pijl-links, pijl-omlaag, pijl-omlaag, Ctrl V om C1 naar A3 te verplaatsen.
  • Druk twee keer op de pijl-omlaag zodat de celaanwijzer nu op de volgende naam in rij A5 staat.
  • Klik op het pictogram "Opname stoppen" op de werkbalk om de opname van de macro te stoppen.

Nou, je hebt je eerste macro opgenomen. Laten we kijken. Ga naar Extra> Macro> Macro's. Markeer Macro1 in de lijst en druk op de knop Bewerken. Je zou iets moeten zien dat er zo uitziet.

Sub Macro1() ' ' Macro1 Macro ' Macro recorded 4/18/99 by Reader ' ' ActiveCell.Offset(1, 0).Range("A1:A3").Select Selection.EntireRow.Insert ActiveCell.Offset(-1, 1).Range("A1").Select Selection.Cut ActiveCell.Offset(1, -1).Range("A1").Select ActiveSheet.Paste ActiveCell.Offset(-1, 2).Range("A1").Select Selection.Cut ActiveCell.Offset(2, -2).Range("A1").Select ActiveSheet.Paste ActiveCell.Offset(2, 0).Range("A1").Select End Sub

Hé, als je geen programmeur bent, ziet dat er waarschijnlijk behoorlijk intimiderend uit. Laat het niet zo zijn. Als er iets is dat u niet begrijpt, is er uitstekende hulp. Klik met je cursor ergens in het trefwoord Offset en druk op F1. Als u het VBA-helpbestand hebt geïnstalleerd, ziet u het helponderwerp voor het trefwoord Offset. De help vertelt u de syntaxis van de instructie. Er staat dat het Offset is (RowOffset, ColumnOffset). Nog steeds niet erg duidelijk? Zoek het groen onderstreepte woord "voorbeeld" bovenaan de Help. Met de VBA-voorbeelden van Excel kunt u leren wat er aan de hand is. In het voorbeeld van Offset staat er om de cel twee rijen onder en drie rijen rechts van de huidige cel te activeren, je zou gebruiken:

ActiveCell.Offset(3, 2).Activate

OK, dus dat is een aanwijzing. De offset-functie is een manier om door het Excel-werkblad te navigeren. Gegeven dit stukje informatie, kun je een beetje zien wat de macro doet. De eerste offset (1, 0) is waar we de celpointer naar beneden hebben verplaatst naar A2. De volgende Offset is waar we een rij (-1 rijen) en meer dan 1 kolom omhoog zijn gegaan. U begrijpt misschien niets anders in de macro, maar het is nog steeds nuttig.

Ga terug naar het Excel-werkblad. Zet je celaanwijzer in cel A5. Kies Extra> Macro> Macro's> Macro1> Uitvoeren. De macro wordt uitgevoerd en uw tweede adres wordt opgemaakt.

U zegt misschien dat het selecteren van deze hele lange reeks opdrachten moeilijker is dan alleen met de hand formatteren. OK, doe dan Extra> Macro> Macro's> Opties. Zeg in het snelkoppelingsvenster Ctrl + w is de sneltoets voor deze macro. Klik op OK en sluit het dialoogvenster Macro met Annuleren. Als u nu op Ctrl w drukt, wordt de macro uitgevoerd. U kunt een adres opmaken met een enkele toetsaanslag.

Ben je klaar voor de grote tijd? Hoeveel adressen heb je nog? Ik druk een paar keer op Ctrl wa, dus ik heb 395 over. Ga terug naar je macro. We gaan de volledige macrocode in een lus plaatsen. Voeg een nieuwe regel in met de tekst "Doe tot activecell.value =" "" vóór de eerste regel macrocode. Voeg een regel in met de tekst "Loop" vóór de End Sub-regel. De Do-lus zal alles tussen de Do- en de Loop-regel uitvoeren totdat het in een lege regel terechtkomt. De macro ziet er nu als volgt uit:

Sub Macro1() ' ' Macro1 Macro ' Macro recorded 4/18/99 by Reader ' ' Do Until ActiveCell.Value = "" ActiveCell.Offset(1, 0).Range("A1:A3").Select Selection.EntireRow.Insert ActiveCell.Offset(-1, 1).Range("A1").Select Selection.Cut ActiveCell.Offset(1, -1).Range("A1").Select ActiveSheet.Paste ActiveCell.Offset(-1, 2).Range("A1").Select Selection.Cut ActiveCell.Offset(2, -2).Range("A1").Select ActiveSheet.Paste ActiveCell.Offset(2, 0).Range("A1").Select Loop End Sub

Ga terug naar uw Excel-blad. Zet de celaanwijzer op de volgende naam. Druk op Ctrl w en de macro zal al uw records binnen enkele seconden formatteren.

De auteurs van Excel-boeken zeggen dat je niets nuttigs kunt doen door een macro op te nemen. Niet waar! Voor de persoon die 800 keer zou moeten knippen en plakken, is deze macro erg handig. Het duurde een paar minuten om op te nemen en aan te passen. Ja, professionele programmeurs zullen erop wijzen dat de code vreselijk inefficiënt is. Excel plaatst er een heleboel dingen in die het daar niet hoeft te doen. Ja, als u wist wat u doet, kunt u dezelfde taak uitvoeren met de helft van de regels die in 1,2 seconden lopen in plaats van 3 seconden. NOU EN? 3 seconden is veel sneller dan de 30 minuten die de taak zou hebben geduurd.

Nog enkele tips voor beginnende macro-recorders:

  • De apostrof wordt gebruikt om een ​​opmerking aan te duiden. Alles na de apostrof wordt genegeerd door VBA
  • Dit is objectgeoriënteerd programmeren. De basissyntaxis is object.action. Als een objectgeoriënteerde compiler aan het voetballen was, zou hij "ball.kick" zeggen om de bal te schoppen. Dus "Selection.Cut" zegt om een ​​"edit> cut" uit te voeren op de huidige selectie.
  • In het bovenstaande voorbeeld zijn de bereikmodificatoren relatief ten opzichte van de actieve cel. Als de actieve cel zich in B2 bevindt en u zegt "ActiveCell.Range (" A1: C3 "). Selecteer", dan selecteert u het gebied van 3 rijen bij 3 kolommen dat begint in cel B2. Met andere woorden, u selecteert B2: D4. Zeggen "ActiveCell.Range (" A1 ")" zegt om het 1 x 1 celbereik te selecteren, beginnend met de actieve cel. Dit is ongelooflijk overbodig. Het is hetzelfde als "ActiveCell.Select" zeggen.
  • Sla uw werkmap op voordat u een macro voor de eerste keer uitvoert. Op deze manier kunt u, als het een fout heeft en iets onverwachts doet, sluiten zonder op te slaan en terugkeren naar de opgeslagen versie.

Hopelijk geeft dit eenvoudige voorbeeld je beginnende macro-recorders de moed om een ​​eenvoudige macro op te nemen de volgende keer dat je een terugkerende taak moet uitvoeren in Excel.

Interessante artikelen...