Record Modify Run Excel Macro - Excel Tips

Dette er det 19. ukentlige Excel-tipet på.com. Mange av Excel-tipsene innebærer et slags makrotriks. Denne uken, for Excel-brukere som aldri har skrevet en makro, tilbyr jeg en primer om hvordan du registrerer og deretter tilpasser en nyttig Excel-makro.

Eksempel på adressedata

La oss si at du har 400 rader med adressedata som vist øverst i figuren til venstre. Navnefeltet er i kolonne A, gateadressen i kolonne B, og byen i kolonne C.

Målet ditt er å konvertere dataene til en enkelt kolonne som vist i andre figur.

Dette enkle problemet vil bli brukt til å illustrere hvordan du registrerer, endrer og deretter kjører en enkel makro.

For brukere av Excel 95: Etter å ha spilt inn makroen, vil Excel legge makroen din på et ark som heter Module1 i arbeidsboken din. Du kan bare klikke på arket for å få tilgang til makroen.

Selv om det er 400 poster i dette regnearket, vil jeg ta opp en liten bit av makroen som tar seg av bare den første adressen. Makroen vil anta at cellepekeren er på fornavnet. Det vil sette inn tre blanke rader. Den kopierer cellen til høyre for den opprinnelige cellen til cellen under den opprinnelige cellen. Den vil kopiere bycellen til cellen 2 rader under den opprinnelige cellen. Den skal da flytte cellepekeren ned slik at den er på neste navn.

Nøkkelen er å tenke ut denne prosessen før du registrerer den. Du vil ikke gjøre mange feil når du tar opp makroen.

Så legg cellepekeren din i celle A1. Gå til menyen og velg Verktøy> Makro> Ta opp ny makro. Dialogboksen Record Macro foreslår et navn på Macro1. Dette er greit, så trykk OK.

Excel makroopptakeren har en veldig dum standardinnstilling som du absolutt må endre for å få denne makroen til å fungere. I Excel 95, gå til Verktøy> Makro> Bruk relative referanser I Excel 97-2003, klikk på det andre ikonet på Stopp opptak-verktøylinjen. Ikonet ser ut som et lite regneark. En rød celle i C3 peker mot en annen rød celle i A3. Ikonet kalles relativ referanse. Når dette ikonet er på, er det noe farger rundt ikonet. Ikonet husker den siste innstillingen fra den nåværende Excel-økten, så du må kanskje klikke den et par ganger for å finne ut hvilken metode som er på eller ikke. I Excel 2007 bruker du Visning - Makroer - Bruk relative referanser.

OK, vi er klare til å dra. Følg disse trinnene:

Trykk på pil ned en gang for å flytte til celle B1.
Hold nede skift-tasten og trykk på pil ned to ganger for å velge rad 2, 3 og 4
Velg Sett inn fra menyen, og velg deretter Rader for å sette inn tre blanke rader.
Trykk på pil opp og deretter høyre pil for å flytte til celle B2.
Trykk Ctrl X for å kutte celle B2.
Trykk på nedpilen, venstre pil, og deretter Ctrl V for å lime inn i celle A2.
Trykk pil opp, høyre pil, høyre pil, Ctrl X, venstre pil, venstre pil, pil ned, pil ned, Ctrl V for å flytte C1 til A3.
Trykk på nedpilen to ganger slik at cellepekeren nå er på neste navn i rad A5.
Klikk på "Stopp opptak" -ikonet på verktøylinjen for å stoppe innspillingen av makroen.

Vel, du har spilt inn din første makro. La oss ta en titt. Gå til Verktøy> Makro> Makroer. Fra listen, uthev Macro1 og trykk på Rediger-knappen. Du bør se noe som ser slik ut.

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

Hei, hvis du ikke er programmerer, ser det sannsynligvis ganske skremmende ut. Ikke la det være. Hvis det er noe du ikke forstår, er det utmerket hjelp. Klikk på markøren et sted i nøkkelordet Offset og trykk F1. Forutsatt at du installerte VBA-hjelpefilen, vil du se hjelpemnet for Offset-nøkkelordet. Hjelpen forteller deg syntaksen for utsagnet. Det står at det er Offset (RowOffset, ColumnOffset). Fortsatt ikke veldig tydelig? Se etter det grønne understrekede ordet "eksempel" nær toppen av hjelpen. Excels VBA-eksempler lar deg lære hva som skjer. I eksemplet med Offset står det å aktivere cellen to rader under og tre rader til høyre for den nåværende cellen, du vil bruke:

ActiveCell.Offset(3, 2).Activate

OK, så det er en anelse. Offset-funksjonen er en måte å bevege seg rundt i Excel-regnearket. Gitt denne biten av informasjonen, kan du se hva makroen gjør. Den første forskyvningen (1, 0) er hvor vi flyttet cellepekeren ned til A2. Den neste forskyvningen er der vi flyttet opp en rad (-1 rader) og over en kolonne. Du forstår kanskje ikke noe annet i makroen, men det er fortsatt nyttig.

Gå tilbake til Excel-regnearket. Sett cellepekeren i celle A5. Velg Verktøy> Makro> Makroer> Makro1> Kjør. Makroen kjører, og den andre adressen din er formatert.

Du kan kanskje si at det å velge hele denne lange, store kommandostrengen er vanskeligere enn bare å formatere for hånd. OK, gjør deretter Verktøy> Makro> Makroer> Alternativer. I snarveisboksen, si Ctrl + w er hurtigtasten for denne makroen. Klikk OK, og avvis deretter makrodialogen med Avbryt. Nå, når du trykker på Ctrl w, vil makroen kjøre. Du kan formatere en adresse i et enkelt tastetrykk.

Er du klar for den store tiden? Hvor mange adresser har du igjen? Jeg traff Ctrl wa noen ganger, så jeg har 395 igjen. Gå tilbake til makroen. Vi skal sette hele makrokoden i en løkke. Sett inn en ny linje som sier "Gjør inntil activecell.value =" "" før den første linjen med makrokode. Sett inn en linje som sier "Loop" før End Sub-linjen. Do-sløyfen utfører alt mellom Do og Loop-linjen til den går inn i en tom linje. Makroen ser nå slik ut:

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

Gå tilbake til Excel-arket. Sett cellepekeren på neste navn. Trykk på Ctrl w, og makroen vil formatere alle postene dine på få sekunder.

Forfatterne av Excel-bøker sier at du ikke kan gjøre noe nyttig ved å registrere en makro. Ikke sant! For personen som måtte klippe og lime inn 800 ganger, er denne makroen veldig nyttig. Det tok noen minutter å ta opp og tilpasse. Ja, profesjonelle programmerere vil påpeke at koden er fryktelig ineffektiv. Excel legger en hel haug med ting der som den ikke trenger å legge inn der. Ja, hvis du visste hva du gjør, kan du utføre den samme oppgaven med halve linjene som vil kjøre på 1,2 sekunder i stedet for 3 sekunder. HVA SÅ? 3 sekunder er langt raskere enn de 30 minuttene oppgaven ville ha tatt.

Noen flere tips for begynnende makroopptakere:

Apostrofen som brukes, indikerer en kommentar. Alt etter apostrofen ignoreres av VBA
Dette er objektorientert programmering. Den grunnleggende syntaksen er object.action. Hvis en objektorientert kompilator spilte fotball, ville det si "ball.kick" for å sparke ballen. Så "Selection.Cut" sier å gjøre et "rediger> klipp" på det nåværende valget.
I eksemplet ovenfor er Range modifikatorene relativt til den aktive cellen. Hvis den aktive cellen er i B2 og du sier "ActiveCell.Range (" A1: C3 "). Velg", og deretter velger du kolonnen 3-rad med 3 fra celle B2. Med andre ord velger du B2: D4. Å si "ActiveCell.Range (" A1 ")" sier å velge 1 x 1 celleområde som begynner med den aktive cellen. Dette er utrolig overflødig. Det tilsvarer å si "ActiveCell.Select".
Lagre arbeidsboken din før du kjører en makro for første gang. På denne måten, hvis den har en feil og gjør noe uventet, kan du lukke uten å lagre og gå tilbake til den lagrede versjonen.

Forhåpentligvis vil dette enkle eksemplet gi deg nybegynnere makroopptakere mot til å spille inn en enkel makro neste gang du har en tilbakevendende oppgave å utføre i Excel.