Ta opp en makro i Excel - TechTV-artikler

Dette er mine shownotater fra utseendet mitt på Episode 33 av Call for Help på TechTV Canada.

VBA Macro Recorder

Hvert eksemplar av Excel som er solgt siden 1995 inkluderer den utrolig kraftige Visual Basic for Applications (VBA) som gjemmer seg bak cellene. Hvis du har Excel, har du VBA.

VBA lar deg automatisere alt du kan gjøre i Excel, pluss å gjøre flere ting som generelt ikke er mulig i Excel.

Heldigvis for oss inkluderte Microsoft en makroopptaker med Excel. Dette lar deg skrive VBA-kode uten å være programmerer. Dessverre er Macro Recorder feil. Det vil ikke produsere kode som vil fungere hver gang. Makroopptakeren vil komme deg nær, men i mange tilfeller må du fikse koden litt for at den skal fungere pålitelig. I andre tilfeller må du vite når du skal bruke relativ opptak for å få makroen til å fungere.

Tracy Syrstad og jeg skrev VBA og makroer Microsoft Excel 2016 for å hjelpe deg med å forstå begrensningene med innspilt kode og lære deg hvordan du kan fikse innspilt kode til noe som vil fungere hver gang.

På showet demonstrerte jeg prosessen med å ta opp en makro som vil fungere perfekt hvis du vet hvordan du bruker Relative Reference-knappen. Noen hadde gitt meg et Excel-regneark med hundrevis av navn og adresser. De ønsket postetiketter. Hvis du noen gang har brukt funksjonen for fletting av Microsoft Word, vet du at du trenger hvert felt i en ny kolonne i regnearket. I stedet hadde dette spesielle regnearket dataene som gikk ned i kolonne A.

Prosessen med å fikse en enkelt etikett er ganske kjedelig.

  • Start med cellepekeren på navnet i kolonne A.
  • Trykk på nedpilen for å flytte til adressen
  • Ctrl + x for å kutte
  • Pil opp, høyre pil for å flytte til kolonne B ved siden av navnet
  • Ctrl + v for å lime inn
  • Pil ned to ganger, venstre pil en gang for å flytte til byen
  • Ctrl + x for å kutte
  • Pil opp to ganger, høyre pil tre ganger
  • Ctrl + v for å lime inn
  • Venstre pil to ganger, ned pil en gang for å flytte til den nå tomme raden.
  • Hold nede Skift-tasten mens du treffer nedoverpilen to ganger
  • Alt + edr for å slette de tomme radene
  • Pil opp og pil ned for å velge bare navnet igjen.

Her er animasjonen som viser disse trinnene:

Sette opp Excel for å tillate makroer

Selv om hver kopi av Excel inneholder VBA, slår Microsoft som standard av muligheten til å kjøre makroer. Du må gjøre en engangsjustering for å la makroer kjøre. Åpne Excel. Fra menyen velger du Verktøy> Makro> Sikkerhet. Hvis makrosikkerhetsnivået for øyeblikket er satt til høyt, endrer du det til Medium.

Forbereder deg på å ta opp makroen

Tenk på trinnene som er nødvendige for å oppnå oppgaven. Du vil være sikker på at du starter med cellepekeren på et navn og slutter med det på neste navn. Dette lar deg kjøre makroen flere ganger. Når du har trinnene klare til å gå, slår du på makroopptakeren. Fra menyen velger du Verktøy> Makro> Ta opp ny makro.

Hvis du vil kjøre denne makroen i mange dager, bør du gi makroen et nyttig navn. Hvis det er en engangsmakro å automatisere en engangsoppgave, er det sannsynligvis greit å legge makronavnet som Makro1. Det viktige feltet her er hurtigtastfeltet. Hvis du lager en makro for engangsbruk, tilordner du makroen til en hurtigtast som Ctrl + a - det er ganske enkelt å trykke Ctrl + a gjentatte ganger siden tastene er nær hverandre. Hvis du skal lage en makro du vil bruke hver dag, vil du tilordne makroen til en tast som ikke allerede er en viktig hurtigtastkombinasjon. Ctrl + j eller Ctrl + k er gode valg.

Engangsmakroer bør lagres i ThisWorkbook. Hvis du trenger å bruke makroen gjentatte ganger på mange forskjellige arbeidsbøker, kan du lagre makroen i Personal Macro Workbook.

Du får nå den minste verktøylinjen i hele Excel; Stopp opptak-verktøylinjen. Den har bare to ikoner og ser slik ut:

Hvis denne verktøylinjen kommer i veien, ikke trykk på X for å lukke den. Ta i stedet tak i den blå linjen og dra verktøylinjen til et nytt sted. Handlingen med å flytte verktøylinjen er ikke registrert i makroen. Hvis du ved et uhell lukker verktøylinjen, kan du få den tilbake med Vis> Verktøylinjer> Stopp opptak. Denne handlingen blir imidlertid spilt inn.

Den første knappen på verktøylinjen er "Stopp opptak" -knappen. Dette er selvforklarende. Når du er ferdig med å registrere makroen, trykker du på stoppopptak-verktøylinjen.

Den viktigste (og sjelden forstås) knappen er "Relativ referanse" -knappen.

I vårt nåværende eksempel må du trykke på relativ referanse-knappen før du begynner. Hvis du tar opp en makro med relative referanser slått på, registrerer Excel trinn som dette:

  • Flytt en celle nedover
  • Klipp den nåværende cellen
  • Flytt en celle opp
  • Flytt en celle til høyre
  • Lim inn
  • etc.

Hvis du i stedet vil registrere makroen uten relative referanser, vil makroen registrere disse trinnene:

  • Gå til celle A4
  • Klipp celle A4
  • Gå til celle A3
  • Gå til celle B3
  • Lim inn i celle B3
  • etc.

Dette ville være veldig galt - vi trenger makroen til å jobbe på celler som er 1 og 2 celler fra startpunktet til makroen. Når du kjører makroen om og om igjen, vil startpunktet være rad 4, rad 5, rad 6 osv. Hvis du tar opp makroen uten at relative referanser er slått på, vil makroen alltid kjøre på rad 3 som utgangspunkt.

Følg disse trinnene:

  • Velg relativ referanseknapp på verktøylinjen Stopp opptak
  • Cellepekeren skal allerede være på et navn i kolonne A.
  • Trykk på nedpilen for å flytte til adressen
  • Ctrl + x for å kutte
  • Pil opp, høyre pil for å flytte til kolonne B ved siden av navnet
  • Ctrl + v for å lime inn
  • Pil ned to ganger, venstre pil en gang for å flytte til byen
  • Ctrl + x for å kutte
  • Pil opp to ganger, høyre pil tre ganger
  • Ctrl + v for å lime inn
  • Venstre pil to ganger, ned pil en gang for å flytte til den nå tomme raden.
  • Hold nede Skift-tasten mens du treffer nedoverpilen to ganger
  • Alt + edr for å slette de tomme radene
  • Pil opp og pil ned for å velge neste navn i listen.
  • Trykk på verktøylinjen Stopp opptak
  • Lagre arbeidsboken
  • Test makroen ved å trykke på hurtigtasten som er tildelt ovenfor. Det bør perfekt fikse neste navn i listen

Når du ser at makroen fungerer som ønsket, kan du holde nede Ctrl + a for raskt å fikse noen få navn per sekund. Hele listen med 500 navn kan fikses på et minutt eller to.

Bonustips - Ikke på showet

Du kan enkelt pakke inn makroen i en enkel sløyfe for å få den til å fikse alle 500 navnene uten at du trenger å trykke Ctrl + flere hundre ganger.

Trykk på Alt + F11-tasten for å åpne makroeditoren.

Hvis du ikke ser Project - VBAProject-ruten, trykker du på Ctrl + r.

Høyreklikk Module1 og velg Vis kode. Du vil se kode som ser slik ut:

Nå trenger du ikke å forstå hva denne koden gjør, men du vet at vi vil kjøre alt i den makroen en gang for hvert navn vi har. Hvis du vet at det er 462 navn, kan du legge til 2 linjer for å kjøre koden 462 ganger. Øverst i makroen setter du inn en ny linje med ordene " For i = 1 to 462". Sett inn en linje som sier " Next i" nederst i makroen . Dette forteller VBA å kjøre koden 462 ganger.

Konklusjon

Etter denne enkle makroen viste jeg et eksempel på showet av en veldig kompleks makro. Denne makroen opprettet pivottabeller og diagrammer for 46 avdelinger i et selskap. Denne rapporten tok tidligere 40 timer hver måned. VBA-makroen kjører nå og oppretter alle 46 rapportene på mindre enn 2 minutter.

Boken VBA og makroer Microsoft Excel 2016 vil føre deg opp i læringskurven, slik at du kan komme fra å ta opp enkle makroer som denne til å kjøre svært komplekse rapporter som kan spare deg for hundrevis av timer per år. Selvfølgelig, hvis du ikke vil lære VBA, ansett en Excel-konsulent for å lage en rapport for deg.

Interessante artikler...